Instans ApsaraDB RDS for PostgreSQL merupakan aplikasi besar. Jika instans RDS Anda memproses banyak permintaan, maka akan mengonsumsi sejumlah besar memori, CPU, I/O, dan sumber daya jaringan. Optimalisasi SQL adalah metode efektif untuk meningkatkan kinerja instans. Untuk hasil optimal, identifikasi pernyataan SQL yang paling banyak mengonsumsi sumber daya seperti I/O.
Sumber daya instans mencakup CPU, memori, dan I/O. Anda dapat menggunakan ekstensi pg_stat_statements untuk mengumpulkan statistik konsumsi sumber daya pada instans RDS Anda dan menganalisis pernyataan SQL yang dieksekusi guna mengidentifikasi pernyataan SQL yang paling banyak mengonsumsi CPU, memori, atau I/O.
Topik ini menjelaskan cara membuat ekstensi pg_stat_statements, menganalisis pernyataan SQL dengan konsumsi sumber daya tertinggi, serta mereset statistik konsumsi sumber daya.
Anda dapat mengaktifkan fitur Penjelajah SQL dan Audit untuk mencatat eksekusi pernyataan SQL dan melakukan analisis agregat terhadapnya. Setelah diaktifkan, fitur ini secara otomatis mencatat pernyataan SQL dari kernel database beserta informasi detail seperti eksekusi, akun eksekusi, dan alamat IP tanpa memengaruhi kinerja instans. Untuk informasi lebih lanjut, lihat Gunakan fitur Penjelajah SQL dan Audit.
Eksekusi pernyataan berikut untuk membuat ekstensi pg_stat_statements di instans RDS Anda:
CREATE EXTENSION pg_stat_statements;Statistik konsumsi sumber daya yang dihasilkan oleh ekstensi pg_stat_statements
Anda dapat menanyakan statistik konsumsi sumber daya dari tampilan yang dihasilkan oleh ekstensi pg_stat_statements. Beberapa kondisi filter dalam pernyataan SQL diganti dengan variabel dalam ekstensi pg_stat_statements untuk mengurangi statistik duplikat.
Tampilan yang dihasilkan oleh ekstensi pg_stat_statements menyediakan informasi penting berikut:
Informasi tentang setiap pernyataan SQL, termasuk jumlah eksekusi, durasi total, durasi terpendek, durasi terpanjang, durasi rata-rata, varian durasi, jumlah baris yang dipindai, dikembalikan, dan diproses. Varian durasi digunakan untuk mencerminkan jitter jaringan.
Penggunaan buffer bersama, termasuk rasio hit, rasio miss, jumlah blok data kotor yang dihasilkan, dan jumlah blok data kotor yang dihapus.
Penggunaan buffer lokal, termasuk rasio hit, rasio miss, jumlah blok data kotor yang dihasilkan, dan jumlah blok data kotor yang dihapus.
Penggunaan buffer temp, termasuk jumlah blok data kotor yang dibaca dan dihapus.
Durasi operasi baca dan tulis pada setiap blok data di instans RDS Anda.
Tabel berikut menjelaskan parameter dalam statistik konsumsi sumber daya yang dihasilkan oleh ekstensi pg_stat_statements.
Parameter | Tipe | Contoh | Deskripsi |
userid | oid | pg_authid.oid | OID pengguna yang mengeksekusi pernyataan. |
dbid | oid | pg_database.oid | OID database di mana pernyataan dieksekusi. |
queryid | bigint | Tidak ada | Kode hash internal, dihitung dari pohon parse pernyataan. |
query | text | Tidak ada | Teks pernyataan representatif. |
calls | bigint | Tidak ada | Jumlah kali dieksekusi. |
total_time | double precision | Tidak ada | Total waktu yang dihabiskan dalam pernyataan, dalam milidetik. |
min_time | double precision | Tidak ada | Waktu minimum yang dihabiskan dalam pernyataan, dalam milidetik. |
max_time | double precision | Tidak ada | Waktu maksimum yang dihabiskan dalam pernyataan, dalam milidetik. |
mean_time | double precision | Tidak ada | Rata-rata waktu yang dihabiskan dalam pernyataan, dalam milidetik. |
stddev_time | double precision | Tidak ada | Standar deviasi populasi dari waktu yang dihabiskan dalam pernyataan, dalam milidetik. |
rows | bigint | Tidak ada | Jumlah total baris yang diambil atau dipengaruhi oleh pernyataan. |
shared_blks_hit | bigint | Tidak ada | Jumlah total cache blok bersama yang terkena oleh pernyataan. |
shared_blks_read | bigint | Tidak ada | Jumlah total blok bersama yang dibaca oleh pernyataan. |
shared_blks_dirtied | bigint | Tidak ada | Jumlah total blok bersama yang dikotori oleh pernyataan. |
shared_blks_written | bigint | Tidak ada | Jumlah total blok bersama yang ditulis oleh pernyataan. |
local_blks_hit | bigint | Tidak ada | Jumlah total cache blok lokal yang terkena oleh pernyataan. |
local_blks_read | bigint | Tidak ada | Jumlah total blok lokal yang dibaca oleh pernyataan. |
local_blks_dirtied | bigint | Tidak ada | Jumlah total blok lokal yang dikotori oleh pernyataan. |
local_blks_written | bigint | Tidak ada | Jumlah total blok lokal yang ditulis oleh pernyataan. |
temp_blks_read | bigint | Tidak ada | Jumlah total blok temp yang dibaca oleh pernyataan. |
temp_blks_written | bigint | Tidak ada | Jumlah total blok temp yang ditulis oleh pernyataan. |
blk_read_time | double precision | Tidak ada | Total waktu yang dihabiskan oleh pernyataan membaca blok, dalam milidetik (jika track_io_timing diaktifkan, jika tidak nol). |
blk_write_time | double precision | Tidak ada | Total waktu yang dihabiskan oleh pernyataan menulis blok, dalam milidetik (jika track_io_timing diaktifkan, jika tidak nol). |
Analisis pernyataan SQL yang paling banyak mengonsumsi sumber daya
Pernyataan SQL dengan Konsumsi Sumber Daya I/O Tertinggi
Eksekusi pernyataan berikut untuk melihat lima pernyataan SQL teratas dengan konsumsi sumber daya I/O tertinggi dalam satu panggilan:
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY (blk_read_time+blk_write_time)/calls DESC LIMIT 5;Eksekusi pernyataan berikut untuk melihat lima pernyataan SQL teratas dengan konsumsi sumber daya I/O tertinggi secara keseluruhan:
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY (blk_read_time+blk_write_time) DESC LIMIT 5;
Pernyataan SQL dengan Waktu Eksekusi Terlama
Eksekusi pernyataan berikut untuk melihat lima pernyataan SQL teratas dengan waktu eksekusi terlama dalam satu panggilan:
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 5;Eksekusi pernyataan berikut untuk melihat lima pernyataan SQL teratas dengan waktu eksekusi terlama secara keseluruhan:
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
Pernyataan SQL dengan Jitter Respons Paling Parah
Eksekusi pernyataan berikut untuk melihat lima pernyataan SQL teratas dengan jitter respons paling parah:
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY stddev_time DESC LIMIT 5;Pernyataan SQL dengan Konsumsi Memori Bersama Tertinggi
Eksekusi pernyataan berikut untuk melihat lima pernyataan SQL teratas dengan konsumsi memori bersama tertinggi:
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY (shared_blks_hit+shared_blks_dirtied) DESC LIMIT 5;Pernyataan SQL dengan Konsumsi Ruang Sementara Tertinggi
Eksekusi pernyataan berikut untuk melihat lima pernyataan SQL teratas dengan konsumsi ruang sementara tertinggi:
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY temp_blks_written DESC LIMIT 5;
Reset statistik konsumsi sumber daya
Ekstensi pg_stat_statements mengumpulkan statistik kumulatif.
Anda dapat mengeksekusi pernyataan berikut untuk menghapus statistik historis secara berkala:
SELECT pg_stat_statements_reset();Referensi
Untuk informasi lebih lanjut, lihat Dokumentasi PostgreSQL 9.6.2 - F.29. pg_stat_statements.