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.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 - VACUUM FULL-ээс ялгаатай нь энэ нь унших, бичих өгөгдөлтэй зэрэгцээ процессуудад саад болохгүй.

Үүний зэрэгцээ, энэ нь бидний устгахыг хүссэн ихэнх зүйлийг шууд цэвэрлэж чадна. Тийм ээ, энэ хүснэгтийн дараагийн асуултууд бидэн рүү очих болно "халуун кэш"-ээр, энэ нь тэдний үргэлжлэх хугацааг богиносгож, улмаар манай үйлчилгээний гүйлгээгээр бусдыг хаах нийт хугацааг багасгах болно.

#2: Гэрт хэн нэгэн байна уу?

Хүснэгтэнд ямар нэгэн зүйл байгаа эсэхийг шалгацгаая:

TABLE tbl LIMIT 1;

Хэрэв ганц ч бичлэг үлдээгүй бол бид зүгээр л хийснээр боловсруулалтад ихээхэн хэмнэлт гаргаж чадна ТРАНКАТ:

Энэ нь хүснэгт бүрийн хувьд болзолгүй DELETE командтай адил үйлчилдэг боловч хүснэгтүүдийг скан хийдэггүй тул илүү хурдан байдаг. Мөн дискний зайг шууд чөлөөлдөг тул дараа нь ВАКУМ хийх шаардлагагүй болно.

Хүснэгтийн дарааллын тоолуурыг (RESTART IDENTITY) дахин тохируулах шаардлагатай эсэхээ та өөрөө шийднэ.

№3: Хүн бүр - ээлжлэн яв!

Бид ширүүн өрсөлдөөнтэй орчинд ажилладаг тул хүснэгтэд ямар ч оруулга байхгүй эсэхийг шалгаж байх хооронд хэн нэгэн тэнд ямар нэгэн зүйл бичсэн байж магадгүй юм. Бид энэ мэдээллийг алдах ёсгүй, тэгвэл яах вэ? Энэ нь зөв, хэн ч үүнийг баттай бичиж чадахгүй байх ёстой.

Үүнийг хийхийн тулд бид идэвхжүүлэх хэрэгтэй ЦУВРАЛ БОЛОМЖТОЙ-бидний гүйлгээг тусгаарлах (тиймээ, бид гүйлгээг эхлүүлж байна) хүснэгтийг "чанга" түгжих:

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

Энэхүү блоклох түвшинг бидний хийхийг хүсч буй үйлдлүүдээр тодорхойлно.

№4: Ашиг сонирхлын зөрчил

Бид энд ирээд тэмдгийг "түгжихийг" хүсч байна - хэрэв тэр үед хэн нэгэн үүн дээр идэвхтэй байсан бол, жишээлбэл түүнээс уншиж байсан бол яах вэ? Бид энэ блок гарахыг хүлээсээр "өлгөх" болно, уншихыг хүссэн бусад хүмүүс бидэнтэй тааралдана ...

Үүнээс урьдчилан сэргийлэхийн тулд бид "өөрсдийгөө золиослох" болно - хэрэв бид тодорхой (зөвшөөрөхүйц богино) хугацаанд түгжээ авч чадаагүй бол баазаас онцгой тохиолдол хүлээн авах болно, гэхдээ ядаж бид хэт их хөндлөнгөөс оролцохгүй. бусад.

Үүнийг хийхийн тулд сессийн хувьсагчийг тохируулна уу түгжих_хугацаа (9.3+ хувилбаруудын хувьд) эсвэл/ба мэдэгдэл_хугацаа. Санах ёстой гол зүйл бол statement_timeout утга нь зөвхөн дараагийн мэдэгдлээс хамаарна. Өөрөөр хэлбэл, наахдаа ингэж - ажиллахгүй: 

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

Хувьсагчийн "хуучин" утгыг дараа нь сэргээх асуудал гарахгүйн тулд бид маягтыг ашигладаг ОРОН САЙН ТОХИРУУЛАХ, энэ нь тохиргооны хамрах хүрээг одоогийн гүйлгээнд хязгаарладаг.

Хүснэгтэд маш олон өгөгдөл байгаа тохиолдолд гүйлгээ нь хүлээн зөвшөөрөгдөөгүй утга хүртэл үргэлжлэхгүйн тулд мэдэгдэл_хугацаа нь дараагийн бүх хүсэлтэд хамаарна гэдгийг бид санаж байна.

# 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;

Өгөгдлийг хоёр дахь удаагаа хуулахгүй байх боломжтой юу?Зарчмын хувьд, хэрэв хүснэгтийн оид нь BL талаас эсвэл DB талаас 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

сэтгэгдэл нэмэх