🥇Apabila VACUUM gagal, kami membersihkan meja secara manual

VACUUM boleh "membersihkan" dari jadual dalam PostgreSQL sahaja tiada siapa boleh nampak - iaitu, tiada satu permintaan aktif yang bermula sebelum rekod ini ditukar.

Tetapi bagaimana jika jenis yang tidak menyenangkan (pemuatan OLAP jangka panjang pada pangkalan data OLTP) masih wujud? Bagaimana bersih secara aktif menukar meja dikelilingi oleh pertanyaan panjang dan tidak memijak garu?

Meletakkan garu

Pertama, mari kita tentukan apakah masalah yang ingin kita selesaikan dan bagaimana ia boleh timbul.

Biasanya keadaan ini berlaku di atas meja yang agak kecil, tetapi di mana ia berlaku banyak perubahan. Biasanya ini atau berbeza meter/agregat/kadaran, yang mana UPDATE sering dilaksanakan, atau buffer-queue untuk memproses beberapa aliran acara yang berterusan, rekod yang sentiasa INSERT/DELETE.

Mari cuba menghasilkan semula pilihan dengan penilaian:

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;

Dan secara selari, dalam hubungan lain, permintaan yang panjang dan panjang bermula, mengumpulkan beberapa statistik yang kompleks, tetapi tidak menjejaskan meja kami:

SELECT pg_sleep(10000);

Kini kami mengemas kini nilai salah satu kaunter berkali-kali. Untuk kesucian eksperimen, mari lakukan ini dalam transaksi berasingan menggunakan dblinkbagaimana ia akan berlaku dalam realiti:

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

Apa yang berlaku? Mengapa walaupun untuk KEMASKINI yang paling mudah bagi satu rekod masa pelaksanaan menurun sebanyak 7 kali - dari 0.524ms kepada 3.808ms? Dan rating kami semakin perlahan.

Ini semua salah MVCC.

Ini semua tentang mekanisme MVCC, yang menyebabkan pertanyaan melihat semua versi entri sebelumnya. Jadi mari bersihkan jadual kami daripada versi "mati":

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, tiada apa yang perlu dibersihkan! selari Permintaan berjalan mengganggu kami - Lagipun, dia mungkin suatu hari nanti mahu beralih kepada versi ini (bagaimana jika?), dan ia sepatutnya tersedia untuknya. Oleh itu, walaupun VACUUM FULL tidak akan membantu kita.

"Meruntuhkan" meja

Tetapi kami tahu pasti bahawa pertanyaan itu tidak memerlukan jadual kami. Oleh itu, kami masih akan cuba mengembalikan prestasi sistem kepada had yang mencukupi dengan menghapuskan semua yang tidak perlu daripada jadual - sekurang-kurangnya "secara manual", kerana VACUUM berputus asa.

Untuk menjadikannya lebih jelas, mari lihat contoh kes jadual penimbal. Iaitu, terdapat aliran INSERT/DELETE yang besar, dan kadangkala jadual itu kosong sepenuhnya. Tetapi jika ia tidak kosong, kita mesti simpan kandungan semasanya.

#0: Menilai keadaan

Adalah jelas bahawa anda boleh cuba melakukan sesuatu dengan jadual walaupun selepas setiap operasi, tetapi ini tidak masuk akal - overhed penyelenggaraan jelas akan lebih besar daripada pemprosesan pertanyaan sasaran.

Mari kita rumuskan kriteria - "sudah tiba masanya untuk bertindak" jika:

VACUUM telah dilancarkan agak lama dahulu
Kami mengharapkan beban yang berat, jadi biarlah 60 saat sejak [auto]VACUUM yang lalu.
saiz jadual fizikal lebih besar daripada sasaran
Mari kita takrifkannya sebagai dua kali ganda bilangan halaman (blok 8KB) berbanding saiz minimum - 1 blk untuk timbunan + 1 blk untuk setiap indeks - untuk meja yang berpotensi kosong. Jika kami menjangkakan bahawa sejumlah data akan sentiasa kekal dalam penimbal "biasa", adalah munasabah untuk mengubah suai formula ini.

Permintaan pengesahan

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: Masih VACUUM

Kami tidak dapat mengetahui terlebih dahulu sama ada pertanyaan selari mengganggu kami dengan ketara - dengan tepat berapa banyak rekod yang telah "lapuk" sejak ia bermula. Oleh itu, apabila kita memutuskan untuk memproses jadual, dalam apa jua keadaan, kita harus terlebih dahulu melaksanakannya VACUUM - tidak seperti VACUUM FULL, ia tidak mengganggu proses selari yang berfungsi dengan data baca-tulis.

Pada masa yang sama, ia boleh segera membersihkan kebanyakan perkara yang ingin kami alih keluar. Ya, dan pertanyaan seterusnya pada jadual ini akan dihantar kepada kami oleh "cache panas", yang akan mengurangkan tempoh mereka - dan, oleh itu, jumlah masa menyekat orang lain melalui transaksi servis kami.

#2: Adakah sesiapa di rumah?

Mari kita semak sama ada terdapat apa-apa dalam jadual sama sekali:

TABLE tbl LIMIT 1;

Jika tiada satu rekod pun yang tinggal, maka kita boleh menjimatkan banyak pemprosesan dengan hanya melakukan TRUNCATE:

Ia bertindak sama seperti perintah DELETE tanpa syarat untuk setiap jadual, tetapi lebih pantas kerana ia sebenarnya tidak mengimbas jadual. Selain itu, ia segera membebaskan ruang cakera, jadi tidak perlu melakukan operasi VACUUM selepas itu.

Sama ada anda perlu menetapkan semula pembilang jujukan jadual (MULAKAN SEMULA IDENTITI) terpulang kepada anda untuk membuat keputusan.

#3: Semua orang - bergilir-gilir!

Memandangkan kami bekerja dalam persekitaran yang sangat berdaya saing, sementara kami di sini menyemak bahawa tiada entri dalam jadual, seseorang mungkin sudah menulis sesuatu di sana. Kita tidak sepatutnya kehilangan maklumat ini, jadi apa? Betul, kita perlu memastikan bahawa tiada siapa yang boleh menuliskannya dengan pasti.

Untuk melakukan ini kita perlu membolehkan BOLEH BERSIRI-pengasingan untuk transaksi kami (ya, di sini kami memulakan transaksi) dan kunci jadual "ketat":

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

Tahap penyekatan ini ditentukan oleh operasi yang ingin kami lakukan padanya.

#4: Konflik kepentingan

Kami datang ke sini dan ingin "mengunci" tanda itu - bagaimana jika seseorang aktif padanya pada masa itu, sebagai contoh, membaca daripadanya? Kami akan "menggantung" menunggu blok ini dikeluarkan, dan orang lain yang ingin membaca akan terserempak dengan kami...

Untuk mengelakkan ini daripada berlaku, kami akan "mengorbankan diri kami" - jika kami tidak dapat mendapatkan kunci dalam masa tertentu (boleh diterima singkat), maka kami akan menerima pengecualian dari pangkalan, tetapi sekurang-kurangnya kami tidak akan terlalu mengganggu yang lain.

Untuk melakukan ini, tetapkan pembolehubah sesi lock_timeout (untuk versi 9.3+) atau/dan penyata_masa tamat. Perkara utama yang perlu diingat ialah nilai statement_timeout hanya digunakan daripada pernyataan seterusnya. Iaitu, seperti ini dalam melekatkan - tidak akan berfungsi:

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

Untuk tidak perlu berurusan dengan memulihkan nilai "lama" pembolehubah kemudian, kami menggunakan borang TETAPKAN TEMPATAN, yang mengehadkan skop tetapan kepada transaksi semasa.

Kami ingat bahawa statement_timeout terpakai pada semua permintaan seterusnya supaya transaksi tidak boleh meregangkan kepada nilai yang tidak boleh diterima jika terdapat banyak data dalam jadual.

#5: Salin data

Jika jadual tidak kosong sepenuhnya, data perlu disimpan semula menggunakan jadual sementara tambahan:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Tandatangan ON COMMIT DROP bermakna pada masa urus niaga tamat, jadual sementara akan tidak lagi wujud, dan tidak perlu memadamkannya secara manual dalam konteks sambungan.

Memandangkan kami mengandaikan bahawa tidak terdapat banyak data "langsung", operasi ini sepatutnya berlaku dengan cepat.

Nah, itu sahaja! Jangan lupa selepas selesai transaksi jalankan ANALISIS untuk menormalkan statistik jadual jika perlu.

Menyusun skrip akhir

Kami menggunakan "pseudo-python" ini:

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

Adakah mungkin untuk tidak menyalin data untuk kali kedua?Pada dasarnya, adalah mungkin jika oid jadual itu sendiri tidak terikat dengan sebarang aktiviti lain dari bahagian BL atau FK dari bahagian 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;

Mari jalankan skrip pada jadual sumber dan semak metrik:

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

Semuanya berjaya! Jadual telah mengecil sebanyak 50 kali dan semua KEMASKINI berjalan dengan pantas sekali lagi.

Sumber: www.habr.com

Apabila VACUUM gagal, kami membersihkan meja secara manual