Mengoptimalkan pemakaian CPU yang tinggi dalam instance

Pemakaian CPU yang tinggi dalam suatu instance dapat disebabkan oleh berbagai alasan, seperti peningkatan workload, transaksi yang berat, kueri yang lambat, dan transaksi yang berjalan lama.

Pemberi rekomendasi instance yang tidak disediakan akan menganalisis pemakaian CPU. Jika tingkat penggunaan CPU berada di atau di atas 95% untuk jangka waktu yang signifikan dalam 30 hari terakhir, pemberi rekomendasi akan memberi tahu Anda dan memberikan insight tambahan untuk membantu menyelesaikan masalah tersebut.

Dokumen ini menjelaskan cara meninjau dan mengoptimalkan instance Cloud SQL untuk MySQL jika instance tersebut diidentifikasi oleh pemberi rekomendasi instance yang tidak memadai sebagai instance yang mengalami pemakaian CPU yang tinggi.

Rekomendasi

Pemakaian CPU meningkat secara proporsional dengan workload. Untuk mengurangi pemakaian CPU, periksa kueri yang sedang berjalan dan optimalkan kueri tersebut. Berikut adalah langkah-langkah untuk memeriksa konsumsi CPU.

  1. Periksa Threads_running dan Threads_connected

    Gunakan kueri berikut untuk melihat jumlah thread yang aktif:

    > SHOW STATUS like 'Threads_%';

    Threads_running adalah subset dari Threads_connected. Thread lainnya tidak ada aktivitas. Peningkatan Threads_running akan berkontribusi pada peningkatan penggunaan CPU. Sebaiknya periksa apa yang sedang berjalan di thread tersebut.

  2. Periksa status kueri

    Jalankan perintah SHOW PROCESSLIST untuk melihat kueri yang sedang berlangsung. Perintah tersebut akan menampilkan semua rangkaian pesan yang terhubung secara berurutan dan pernyataan SQL yang sedang berjalan. 

    mysql> SHOW [FULL] PROCESSLIST;

    Perhatikan kolom status dan durasi. Periksa apakah ada banyak kueri yang terhenti pada status yang sama.

    • Jika banyak thread menampilkan Updating, mungkin ada pertentangan kunci terhadap kumpulan data. Lihat langkah berikutnya.
    • Jika banyak thread menampilkan Waiting untuk kunci metadata tabel, periksa kueri untuk mengetahui tabel tersebut, lalu cari DDL (seperti ALTER TABLE) yang mungkin menyimpan kunci metadata. DDL mungkin juga menunggu kunci metadata tabel jika kueri awal, seperti SELECT query yang berjalan lama, menyimpannya.

  3. Periksa pertentangan kunci terhadap kumpulan data

    Saat transaksi menyimpan kunci pada kumpulan data indeks populer, transaksi tersebut akan memblokir transaksi lain yang meminta kunci yang sama. Hal ini dapat menjadi efek berantai dan menyebabkan sejumlah permintaan terhenti serta peningkatan nilai Threads_running. Untuk mendiagnosis pertentangan kunci, gunakan tabel information_schema.innodb_lock_waits.

    Kueri berikut mencantumkan setiap transaksi pemblokiran dan jumlah transaksi terkait yang diblokir. 

    SELECT 
  t.trx_id, 
  t.trx_state, 
  t.trx_started, 
  COUNT(distinct w.requesting_trx_id) AS blocked_trxs
FROM 
  information_schema.innodb_lock_waits w 
INNER JOIN information_schema.innodb_trx t
   ON t.trx_id = w.blocking_trx_id 
GROUP BY t.trx_id,t.trx_state, t.trx_started
ORDER BY t.trx_id;

    Baik satu DML besar maupun banyak DML kecil yang bersamaan dapat menyebabkan pertentangan kunci baris. Anda dapat mengoptimalkannya dari sisi aplikasi menggunakan langkah-langkah berikut:

    • Hindari transaksi yang panjang, karena kunci baris disimpan hingga transaksi berakhir.
    • Uraikan satu DML besar menjadi DML yang berukuran kecil.
    • Kelompokkan DML satu baris menjadi beberapa bagian kecil.
    • Minimalkan pertentangan di antara thread; misalnya, jika kode aplikasi menggunakan kumpulan koneksi, tetapkan rentang ID ke thread yang sama.

  4. Temukan transaksi yang berjalan lama

    • Gunakan SHOW ENGINE INNODB STATUS

      Di bagian TRANSAKSI, Anda dapat melihat semua transaksi terbuka yang diurutkan dari yang paling awal hingga yang paling lama. 

      mysql> SHOW ENGINE INNODB STATUS\G
……
------------
TRANSACTIONS
------------
…
---TRANSACTION 245762, ACTIVE 262 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 9210, OS thread handle 140262286128896, query id 202218 localhost root

      Mulailah dengan transaksi yang paling lama dan temukan jawaban atas pertanyaan berikut:

      • Berapa lama transaksi ini telah berjalan?
      • Berapa banyak struktur kunci dan kunci baris yang ada?
      • Ada berapa banyak entri log yang diurungkan?
      • Apa saja yang menghubungkan host dan pengguna?
      • Apa yang dimaksud dengan pernyataan SQL yang sedang berlangsung?

    • Gunakan information_schema.innodb_trx

      Jika SHOW ENGINE INNODB STATUS terpotong, cara alternatif untuk memeriksa semua transaksi yang terbuka adalah dengan menggunakan tabel information_schema.innodb_trx:

      SELECT 
 trx_id, trx_state, 
 timestampdiff(second, trx_started, now()) AS active_secs, 
 timestampdiff(second, trx_wait_started, now()) AS wait_secs, trx_tables_in_use,
 trx_tables_locked, 
 trx_lock_structs, 
 trx_rows_locked, 
 trx_rows_modified, 
 trx_query 
FROM information_schema.innodb_trx

    Jika transaksi menunjukkan pernyataan yang berjalan lama saat ini, Anda dapat memutuskan untuk menghentikan transaksi guna mengurangi tekanan pada server atau menunggu hingga transaksi penting selesai. Jika transaksi yang lebih lama tidak menampilkan aktivitas apa pun, lanjutkan ke langkah berikutnya untuk menemukan histori transaksi.

  5. Periksa pernyataan SQL dari transaksi yang berjalan lama

    • Gunakan performance_schema

      Untuk menggunakan performance_schema, Anda harus mengaktifkannya terlebih dahulu. Ini adalah perubahan yang mengharuskan instance dimulai ulang. Setelah performance_schema aktif, pastikan instrumen dan konsumen telah diaktifkan:

      SELECT * FROM setup_consumers where name like 'events_statements_history';
SELECT * FROM setup_instruments where name like 'statement/sql/%';

      Jika belum diaktifkan, aktifkan terlebih dahulu:

      UPDATE setup_instruments SET ENABLED = 'YES', timed = 'YES' WHERE NAME LIKE 'statement/%';
UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_statements%';

      Secara default, setiap thread akan menyimpan 10 peristiwa terakhir yang ditentukan oleh performance_schema_events_statements_history_size. Peristiwa tersebut biasanya cukup untuk menemukan transaksi dalam kode aplikasi. Parameter ini tidak bersifat dinamis.

      Dengan mysql thread id, yaitu processlist_id, buatlah kueri peristiwa histori:

      SELECT 
 t.thread_id, 
 event_name, 
 sql_text, 
 rows_affected, 
 rows_examined, 
 processlist_id, 
 processlist_time, 
 processlist_state 
FROM events_statements_history h 
INNER JOIN threads t 
ON h.thread_id = t.thread_id 
WHERE processlist_id = <mysql thread id>
ORDER BY event_id;

    • Gunakan log kueri yang lambat

      Untuk proses debug, Anda dapat menangkap semua kueri yang memerlukan waktu lebih dari N detik ke dalam log kueri yang lambat. Anda dapat mengaktifkan log kueri yang lambat dengan mengedit setelan instance pada halaman instance di Konsol Google Cloud atau gcloud CLI, lalu melihat log menggunakan Logs Viewer di Konsol Google Cloud atau gloud CLI.

  6. Periksa pertentangan semaphore

    Dalam lingkungan serentak, mutex dan baca/tulis latch pada resource bersama mungkin menjadi titik pertentangan, yang memperlambat performa server. Selain itu, jika waktu tunggu semaphore lebih dari 600 detik, sistem dapat mengalami error untuk keluar dari kondisi terhenti.

    Untuk melihat pertentangan semaphore, gunakan perintah berikut:

    mysql> SHOW ENGINE INNODB STATUS\G
----------
SEMAPHORES
----------
...
  --Thread 140396021667584 has waited at row0purge.cc line 862 for 241.00 seconds the semaphore:
  S-lock on RW-latch at 0x30c03e8 created in file dict0dict.cc line 1183
  a writer (thread id 140395996489472) has reserved it in mode  exclusive
  number of readers 0, waiters flag 1, lock_word: 0
  Last time read locked in file row0purge.cc line 862
  Last time write locked in file /build/mysql-5.7-FFKPr6/mysql-5.7-5.7.22/storage/innobase/dict/dict0stats.cc line 2376
...

    Dengan waktu tunggu setiap semaphore, baris pertama menampilkan thread yang menunggu, semaphore tertentu, dan lama waktu tunggu. Jika waktu tunggu semaphore sering terjadi saat berulang kali menjalankan SHOW ENGINE INNODB STATUS, terutama jika waktu tunggu berlangsung lebih dari beberapa detik, itu berarti sistem mengalami bottleneck serentak.

    Terdapat titik pertentangan yang berbeda dalam workload dan konfigurasi yang berbeda.

    Ketika semaphore sering berada di btr0sea.c, pengindeksan hash adaptif mungkin menjadi sumber pertentangan. Coba nonaktifkan menggunakan Konsol Google Cloud atau gcloud CLI.

  7. Optimalkan kueri SELECT yang panjang

    Pertama, tinjau kueri tersebut. Identifikasi tujuan kueri dan cara terbaik untuk mendapatkan hasilnya. Rencana kueri terbaik adalah yang meminimalkan akses data.

    • Periksa rencana eksekusi kueri:
    mysql> EXPLAIN <the query>;

    Lihat dokumentasi MySQL untuk mempelajari cara menafsirkan output dan mengevaluasi efisiensi kueri.

    • Gunakan indeks yang tepat

    Periksa kolom kunci untuk melihat apakah indeks yang diharapkan sudah digunakan. Jika belum, perbarui statistik indeks:

    mysql> analyze table <table_name>

    Tingkatkan jumlah halaman contoh yang digunakan untuk menghitung statistik indeks. Untuk mempelajari lebih lanjut, lihat dokumentasi MySQL.

    • Manfaatkan indeks sepenuhnya

    Saat menggunakan indeks multikolom, periksa kolom key_len untuk melihat apakah indeks dimanfaatkan sepenuhnya untuk memfilter kumpulan data. Kolom paling kiri harus menjadi perbandingan yang sama, dan indeks dapat digunakan hingga dan termasuk kondisi rentang pertama.

    • Gunakan petunjuk pengoptimal

    Cara lain untuk memastikan indeks yang tepat adalah dengan menggunakan petunjuk indeks dan petunjuk untuk urutan penggabungan tabel.

  8. Hindari daftar histori panjang dengan READ COMMITTED

    Daftar histori adalah daftar transaksi yang tidak dihapus dalam tablespace urungkan. Tingkat isolasi default transaksi adalah REPEATABLE READ, yang mengharuskan transaksi membaca snapshot yang sama selama durasinya. Oleh sebab itu, kueri SELECT memblokir penghapusan permanen kumpulan data log urungkan yang dibuat sejak kueri (atau transaksi) dimulai. Daftar histori yang panjang akan memperlambat performa kueri. Salah satu cara untuk menghindari pembuatan daftar histori yang panjang adalah dengan mengubah tingkat transaction isolation menjadi READ COMMITTED. Dengan READ COMMITTED, tidak perlu lagi menyimpan daftar histori untuk tampilan baca yang konsisten. Anda dapat mengubah tingkat transaction isolation secara global untuk semua sesi, untuk satu sesi, atau untuk transaksi tunggal berikutnya. Untuk mempelajari lebih lanjut, lihat dokumentasi MySQL.

  9. Sesuaikan konfigurasi server

    Ada banyak hal yang dapat dikatakan tentang konfigurasi server. Meskipun cerita lengkapnya berada di luar cakupan dokumen ini, perlu diingat bahwa server juga melaporkan berbagai variabel status yang memberi petunjuk tentang seberapa baik konfigurasi yang terkait. Contoh:

    • Sesuaikan thread_cache_size jika Threads_created/Connections berukuran besar. Cache thread yang tepat akan mengurangi waktu pembuatan thread dan membantu workload yang sangat serentak.
    • Sesuaikan table_open_cache jika Table_open_cache_misses/Table_open_cache_hits tidak mudah. Memiliki tabel dalam cache tabel menghemat waktu eksekusi kueri dan dapat membuat perbedaan dalam lingkungan yang sangat serentak.

  10. Akhiri koneksi yang tidak diinginkan

    Anda dapat menghentikan kueri tersebut jika kueri tampak tidak valid atau tidak diperlukan lagi. Untuk mempelajari cara mengidentifikasi dan mengakhiri thread MySQL, lihat Mengelola koneksi database.

Terakhir, jika penggunaan CPU masih tinggi dan kueri menghasilkan traffic yang diperlukan, pertimbangkan untuk meningkatkan resource CPU di instance Anda untuk menghindari error atau periode nonaktif database.