Locks in Postgres: 7 Tips untuk Bekerja dengan Locks

Halo lagi! Selasa depan, aliran baru dimulai pada kursus "DBMS Relasional" , jadi kami terus menerbitkan materi yang berguna tentang topik tersebut. Ayo pergi.



Minggu lalu saya menulis tentang akses kompetitif di Postgres , tim mana yang saling memblokir, dan bagaimana Anda dapat mendiagnosis tim yang diblokir. Tentu saja, setelah diagnosis, Anda mungkin perlu perawatan. Dengan Postgres, Anda dapat menembak diri sendiri di kaki, tetapi Postgres juga menawarkan cara untuk tidak mencapai ujung. Berikut adalah beberapa tips penting tentang bagaimana dan bagaimana tidak melakukannya yang menurut kami berguna ketika bekerja dengan pengguna untuk bermigrasi dari database Postgres tunggal mereka ke Citus atau ketika membuat aplikasi analitik waktu-nyata yang baru.

1. Jangan pernah menambahkan kolom dengan nilai default

Aturan Golden PostgreSQL: Saat menambahkan kolom ke tabel di lingkungan produksi, jangan pernah menentukan nilai default .

Menambahkan kolom memerlukan kunci meja yang sangat agresif, yang menghalangi baik membaca dan menulis. Jika Anda menambahkan kolom dengan nilai default, PostgreSQL akan menimpa seluruh tabel untuk mengisi nilai default untuk setiap baris, yang mungkin memakan waktu beberapa jam dalam tabel besar. Pada saat yang sama, semua permintaan akan diblokir, sehingga basis data Anda tidak akan tersedia.

Jangan lakukan ini:

--     ,       (?) ALTER TABLE items ADD COLUMN last_update timestamptz DEFAULT now(); 

Lakukan lebih baik seperti ini:

 -- select, update, insert  delete ,      () ALTER TABLE items ADD COLUMN last_update timestamptz; -- select  insert ,  update  delete ,    UPDATE items SET last_update = now(); 

Atau, lebih baik lagi, hindari memperbarui dan delete kunci untuk waktu yang lama, memperbarui dalam batch kecil, misalnya:

 do { numRowsUpdated = executeUpdate( "UPDATE items SET last_update = ? " + "WHERE ctid IN (SELECT ctid FROM items WHERE last_update IS NULL LIMIT 5000)", now); } while (numRowsUpdate > 0); 

Dengan cara ini Anda dapat menambah dan mengisi kolom baru dengan gangguan minimal pada pengguna Anda.

2. Waspadalah terhadap antrian kunci, gunakan batas waktu

Setiap kunci di PostgreSQL memiliki prioritas. Jika transaksi B mencoba untuk memegang kunci yang sudah dipegang oleh transaksi A dengan tingkat kunci yang saling bertentangan, transaksi B akan menunggu dalam antrian kunci. Sekarang sesuatu yang menarik terjadi: jika transaksi C lain tiba, ia harus memeriksa tidak hanya konflik dengan A, tetapi juga dengan transaksi B dan transaksi lainnya dalam antrian kunci.

Ini berarti bahwa bahkan jika perintah DDL Anda dapat mengeksekusi dengan sangat cepat, perintah itu dapat berada dalam antrian untuk waktu yang lama, menunggu permintaan selesai, dan permintaan yang berjalan setelah itu akan diblokir di belakangnya .

Jika Anda dapat menemukan kueri SELECT panjang terhadap sebuah tabel, jangan lakukan ini:

 ALTER TABLE items ADD COLUMN last_update timestamptz; 

Lebih baik lakukan ini:

 SET lock_timeout TO '2s' ALTER TABLE items ADD COLUMN last_update timestamptz; 

Jika lock_timeout diatur lock_timeout perintah DDL tidak akan dieksekusi jika menunggu kunci dan, dengan demikian, memblokir permintaan selama lebih dari 2 detik. Kelemahannya adalah bahwa ALTER TABLE mungkin tidak dieksekusi, tetapi Anda dapat mencoba lagi nanti. Anda dapat meminta pg_stat_activity untuk melihat apakah Anda memiliki pertanyaan panjang sebelum menjalankan perintah DDL.

3. Gunakan pembuatan indeks non-blocking

Aturan emas lain dari PostgreSQL: selalu gunakan pembuatan indeks non-blocking.
Membuat indeks untuk dataset besar dapat memakan waktu berjam-jam atau bahkan berhari-hari, dan perintah CREATE INDEX biasa mengunci semua rekaman selama durasi perintah. Terlepas dari kenyataan bahwa itu tidak memblokir SELECT, itu masih sangat buruk, dan ada cara yang lebih baik: CREATE INDEX CONCURRENTLY .

Jangan lakukan ini:

 --    CREATE INDEX items_value_idx ON items USING GIN (value jsonb_path_ops); 

Sebaliknya, lakukan ini:

 --    DDL CREATE INDEX CONCURRENTLY items_value_idx ON items USING GIN (value jsonb_path_ops); 

Pembuatan indeks non-blocking memiliki sisi buruk. Jika terjadi kesalahan, itu tidak mundur dan meninggalkan indeks yang tidak lengkap ("tidak valid"). Jika ini terjadi, jangan khawatir, jalankan saja

 DROP INDEX CONCURRENTLY items_value_idx 

dan coba buat lagi.

4. Gunakan kunci agresif selambat mungkin

Ketika Anda perlu menjalankan perintah yang menerima kunci tabel agresif, coba lakukan ini selambat mungkin dalam transaksi sehingga permintaan dapat berlanjut selama mungkin.

Misalnya, jika Anda ingin sepenuhnya mengganti konten tabel. Jangan lakukan ini:

 BEGIN; --     : TRUNCATE items; -  : \COPY items FROM 'newdata.csv' WITH CSV COMMIT; 

Alih-alih, muat data ke tabel baru, lalu ganti yang lama:

 BEGIN; CREATE TABLE items_new (LIKE items INCLUDING ALL); --  : \COPY items_new FROM 'newdata.csv' WITH CSV --     : DROP TABLE items; ALTER TABLE items_new RENAME TO items; COMMIT; 

Ada satu masalah: kami tidak memblokir rekaman dari awal, dan tabel elemen yang lama bisa berubah pada saat kami meresetnya. Untuk mencegah hal ini, kita dapat secara eksplisit mengunci tabel untuk menulis, tetapi tidak untuk membaca:

 BEGIN; LOCK items IN EXCLUSIVE MODE; ... 

Terkadang lebih baik mengambil pemblokiran ke tangan Anda sendiri.

5. Menambahkan kunci utama dengan pemblokiran minimal

Menambahkan kunci utama ke tabel Anda seringkali merupakan ide yang bagus. Misalnya, jika Anda ingin menggunakan replikasi logis atau memigrasi database menggunakan Citus Warp .

Postgres membuatnya sangat mudah untuk membuat kunci utama menggunakan ALTER TABLE , tetapi saat membuat indeks untuk kunci utama, yang bisa memakan banyak waktu jika tabelnya besar, semua permintaan akan diblokir.

 ALTER TABLE items ADD PRIMARY KEY (id); --      

Untungnya, Anda dapat melakukan semua kerja keras terlebih dahulu menggunakan CREATE UNIQUE INDEX CONCURRENTLY , dan kemudian gunakan indeks unik sebagai kunci utama, yang merupakan operasi cepat.

 CREATE UNIQUE INDEX CONCURRENTLY items_pk ON items (id); --   ,     ALTER TABLE items ADD CONSTRAINT items_pk PRIMARY KEY USING INDEX items_pk; --  ,   

Pembagian pembuatan kunci utama menjadi dua tahap praktis tidak mempengaruhi pengguna.

6. Jangan pernah gunakan VACUUM FULL

Pengalaman pengguna postgres terkadang hanya sedikit mengagumkan. Meskipun VACUUM FULL terdengar seperti apa yang ingin Anda lakukan untuk membersihkan "debu" dari database Anda, perintah yang lebih cocok adalah:

 PLEASE FREEZE MY DATABASE FOR HOURS; 

VACUUM FULL menimpa seluruh tabel ke disk, yang dapat memakan waktu berjam-jam atau berhari-hari, dan pada saat yang sama memblokir semua permintaan. Meskipun ada beberapa VACUUM FULL penggunaan yang valid untuk VACUUM FULL , seperti tabel yang dulunya besar, tetapi sekarang kecil dan masih membutuhkan banyak ruang, tetapi ini mungkin bukan pilihan Anda.
Meskipun Anda harus berusaha mengonfigurasi opsi pembersihan otomatis dan menggunakan indeks untuk mempercepat kueri, terkadang Anda dapat menjalankan VACUUM , tetapi BUKAN VACUUM FULL .

7. Hindari kebuntuan dengan mengatur perintah

Jika Anda telah menggunakan PostgreSQL untuk beberapa waktu, kemungkinan Anda telah melihat kesalahan seperti:

 ERROR: deadlock detected DETAIL: Process 13661 waits for ShareLock on transaction 45942; blocked by process 13483. Process 13483 waits for ShareLock on transaction 45937; blocked by process 13661. 

Ini terjadi ketika transaksi bersamaan mengambil kunci yang sama dalam urutan yang berbeda. Sebagai contoh, satu transaksi mengeksekusi perintah berikut.

 BEGIN; UPDATE items SET counter = counter + 1 WHERE key = 'hello'; --    hello UPDATE items SET counter = counter + 1 WHERE key = 'world'; --    world END; 

Pada saat yang sama, transaksi lain mungkin mengeluarkan perintah yang sama, tetapi dalam urutan yang berbeda.

 BEGIN UPDATE items SET counter = counter + 1 WHERE key = 'world'; --    world UPDATE items SET counter = counter + 1 WHERE key = 'hello'; --    hello END; 

Jika blok-blok transaksi ini dieksekusi pada saat yang sama, kemungkinan mereka akan mandek menunggu satu sama lain dan tidak akan pernah berakhir. Postgres mengenali situasi ini dalam sekitar satu detik dan akan membatalkan salah satu transaksi untuk menyelesaikan yang lain. Ketika ini terjadi, Anda harus melihat aplikasi Anda untuk mencari tahu apakah Anda dapat memastikan bahwa transaksi Anda selalu dijalankan dalam urutan yang sama. Jika kedua transaksi mengubah hello terlebih dahulu, maka world , maka transaksi pertama akan mengunci hello kedua pada hello sebelum dapat menangkap kunci lainnya.
Bagikan tips Anda!

Kami harap Anda menemukan rekomendasi ini bermanfaat. Jika Anda memiliki tips lain, silakan tweet @citusdata atau komunitas pengguna Citus aktif kami di Slack .

Kami mengingatkan Anda bahwa dalam beberapa jam akan ada hari terbuka di mana kami akan berbicara secara rinci tentang program untuk kursus yang akan datang.