ProHoster > Блог > басқарма > VACUUM сәтсіз болғанда, біз үстелді қолмен тазалаймыз

VACUUM сәтсіз болғанда, біз үстелді қолмен тазалаймыз

VACUUM PostgreSQL-дегі кестеден тек нені «тазалай» алады ешкім көре алмайды - яғни бұл жазбалар өзгертілмей тұрып басталған бірде-бір белсенді сұрау жоқ.

Бірақ мұндай жағымсыз түрі (OLTP дерекқорында ұзақ мерзімді OLAP жүктемесі) әлі де бар болса ше? Қалай белсенді өзгеретін үстелді тазалаңыз ұзақ сұраулармен қоршалған және тырмаға баспайсыз ба?

VACUUM сәтсіз болғанда, біз үстелді қолмен тазалаймыз

Тырманы төсеу

Алдымен, біз шешкіміз келетін мәселенің не екенін және оның қалай туындауы мүмкін екенін анықтайық.

Әдетте мұндай жағдай орын алады салыстырмалы түрде кішкентай үстелде, бірақ ол қай жерде кездеседі көп өзгерістер. Әдетте бұл немесе басқаша метрлер/агрегаттар/рейтингтер, 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.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 FULL бізге көмектеспейді.

Үстелді «жиыру».

Бірақ біз бұл сұрауға біздің кестемізді қажет етпейтінін анық білеміз. Сондықтан, біз әлі де кестеден қажет емес барлық нәрсені жою арқылы жүйе өнімділігін барабар шектеулерге қайтаруға тырысамыз - кем дегенде «қолмен», өйткені ВАКУМ береді.

Түсінікті болу үшін буферлік кесте жағдайының мысалын қарастырайық. Яғни, INSERT/DELETE үлкен ағыны бар, кейде кесте толығымен бос болады. Бірақ бос болмаса, біз керек оның ағымдағы мазмұнын сақтаңыз.

№0: Жағдайды бағалау

Әрбір операциядан кейін де кестемен бірдеңе жасауға тырысуға болатыны анық, бірақ мұның мағынасы жоқ - техникалық қызмет көрсетуге арналған үстеме шығындар мақсатты сұраулардың өткізу қабілетінен көбірек болатыны анық.

Критерийлерді тұжырымдаймыз - «әрекет ету уақыты келді», егер:

  • ВАКУУМ көп уақыт бұрын іске қосылған
    Біз ауыр жүк күтеміз, солай болсын 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 - VACUUM FULL-тен айырмашылығы, ол оқу-жазу деректерімен жұмыс істейтін параллельді процестерге кедергі жасамайды.

Сонымен бірге ол біз жойғымыз келетін нәрселердің көпшілігін бірден тазалай алады. Иә, және осы кестедегі келесі сұраулар бізге жіберіледі «ыстық кэш» арқылы, бұл олардың ұзақтығын қысқартады - демек, біздің қызмет көрсету транзакциясы арқылы басқаларды блоктаудың жалпы уақыты.

№2: Үйде біреу бар ма?

Кестеде бірдеңе бар-жоғын тексерейік:

TABLE tbl LIMIT 1;

Егер бірде-бір жазба қалмаса, біз жай ғана орындау арқылы өңдеуге көп нәрсені үнемдей аламыз ТРУНКАТ:

Ол әрбір кесте үшін шартсыз ЖОЮ пәрменімен бірдей әрекет етеді, бірақ ол кестелерді сканерлемейтіндіктен әлдеқайда жылдамырақ. Оның үстіне ол дискілік кеңістікті бірден босатады, сондықтан кейін ВАКУМ операциясын орындаудың қажеті жоқ.

Кесте реті есептегішін (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 ...;

Кейінірек айнымалының «ескі» мәнін қалпына келтірумен айналыспау үшін пішінді қолданамыз ЖЕРГІЛІКТІ ОРНАТУ, ол параметр ауқымын ағымдағы транзакцияға шектейді.

Кестеде деректер көп болған жағдайда транзакция қабылданбайтын мәндерге дейін созылмауы үшін мәлімдеме_уақытының аяқталуы барлық кейінгі сұрауларға қолданылатынын есте ұстаймыз.

№5: Деректерді көшіру

Кесте толығымен бос болмаса, деректерді қосалқы уақытша кестені пайдаланып қайта сақтау керек болады:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Қол қою ON COMMITI DOP транзакция аяқталған сәтте уақытша кестенің жұмысын тоқтататынын және оны қосылым контекстінде қолмен жоюдың қажеті жоқ дегенді білдіреді.

«Тірі» деректер көп емес деп есептейтіндіктен, бұл операция өте жылдам орындалуы керек.

Жарайды, бәрі осы! Транзакцияны аяқтағаннан кейін ұмытпаңыз 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-ға байланысты болмаса мүмкін:

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

пікір қалдыру