ApsaraDB RDS：Cara Menangani Pemanfaatan CPU Tinggi pada ApsaraDB RDS for PostgreSQL

Deskripsi masalah

Saat menggunakan ApsaraDB RDS for PostgreSQL, Anda mungkin mengalami pemanfaatan CPU yang tinggi atau bahkan mencapai 100%. Artikel ini menjelaskan penyebab umum dan solusi dengan menganalisis skenario tipikal di mana pemanfaatan CPU mencapai 100%.

Solusi

Ketika mengalami pemanfaatan CPU 100%, periksa apakah terjadi lonjakan koneksi aktif selama jam sibuk bisnis dan apakah sumber daya cadangan database tidak mencukupi. Verifikasi bahwa jumlah koneksi aktif jauh lebih besar dari biasanya saat masalah terjadi. Untuk instance ApsaraDB RDS for PostgreSQL, Anda dapat melihat perubahan koneksi ke database di bagian pemantauan dan peringatan di Konsol ApsaraDB RDS. Untuk koneksi aktif saat ini, Anda dapat terhubung ke database dan menjalankan pernyataan berikut untuk menanyakan:

select count( * ) from pg_stat_activity where state not like '%idle';

Lacak Pernyataan SQL Lambat

Jika jumlah koneksi aktif berada dalam rentang normal, penyebabnya mungkin adalah banyaknya pernyataan SQL lambat. Dalam kasus ini, gunakan log query lambat ApsaraDB RDS untuk mengidentifikasi pernyataan yang memakan waktu. Namun, ketika masalah ini terjadi, seluruh sistem menjadi macet dan semua pernyataan SQL melambat. Oleh karena itu, sulit untuk mengidentifikasi pernyataan lambat yang menyebabkan masalah karena banyak pernyataan lambat mungkin tercatat dalam log. Berikut adalah metode untuk melacak pernyataan SQL lambat:

1. Metode 1: Gunakan Plugin pg_stat_statements untuk mengidentifikasi pernyataan SQL lambat, yang hanya berlaku untuk ApsaraDB RDS for PostgreSQL. Ikuti langkah-langkah berikut:
   1. Jika basis data Anda tidak memiliki Plugin tersebut, buatlah satu. Metode ini menggunakan Plugin dan informasi penghitungan, seperti akumulasi waktu eksekusi SQL. Informasi penghitungan terus bertambah dan mencakup informasi historis pernyataan. Oleh karena itu, untuk menangani pemanfaatan CPU tinggi, jalankan perintah berikut untuk mereset penghitung:

      create extension pg_stat_statements;
      select pg_stat_reset();
      select pg_stat_statements_reset();
      
      

   2. Tunggu penghitung mengumpulkan informasi yang cukup selama periode tertentu, misalnya satu menit.
   3. Gunakan perintah berikut untuk menanyakan pernyataan SQL yang memakan waktu. Kemudian, Anda akan mendapatkan penyebab langsung dari masalah tersebut.

      select * from pg_stat_statements order by total_time desc limit 5;

   4. Gunakan pernyataan berikut untuk menanyakan pernyataan SQL yang membaca buffer sebanyak-banyaknya kali, yang mungkin disebabkan oleh kurangnya indeks untuk data yang diminta. Ini mengonsumsi sejumlah besar sumber daya CPU.

      select * from pg_stat_statements order by shared_blks_hit+shared_blks_read desc limit 5;

2. Metode 2: Gunakan tampilan pg_stat_activity dan referensi pernyataan berikut untuk menanyakan pernyataan SQL yang telah berjalan lama. Pernyataan-pernyataan ini mungkin menyebabkan penggunaan CPU tinggi.

   select datname,
          usename,
          client_addr,
          application_name,
          state,
          backend_start,
          xact_start,
          xact_stay,
          query_start,
          query_stay,
          replace(query, chr(10), ' ') as query
   from
     (select pgsa.datname as datname,
             pgsa.usename as usename,
             pgsa.client_addr client_addr,
             pgsa.application_name as application_name,
             pgsa.state as state,
             pgsa.backend_start as backend_start,
             pgsa.xact_start as xact_start,
             extract(epoch
                     from (now() - pgsa.xact_start)) as xact_stay,
             pgsa.query_start as query_start,
             extract(epoch
                     from (now() - pgsa.query_start)) as query_stay,
             pgsa.query as query
      from pg_stat_activity as pgsa
      where pgsa.state != 'idle'
        and pgsa.state != 'idle in transaction'
        and pgsa.state != 'idle in transaction (aborted)') idleconnections
   order by query_stay desc
   limit 5;

3. Metode 3: Tanyakan tabel yang tidak memiliki indeks, mulai dari informasi yang direkam oleh fitur pemindaian tabel. Jika sebuah tabel tidak memiliki indeks, dan sebagian besar data panas disimpan di memori (misalnya, 8 GB memori dan 6 GB data panas), database hanya dapat menggunakan pemindaian tabel dan memproses sejumlah besar catatan yang tidak relevan di memori, yang mengonsumsi sejumlah besar sumber daya CPU. Terutama untuk tabel dengan lebih dari 100 catatan, pemindaian tabel hampir sepenuhnya menghabiskan sumber daya CPU dan memulai beberapa koneksi secara bersamaan, misalnya, ratusan koneksi.
   1. Gunakan pernyataan SQL berikut untuk menanyakan tabel yang paling sering menggunakan pemindaian tabel:

      select * from pg_stat_user_tables where n_live_tup > 100000 and seq_scan > 0 order by seq_tup_read desc limit 10;

   2. Gunakan pernyataan SQL berikut untuk menanyakan query lambat yang sedang berjalan untuk mengakses tabel:

      select * from pg_stat_activity where query ilike '%<table name>%' and query_start - now() > interval '10 seconds';

      Catatan

      Catatan: Anda juga dapat menjalankan pernyataan berikut untuk menemukan query yang terkait dengan tabel menggunakan Plugin pg_stat_statements: select * from pg_stat_statements where query ilike '%<table>%'order by shared_blks_hit+shared_blks_read desc limit 3;

Tangani Pernyataan SQL Lambat

Untuk pernyataan SQL lambat yang diperoleh dengan menggunakan metode sebelumnya seperti yang ditunjukkan dalam contoh berikut, Anda harus menghentikan tugas mereka untuk memulihkan bisnis Anda.

select pg_cancel_backend(pid) from pg_stat_activity where  query like '%<query text>%' and pid != pg_backend_pid();
select pg_terminate_backend(pid) from pg_stat_activity where  query like '%<query text>%' and pid != pg_backend_pid();

Jika pernyataan SQL lambat tersebut penting untuk bisnis Anda, optimalkan dengan menggunakan metode berikut:

1. Jalankan pernyataan ANALYZE [$Table] atau VACUUM ANALYZE [$Table] di tabel yang terkait dengan pernyataan SQL lambat yang diperoleh untuk memperbarui statistik tabel. Dengan cara ini, rencana eksekusi query dapat lebih akurat. Untuk meminimalkan dampak pada bisnis Anda, jalankan pernyataan tersebut selama jam-jam sepi bisnis.
   Catatan

   Catatan: [$Table] mewakili tabel yang terkait dengan pernyataan SQL lambat.

2. Pilih salah satu pernyataan SQL berikut untuk dijalankan dan lihat rencana eksekusi SQL. Pernyataan SQL pertama tidak benar-benar menjalankan pernyataan SQL. Pernyataan SQL kedua dijalankan, dan informasi eksekusi detail diperoleh. Dalam hal ini, indeks dibuat untuk tabel yang terkait dengan fitur pemindaian tabel.

   explain [$Query_Text]
   explain (buffers true, analyze true, verbose true) [$Query_Text]

   Catatan

   Catatan: [$Query_Text] mewakili file atau pernyataan SQL.

3. Tulis ulang pernyataan SQL lambat untuk menghapus subquery yang tidak perlu. Untuk mengoptimalkan lebih lanjut pernyataan tersebut, Anda dapat menulis ulang UNION ALL dan menggunakan klausa JOIN untuk urutan JOIN tetap. Detail tidak dijelaskan di sini.

Ruang lingkup aplikasi

• ApsaraDB RDS for PostgreSQL