DBA: ketika VACUUM lewat - kami membersihkan tabel secara manual

VACUUM dapat "membersihkan" dari tabel di PostgreSQL hanya yang tidak dapat dilihat oleh siapa pun - yaitu, tidak ada satu pun permintaan aktif yang dimulai lebih awal dari catatan-catatan ini diubah.

Tetapi jika ada jenis yang tidak menyenangkan (beban OLAP jangka panjang pada basis OLTP)? Bagaimana cara membersihkan meja yang aktif berubah dikelilingi oleh pertanyaan panjang dan tidak menginjak menyapu?

Kami menyebarkan menyapu

Pertama, kami menentukan apa itu dan bagaimana masalah yang ingin kami selesaikan dapat muncul.

Biasanya situasi ini terjadi di atas meja yang relatif kecil , tetapi di mana ada banyak perubahan . Biasanya, ini adalah penghitung / agregat / peringkat yang berbeda , di mana UPDATE sering-sering dieksekusi, atau antrian penyangga untuk memproses semacam aliran acara yang terus berjalan, catatan tentang yang selalu INSERT / HAPUS.

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, dalam koneksi yang berbeda, permintaan lama dimulai, mengumpulkan beberapa statistik kompleks, tetapi tidak mempengaruhi tabel kami :

 SELECT pg_sleep(10000); 

Sekarang kami memperbarui nilai salah satu penghitung berkali-kali. Untuk kemurnian percobaan, kami akan melakukan ini dalam transaksi terpisah menggunakan dblink , karena ini 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 terjadi Mengapa, bahkan untuk UPDATE paling sederhana dari satu catatan , runtime terdegradasi sebanyak 7 kali - dari 0,524 ms ke 3,808 ms? Dan peringkat kami sedang dibangun lebih lambat dan lebih lambat.

MVCC yang harus disalahkan

Ini semua tentang mekanisme MVCC , yang memaksa permintaan untuk melihat semua versi catatan sebelumnya. Jadi mari kita bersihkan meja kita dari 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, tidak ada yang perlu dibersihkan! Permintaan paralel mengganggu kita - setelah semua, suatu hari dia mungkin ingin merujuk ke versi ini (bagaimana jika?), Dan mereka harus tersedia baginya. Dan bahkan VACUUM FULL tidak akan membantu kami.

"Menjepit" meja

Tetapi kami tahu pasti bahwa tabel kami tidak membutuhkan kueri kami. Oleh karena itu, mari kita coba mengembalikan kinerja sistem ke kerangka kerja yang memadai, setelah membuang semuanya berlebihan dari tabel - setidaknya "secara manual", sejak VACUUM berlalu.

Untuk membuatnya lebih jelas, mari kita perhatikan contoh tabel buffer. Artinya, ada aliran INSERT / DELETE besar, dan kadang-kadang tabel benar-benar kosong. Tetapi jika tidak kosong di sana, kita harus menyimpan isinya saat ini .

# 0: evaluasi situasinya

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

Kami merumuskan kriteria - "saatnya untuk bertindak", jika:

• VACUUM sudah berjalan lama
  Kami mengharapkan beban yang besar, jadi biarlah 60 detik dari VACUUM [otomatis] terakhir.
• ukuran tabel fisik lebih besar dari target
  Kami mendefinisikannya sebagai jumlah halaman berlipat ganda (blok 8KB) relatif terhadap ukuran minimum - 1 blk per heap + 1 blk untuk masing-masing indeks - untuk tabel yang berpotensi kosong. Jika kami berharap bahwa sejumlah data tertentu akan selalu tetap dalam buffer "secara normal", masuk akal untuk mengencangkan formula ini.

# 1: Pokoknya VACUUM

Kami tidak dapat mengetahui sebelumnya apakah permintaan paralel benar-benar menghambat kami - persis berapa banyak rekaman yang “kedaluwarsa” sejak awal. Oleh karena itu, ketika kami memutuskan untuk memproses tabel tersebut, bagaimanapun, Anda harus terlebih dahulu menjalankan VACUUM di atasnya - itu, tidak seperti VACUUM FULL, tidak mengganggu proses paralel dengan data baca-tulis.

Pada saat yang sama, dia dapat segera membersihkan sebagian besar dari apa yang ingin kita hapus. Ya, dan permintaan selanjutnya untuk tabel ini akan diberikan kepada kami dalam "hot cache" , yang akan mengurangi durasinya - dan, karenanya, total waktu untuk memblokir orang lain dengan transaksi penayangan 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 pada pemrosesan - hanya dengan melakukan TRUNCATE

Kerjanya dengan cara yang sama seperti perintah DELETE tanpa syarat untuk setiap tabel, tetapi jauh lebih cepat, karena sebenarnya tidak memindai tabel. Selain itu, ia segera membebaskan ruang disk, jadi tidak perlu melakukan operasi VACUUM setelahnya.

Apakah Anda perlu mengatur ulang penghitung urutan tabel (RESTART IDENTITY) secara bersamaan - putuskan sendiri.

# 3: Semuanya - pada gilirannya!

Karena kami bekerja dalam kondisi daya saing tinggi, sementara kami memeriksa di sini untuk tidak adanya entri dalam tabel, seseorang sudah bisa menulis sesuatu di sana. Kita tidak boleh kehilangan informasi ini, jadi apa? Itu benar, itu harus dilakukan agar tidak ada yang bisa merekam dengan pasti.

Untuk melakukan ini, kita perlu mengaktifkan isolasi SERIALIZABLE untuk transaksi kita (ya, di sini kita memulai transaksi) dan mengunci tabel "erat":

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

Tingkat pemblokiran ini disebabkan oleh operasi yang ingin kami lakukan di atasnya.

# 4: Konflik Kepentingan

Kami datang ke sini dan kami ingin "mengunci" tablet - dan jika seseorang aktif pada saat itu, misalnya, membacanya? Kami akan "menunggu" untuk mengantisipasi pemblokiran ini, sementara orang lain yang ingin membaca sudah terkubur di dalam kita ...

Untuk mencegah hal ini terjadi, “korbankan diri kita sendiri” - jika kita masih tidak bisa mendapatkan kunci untuk waktu tertentu (diizinkan kecil), maka kita akan mendapatkan pengecualian dari database, tetapi setidaknya kita tidak akan mengganggu sisanya.

Untuk melakukan ini, setel variabel sesi lock_timeout (untuk versi 9.3+) atau / dan statement_timeout . Hal utama yang perlu diingat adalah bahwa nilai statement_timeout hanya berlaku dari pernyataan berikutnya. Yaitu, seperti ini dalam mengelem, itu tidak akan bekerja :

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

Agar tidak berurusan dengan pemulihan selanjutnya dari nilai "lama" variabel, kami menggunakan formulir SET LOCAL , yang membatasi ruang lingkup pengaturan untuk transaksi saat ini.

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: menyalin data

Jika tabel ternyata tidak sepenuhnya kosong, data harus disimpan kembali melalui label sementara tambahan:

 CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl; 

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

Karena kami berasumsi bahwa tidak ada banyak data "langsung", operasi ini harus berjalan cukup cepat.

Yah, itu saja! Ingatlah untuk menjalankan ANALYZE setelah transaksi selesai untuk menormalkan statistik tabel, jika perlu.

Kami mengumpulkan skrip terakhir

Kami menggunakan "pseudo python" seperti:

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

Mari kita jalankan skrip pada tabel sumber dan periksa 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 berhasil! Tabel telah menyusut sebanyak 50 kali, dan semua UPDATE berjalan cepat lagi.