عندما يفشل VACUUM، نقوم بتنظيف الطاولة يدويًا

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

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

تتكشف أشعل النار

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

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

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

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.524 مللي ثانية إلى 3.808 مللي ثانية؟ وتقييمنا يتزايد ببطء أكثر.

كل هذا خطأ 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 يستسلم.

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

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

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

فلنقم بصياغة المعايير - "حان وقت العمل" إذا:

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

طلب التحقق

SELECT
  relpages
, ((
    SELECT
      count(*)
    FROM
      pg_index
    WHERE
      indrelid = cl.oid
  ) + 1) << 13 size_norm -- тут правильнее делать * current_setting('block_size')::bigint, но кто меняет размер блока?..
, 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 -- tbl
LIMIT 1;

relpages | size_norm | size    | vaclag
-------------------------------------------
       0 |     24576 | 1105920 | 3392.484835

رقم 1: لا يزال فراغًا

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

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

رقم 2: هل يوجد أحد في المنزل؟

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

TABLE tbl LIMIT 1;

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

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

إن ما إذا كنت بحاجة إلى إعادة تعيين عداد تسلسل الجدول (إعادة تشغيل الهوية) هو أمر متروك لك لتقرره.

رقم 3: الجميع - يتناوبون!

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

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

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

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

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

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

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

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

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

لكي لا نضطر إلى التعامل مع استعادة القيمة "القديمة" للمتغير لاحقًا، نستخدم النموذج تعيين محلي، مما يحد من نطاق الإعداد على المعاملة الحالية.

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

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

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

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

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

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

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

تجميع النص النهائي

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

# собираем статистику с таблицы
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;

هل من الممكن عدم نسخ البيانات مرة ثانية؟من حيث المبدأ، من الممكن أن يكون معرف الجدول نفسه غير مرتبط بأي أنشطة أخرى من جانب BL أو FK من جانب قاعدة البيانات:

CREATE TABLE _swap_%table(LIKE %table INCLUDING ALL);
INSERT INTO _swap_%table TABLE %table;
DROP TABLE %table;
ALTER TABLE _swap_%table RENAME TO %table;

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

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 مرة وجميع التحديثات تعمل بسرعة مرة أخرى.

المصدر: www.habr.com