Apa yang harus saya lakukan (dan jelas tidak) jika Anda perlu
memperbarui sejumlah besar catatan dalam tabel PostgreSQL "multi-juta" yang digunakan secara aktif - menginisialisasi nilai bidang baru atau memperbaiki kesalahan dalam catatan yang ada? Dan pada saat yang sama menghemat waktu Anda dan tidak kehilangan uang perusahaan karena downtime.
Siapkan data uji:
CREATE TABLE tbl(k text, v integer); INSERT INTO tbl SELECT chr(ascii('a'::text) + (random() * 26)::integer) k , (random() * 100)::integer v FROM generate_series(1, 1000000) i;
Misalkan kita hanya ingin meningkatkan nilai v dengan 1 untuk semua catatan dengan k dalam kisaran 'q' .. 'z'.
Tetapi, sebelum memulai eksperimen, kami akan menyimpan set data asli agar mendapatkan hasil "bersih" setiap kali:
CREATE TABLE _tbl AS TABLE tbl;
UPDATE: satu untuk semua, dan semua untuk satu
Opsi termudah yang langsung terlintas dalam pikiran adalah melakukan segala sesuatu "dalam satu PEMBARUAN":
UPDATE tbl SET v = v + 1 WHERE k BETWEEN 'q' AND 'z';
[lihat menjelaskan.tensor.ru]Tampaknya, operasi yang agak sederhana, pada garis yang sepenuhnya "pendek" memerlukan waktu lebih dari 2,5 detik. Dan jika ekspresi Anda lebih rumit, garis lebih otentik, ada lebih banyak catatan, dan bahkan beberapa pemicu campur tangan - waktu dapat meningkat bahkan hingga menit, tetapi hingga jam. Misalkan Anda siap menunggu, dan seluruh sistem Anda, terikat pada basis ini, jika memiliki beban OLTP aktif?
Masalahnya adalah bahwa begitu UPDATE mendapatkan catatan tertentu, ia
memblokirnya sampai akhir eksekusi . Jika secara bersamaan dengan catatan yang sama ia ingin mengerjakan UPDATE "spot" yang diluncurkan secara parallelly, ia masih akan
"mengait" saat menunggu blok untuk permintaan pembaruan, dan akan melorot hingga akhir pekerjaannya.
©
wumo.com/wumoSkenario kasus terburuk adalah sistem web, di mana koneksi ke database dibuat sesuai kebutuhan - setelah semua, koneksi "menggantung" seperti itu menumpuk dan akan menghabiskan sumber daya baik dari database dan klien jika Anda tidak membuat mekanisme pertahanan terpisah dari ini.
Transaksi terpisah
Secara umum, semuanya tidak terlalu baik jika semuanya dilakukan dalam satu permintaan. Ya, dan bahkan jika kami membagi satu UPDATE besar menjadi beberapa yang kecil, tetapi membiarkan semuanya bekerja
dalam satu transaksi , masalah dengan penguncian akan tetap sama, karena catatan yang dapat diubah dikunci hingga akhir seluruh transaksi.
Jadi, kita perlu membagi satu transaksi besar menjadi beberapa. Untuk melakukan ini, kita dapat menggunakan sarana eksternal dan menulis semacam skrip yang menghasilkan transaksi terpisah, atau menggunakan peluang yang dapat disediakan oleh basis data itu sendiri.
PANGGILAN dan manajemen transaksi
Dimulai dengan PostgreSQL 11,
dimungkinkan untuk mengelola transaksi tepat di dalam kode prosedural:
Dalam prosedur yang disebut oleh perintah CALL, serta dalam blok kode anonim (dalam perintah DO), Anda dapat menyelesaikan transaksi dengan mengeksekusi COMMIT dan ROLLBACK. Setelah transaksi selesai dengan perintah-perintah ini, yang baru akan dimulai secara otomatis.
Tetapi versi ini jauh dari semua orang, dan bekerja dengan CALL memiliki keterbatasan. Oleh karena itu, kami akan mencoba menyelesaikan masalah kami tanpa cara eksternal, dan agar dapat berfungsi pada semua versi saat ini, dan bahkan dengan perubahan minimal pada server itu sendiri - sehingga tidak perlu untuk mengkompilasi dan memulai kembali apa pun.
Untuk alasan yang sama, kami tidak akan mempertimbangkan opsi untuk mengatur
transaksi otonom melalui pg_background .
Mengelola koneksi "di dalam" pangkalan
PostgreSQL secara historis menggunakan metode yang berbeda untuk
meniru transaksi otonom , menghasilkan koneksi tambahan yang terpisah - melalui bahasa prosedural tambahan atau
modul dblink standar. Keuntungan dari yang terakhir adalah bahwa secara default itu termasuk dalam sebagian besar distribusi, dan hanya satu perintah yang diperlukan untuk mengaktifkannya dalam database:
CREATE EXTENSION dblink;
"... dan banyak, banyak anak-anak menjijikkan yang dibawa"
Tetapi sebelum membuat pengikatan dblink, pertama mari kita mencari tahu bagaimana "pengembang reguler" memecah dataset besar, yang perlu dia perbarui, menjadi yang kecil.
BATAS naif ... OFFSET
Gagasan pertama adalah melakukan pencarian "pagination":
"Ayo pilih seribu catatan berikutnya setiap kali" dengan meningkatkan OFFSET dalam setiap permintaan baru:
UPDATE tbl T SET v = Tv + 1 FROM ( SELECT k , v FROM tbl WHERE k BETWEEN 'q' AND 'z' ORDER BY
Sebelum menguji kinerja solusi ini, kami akan mengembalikan dataset:
TRUNCATE TABLE tbl; INSERT INTO tbl TABLE _tbl;
Seperti yang kita lihat dalam rencana di atas, kita perlu memperbarui sekitar 384K catatan. Oleh karena itu, mari kita segera melihat bagaimana pembaruan akan dilakukan lebih dekat sampai akhir -
di wilayah iterasi 300 dari 1000 entri :
[lihat menjelaskan.tensor.ru]Oh ... Memutakhirkan sampel pada akhir
seluruh catatan 1K akan dikenakan biaya hampir sebanyak waktu
seluruh versi asli !
Ini bukan pilihan kita. Itu masih bisa digunakan entah bagaimana jika Anda mendapatkan beberapa iterasi dan nilai OFFSET kecil. Karena
LIMIT X OFFSET Y untuk database setara dengan "
kurangi / pilih / bentuk catatan X + Y pertama, dan kemudian lemparkan Y pertama ke tempat sampah ", yang untuk nilai besar Y terlihat tragis.
Sebenarnya, metode
ini tidak dapat diterapkan sama sekali ! Kami tidak hanya mengandalkan nilai yang diperbarui untuk pemilihan, kami juga berisiko melewatkan bagian dari catatan, dan memperbarui bagian lainnya dua kali jika blok dengan kunci yang sama sampai ke batas halaman:
Dalam contoh ini, kami memperbarui catatan hijau dua kali, dan catatan merah tidak pernah. Hanya karena dengan nilai yang sama dari kunci pengurutan, urutan catatan itu sendiri di dalam blok semacam itu tidak diperbaiki.
ORDER Sedih DENGAN ... BATAS
Mari kita sedikit memodifikasi tugas - tambahkan bidang baru di mana kita akan menulis nilai kita v + 1:
ALTER TABLE tbl ADD COLUMN x integer;
Harap dicatat bahwa desain ini bekerja hampir secara instan, tanpa menulis ulang seluruh tabel. Tetapi jika Anda menambahkan nilai DEFAULT, maka - hanya
dari versi ke-11 .
Sudah diajarkan oleh pengalaman pahit, mari kita segera membuat indeks di mana hanya entri yang tidak diinisialisasi yang akan tetap:
CREATE INDEX CONCURRENTLY ON tbl(k, v) WHERE x IS NULL;
Indeks CONCURRENTLY tidak memblokir pekerjaan baca-tulis dengan tabel, sementara itu perlahan-lahan bergulir bahkan ke dataset besar.
Sekarang idenya adalah
"Mari kita pilih dari indeks ini setiap kali hanya seribu catatan pertama " :
UPDATE tbl T SET x = Tv + 1 FROM ( SELECT k, v FROM tbl WHERE k BETWEEN 'q' AND 'z' AND x IS NULL ORDER BY k, v LIMIT 1000
[lihat menjelaskan.tensor.ru]Sudah jauh lebih baik - durasi setiap transaksi individu sekarang lebih pendek sekitar 6 kali.
Tapi mari kita lihat lagi apa rencana iterasi ke-200 akan berubah menjadi:
Update on tbl t (actual time=530.591..530.591 rows=0 loops=1) Buffers: shared hit=789337 read=1 dirtied=1
Waktu semakin memburuk lagi (hanya 25%), dan buffer meningkat - tetapi mengapa?
Faktanya adalah bahwa
MVCC di PostgreSQL meninggalkan "dead souls" dalam indeks - versi dari catatan yang sudah diperbarui, sekarang tidak lagi cocok untuk indeks. Artinya, dengan hanya mengambil 1000 catatan pertama pada iterasi ke-200, kami
masih memindai , meskipun kemudian kami buang, versi 199K tupel sebelumnya sudah berubah.
Jika iterasi pada kami diperlukan bukan beberapa ratus, tetapi beberapa ratus ribu, maka degradasi akan lebih terlihat dengan setiap eksekusi permintaan berikutnya.
DIPERBARUI menurut segmen
Sebenarnya, mengapa kita begitu terikat pada nilai "1000 rekaman" ini? Lagi pula, kami
tidak memiliki alasan untuk memilih tepat 1000 atau nomor tertentu lainnya. Kami hanya ingin hanya "memotong" seluruh dataset menjadi beberapa, tidak harus sama,
segmen terpisah - jadi mari kita gunakan indeks kami untuk tujuan yang dimaksudkan.
Pasangan yang diindeks (k, v) sangat baik untuk tugas kita. Mari kita membangun kueri sehingga dapat membangun pada pasangan yang diproses terakhir:
WITH kv AS ( SELECT k, v FROM tbl WHERE (k, v) > ($1, $2) AND k BETWEEN 'q' AND 'z' AND x IS NULL ORDER BY k, v LIMIT 1 ) , upd AS ( UPDATE tbl T SET x = Tv + 1 WHERE (Tk, Tv) = (TABLE kv) AND Tx IS NULL RETURNING k, v ) TABLE upd LIMIT 1;
Pada iterasi pertama, cukup bagi kita untuk mengatur parameter kueri ke nilai
"nol" ('', 0) , dan untuk setiap iterasi berikutnya kita mengambil
hasil dari kueri sebelumnya .
[lihat menjelaskan.tensor.ru]Waktu transaksi / kunci kurang dari satu milidetik, tidak ada degradasi dari jumlah iterasi, pemindaian awal penuh dari semua data dalam tabel tidak diperlukan. Hebat!
Puting versi final dengan dblink DO $$ DECLARE k text = ''; v integer = 0; BEGIN PERFORM dblink_connect('dbname=' || current_database() || ' port=' || current_setting('port'));
Keuntungan tambahan dari metode ini adalah kemampuan untuk menghentikan eksekusi skrip ini kapan saja, dan kemudian melanjutkan dari titik yang diinginkan.
Perhitungan kompleks dalam UPDATE
Saya akan menyebutkan situasi secara terpisah dengan perhitungan sulit dari nilai yang diberikan - ketika Anda perlu menghitung sesuatu dari tabel tertaut.
Waktu yang dihabiskan untuk komputasi juga meningkatkan durasi transaksi. Oleh karena itu, opsi terbaik adalah
mengambil proses penghitungan nilai-nilai ini di luar UPDATE.
Misalnya, kami ingin mengisi bidang baru x dengan jumlah catatan yang memiliki nilai yang sama (k, v). Mari kita buat tabel "sementara", yang generasinya tidak memaksakan kunci tambahan:
CREATE TABLE tmp AS SELECT k, v, count(*) x FROM tbl GROUP BY 1, 2; CREATE INDEX ON tmp(k, v);
Sekarang kita dapat mengulangi sesuai dengan model yang dijelaskan di atas sesuai dengan tabel ini, memperbarui target:
UPDATE tbl T SET x = Sx FROM tmp S WHERE (Tk, Tv) = (Sk, Sv) AND (Sk, Sv) = ($1, $2);
Seperti yang Anda lihat, tidak diperlukan perhitungan yang rumit.
Hanya ingat untuk menghapus tabel tambahan nanti.