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 disediakan sebagai memiliki 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.
Periksa
Threads_running
danThreads_connected
Gunakan kueri berikut untuk melihat jumlah thread yang aktif:
> SHOW STATUS like 'Threads_%';
Threads_running
adalah subset dariThreads_connected
. Thread lainnya tidak ada aktivitas. PeningkatanThreads_running
akan berkontribusi pada peningkatan penggunaan CPU. Sebaiknya periksa apa yang sedang berjalan di thread tersebut.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 (sepertiALTER TABLE
) yang mungkin menyimpan kunci metadata. DDL mungkin juga menunggu kunci metadata tabel jika kueri awal, sepertiSELECT query
yang berjalan lama, menyimpannya.
- Jika banyak thread menampilkan
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 tabelinformation_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.
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 tabelinformation_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.
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. Setelahperformance_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
, yaituprocesslist_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 ataugcloud CLI
lalu melihat log menggunakan logs viewer di Konsol Google Cloud ataugloud CLI
.
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
.Optimalkan kueri
SELECT
yang panjangPertama, 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.
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.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
jikaThreads_created/Connections
berukuran besar. Cache thread yang tepat akan mengurangi waktu pembuatan thread dan membantu workload yang sangat serentak. - Sesuaikan
table_open_cache
jikaTable_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.
- Sesuaikan
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.