ProHoster > Blog > İdarə > VAKUUM uğursuz olduqda, masanı əl ilə təmizləyirik

VAKUUM uğursuz olduqda, masanı əl ilə təmizləyirik

VACUUM PostgreSQL-də cədvəldən yalnız nəyi "təmizləyə" bilər heç kim görə bilməz - yəni bu qeydlər dəyişdirilmədən əvvəl başlanmış bir dənə də aktiv sorğu yoxdur.

Bəs belə xoşagəlməz bir növ (OLTP verilənlər bazasında uzunmüddətli OLAP yükü) hələ də mövcuddursa? Necə aktiv dəyişən masanı təmizləyin uzun sorğularla əhatə olunub və dırmıq üzərində addım atmırsınız?

VAKUUM uğursuz olduqda, masanı əl ilə təmizləyirik

Dırmağın qoyulması

Əvvəlcə həll etmək istədiyimiz problemin nə olduğunu və necə yarana biləcəyini müəyyən edək.

Adətən bu vəziyyət olur nisbətən kiçik bir masada, lakin hansında baş verir çoxlu dəyişikliklər. Adətən bu və ya fərqlidir metr/aqreqat/reytinqlər, hansı UPDATE tez-tez icra olunur, və ya bufer növbəsi qeydləri daim INSERT/DELETE olan bəzi davamlı davam edən hadisələr axınını emal etmək.

Seçimi reytinqlərlə təkrarlamağa çalışaq:

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;

Və paralel olaraq, başqa bir əlaqədə, bəzi mürəkkəb statistikaları toplayan uzun, uzun bir sorğu başlayır, lakin süfrəmizə təsir etmir:

SELECT pg_sleep(10000);

İndi sayğaclardan birinin dəyərini dəfələrlə yeniləyirik. Təcrübənin təmizliyi üçün bunu edək dblink istifadə edərək ayrı-ayrı əməliyyatlardareallıqda necə olacaq:

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

Nə olub? Niyə bir rekordun ən sadə YENİLƏMƏSİ üçün belə icra müddəti 7 dəfə azalıb - 0.524ms-dən 3.808ms-ə qədər? Və reytinqimiz getdikcə daha yavaş qurulur.

Bütün bunlar MVCC-nin günahıdır.

Hər şeyə aiddir MVCC mexanizmi, bu, sorğunun girişin bütün əvvəlki versiyalarını nəzərdən keçirməsinə səbəb olur. Beləliklə, masamızı "ölü" versiyalardan təmizləyək:

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

Oh, təmizləmək üçün heç bir şey yoxdur! Paralel Çalışan sorğu bizə müdaxilə edir - axı o, nə vaxtsa bu versiyalara müraciət etmək istəyə bilər (nə olar?) və onlar onun üçün əlçatan olmalıdır. Və buna görə də hətta VACUUM FULL bizə kömək etməyəcək.

Masanın "yıxılması"

Amma biz dəqiq bilirik ki, o sorğuya bizim cədvəlimiz lazım deyil. Buna görə də, biz hələ də cədvəldən lazımsız hər şeyi silməklə sistemin işini adekvat hədlərə qaytarmağa çalışacağıq - ən azı "əl ilə" VACUUM verdiyi üçün.

Daha aydın olmaq üçün bufer cədvəlinin nümunəsinə baxaq. Yəni INSERT/DELETE-in böyük axını var və bəzən cədvəl tamamilə boş olur. Amma boş deyilsə, etməliyik cari məzmununu yadda saxlayın.

# 0: Vəziyyətin qiymətləndirilməsi

Aydındır ki, hətta hər əməliyyatdan sonra da cədvəllə nəsə etməyə cəhd edə bilərsiniz, lakin bu o qədər də məntiqli deyil - texniki xidmət xərcləri açıq şəkildə hədəf sorğuların ötürmə qabiliyyətindən çox olacaq.

Gəlin meyarları formalaşdıraq - "hərəkət etmək vaxtıdır" əgər:

  • VACUUM kifayət qədər uzun müddət əvvəl istifadəyə verilmişdir
    Ağır yük gözləyirik, qoy olsun 60 saniyə son [avtomatik]VAKUUM-dan bəri.
  • fiziki masa ölçüsü hədəfdən böyükdür
    Gəlin onu minimum ölçüyə nisbətən iki dəfə çox səhifə sayı (8KB blok) kimi müəyyən edək - Yığın üçün 1 blk + hər indeks üçün 1 blk - potensial boş masa üçün. Müəyyən bir miqdarda məlumatın həmişə buferdə “normal olaraq” qalacağını gözləsək, bu düsturun dəyişdirilməsi məqsədəuyğun olar.

Doğrulama sorğusu

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: Hələ də vakuum

Paralel sorğunun bizə əhəmiyyətli dərəcədə mane olub-olmadığını əvvəlcədən bilə bilmərik - başlanandan bəri dəqiq neçə qeyd "köhnəlmişdir". Buna görə də, cədvəli bir şəkildə emal etmək qərarına gəldikdə, hər halda, ilk növbədə onun üzərində icra etməliyik VACUUM - VACUUM FULL-dan fərqli olaraq, oxumaq-yazma məlumatları ilə işləyən paralel proseslərə müdaxilə etmir.

Eyni zamanda, silmək istədiklərimizin çoxunu dərhal təmizləyə bilər. Bəli və bu cədvəldəki sonrakı sorğular bizə gedəcək "isti önbellek" ilə, bu onların müddətini azaldacaq - və deməli, xidmət əməliyyatımızla başqalarının bloklanmasının ümumi vaxtını.

# 2: Evdə kimsə varmı?

Cədvəldə hər hansı bir şeyin olub olmadığını yoxlayaq:

TABLE tbl LIMIT 1;

Bir dənə də olsun qeyd yoxdursa, biz sadəcə olaraq emalda çox qənaət edə bilərik QARŞI:

O, hər bir cədvəl üçün qeyd-şərtsiz SİL əmri kimi işləyir, lakin əslində cədvəlləri skan etmədiyi üçün daha sürətli işləyir. Üstəlik, o, dərhal disk yerini boşaldır, ona görə də sonradan VAKUUM əməliyyatını yerinə yetirməyə ehtiyac yoxdur.

Cədvəl ardıcıllığı sayğacını (YENİDƏN BAŞLAT ŞƏXSİYYƏT) sıfırlamağınız lazım olub-olmamağınıza qərar vermək sizin ixtiyarınızdadır.

# 3: Hər kəs - növbə ilə!

Biz yüksək rəqabət mühitində işlədiyimiz üçün burada cədvəldə heç bir qeydin olmadığını yoxlayarkən kimsə artıq orada nəsə yazmış ola bilərdi. Bu məlumatı itirməməliyik, bəs nə olacaq? Düzdü, elə etməliyik ki, heç kim bunu dəqiqliklə yaza bilməsin.

Bunu etmək üçün aktivləşdirməliyik SERİYALANABİLƏN- əməliyyatımız üçün izolyasiya (bəli, burada əməliyyata başlayırıq) və masanı "sıx" bağlayın:

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

Bu bloklama səviyyəsi bizim onun üzərində yerinə yetirmək istədiyimiz əməliyyatlarla müəyyən edilir.

# 4: Maraqların toqquşması

Biz bura gəlirik və işarəni "kilidləmək" istəyirik - əgər o anda kimsə aktiv olsaydı, məsələn, ondan oxusa? Bu blokun buraxılmasını gözləyərək “asacağıq” və oxumaq istəyən başqaları da üzümüzə qaçacaq...

Bunun baş verməməsi üçün biz “özümüzü qurban verəcəyik” – əgər müəyyən (məqbul dərəcədə qısa) müddətdə kilid əldə edə bilməsək, o zaman bazadan bir istisna alacağıq, amma heç olmasa, çox da müdaxilə etməyəcəyik. başqaları.

Bunu etmək üçün sessiya dəyişənini təyin edin lock_timeout (9.3+ versiyaları üçün) və ya/və bəyanat_zaman aşımı. Xatırlamaq lazım olan əsas şey odur ki, statement_timeout dəyəri yalnız növbəti ifadəyə aiddir. Yəni yapışdırmada belə - işləməyəcək: 

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

Daha sonra dəyişənin “köhnə” dəyərini bərpa etməklə məşğul olmamaq üçün formadan istifadə edirik YERLİ AYARLAR, bu parametrin əhatə dairəsini cari əməliyyatla məhdudlaşdırır.

Cədvəldə çoxlu məlumat varsa, əməliyyatın qəbuledilməz dəyərlərə çatmaması üçün bəyanat_zamanının bütün sonrakı sorğulara tətbiq olunduğunu xatırlayırıq.

# 5: Məlumatları kopyalayın

Cədvəl tamamilə boş deyilsə, məlumatlar köməkçi müvəqqəti cədvəldən istifadə edərək yenidən saxlanmalı olacaq:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

İmza TƏHLÜKƏSİZ OLUN o deməkdir ki, əməliyyat başa çatan anda müvəqqəti cədvəl mövcudluğunu dayandıracaq və əlaqə kontekstində onu əl ilə silməyə ehtiyac yoxdur.

Çox "canlı" məlumatın olmadığını güman etdiyimiz üçün bu əməliyyat olduqca tez baş verməlidir.

Yaxşı, hamısı budur! Əməliyyatı tamamladıqdan sonra unutmayın ANALYZE işlədin zəruri hallarda cədvəl statistikasını normallaşdırmaq.

Son skriptin yığılması

Bu "psevdo-python" istifadə edirik:

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

Məlumatı ikinci dəfə köçürməmək mümkündürmü?Prinsipcə, cədvəlin oidinin özü BL tərəfdən və ya DB tərəfdən FK-dan başqa fəaliyyətlərə bağlı olmadıqda mümkündür:

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

Gəlin mənbə cədvəlində skripti işə salaq və göstəriciləri yoxlayaq:

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

Hər şey alındı! Cədvəl 50 dəfə kiçildi və bütün YENİLƏNİBLƏR yenidən sürətlə işləyir.

Mənbə: www.habr.com

Добавить комментарий