Pemakaian CPU yang tinggi dapat berdampak buruk pada performa instance Anda. Setiap aktivitas yang dilakukan pada instance selalu menggunakan CPU. Oleh karena itu, jika ada pemberitahuan tentang penggunaan CPU yang tinggi, Anda harus terlebih dahulu mengidentifikasi penyebab utama masalah ini, baik itu kueri yang ditulis dengan buruk, transaksi yang berjalan lama, atau aktivitas database lainnya.
Dokumen ini menjelaskan cara mengidentifikasi bottleneck CPU dalam instance dan mengurangi masalah penggunaan CPU dalam instance.
Mengidentifikasi bottleneck CPU
Menggunakan insight kueri untuk mengidentifikasi kueri yang memiliki konsumsi CPU tinggi
Insight kueri membantu Anda mendeteksi, mendiagnosis, dan mencegah masalah performa kueri untuk database Cloud SQL.
Menggunakan pg_proctab ekstensi
Gunakan ekstensi pg_proctab dengan kombinasi utilitas pg_top
untuk mendapatkan output sistem operasi yang memberikan informasi penggunaan CPU per proses.
Menggunakan kueri
Mengidentifikasi koneksi aktif berdasarkan status
Setiap koneksi aktif ke database memerlukan sejumlah CPU, sehingga jika instance memiliki jumlah koneksi yang tinggi, pemakaian kumulatif dapat menjadi tinggi. Gunakan kueri berikut untuk mendapatkan informasi tentang jumlah koneksi berdasarkan status.
SELECT
state,
usename,
count(1)
FROM
pg_stat_activity
WHERE
pid <> pg_backend_pid()
group by
state,
usename
order by
1;
Outputnya terlihat mirip dengan yang berikut ini:
state | usename | count
---------------------+---------------+-------
active | ltest | 318
active | sbtest | 95
active | | 2
idle | cloudsqladmin | 2
idle in transaction | ltest | 32
idle in transaction | sbtest | 5
| cloudsqladmin | 3
| | 4
(8 rows)
Jika jumlah koneksi aktif tinggi, periksa kueri yang berjalan lama atau peristiwa tunggu yang memblokir kueri agar tidak dijalankan.
Jika jumlah koneksi tidak ada aktivitas tinggi, jalankan kueri berikut untuk menghentikan koneksi, setelah mengambil persetujuan yang diperlukan.
SELECT
pg_terminate_backend(pid)
FROM
pg_stat_activity
WHERE
usename = 'sbtest'
and pid <> pg_backend_pid()
and state in ('idle');
Anda juga dapat menghentikan koneksi satu per satu dengan pg_terminate_backend menggunakan kueri berikut:
SELECT pg_terminate_backend (<pid>);
Di sini, Anda dapat memperoleh PID dari pg_stat_activity.
Mengidentifikasi koneksi jangka panjang
Berikut adalah contoh kueri yang mengembalikan kueri yang berjalan lama. Pada kasus ini, Anda dapat mengidentifikasi kueri yang telah aktif selama lebih dari 5 menit.
SELECT
pid,
query_start,
xact_start,
now() - pg_stat_activity.query_start AS duration,
query,
state
FROM
pg_stat_activity
WHERE
(
now() - pg_stat_activity.query_start
) > interval '5 minutes' order by 4 desc;
Meninjau rencana penjelasan untuk mengidentifikasi kueri yang ditulis dengan buruk
Gunakan RENCANA PENJELASAN untuk menyelidiki kueri yang ditulis dengan buruk dan menulis ulang kueri, jika perlu. Secara opsional, pertimbangkan untuk membatalkan kueri yang berjalan lama menggunakan perintah berikut dengan persetujuan yang diperlukan.
SELECT pg_cancel_backend(<pid>);
Pantau aktivitas VACUUM
Aktivitas AUTOVACUUM yang menghapus tuple yang mati adalah operasi yang banyak menggunakan CPU. Jika instance Anda menggunakan PostgreSQL versi 11 atau yang lebih baru, gunakan kueri berikut untuk memeriksa apakah ada aktivitas AUTOVACUUM atau VACUUM aktif yang sedang berlangsung.
SELECT
relid :: regclass,
pid,
phase,
heap_blks_total,
heap_blks_scanned,
heap_blks_vacuumed,
index_vacuum_count,
max_dead_tuples,
num_dead_tuples
FROM
pg_stat_progress_vacuum;
Periksa apakah ada aktivitas VACUUM yang sedang berlangsung dalam instance, menggunakan kueri berikut:
SELECT
pid,
datname,
usename,
query
FROM
pg_stat_activity
WHERE
query like '%vacuum%';
Selain itu, Anda dapat mengoptimalkan dan memecahkan masalah operasi VACUUM di PostgreSQL.
Tambahkan ekstensi pg_stat_statements
Siapkan ekstensi pg_stat_statements untuk mendapatkan informasi kamus yang ditingkatkan tentang aktivitas instance.
Pos pemeriksaan yang sering digunakan
Pos pemeriksaan yang sering digunakan akan menurunkan performa. Pertimbangkan untuk menyesuaikan flag checkpoint_timeout jika log pemberitahuan PostgreSQL melaporkan peringatan checkpoint occurring too frequently.
Kumpulkan statistik
Pastikan perencana kueri memiliki statistik terbaru tentang tabel untuk memilih rencana kueri terbaik. Operasi ANALYZE mengumpulkan statistik tentang konten tabel dalam database, dan menyimpan hasilnya dalam katalog sistem pg_statistic. Selanjutnya, perencana kueri menggunakan statistik ini untuk membantu menentukan rencana eksekusi yang paling efisien untuk kueri. Proses AUTOVACUUM menganalisis tabel secara otomatis secara berkala, jadi lakukanlah perintah berikut untuk memeriksa apakah semua tabel telah dianalisis dan memiliki metadata terbaru yang tersedia untuk perencana.
SELECT
relname,
last_autovacuum,
last_autoanalyze
FROM
pg_stat_user_tables;
Setelan sistem yang tidak memadai
Ada faktor lain dan setelan tanda atau faktor sistem yang dapat memengaruhi performa kueri Anda. Jalankan kueri berikut untuk memeriksa peristiwa tunggu dan jenisnya untuk mendapatkan insight tentang performa setelan sistem lainnya.
SELECT
datname,
usename,
(
case when usename is not null then state else query end
) AS what,
wait_event_type,
wait_event,
backend_type,
count(*)
FROM
pg_stat_activity
GROUP BY
1,
2,
3,
4,
5,
6
ORDER BY
1,
2,
3,
4 nulls first,
5,
6;
Outputnya terlihat mirip dengan ini:
.. | .. | what | wait_event_type | wait_event | .. | count
-..--+-..-+----------------+-----------------+----------------------+-..----+------
..
.. | .. | active | IO | CommitWaitFlush | .. | 750
.. | .. | idle | IO | CommitWaitFlush | .. | 360
.. | .. | active | LWLock | BufferMapping | .. | 191
Memantau pemindaian berurutan
Pemindaian berurutan yang sering dilakukan pada tabel yang berisi lebih dari beberapa puluhan baris biasanya menunjukkan indeks yang hilang. Saat pemindaian menyentuh ribuan atau bahkan ratusan ribu baris, hal ini dapat menyebabkan penggunaan CPU yang berlebihan.
Pemindaian berurutan yang sering dilakukan pada tabel dengan ratusan ribu baris dapat menyebabkan penggunaan CPU yang berlebihan. Hindari pemindaian berurutan pada tabel tersebut dengan membuat indeks yang diperlukan.
Jalankan kueri berikut untuk memeriksa berapa kali pemindaian berurutan dilakukan pada tabel apa pun.
SELECT
relname,
idx_scan,
seq_scan,
n_live_tup
FROM
pg_stat_user_tables
WHERE
seq_scan > 0
ORDER BY
n_live_tup desc;
Terakhir, jika CPU masih tinggi dan Anda merasa kueri tersebut adalah traffic yang sah, pertimbangkan untuk meningkatkan resource CPU di instance Anda untuk menghindari error atau periode nonaktif database.