ProHoster > Blog > İdarə etmə > VACUUM zamanı— cədvəli əl ilə təmizləyirik

VACUUM zamanı— cədvəli əl ilə təmizləyirik

VACUUM PostgreSQL-də cədvəldən yalnız bunu heç kəs görə bilmir — yəni bu qeydlər dəyişdirilənə qədər başlamış aktiv sorğu yoxdur.

Bəs belə narahat bir şəxs (OLTP bazasında davamlı OLAP yükü) varsa? Necə aktiv dəyişən cədvəli uzun sorğular mühitində təmizləmək və tələ düşmədən necədir?

VACUUM zamanı— cədvəli əl ilə təmizləyirik

Tələni açırıq

İlk öncə, həqiqətən nədən ibarət olduğunu və hansı problemi həll etməyə çalışdığımızı müəyyənləşdirək.

Adətən belə bir vəziyyət nisbətən kiçik cədvəldə baş verir, lakin burada çox sayda dəyişiklik baş verir. Adətən bu ya fərqli sayğaclar/pansumanlar/reytinqlərdir, tez-tez UPDATE həyata keçirilir, ya da buffer-queuing daimi bir hadisələr axınının işlənməsi üçün, qeyd olunan INSERT/DELETE olunur.

Reytinqlə bir variantı reprodukt etməyə çalışaq:

CREATE TABLE tbl(k text PRIMARY KEY, v integer);
CREATE INDEX ON tbl(v DESC); -- bu indeks üzrə reytinq quracağıq

INSERT INTO
  tbl
SELECT
  chr(ascii('a'::text) + i) k
, 0 v
FROM
  generate_series(0, 25) i;

Eyni zamanda, digər bir birləşdirmədə uzun bir sorğu başlayır, bir neçə mürəkkəb statistikaları toplayan, amma cədvəlimizi təsir etmir:

SELECT pg_sleep(10000);

İndi bir sayğacın dəyərini çoxsaylı dəfə yeniləyirik. Eksperimentin düzgünlüyü üçün bunu ayrı tranzaksiyalarda dblink ilə edək, gerçəkdə baş verəcəyi kimi:

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ə oldu? Niyə hətta təkcə bir qeydin sadə UPDATE-i üçün yerinə yetirmə müddəti 7 dəfə azaldı — 0.524ms-dən 3.808ms-ə? Həmçinin reytinqimiz daha yavaş qurulur.

Bütün günah MVCC-dadır

Məsələ MVCC mexanizmindədir, bu sorğunun bütün əvvəlki versiyaları araşdırmasını təmin edir. Elə isə gəlin cədvəlimizi "ölü" versiyalardan təmizləyək:

VACUUM VERBOSE tbl;

INFO:  vacuuming "public.tbl"
INFO:  "tbl": 0 silinən, 10026 silinməyən row versiyaları tapıldı, 45 in 45 səhifədə
DETAIL:  10000 ölülərin versiyaları hələ silinmir, ən qədim xmin: 597439602

Aman, amma təmizləməyə heç nə yoxdur! Paralel icra olunan sorğumuz bizə mane olur — axı birdə bu versiyalara müraciət etmək istəyir, (ah bəlkə də?), və onlar ona əlçatan olmalıdır. Buna görə hətta VACUUM FULL-da bizə kömək etməyəcək.

Cədvəli "sıxılırıq"

Amma biz dəqiq bilirik ki, həmin sorğuya bizim cədvəl lazım deyil. Bu səbəbdən sistemin məhsuldarlığını adekvat həddə qaytarmağa çalışaq, cədvəldən bütün lazımsız elementləri ataraq — bəlkə də "əllə", çünki VACUUM çıxış verir.

Daha aydın olması üçün, yastı cədvəl nümunəsinə baxaq. Yəni, burada böyük bir INSERT/DELETE axını var və bəzən cədvəldə tamamilə heç nə olmaya bilər. Lakin orada heç olmasa bir şey varsa, biz mövcud məzmununu qorumağa çalışmalıyıq..

#0: Оцениваем ситуацию

Aydındır ki, cədvəllə hər əməliyyatdan sonra bir şeylər etmək mümkündür, lakin bunun böyük bir mənası yoxdur — xidmət xərcləri açıq-aşkar hədəf sorğularının keçirilmə imkanından daha çox olacaq.

Kriteriyaları müəyyənləşdirək — "artıq fəaliyyət göstərmək vaxtıdır", əgər:

  • VACUUM kifayət qədər əvvəl başlamışdı.
    Biz güclü yük gözləyirik, buna görə də qoy bu 60 saniyə son [auto]VACUUM-dan.
  • cədvəlin fiziki ölçüsü hədəfdən böyükdür.
    Onu minimal ölçüyə nisbətən iki dəfə olan səhifələr (8KB bloku) sayı kimi müəyyən edək — 1 blk yığın + hər bir indeks üçün 1 blk — potensial olaraq boş cədvəl üçün. Əgər biz gözləyiriksə ki, yastıqda "normada" daima müəyyən bir məlumat həcmi qalacaq, bu formulu ağıllı şəkildə tənzimləmək məqbuldur.

Yoxlama sorğusu

SELECT
  relpages
, ((
    SELECT
      count(*)
    FROM
      pg_index
    WHERE
      indrelid = cl.oid
  ) + 1) << 13 size_norm -- burada daha düzgün olanı current_setting('block_size')::bigint ilə vurmaqdır, amma kim blok ölçüsünü dəyişir?..
, 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

Biz əvvəlcədən bilə bilmirik ki, paralel sorğu nə qədər mane olur — başladığı andan etibarən neçə "köhnəlmiş" qeyd var. Buna görə də, cədvəli nəhayət bir şəkildə işləməyə qərar verdikdə, əvvəlcə cim cədvəli icra etməyə dəyər VACUUM — çünki, VACUUM FULL-dan fərqli olaraq, paralel proseslərin məlumatlarla oxuma-yazma işləri ilə işləməsinə mane olmur.

Eyni zamanda, o, istədiyimiz şeylərin əksəriyyətini təmizləyə bilər. Və bu cədvəl üzrə sonrakı sorğularımız «isti keş», bu da onların müddətini azaldar — yəni, digər xidmət edən transaksiyamızın bloklanma müddətini də.

#2: Есть кто-нибудь дома?

Gəlin yoxlayaq — cədvəlində yalnız bir şey varmı:

TABLE tbl LIMIT 1;

Əgər heç bir qeyd qalmayıbsa, onda emalda ciddi qənaət edə bilərik — sadəcə olaraq icra edərək TRUNCATE:

Bu, hər bir cədvəl üçün şərtsiz DELETE əmri kimi işləyir, lakin daha sürətlidir, çünki faktiki olaraq cədvəlləri skan etmir. Bundan əlavə, o, dərhal diskdəki boşluqları azad edir, buna görə də onun ardınca VACUUM əməlini icra etməyə ehtiyac yoxdur.

Tablonun sıra sayacını sıfırlayıp sıfırlamayacağınıza (RESTART IDENTITY) kendiniz karar verirsiniz.

#3: Все — по-очереди!

Rekabet dolu bir ortamda çalıştığımız için, biz burada tabloda kayıt olmadığını kontrol ederken, birisi zaten bir şeyler kayıt etmiş olabilir. Bu bilgiyi kaybetmemeliyiz, yani ne yapmalıyız? Doğru, kimsenin yazmasını kesinlikle engellememiz gerekiyor.

Bunun için şunu açmalıyız SERIALIZABLE-işlemlerimiz için izolasyonu sağlamalıyız (evet, burada bir işlem başlatıyoruz) ve tabloyu "katı" bir şekilde kilitlemeliyiz:

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

Bu kilitleme seviyesi, üzerinde gerçekleştirmek istediğimiz işlemlerle belirleniyor.

#4: Конфликт интересов

Buraya gelip tabloyu "kilitlemek" istiyoruz — eğer o anda bu tabloda birisi aktifse, örneğin ondan veri okuyorsa? Kilitleme bekleyerek sıkışırız ve okumak isteyen diğerleri de bize takılır...

Bunun olmaması için "kendimizi feda edelim" — eğer belirli (kabul edilebilir derecede kısa) bir zaman diliminde yine de kilit elde edemezsek, veritabanından bir istisna alırız ama en azından başkalarına çok fazla engel olmayız.

Bunun için oturum değişkenini ayarlayalım lock_timeout (9.3+ sürümleri için) veya/veya statement_timeout. Önemli olan, statement_timeout değeri yalnızca bir sonraki ifadeye uygulandığını unutmamaktır. Yani, bu gibi bir bağlantı — çalışmayacak:

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

Sonrasındaki ifadeler için "eski" değişken değerini geri yüklemeye uğraşmamak için, şu şekli kullanıyoruz SET LOCAL, bu, ayarın geçerliliğinin mevcut işlemle sınırlı olmasını sağlar.

statement_timeout'un tüm takip eden sorgulara yayıldığını hatırlıyoruz, böylece işlemin kabul edilemez boyutlara ulaşması engellenmiş oluyor.

#5: Копируем данные

Eğer tablo pek de boş değilse — verileri yardımcı bir geçici tablo aracılığıyla yeniden kaydetmek gerekecek:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

İmza ON COMMIT DROP ifade, işlem tamamlandığında geçici tablonun varlığını kaybedeceği anlamına gelir ve bağlantı bağlamında manuel silme işlemi yapılmasına gerek yoktur.

Yaşayan verilerin çok fazla olmadığını varsaydığımızdan, bu işlem oldukça hızlı geçmelidir.

Aslında bu kadar! İşlemin tamamlanmasından sonra unutmayın ANALYZE'yi çalıştırmak tablonun istatistiklerini normalleştirmek gerekirse.

Sonuç betiğimizi topluyoruz

Böyle bir "sahte python" kullanıyoruz:

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

Verileri ikinci kez kopyalamak zorunda mıyız?Teknik olarak, eğer tablonun oid'sine bağlı başka aktiviteler yoksa, bunu yapmak mümkün.

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

Betigi başlangıç tablosunda çalıştıracağız ve metrikleri kontrol edeceğiz:

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 oldu! Cədvəl 50 dəfə kiçildi və bütün UPDATE-lər yenidən sürətlə işləyir.

Mənbə: habr.com

DDoS qoruması olan saytlara etibarlı hosting satın alın, VPS VDS serverlər 🔥 DDoS qoruması olan saytlara etibarlı hosting satın alın, VPS VDS serverlər | ProHoster