Mendiagnosis bloat tabel dan indeks

Anda dapat menggunakan Multiversion Concurrency Control (MVCC) PostgreSQL untuk membantu menjaga integritas data. MVCC PostgreSQL beroperasi dengan menyimpan salinan internal baris yang diperbarui atau dihapus (juga disebut tuple) sampai transaksi dikomit atau di-rollback. Salinan internal yang disimpan ini tidak terlihat oleh pengguna. Namun, bloat tabel dapat terjadi ketika salinan yang tidak terlihat tersebut tidak dibersihkan secara teratur oleh utilitas VACUUM atau AUTOVACUUM. Bloat tabel yang tidak terpantau dapat menimbulkan peningkatan biaya penyimpanan dan memperlambat kecepatan pemrosesan Anda.

Dalam banyak kasus, pengaturan default untuk VACUUM atau AUTOVACUUM di Aurora cukup untuk menangani bloat tabel yang tidak diinginkan. Namun, Anda sebaiknya memeriksa bloat jika aplikasi Anda mengalami kondisi berikut:

• Memproses sejumlah besar transaksi dalam waktu yang relatif singkat di antara proses VACUUM.

• Beperforma buruk dan kehabisan penyimpanan.

Untuk memulai, kumpulkan informasi paling akurat tentang jumlah ruang yang digunakan oleh tuple nonaktif dan jumlah yang dapat Anda pulihkan dengan membersihkan bloat tabel dan indeks. Untuk melakukannya, gunakan ekstensi pgstattuple untuk mengumpulkan statistik tentang klaster Aurora Anda. Untuk informasi selengkapnya, lihat pgstattuple. Hak akses untuk menggunakan ekstensi pgstattuple dibatasi pada peran pg_stat_scan_tables dan superuser basis data.

Untuk membuat ekstensi pgstattuple di Aurora, hubungkan sesi klien ke klaster, misalnya, psql atau pgAdmin, lalu gunakan perintah berikut:

CREATE EXTENSION pgstattuple;

Buat ekstensi di setiap basis data yang ingin Anda buat profilnya. Setelah membuat ekstensi, gunakan antarmuka baris perintah (CLI) untuk mengukur jumlah ruang yang tidak dapat digunakan yang dapat Anda klaim kembali. Sebelum mengumpulkan statistik, ubah grup parameter klaster dengan menetapkan AUTOVACUUM ke 0. Pengaturan 0 akan mencegah Aurora membersihkan tuple nonaktif secara otomatis yang ditinggalkan oleh aplikasi Anda, yang dapat memengaruhi keakuratan hasil. Masukkan perintah berikut untuk membuat tabel sederhana:

postgres=> CREATE TABLE lab AS SELECT generate_series (0,100000);
SELECT 100001

Dalam contoh berikut, kita menjalankan kueri dengan AUTOVACUUM diaktifkan untuk klaster DB. dead_tuple_count adalah 0, yang menunjukkan bahwa AUTOVACUUM telah menghapus data atau tuple usang dari basis data PostgreSQL.

Untuk menggunakan pgstattuple dalam mengumpulkan informasi tentang tabel, tentukan nama tabel atau pengidentifikasi objek (OID) dalam kueri:

postgres=> SELECT * FROM pgstattuple('lab');

    
    
table_len  | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
3629056    | 100001      | 2800028   | 77.16         | 0                | 0              | 0                  | 16616      | 0.46
(1 row)
        
    

Dalam kueri berikut, kita menonaktifkan AUTOVACUUM dan memasukkan perintah yang menghapus 25.000 baris dari tabel. Akibatnya, dead_tuple_count meningkat menjadi 25000.

postgres=> DELETE FROM lab WHERE generate_series < 25000;    

DELETE 25000  
   

SELECT * FROM pgstattuple('lab');
    

        
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
3629056 | 75001 | 2100028 | 57.87 | 25000 | 700000 | 19.29 | 16616 | 0.46
(1 row)        
    
    

Untuk mengklaim kembali tuple nonaktif, mulailah proses VACUUM.

Mengamati bloat tanpa menghentikan aplikasi Anda

Pengaturan pada klaster Aurora dioptimalkan untuk memberikan praktik terbaik untuk sebagian besar beban kerja. Namun, Anda sebaiknya mengoptimalkan klaster agar lebih sesuai dengan aplikasi dan pola penggunaan Anda. Dalam hal ini, Anda dapat menggunakan ekstensi pgstattuple tanpa menghentikan aplikasi yang sibuk. Untuk melakukannya, lakukan langkah-langkah berikut ini:

1. Kloning instans Aurora Anda.

2. Ubah file parameter untuk menonaktifkan AUTOVACUUM di klon.

3. Lakukan kueri pgstattuple saat menguji klon dengan beban kerja sampel atau dengan pgbench, yang merupakan program untuk menjalankan pengujian tolok ukur di PostgreSQL. Untuk informasi selengkapnya, lihat pgbench.

Setelah menjalankan aplikasi Anda dan melihat hasilnya, gunakan pg_repack atau VACUUM FULL pada salinan yang dipulihkan dan bandingkan perbedaannya. Jika Anda melihat penurunan yang signifikan pada dead_tuple_count, dead_tuple_len, atau dead_tuple_percent, sesuaikan jadwal vacuum pada klaster produksi Anda untuk meminimalkan bloat.

Menghindari bloat di tabel sementara

Jika aplikasi Anda membuat tabel sementara, pastikan bahwa aplikasi Anda menghapus tabel sementara ketika sudah tidak lagi diperlukan. Proses autovacuum tidak menemukan tabel sementara. Jika tidak terpantau, tabel sementara dapat dengan cepat membuat bloat basis data. Selain itu, bloat dapat meluas ke tabel sistem, yang merupakan tabel internal yang melacak objek dan atribut PostgreSQL, seperti pg_attribute dan pg_depend.

Ketika tabel sementara tidak lagi diperlukan, Anda dapat menggunakan pernyataan TRUNCATE untuk mengosongkan tabel dan membebaskan ruang. Kemudian, lakukan vacuum manual pada tabel pg_attribute dan pg_depend. Dengan melakukan vacuum pada tabel ini, akan dipastikan bahwa pembuatan dan pemotongan/penghapusan tabel sementara secara terus-menerus tidak akan menambahkan tuple dan berkontribusi pada bloat sistem.

Anda dapat menghindari masalah ini saat membuat tabel sementara dengan menyertakan sintaksis berikut yang menghapus baris baru saat konten dikomit:

CREATE TEMP TABLE IF NOT EXISTS table_name(table_description) ON COMMIT DELETE ROWS;

Klausa ON COMMIT DELETE ROWS memotong tabel sementara ketika transaksi dikomit.

Menghindari bloat dalam indeks

Saat Anda mengubah bidang yang diindeks dalam tabel, pembaruan indeks akan menghasilkan satu atau beberapa tuple nonaktif dalam indeks tersebut. Secara default, proses autovacuum membersihkan bloat dalam indeks, tetapi pembersihan tersebut menggunakan sejumlah besar waktu dan sumber daya. Untuk menentukan preferensi pembersihan indeks saat Anda membuat tabel, sertakan klausa vacuum_index_cleanup. Secara default, pada waktu pembuatan tabel, klausa ini diatur ke AUTO, yang berarti bahwa server akan memutuskan apakah indeks Anda memerlukan pembersihan saat melakukan vaccum pada tabel. Anda dapat mengatur klausa ini ke ON untuk mengaktifkan pembersihan indeks untuk tabel tertentu, atau OFF untuk menonaktifkan pembersihan indeks untuk tabel tersebut. Ingat, menonaktifkan pembersihan indeks mungkin menghemat waktu, tetapi berpotensi menyebabkan indeks mengalami bloat.

Anda dapat mengontrol pembersihan indeks secara manual saat Anda melakukan VACUUM pada tabel di baris perintah. Untuk melakukan vacuum pada tabel dan menghapus tuple nonaktif dari indeks, sertakan klausa INDEX_CLEANUP dengan nilai ON dan nama tabel:

acctg=> VACUUM (INDEX_CLEANUP ON) receivables;
        
INFO: aggressively vacuuming "public.receivables"
VACUUM

Untuk melakukan vacuum pada tabel tanpa membersihkan indeks, tentukan nilai OFF:

acctg=> VACUUM (INDEX_CLEANUP OFF) receivables;
        
INFO: aggressively vacuuming "public.receivables"
VACUUM