เมื่อ 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

เกิดอะไรขึ้น ทำไมแม้แต่ UPDATE ที่ง่ายที่สุดของบันทึกเดียว เวลาดำเนินการลดลง 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 FULL ก็ไม่สามารถช่วยเราได้

“ยุบ” โต๊ะ

แต่เรารู้แน่ว่าแบบสอบถามนั้นไม่ต้องการตารางของเรา ดังนั้นเราจะยังคงพยายามคืนประสิทธิภาพของระบบกลับไปสู่ขีดจำกัดที่เพียงพอโดยกำจัดทุกสิ่งที่ไม่จำเป็นออกจากตาราง - อย่างน้อยก็ "ด้วยตนเอง" เนื่องจาก VACUUM ยอมแพ้

เพื่อให้ชัดเจนยิ่งขึ้น เรามาดูตัวอย่างกรณีของตารางบัฟเฟอร์กันดีกว่า นั่นคือมีโฟลว์ INSERT/DELETE จำนวนมาก และบางครั้งตารางก็ว่างเปล่าโดยสิ้นเชิง แต่ถ้าไม่ว่างก็ต้องทำ บันทึกเนื้อหาปัจจุบัน.

#0: การประเมินสถานการณ์

เป็นที่ชัดเจนว่าคุณสามารถลองทำอะไรบางอย่างกับตารางได้แม้หลังจากการดำเนินการแต่ละครั้ง แต่ก็ไม่สมเหตุสมผลนัก - ค่าใช้จ่ายในการบำรุงรักษาจะมากกว่าปริมาณงานของการสืบค้นเป้าหมายอย่างชัดเจน

มากำหนดเกณฑ์กัน - "ถึงเวลาต้องดำเนินการ" หาก:

• VACUUM เปิดตัวไปนานแล้ว
  เราคาดว่าจะมีภาระหนักดังนั้นปล่อยให้เป็นไป 60 วินาที ตั้งแต่ [อัตโนมัติ]VACUUM ครั้งล่าสุด
• ขนาดตารางจริงมีขนาดใหญ่กว่าเป้าหมาย
  ลองนิยามมันเป็นสองเท่าของจำนวนหน้า (บล็อก 8KB) เทียบกับขนาดขั้นต่ำ - 1 blk สำหรับฮีป + 1 blk สำหรับแต่ละดัชนี - สำหรับโต๊ะที่อาจว่างเปล่า หากเราคาดหวังว่าข้อมูลจำนวนหนึ่งจะยังคงอยู่ในบัฟเฟอร์ "ตามปกติ" เสมอ ก็สมเหตุสมผลที่จะปรับแต่งสูตรนี้

คำขอตรวจสอบ

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;

หากไม่มีบันทึกเหลืออยู่เลย เราสามารถประหยัดการประมวลผลได้มากเพียงแค่เรียกใช้ ตัด:

มันทำหน้าที่เหมือนกับคำสั่ง DELETE ที่ไม่มีเงื่อนไขสำหรับแต่ละตาราง แต่จะเร็วกว่ามากเนื่องจากไม่ได้สแกนตารางจริงๆ นอกจากนี้ ยังช่วยเพิ่มพื้นที่ว่างในดิสก์ทันที ดังนั้นจึงไม่จำเป็นต้องดำเนินการ VACUUM ในภายหลัง

ไม่ว่าคุณจะต้องรีเซ็ตตัวนับลำดับตาราง (RESTART IDENTITY) หรือไม่นั้นก็ขึ้นอยู่กับคุณตัดสินใจ

#3: ทุกคน - ผลัดกัน!

เนื่องจากเราทำงานในสภาพแวดล้อมที่มีการแข่งขันสูง ในขณะที่เรากำลังตรวจสอบว่าไม่มีรายการในตาราง ก็อาจมีบางคนเขียนอะไรบางอย่างไว้ที่นั่นแล้ว เราไม่ควรสูญเสียข้อมูลนี้ แล้วไงล่ะ? ถูกต้อง เราต้องแน่ใจว่าไม่มีใครจดบันทึกได้อย่างแน่นอน

ในการทำเช่นนี้เราจำเป็นต้องเปิดใช้งาน ซีเรียลไลซ์ได้- การแยกธุรกรรมของเรา (ใช่ ที่นี่เราเริ่มธุรกรรม) และล็อคตาราง "แน่น":

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

การบล็อกระดับนี้ถูกกำหนดโดยการดำเนินการที่เราต้องการดำเนินการ

#4: ความขัดแย้งทางผลประโยชน์

เรามาที่นี่และต้องการ "ล็อค" ป้าย - จะเกิดอะไรขึ้นถ้ามีคนใช้งานอยู่ในขณะนั้น เช่น กำลังอ่านข้อความอยู่? เราจะ “หยุด” เพื่อรอบล็อกนี้ออก และคนอื่นๆ ที่อยากอ่านก็จะวิ่งเข้ามาหาเรา...

เพื่อป้องกันไม่ให้สิ่งนี้เกิดขึ้น เราจะ "เสียสละตัวเอง" - หากเราไม่สามารถรับล็อคได้ภายในเวลาที่กำหนด (สั้นพอสมควร) เราจะได้รับข้อยกเว้นจากฐาน แต่อย่างน้อย เราก็จะไม่เข้าไปยุ่งมากเกินไปกับ คนอื่น.

เมื่อต้องการทำเช่นนี้ ให้ตั้งค่าตัวแปรเซสชัน lock_timeout (สำหรับเวอร์ชัน 9.3+) หรือ/และ คำสั่ง_หมดเวลา. สิ่งสำคัญที่ต้องจำคือค่าstatement_timeoutจะใช้เฉพาะกับคำสั่งถัดไปเท่านั้น นั่นคือเช่นนี้ในการติดกาว - จะไม่ทำงาน:

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

เพื่อไม่ให้ต้องจัดการกับการกู้คืนค่า "เก่า" ของตัวแปรในภายหลัง เราใช้แบบฟอร์ม ตั้งค่าท้องถิ่นซึ่งจำกัดขอบเขตของการตั้งค่าไว้ที่ธุรกรรมปัจจุบัน

เราจำได้ว่า state_timeout ใช้กับคำขอที่ตามมาทั้งหมดเพื่อให้ธุรกรรมไม่สามารถขยายเป็นค่าที่ยอมรับไม่ได้หากมีข้อมูลจำนวนมากในตาราง

#5: คัดลอกข้อมูล

หากตารางไม่ว่างเปล่าทั้งหมด ข้อมูลจะต้องถูกบันทึกใหม่โดยใช้ตารางชั่วคราวเสริม:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

ลายเซ็น เมื่อตกลงยอมรับ หมายความว่าในขณะที่ธุรกรรมสิ้นสุดลง ตารางชั่วคราวจะหยุดอยู่ และไม่จำเป็นต้องลบออกด้วยตนเองในบริบทการเชื่อมต่อ

เนื่องจากเราถือว่ามีข้อมูล "สด" ไม่มากนัก การดำเนินการนี้จึงควรเกิดขึ้นอย่างรวดเร็ว

นั่นคือทั้งหมด! อย่าลืมหลังจากทำรายการเสร็จเรียบร้อย เรียกใช้การวิเคราะห์ เพื่อทำให้สถิติตารางเป็นมาตรฐานหากจำเป็น

กำลังรวบรวมสคริปท์สุดท้าย

เราใช้ "pseudo-python" นี้:

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

เป็นไปได้ไหมที่จะไม่คัดลอกข้อมูลเป็นครั้งที่สอง?โดยหลักการแล้ว เป็นไปได้ถ้า oid ของตารางไม่ได้เชื่อมโยงกับกิจกรรมอื่นใดจากฝั่ง BL หรือ FK จากฝั่ง DB:

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 เท่าและการอัปเดตทั้งหมดทำงานอย่างรวดเร็วอีกครั้ง

ที่มา: will.com