როდესაც 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 FULL არ დაგვეხმარება.

მაგიდის "ნგრევა".

მაგრამ ჩვენ ზუსტად ვიცით, რომ ამ შეკითხვას ჩვენი ცხრილი არ სჭირდება. ამიტომ, ჩვენ კვლავ ვეცდებით სისტემის მუშაობის ადეკვატურ ზღვრებზე დაბრუნებას, ცხრილიდან ყველაფრის არასაჭირო ამოღებით - სულ მცირე „ხელით“, რადგან VACUUM დათმობს.

უფრო გასაგებად, მოდით შევხედოთ ბუფერული ცხრილის შემთხვევის მაგალითს. ანუ არის INSERT/DELETE-ის დიდი ნაკადი და ზოგჯერ ცხრილი სრულიად ცარიელია. მაგრამ თუ ცარიელი არ არის, ჩვენ უნდა შეინახეთ მისი მიმდინარე შინაარსი.

#0: სიტუაციის შეფასება

გასაგებია, რომ შეგიძლიათ სცადოთ რაიმე გააკეთოთ მაგიდასთან ყოველი ოპერაციის შემდეგაც, მაგრამ ამას დიდი აზრი არ აქვს - ტექნიკური ხარჯები აშკარად უფრო მეტი იქნება, ვიდრე სამიზნე მოთხოვნების გამტარუნარიანობა.

მოდით ჩამოვაყალიბოთ კრიტერიუმები - "მოქმედების დროა", თუ:

• VACUUM ამოქმედდა საკმაოდ დიდი ხნის წინ
  ჩვენ ველით მძიმე დატვირთვას, ასე რომ იყოს 60 წამი ბოლო [ავტო]ვაკუუმიდან.
• ფიზიკური მაგიდის ზომა სამიზნეზე დიდია
  მოდით განვსაზღვროთ როგორც ორჯერ მეტი გვერდების რაოდენობა (8KB ბლოკები) მინიმალურ ზომასთან შედარებით - 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;

თუ არც ერთი ჩანაწერი არ დარჩა, მაშინ ჩვენ შეგვიძლია დაზოგოთ ბევრი რამ დამუშავებაზე, უბრალოდ გაკეთებით მორთვა:

ის მოქმედებს ისევე, როგორც უპირობო DELETE ბრძანება თითოეული ცხრილისთვის, მაგრამ ბევრად უფრო სწრაფია, რადგან ის რეალურად არ ასკანირებს ცხრილებს. უფრო მეტიც, ის დაუყოვნებლივ ათავისუფლებს დისკზე ადგილს, ამიტომ არ არის საჭირო შემდეგ ვაკუუმის ოპერაციის შესრულება.

გჭირდებათ თუ არა ცხრილის თანმიმდევრობის მრიცხველის გადატვირთვა (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 ...;

იმისათვის, რომ მოგვიანებით არ გვქონდეს საქმე ცვლადის „ძველი“ მნიშვნელობის აღდგენასთან, ჩვენ ვიყენებთ ფორმას ადგილობრივის დაყენება, რომელიც ზღუდავს პარამეტრის ფარგლებს მიმდინარე ტრანზაქციამდე.

ჩვენ გვახსოვს, რომ statement_timeout ვრცელდება ყველა შემდგომ მოთხოვნაზე, ისე რომ ტრანზაქცია არ შეიძლება გაიზარდოს მიუღებელ მნიშვნელობებამდე, თუ ცხრილში ბევრი მონაცემია.

#5: დააკოპირეთ მონაცემები

თუ ცხრილი არ არის მთლიანად ცარიელი, მონაცემები ხელახლა უნდა შეინახოს დამხმარე დროებითი ცხრილის გამოყენებით:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

ხელმოწერა ON COMIT 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;

შესაძლებელია თუ არა მონაცემების მეორედ დაკოპირება?პრინციპში, შესაძლებელია, თუ თავად ცხრილის ოიდი არ არის მიბმული სხვა აქტივობებთან 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-ჯერ შემცირდა და ყველა განახლება ისევ სწრაფად გადის.

წყარო: www.habr.com