ProHoster > blog > administrasi > Ketika VACUUM gagal, kami membersihkan meja secara manual

Ketika VACUUM gagal, kami membersihkan meja secara manual

KEKOSONGAN dapat "membersihkan" dari tabel di PostgreSQL hanya apa tidak ada yang bisa melihat - yaitu, tidak ada satu pun permintaan aktif yang dimulai sebelum catatan ini diubah.

Namun bagaimana jika tipe yang tidak menyenangkan tersebut (pemuatan OLAP jangka panjang pada database OLTP) masih ada? Bagaimana membersihkan meja ganti secara aktif dikelilingi oleh pertanyaan panjang dan tidak menginjak penggaruk?

Ketika VACUUM gagal, kami membersihkan meja secara manual

Meletakkan penggaruk

Pertama, mari kita tentukan masalah apa yang ingin kita pecahkan dan bagaimana masalah itu bisa timbul.

Biasanya situasi ini terjadi di atas meja yang relatif kecil, tapi di mana hal itu terjadi banyak perubahan. Biasanya ini atau berbeda meter/agregat/peringkat, di mana UPDATE sering dijalankan, atau antrian penyangga untuk memproses beberapa aliran peristiwa yang terus-menerus berlangsung, catatannya terus-menerus INSERT/DELETE.

Mari kita coba mereproduksi opsi dengan peringkat:

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 paralel, di koneksi lain, permintaan yang sangat panjang dimulai, mengumpulkan beberapa statistik rumit, tapi tidak mempengaruhi meja kami:

SELECT pg_sleep(10000);

Sekarang kami memperbarui nilai salah satu penghitung berkali-kali. Demi kemurnian eksperimen, mari kita lakukan ini dalam transaksi terpisah menggunakan dblinkbagaimana hal itu akan terjadi dalam kenyataan:

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 telah terjadi? Mengapa bahkan untuk UPDATE yang paling sederhana dari satu catatan waktu eksekusi menurun 7 kali — dari 0.524 md hingga 3.808 md? Dan peringkat kami meningkat semakin lambat.

Itu semua salah MVCC.

Semua tentang mekanisme MVCC, yang menyebabkan kueri memeriksa semua versi entri sebelumnya. Jadi mari kita bersihkan tabel kita dari versi yang "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, tidak ada yang perlu dibersihkan! Paralel Permintaan yang berjalan mengganggu kami - lagi pula, suatu hari nanti dia mungkin ingin beralih ke versi ini (bagaimana jika?), dan versi tersebut harus tersedia untuknya. Dan oleh karena itu bahkan VACUUM FULL tidak akan membantu kita.

“Meruntuhkan” meja

Namun kami tahu pasti bahwa kueri itu tidak memerlukan tabel kami. Oleh karena itu, kami akan tetap mencoba mengembalikan kinerja sistem ke batas yang memadai dengan menghilangkan semua yang tidak perlu dari tabel - setidaknya “secara manual”, karena VACUUM menyerah.

Agar lebih jelas mari kita lihat contoh kasus tabel buffer. Artinya, ada aliran INSERT/DELETE yang besar, dan terkadang tabel benar-benar kosong. Tapi kalau tidak kosong, kita harus menyimpan isinya saat ini.

#0: Menilai situasi

Jelas bahwa Anda dapat mencoba melakukan sesuatu dengan tabel bahkan setelah setiap operasi, tetapi ini tidak masuk akal - overhead pemeliharaan jelas akan lebih besar daripada throughput kueri target.

Mari kita rumuskan kriterianya - “saatnya bertindak” jika:

  • VACUUM diluncurkan cukup lama
    Kami mengharapkan beban yang berat, biarlah 60 detik sejak VAKUM [otomatis] terakhir.
  • ukuran tabel fisik lebih besar dari target
    Mari kita definisikan sebagai dua kali jumlah halaman (blok 8KB) dibandingkan dengan ukuran minimum - 1 blk untuk heap + 1 blk untuk setiap indeks - untuk tabel yang berpotensi kosong. Jika kita berharap bahwa sejumlah data tertentu akan selalu berada di buffer “secara normal”, maka masuk akal untuk mengubah rumus ini.

Permintaan verifikasi

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 VAKUM

Kami tidak dapat mengetahui sebelumnya apakah kueri paralel mengganggu kami secara signifikan - tepatnya berapa banyak catatan yang menjadi "kedaluwarsa" sejak kueri tersebut dimulai. Oleh karena itu, ketika kita memutuskan untuk memproses tabel, bagaimanapun juga, kita harus mengeksekusinya terlebih dahulu KEKOSONGAN - tidak seperti VACUUM FULL, ini tidak mengganggu proses paralel yang bekerja dengan data baca-tulis.

Pada saat yang sama, ia dapat segera membersihkan sebagian besar dari apa yang ingin kita hapus. Ya, dan pertanyaan selanjutnya pada tabel ini akan dikirimkan kepada kami dengan "cache panas", yang akan mengurangi durasinya - dan, oleh karena itu, total waktu pemblokiran orang lain oleh transaksi layanan kami.

#2: Apakah ada orang di rumah?

Mari kita periksa apakah ada sesuatu di tabel:

TABLE tbl LIMIT 1;

Jika tidak ada satu catatan pun yang tersisa, maka kita dapat menghemat banyak pemrosesan hanya dengan melakukan MEMOTONG:

Tindakannya sama seperti perintah DELETE tanpa syarat untuk setiap tabel, namun jauh lebih cepat karena tidak benar-benar memindai tabel. Terlebih lagi, ini segera mengosongkan ruang disk, sehingga tidak perlu melakukan operasi VACUUM setelahnya.

Apakah Anda perlu mengatur ulang penghitung urutan tabel (RESTART IDENTITY) terserah Anda.

#3: Semuanya - bergiliran!

Karena kami bekerja di lingkungan yang sangat kompetitif, sementara kami di sini memeriksa bahwa tidak ada entri dalam tabel, seseorang mungkin sudah menulis sesuatu di sana. Kita tidak boleh kehilangan informasi ini, lalu kenapa? Benar sekali, kita perlu memastikan tidak ada orang yang bisa menuliskannya secara pasti.

Untuk melakukan ini kita perlu mengaktifkan SERIALISASI-isolasi untuk transaksi kita (ya, di sini kita memulai transaksi) dan mengunci meja “rapat”:

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

Tingkat pemblokiran ini ditentukan oleh operasi yang ingin kita lakukan padanya.

#4: Konflik kepentingan

Kami datang ke sini dan ingin “mengunci” tanda tersebut - bagaimana jika seseorang sedang aktif pada tanda tersebut pada saat itu, misalnya, membacanya? Kami akan "menggantung" menunggu blok ini dilepaskan, dan orang lain yang ingin membaca akan menemui kami...

Untuk mencegah hal ini terjadi, kami akan "mengorbankan diri kami sendiri" - jika kami tidak dapat memperoleh kunci dalam waktu tertentu (yang cukup singkat), maka kami akan menerima pengecualian dari pangkalan, tetapi setidaknya kami tidak akan terlalu banyak ikut campur dalam hal ini. yang lain.

Untuk melakukan ini, atur variabel sesi lock_timeout (untuk versi 9.3+) atau/dan pernyataan_waktu habis. Hal utama yang perlu diingat adalah nilai statement_timeout hanya berlaku dari pernyataan berikutnya. Artinya, seperti ini dalam menempelkan - tidak akan berhasil: 

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

Agar tidak harus berurusan dengan pemulihan nilai variabel yang "lama" nanti, kami menggunakan formulir ATUR LOKAL, yang membatasi cakupan pengaturan pada transaksi saat ini.

Kita ingat bahwa statement_timeout berlaku untuk semua permintaan berikutnya sehingga transaksi tidak dapat mencapai nilai yang tidak dapat diterima jika ada banyak data dalam tabel.

#5: Salin data

Jika tabel tidak sepenuhnya kosong, data harus disimpan ulang menggunakan tabel sementara tambahan:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Tanda tangan PADA KOMITMEN DROP berarti bahwa pada saat transaksi berakhir, tabel sementara tidak akan ada lagi, dan tidak perlu menghapusnya secara manual dalam konteks koneksi.

Karena kami berasumsi bahwa tidak banyak data "langsung", operasi ini akan dilakukan dengan cukup cepat.

Ya, itu saja! Jangan lupa setelah selesai transaksi jalankan ANALISIS untuk menormalkan statistik tabel jika perlu.

Menyusun naskah akhir

Kami menggunakan "python semu" 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;

Apakah mungkin untuk tidak menyalin data untuk kedua kalinya?Pada prinsipnya dimungkinkan jika oid tabel itu sendiri tidak terikat dengan kegiatan lain dari pihak BL atau FK dari pihak 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 tabel sumber dan periksa metriknya:

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 berhasil! Tabel telah menyusut 50 kali lipat dan semua UPDATE berjalan cepat kembali.

Sumber: www.habr.com

Tambah komentar