Mengukur dan mengatasi pembengkakan tabel dan indeks

Pembengkakan tabel dan indeks terjadi saat operasi vacuum tidak mengimbangi laju Bahasa Pengolahan Data (DML) terhadap tabel. Pembengkakan tabel dan indeks tidak dapat dihindari dan umumnya dapat diterima karena transaksi dapat menggunakan kembali ruang tersebut. Bloat parah menyebabkan masalah performa, seperti waktu kueri yang lebih lambat, peningkatan I/O, penggunaan cache dan buffer bersama yang kurang efisien, serta peningkatan biaya penyimpanan.

Cara terbaik untuk memperkirakan pembengkakan adalah dengan menggunakan modul pgstattuple. Namun, penggunaan pgstattuple kurang efisien jika ekstensi harus membaca semua halaman relasi. Jika tidak dapat menggunakan pgstattuple, Anda dapat menggunakan skrip lain untuk memperkirakan pembengkakan, meskipun pendekatan ini memberikan akurasi yang lebih rendah.

Membaca output pgstattuple

Saat Anda menggunakan pgstattuple untuk melaporkan karakteristik penyimpanan fisik tabel atau indeks, outputnya dalam format berikut:

> SELECT * FROM pgstattuple('large_table');
-[ RECORD 1 ]------+-----------
table_len          | 7431888896
tuple_count        | 66666666
tuple_len          | 2796296271
tuple_percent      | 37.63
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 3873153652
free_percent       | 52.12

Berikut deskripsi output:

table_len (7431888896)

Total ukuran tabel dalam byte. Bagian ini mencakup semua tuple aktif, tuple tidak aktif, dan ruang kosong. Dalam hal ini, large_table menempati sekitar 7.088 GB.

tuple_count (66666666)

Mewakili jumlah tuple aktif—baris yang terlihat dan aktif—yang ada dalam tabel. Tabel ini berisi sekitar 66 juta baris aktif.

tuple_len (2796296271)

Total panjang dalam byte dari semua tuple live yang digabungkan, yang kira-kira 2,6 GB.

tuple_percent (37.63)

Persentase table_len yang ditempati oleh tuple aktif. Anda menghitungnya sebagai berikut:

(tuple_len/table_len) ∗ 100 = (2796296271/7431888896) ∗ 100 ≈ 37.63 persen

Output ini menunjukkan bahwa data aktif menggunakan kurang dari 40% ruang disk tabel.

dead_tuple_count (0)

Jumlah tuple tidak aktif—baris yang belum dihapus oleh operasi pembersihan setelah pembaruan atau penghapusan. Nilai 0 berarti tabel bersih dan tidak memiliki tuple tidak aktif yang menunggu untuk diklaim kembali. Hal ini sering menunjukkan bahwa VACUUM, atau autovacuum, berjalan secara efisien di tabel.

dead_tuple_len (0)

Total panjang dalam byte dari semua tuple yang tidak aktif. Sesuai dengan dead_tuple_count, nilai 0 berarti baris yang tidak aktif tidak menggunakan ruang.

dead_tuple_percent (0)

Persentase table_len yang ditempati oleh tuple tidak aktif. Anda menghitungnya sebagai berikut:

(dead_tuple_len/table_len) ∗ 100 = (0/7431888896) ∗ 100 = 0 persen

free_space (3873153652)

Nilai ini menunjukkan jumlah ruang kosong di halaman data tabel. Anda dapat menggunakan kembali ruang ini untuk penyisipan atau pembaruan baru tanpa memerlukan perluasan tabel. Hal ini menunjukkan jumlah ruang kosong yang signifikan, sekitar 3,7 GB.

free_percent (52.12)

Nilai ini mewakili persentase table_len yang merupakan ruang kosong. Anda menghitungnya sebagai berikut:

(free_space/table_len) ∗ 100 = (3873153652/7431888896) ∗ 100 ≈ 52.12 persen

Memperkirakan pembengkakan menggunakan kueri

Anda dapat memperkirakan pembengkakan menggunakan kueri terhadap tabel katalog sistem, meskipun metode ini kurang akurat dibandingkan menggunakan ekstensi seperti pgstattuple. Jika Anda menggunakan skrip seperti pemeriksaan pembengkakan tabel PostgreSQL, statistik tabel Anda harus terbaru, jika tidak, perkiraan akan tidak akurat.

Saat Anda membandingkan output kueri dengan modul pgstattuple, perkiraan dapat dibandingkan, tetapi bukan pengukuran pasti pembengkakan. Modul ini masih menjadi pengukur pembengkakan yang lebih akurat karena kueri SQL mengandalkan mesin statistik kumulatif PostgreSQL, sedangkan modul membaca halaman data yang sebenarnya.

Contoh berikut membandingkan output dari dua metode berbeda untuk menganalisis pembengkakan tabel di PostgreSQL untuk tabel bernama large_table.

> SELECT * FROM pgstattuple('large_table');
-[ RECORD 1 ]------+-----------
table_len          | 2475892736
tuple_count        | 22000000
tuple_len          | 922925931
tuple_percent      | 37.28
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 1293696900
free_percent       | 52.25

Vs.

-[ RECORD 1 ]+------------
databasename | postgres
schemaname   | public
tablename    | large_table
can_estimate | t
est_rows     | 21965400
pct_bloat    | 54
mb_bloat     | 1269.24
table_mb     | 2361.195

Penggunaan alamat yang berlebihan

Anda dapat mengelola bloat dengan cara berikut, yang masing-masing memiliki kelebihan dan kekurangan, sehingga memerlukan perencanaan yang cermat:

• Lakukan penyedotan debu secara menyeluruh. Operasi ini memerlukan penguncian tabel penuh selama durasi vakum, dan memerlukan periode nonaktif.
• Pindahkan data ke tabel baru. Untuk menggunakan metode ini, Anda harus menghentikan perubahan tabel untuk memastikan bahwa perubahan tidak ditransfer ke relasi baru.
• Gunakan pg_squeeze. Ekstensi ini mengurangi bloat online. Ekstensi membuat tabel bayangan, memasukkan semua data, lalu mempertahankan perubahan tabel menggunakan slot replikasi hingga langkah terakhir, yaitu mengganti tabel yang membengkak dengan tabel baru yang tidak membengkak. Kemudian, ia meminta penguncian tabel singkat untuk memindahkan tabel yang tidak membengkak guna menggantikan tabel yang membengkak. Metode ini memerlukan dua kali ruang objek selama durasi operasi, tetapi paling tidak mengganggu operasi normal.