ديسيبل: عندما يمر فراغ - نقوم بتنظيف الجدول يدويًا

يمكن لـ VACUUM "تنظيف" من جدول في PostgreSQL فقط مما لا يمكن لأحد رؤيته - أي أنه لا يوجد استعلام نشط واحد بدأ قبل تغيير هذه السجلات.

ولكن إذا كان هناك مثل هذا النوع غير السار (حمل OLAP طويل الأجل على قاعدة OLTP)؟ كيف يمكن تنظيف طاولة متغيرة بنشاط وتحيط بها استعلامات طويلة وليس خطوة على أشعل النار؟

نشرنا أشعل النار

أولاً ، نحدد ماهية المشكلة وكيف يمكن أن تنشأ المشكلة التي نريد حلها.

عادة ما يحدث هذا الموقف على طاولة صغيرة نسبيًا ، ولكن هناك الكثير من التغييرات . عادةً ما تكون هذه إما عدادات / تجميعات / تصنيفات مختلفة ، وغالبًا ما يتم تنفيذ UPDATE ، أو قائمة انتظار عازلة لمعالجة نوع من الأحداث الجارية باستمرار ، سجلات حولها دائمًا INSERT / DELETE.

دعنا نحاول إعادة إنتاج الخيار مع التصنيفات:

CREATE TABLE tbl(k text PRIMARY KEY, v integer); CREATE INDEX ON tbl(v DESC); --       INSERT INTO tbl SELECT chr(ascii('a'::text) + i) k , 0 v FROM generate_series(0, 25) i; 

وبالتوازي مع ذلك ، في اتصال مختلف ، يبدأ استعلام طويل المدى ، يجمع بعض الإحصاءات المعقدة ، لكن لا يؤثر على جدولنا :

 SELECT pg_sleep(10000); 

الآن نقوم بتحديث قيمة أحد العدادات عدة مرات. من أجل نقاء التجربة ، سنفعل ذلك في معاملات منفصلة باستخدام dblink ، لأن هذا سيحدث في الواقع:

 DO $$ DECLARE i integer; tsb timestamp; tse timestamp; d double precision; BEGIN PERFORM dblink_connect('dbname=' || current_database() || ' port=' || current_setting('port')); FOR i IN 1..10000 LOOP tsb = clock_timestamp(); PERFORM dblink($e$UPDATE tbl SET v = v + 1 WHERE k = 'a';$e$); tse = clock_timestamp(); IF i % 1000 = 0 THEN d = (extract('epoch' from tse) - extract('epoch' from tsb)) * 1000; RAISE NOTICE 'i = %, exectime = %', lpad(i::text, 5), lpad(d::text, 5); END IF; END LOOP; PERFORM dblink_disconnect(); END; $$ LANGUAGE plpgsql; 

 NOTICE: i = 1000, exectime = 0.524 NOTICE: i = 2000, exectime = 0.739 NOTICE: i = 3000, exectime = 1.188 NOTICE: i = 4000, exectime = 2.508 NOTICE: i = 5000, exectime = 1.791 NOTICE: i = 6000, exectime = 2.658 NOTICE: i = 7000, exectime = 2.318 NOTICE: i = 8000, exectime = 2.572 NOTICE: i = 9000, exectime = 2.929 NOTICE: i = 10000, exectime = 3.808 

ماذا حدث؟ لماذا ، حتى بالنسبة لأبسط تحديث لسجل واحد ، تدهور وقت التشغيل بمقدار 7 مرات - من 0.524ms إلى 3.808ms؟ ويجري بناء تصنيفنا ببطء أكثر وأكثر ببطء.

MVCC هو المسؤول

الأمر كله يتعلق بآلية MVCC ، والتي تفرض على الطلب النظر في جميع الإصدارات السابقة من السجل. لذلك دعونا نقوم بتنظيف طاولتنا من الإصدارات "الميتة":

 VACUUM VERBOSE tbl; 

 INFO: vacuuming "public.tbl" INFO: "tbl": found 0 removable, 10026 nonremovable row versions in 45 out of 45 pages DETAIL: 10000 dead row versions cannot be removed yet, oldest xmin: 597439602 

أوه ، لا يوجد شيء للتنظيف! هناك استفسار مواز يزعجنا - بعد كل شيء ، قد يرغب في يوم من الأيام في الإشارة إلى هذه الإصدارات (ماذا لو؟) ، وينبغي أن تكون متاحة له. وحتى الفراغ الكامل لن يساعدنا.

"تحامل" الجدول

لكننا نعرف بالتأكيد أن جدولنا لا يحتاج إلى استعلامنا. لذلك ، دعونا نحاول إعادة أداء النظام إلى إطار عمل ملائم ، بعد إلقاء كل شيء لا لزوم له من الجدول - على الأقل "يدويًا" ، حيث يمر VACUUM.

لجعله أكثر وضوحا ، دعونا ننظر في مثال لجدول المخزن المؤقت. بمعنى ، هناك دفق INSERT / DELETE كبير وأحيانًا يكون الجدول فارغًا تمامًا. ولكن إذا لم تكن فارغة هناك ، يجب علينا حفظ محتوياته الحالية .

# 0: تقييم الوضع

من الواضح أنه يمكنك محاولة القيام بشيء ما مع الجدول حتى بعد كل عملية ، لكن هذا ليس منطقيًا كثيرًا - سيكون حمل الصيانة أكبر بشكل واضح من الإنتاجية للطلبات المستهدفة.

نضع المعايير - "حان وقت العمل" ، إذا:

• تم تشغيل VACUUM لفترة طويلة
  نتوقع حمولة كبيرة ، لذلك يجب أن يكون 60 ثانية من آخر فراغ [تلقائي].
• حجم الجدول الفعلي أكبر من الهدف
  نحن نعرّفها على أنها عدد الصفحات المضاعفة (كتل 8 كيلوبايت) نسبة إلى الحد الأدنى للحجم - 1 blk لكل كومة + 1 blk لكل من المؤشرات - لجدول يحتمل أن يكون فارغًا. إذا كنا نتوقع أن يبقى قدر معين من البيانات دائمًا في المخزن المؤقت "بشكل طبيعي" ، فمن المعقول تشديد هذه الصيغة.

# 1: فراغ على أي حال

لا يمكننا أن نعرف مقدمًا ما إذا كان الاستعلام الموازي يعيقنا حقًا - بالضبط عدد السجلات التي "عفا عليها الزمن" منذ إنشائها. لذلك ، عندما نقرر معالجة الجدول بطريقة أو بأخرى ، على أي حال ، يجب عليك أولاً تشغيل VACUUM عليه - على عكس VACUUM FULL ، لا يتداخل مع العمليات الموازية مع بيانات القراءة والكتابة.

في الوقت نفسه ، يمكنه تنظيف معظم ما نود إزالته فورًا. نعم ، والطلبات اللاحقة لهذا الجدول ستذهب إلينا في "ذاكرة تخزين مؤقت ساخنة" ، مما يقلل من مدتها - وبالتالي ، فإن إجمالي الوقت لحظر الآخرين مع معاملة عرضنا.

# 2: هل أي شخص في المنزل؟

دعنا نتحقق - هل هناك أي شيء على الإطلاق في الجدول:

 TABLE tbl LIMIT 1; 

إذا لم يتبق سجل واحد ، فيمكننا توفير الكثير عند المعالجة - فقط عن طريق إجراء TRUNCATE :

إنه يعمل بنفس طريقة الأمر DELETE غير المشروط لكل جدول ، ولكنه أسرع بكثير ، لأنه لا يقوم بالفعل بمسح الجداول. علاوة على ذلك ، فإنه يحرر مساحة القرص على الفور ، لذلك ليست هناك حاجة لإجراء عملية فراغ بعد ذلك.

ما إذا كنت بحاجة إلى إعادة تعيين عداد تسلسل الجدول (RESTART IDENTITY) في نفس الوقت - قرر بنفسك.

# 3: كل شيء - بدوره!

نظرًا لأننا نعمل في ظل ظروف تنافسية عالية ، بينما نتحقق هنا من عدم وجود إدخالات في الجدول ، يمكن لشخص ما كتابة شيء هناك بالفعل. يجب ألا نفقد هذه المعلومات ، فماذا في ذلك؟ هذا صحيح ، يجب أن يتم ذلك حتى لا يتمكن أحد من التسجيل بشكل مؤكد.

للقيام بذلك ، نحتاج إلى تمكين عزل SERIALIZABLE لمعاملاتنا (نعم ، هنا نبدأ المعاملة) ونغلق الطاولة "بإحكام":

 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; LOCK TABLE tbl IN ACCESS EXCLUSIVE MODE; 

يرجع هذا المستوى من الحظر إلى العمليات التي نريد تنفيذها عليها.

# 4: تضارب المصالح

لقد جئنا إلى هنا ونريد "قفل" الجهاز اللوحي - وإذا كان شخص ما نشطًا فيه في تلك اللحظة ، على سبيل المثال ، أقرأ منه؟ سنقوم "بالتعليق" تحسباً لإصدار هذه المجموعة ، بينما سيتم دفن الآخرين الذين يرغبون في القراءة فينا ...

لمنع حدوث ذلك ، "التضحية بأنفسنا" - إذا لم نتمكن من الحصول على القفل لفترة معينة (صغيرة مسموح بها) ، فسنحصل على استثناء من قاعدة البيانات ، لكننا على الأقل لن نزعج الآخرين.

للقيام بذلك ، قم بتعيين متغير lock_timeout للجلسة (للإصدارات 9.3+) أو / و statement_timeout . الشيء الرئيسي الذي يجب تذكره هو أن قيمة statement_timeout لا تنطبق إلا من العبارة التالية. هذا ، مثل هذا في الإلتصاق ، لن ينجح :

 SET statement_timeout = ...;LOCK TABLE ...; 

من أجل عدم التعامل مع الاستعادة اللاحقة للقيمة "القديمة" للمتغير ، نستخدم نموذج SET LOCAL ، الذي يحد من نطاق الإعدادات للمعاملة الحالية.

تذكر أن statement_timeout ينطبق على جميع الطلبات اللاحقة بحيث لا يمكن أن تمتد المعاملة إلى قيم غير مقبولة إذا كان هناك الكثير من البيانات في الجدول.

# 5: نسخ البيانات

إذا كان الجدول غير فارغ تمامًا ، فسوف يتعين إعادة حفظ البيانات من خلال الملصق المؤقت الإضافي:

 CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl; 

يعني توقيع ON COMMIT DROP أنه في الوقت الذي تنتهي فيه المعاملة ، سوف يتوقف الجدول المؤقت عن الوجود ، ولن تحتاج إلى حذفه يدويًا في سياق الاتصال.

بما أننا نفترض أنه لا يوجد الكثير من البيانات "المباشرة" ، يجب أن تتم هذه العملية بسرعة كافية.

حسنا ، هذا كل شيء! تذكر أن تقوم بتشغيل ANALYZE بعد اكتمال المعاملة لتطبيع إحصائيات الجدول ، إذا لزم الأمر.

نحن نجمع البرنامج النصي النهائي

نستخدم مثل هذا "الثعبان الزائف":

 #     stat <- SELECT relpages , (( SELECT count(*) FROM pg_index WHERE indrelid = cl.oid ) + 1) << 13 size_norm , pg_total_relation_size(oid) size , coalesce(extract('epoch' from (now() - greatest( pg_stat_get_last_vacuum_time(oid) , pg_stat_get_last_autovacuum_time(oid) ))), 1 << 30) vaclag FROM pg_class cl WHERE oid = $1::regclass -- table_name LIMIT 1; #      VACUUM   if stat.size > 2 * stat.size_norm and stat.vaclag is None or stat.vaclag > 60: -> VACUUM %table; try: -> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; #         1s -> SET LOCAL statement_timeout = '1s'; SET LOCAL lock_timeout = '1s'; -> LOCK TABLE %table IN ACCESS EXCLUSIVE MODE; #          row <- TABLE %table LIMIT 1; #       ""  -   ,    - ""      if row is None: -> TRUNCATE TABLE %table RESTART IDENTITY; else: #      - -> CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE %table; #      -> TRUNCATE TABLE %table; #         -> INSERT INTO %table TABLE _tmp_swap; -> COMMIT; except Exception as e: #    ,     "" -   if not isinstance(e, InterfaceError): -> ROLLBACK; 

لنقم بتشغيل البرنامج النصي في الجدول المصدر والتحقق من المقاييس:

 VACUUM tbl; BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET LOCAL statement_timeout = '1s'; SET LOCAL lock_timeout = '1s'; LOCK TABLE tbl IN ACCESS EXCLUSIVE MODE; CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl; TRUNCATE TABLE tbl; INSERT INTO tbl TABLE _tmp_swap; COMMIT; 

 relpages | size_norm | size | vaclag ------------------------------------------- 0 | 24576 | 49152 | 32.705771 

كل شيء يعمل بها! تقلص الجدول بنسبة 50 مرة ، ويتم تشغيل جميع التحديثات بسرعة مرة أخرى.