Sebuah cerita pendek tentang permintaan "berat" dan solusi elegan untuk masalah ini
Baru-baru ini, pada malam hari, peringatan mulai membangunkan kami: tidak ada cukup ruang disk. Kami dengan cepat menemukan bahwa masalahnya ada di tugas ETL.
Tugas ETL dilakukan dalam tabel di mana catatan biner dan dump disimpan. Setiap malam, tugas ini adalah untuk menghapus duplikat dump dan membebaskan ruang.
Untuk mencari dump duplikat, kami menggunakan kueri ini:
id, MIN(id) OVER (PARTITION BY blob ORDER BY id) FROM dumps
Kueri menggabungkan dump yang sama oleh bidang BLOB. Menggunakan fungsi jendela, kita mendapatkan pengidentifikasi penampilan pertama setiap dump. Kemudian dengan permintaan ini, kami menghapus semua dump duplikat.
Permintaan itu dieksekusi untuk beberapa waktu, dan, seperti dapat dilihat dari log, memakan banyak memori. Grafik menunjukkan bagaimana ia mencetak ruang disk kosong setiap malam:
Seiring waktu, permintaan tersebut membutuhkan lebih banyak memori, kegagalan semakin dalam. Dan, melirik rencana eksekusi, kami segera melihat ke mana semuanya berjalan:
Buffers: shared hit=3916, temp read=3807 written=3816 -> Sort (cost=69547.50..70790.83 rows=497332 width=36) (actual time=107.607..127.485 rows=39160) Sort Key: blob, id Sort Method: external merge Disk: 30456kB Buffers: shared hit=3916, temp read=3807 written=3816 -> Seq Scan on dumps (cost=0..8889.32 rows=497332 width=36) (actual time=0.022..8.747 rows=39160) Buffers: shared hit=3916 Execution time: 159.960 ms
Penyortiran memakan banyak memori. Dalam hal pelaksanaan, penyortiran membutuhkan sekitar 30 MB memori dari set data uji.
Kenapa begitu
PostgreSQL mengalokasikan memori untuk hashing dan sortasi. Jumlah memori dikendalikan oleh parameter work_mem . Ukuran default work_mem adalah 4 MB. Jika lebih dari 4 MB diperlukan untuk hashing atau sortasi, PostgreSQL sementara menghabiskan ruang disk.
Permintaan kami mengkonsumsi jelas lebih dari 4 MB, sehingga database menggunakan begitu banyak memori. Kami memutuskan: kami tidak akan terburu-buru, dan tidak meningkatkan parameter dan memperluas penyimpanan. Lebih baik mencari cara lain untuk memangkas memori untuk penyortiran .
Penyortiran ekonomis
"Seberapa banyak penyortiran akan makan tergantung pada ukuran set data dan kunci sortir. Anda tidak dapat mengurangi set data, tetapi ukuran kunci itu mungkin .
Untuk titik referensi, kami mengambil ukuran rata-rata kunci pengurutan:
avg ---------- 780
Setiap kunci memiliki bobot 780. Untuk mengurangi kunci biner, ia dapat di-hash. Di PostgreSQL ada md5 untuk ini (ya, bukan keamanan, tetapi untuk tujuan kita itu akan dilakukan). Mari kita lihat berapa besar BLOB hash dengan md5:
avg ----------- 36
Ukuran hash kunci melalui md5 adalah 36 byte. Kunci hash hanya berbobot 4% dari opsi asli .
Selanjutnya, kami meluncurkan permintaan asli dengan kunci hash:
id, MIN(id) OVER ( PARTITION BY md5(array_to_string(blob, '') ) ORDER BY id) FROM dumps;
Dan rencana implementasi:
Buffers: shared hit=3916 -> Sort (cost=7490.74..7588.64 rows=39160 width=36) (actual time=349.383..353.045 rows=39160) Sort Key: (md5(array_to_string(blob, ''::text))), id Sort Method: quicksort Memory: 4005kB Buffers: shared hit=3916 -> Seq Scan on dumps (cost=0..4503.40 rows=39160 width=36) (actual time=0.055..292.070 rows=39160) Buffers: shared hit=3916 Execution time: 374.125 ms
Dengan kunci hash, permintaan hanya mengkonsumsi 4 megabyte tambahan, yaitu, sedikit lebih dari 10% dari 30 MB sebelumnya. Jadi ukuran kunci sortir sangat memengaruhi seberapa banyak memori yang disortir .
Lebih jauh lagi
Dalam contoh ini, kami hash BLOB menggunakan md5 . Hash yang dibuat dengan MD5 harus memiliki berat 16 byte. Dan kami mendapat lebih banyak:
md5_size ------------- 32
Hash kami persis dua kali lebih besar, karena md5 menghasilkan hash dalam bentuk teks heksadesimal.
Di PostgreSQL, Anda bisa menggunakan MD5 untuk hashing dengan ekstensi pgcrypto . pgcrypto membuat MD5 dari tipe bytea (dalam biner) :
select pg_column_size( digest('foo', 'md5') ) as crypto_md5_size crypto_md5_size --------------- 20
Hash masih 4 byte lebih dari yang seharusnya. Hanya saja tipe bytea menggunakan 4 byte ini untuk menyimpan panjang nilainya, tetapi kami tidak akan membiarkannya begitu saja.
Ternyata tipe uuid dalam PostgreSQL memiliki berat tepat 16 byte dan mendukung nilai arbitrer apa pun, jadi kami menyingkirkan empat byte yang tersisa:
uuid_size --------------- 16
Itu saja. 32 byte dengan md5 berubah menjadi 16 dengan uuid .
Saya memeriksa efek dari perubahan dengan mengambil dataset yang lebih besar. Data itu sendiri tidak dapat ditampilkan, tetapi saya akan membagikan hasilnya:
Seperti yang dapat Anda lihat dari tabel, permintaan bermasalah semula berbobot 300 MB (dan membangunkan kami di tengah malam). Dengan kunci uuid , pengurutan hanya membutuhkan 7 MB.
Pertimbangan Tindak Lanjut
Permintaan dengan kunci penyortiran memori hash mengkonsumsi lebih sedikit, tetapi kerjanya jauh lebih lambat:
Hashing menggunakan lebih banyak CPU, jadi permintaan dengan hash lebih lambat. Tetapi kami mencoba untuk memecahkan masalah dengan ruang disk, terlebih lagi, tugas dilakukan pada malam hari, jadi waktu tidak menjadi masalah. Kami berkompromi untuk menghemat memori.
Ini adalah contoh yang bagus dari fakta bahwa Anda tidak selalu perlu mencoba mempercepat kueri basis data . Lebih baik menggunakan apa yang seimbang dan memeras sumber daya minimum.