Memecahkan masalah penggunaan CPU tinggi di Azure Database for MySQL - Server Fleksibel
BERLAKU UNTUK: 
Azure Database for MySQL - Server Tunggal Azure Database for MySQL - Server Fleksibel
Penting
Server tunggal Azure Database for MySQL berada di jalur penghentian. Kami sangat menyarankan Agar Anda meningkatkan ke server fleksibel Azure Database for MySQL. Untuk informasi selengkapnya tentang migrasi ke server fleksibel Azure Database for MySQL, lihat Apa yang terjadi pada Server Tunggal Azure Database for MySQL?
Server fleksibel Azure Database for MySQL menyediakan berbagai metrik yang dapat Anda gunakan untuk mengidentifikasi penyempitan sumber daya dan masalah performa di server. Untuk menentukan apakah server Anda mengalami pemanfaatan CPU yang tinggi, pantau metrik seperti "Host CPU percent", "Total Connections", "Host Memory Percent", dan "IO Percent". Terkadang, melihat kombinasi metrik ini akan memberikan wawasan tentang apa yang mungkin menyebabkan peningkatan pemanfaatan CPU pada instans server fleksibel Azure Database for MySQL Anda.
Misalnya, pertimbangkan lonjakan koneksi mendadak yang memulai lonjakan kueri database yang menyebabkan pemanfaatan CPU meningkat.
Selain menangkap metrik, penting untuk juga melacak beban kerja guna memahami apakah satu atau beberapa kueri menyebabkan lonjakan pemanfaatan CPU.
Penyebab CPU tinggi
Lonjakan CPU dapat terjadi karena berbagai alasan, terutama karena lonjakan koneksi dan kueri SQL yang ditulis dengan buruk, atau kombinasi keduanya:
Lonjakan koneksi
Peningkatan koneksi dapat menyebabkan peningkatan thread, yang pada gilirannya dapat menyebabkan peningkatan penggunaan CPU karena harus mengelola koneksi ini beserta kueri dan sumber dayanya. Untuk memecahkan masalah lonjakan koneksi, Anda harus memeriksa metrik Total Koneksi dan merujuk ke bagian berikutnya untuk detail selengkapnya tentang koneksi ini. Anda dapat memanfaatkan performance_schema untuk mengidentifikasi host dan pengguna yang saat ini terhubung ke server dengan perintah berikut:
Host yang terhubung saat ini
select HOST,CURRENT_CONNECTIONS From performance_schema.hosts
where CURRENT_CONNECTIONS > 0
and host not in ('NULL','localhost');
Pengguna yang terhubung saat ini
select USER,CURRENT_CONNECTIONS from performance_schema.users
where CURRENT_CONNECTIONS >0
and USER not in ('NULL','azure_superuser');
Kueri SQL yang ditulis dengan buruk
Kueri yang mahal untuk dieksekusi dan memindai sejumlah besar baris tanpa indeks, atau kueri yang menjalankan pengurutan sementara bersama dengan rencana tidak efisien lainnya, dapat menyebabkan lonjakan CPU. Meskipun beberapa kueri mungkin dijalankan dengan cepat dalam satu sesi, kueri tersebut dapat menyebabkan lonjakan CPU saat dijalankan dalam beberapa sesi. Oleh karena itu, sangat penting untuk selalu menjelaskan kueri Anda yang Anda ambil dari daftar proses pertunjukan dan memastikan rencana eksekusinya efisien. Ini dapat dicapai dengan memastikan mereka memindai jumlah baris minimal dengan menggunakan filter/klausul di mana, menggunakan indeks dan menghindari penggunaan pengurutan sementara besar bersama dengan rencana eksekusi buruk lainnya. Untuk informasi selengkapnya tentang rencana eksekusi, lihat MENJELASKAN Format Output.
Menangkap detail beban kerja saat ini
Perintah SHOW (FULL) PROCESSLIST menampilkan daftar semua sesi pengguna yang saat ini tersambung ke instans server fleksibel Azure Database for MySQL. Ini juga memberikan detail tentang status dan aktivitas saat ini dari setiap sesi.
Perintah ini hanya menghasilkan snapshot status sesi saat ini dan tidak memberikan informasi tentang aktivitas sesi historis.
Mari kita lihat contoh output dari menjalankan perintah ini.
mysql> SHOW FULL PROCESSLIST;
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 13 | Waiting for next activation | NULL |
| 6 | azure_superuser | 127.0.0.1:33571 | NULL | Sleep | 115 | | NULL
|
| 24835 | adminuser | 10.1.1.4:39296 | classicmodels | Query | 7 | Sending data | select * from classicmodels.orderdetails;|
| 24837 | adminuser | 10.1.1.4:38208 | NULL | Query | 0 | starting | SHOW FULL PROCESSLIST |
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
5 rows in set (0.00 sec)
Perhatikan bahwa ada dua sesi yang dimiliki oleh "adminuser" pengguna milik pelanggan, keduanya dari alamat IP yang sama:
- Sesi 24835 telah menjalankan pernyataan SELECT selama tujuh detik terakhir.
- Sesi 24837 menjalankan pernyataan "tampilkan daftar proses penuh".
Jika perlu, mungkin diperlukan untuk mengakhiri kueri, seperti pelaporan atau kueri HTAP yang telah menyebabkan penggunaan CPU beban kerja produksi Anda lonjakan. Namun, selalu pertimbangkan konsekuensi potensial dari penghentian kueri sebelum mengambil tindakan dalam upaya untuk mengurangi pemanfaatan CPU. Di lain waktu, jika ada kueri yang berjalan lama yang diidentifikasi yang mengarah ke lonjakan CPU, sesuaikan kueri ini sehingga sumber daya dimanfaatkan secara optimal.
Analisis beban kerja terperinci saat ini
Anda perlu menggunakan setidaknya dua sumber informasi untuk mendapatkan informasi yang akurat tentang status sesi, transaksi, dan kueri:
- Daftar proses server dari INFORMATION_SCHEMA. Tabel PROCESSLIST, yang juga dapat Anda akses dengan menjalankan perintah SHOW [FULL] PROCESSLIST.
- Metadata transaksi InnoDB dari tabel INFORMATION_SCHEMA. INNODB_TRX.
Dengan informasi hanya dari salah satu sumber ini, tidak mungkin untuk menggambarkan koneksi dan status transaksi. Misalnya, daftar proses tidak memberi tahu Anda apakah ada transaksi terbuka yang terkait dengan salah satu sesi. Di sisi lain, metadata transaksi tidak menampilkan status sesi dan waktu yang dihabiskan dalam status tersebut.
Contoh kueri berikut yang menggabungkan informasi daftar proses dengan beberapa bagian penting metadata transaksi InnoDB:
mysql> select p.id as session_id, p.user, p.host, p.db, p.command, p.time, p.state, substring(p.info, 1, 50) as info, t.trx_started, unix_timestamp(now()) - unix_timestamp(t.trx_started) as trx_age_seconds, t.trx_rows_modified, t.trx_isolation_level from information_schema.processlist p left join information_schema.innodb_trx t on p.id = t.trx_mysql_thread_id \G
Contoh berikut menunjukkan output dari kueri ini:
*************************** 1. row ***************************
session_id: 11
user: adminuser
host: 172.31.19.159:53624
db: NULL
command: Sleep
time: 636
state: cleaned up
info: NULL
trx_started: 2019-08-01 15:25:07
trx_age_seconds: 2908
trx_rows_modified: 17825792
trx_isolation_level: REPEATABLE READ
*************************** 2. row ***************************
session_id: 12
user: adminuser
host: 172.31.19.159:53622
db: NULL
command: Query
time: 15
state: executing
info: select * from classicmodels.orders
trx_started: NULL
trx_age_seconds: NULL
trx_rows_modified: NULL
trx_isolation_level: NULL
Analisis informasi ini, menurut sesi, tercantum dalam tabel berikut.
| Area | Analisis |
|---|---|
| Sesi 11 | Sesi ini saat ini tidak aktif (tidur) tanpa kueri yang berjalan, dan sudah selama 636 detik. Dalam sesi tersebut, transaksi yang terbuka selama 2908 detik telah memodifikasi 17.825.792 baris, dan menggunakan isolasi REPEATABLE READ. |
| Sesi 12 | Sesi saat ini menjalankan pernyataan SELECT, yang telah berjalan selama 15 detik. Tidak ada kueri yang berjalan dalam sesi, seperti yang ditunjukkan oleh nilai NULL untuk trx_started dan trx_age_seconds. Sesi akan terus memegang batas pengumpulan sampah selama berjalan kecuali jika menggunakan isolasi READ COMMITTED yang lebih santai. |
Perhatikan bahwa jika sesi dilaporkan tidak aktif, sesi tidak lagi menjalankan pernyataan apa pun. Pada titik ini, sesi telah menyelesaikan pekerjaan sebelumnya dan sedang menunggu pernyataan baru dari klien. Namun, sesi tidak aktif masih bertanggung jawab atas beberapa konsumsi CPU dan penggunaan memori.
Mencantumkan transaksi terbuka
Output dari kueri berikut menyediakan daftar semua transaksi yang saat ini berjalan terhadap server database dalam urutan waktu mulai transaksi sehingga Anda dapat dengan mudah mengidentifikasi apakah ada transaksi yang berjalan lama dan memblokir melebihi runtime yang diharapkan.
SELECT trx_id, trx_mysql_thread_id, trx_state, Unix_timestamp() - ( To_seconds(trx_started) - To_seconds('1970-01-01 00:00:00') ) AS trx_age_seconds, trx_weight, trx_query, trx_tables_in_use, trx_tables_locked, trx_lock_structs, trx_rows_locked, trx_rows_modified, trx_isolation_level, trx_unique_checks, trx_is_read_only FROM information_schema.innodb_trx ORDER BY trx_started ASC;
Memahami status utas
Transaksi yang berkontribusi pada pemanfaatan CPU yang lebih tinggi selama eksekusi dapat memiliki utas di berbagai status, seperti yang dijelaskan di bagian berikut. Gunakan informasi ini untuk lebih memahami siklus hidup kueri dan berbagai status utas.
Memeriksa izin/Membuka tabel
Status ini biasanya berarti operasi tabel terbuka membutuhkan waktu lama. Biasanya, Anda dapat meningkatkan ukuran cache tabel guna meningkatkan masalah. Namun, tabel yang dibuka secara perlahan juga dapat mengindikasikan masalah lain, seperti memiliki terlalu banyak tabel di bawah database yang sama.
Mengirim data
Meskipun status ini dapat berarti bahwa utas mengirim data melalui jaringan, ini juga dapat menunjukkan bahwa kueri membaca data dari disk atau memori. Status ini dapat disebabkan oleh pemindaian tabel berurutan. Anda harus memeriksa nilai innodb_buffer_pool_reads dan innodb_buffer_pool_read_requests untuk menentukan apakah sejumlah besar halaman dilayani dari disk ke dalam memori. Untuk informasi selengkapnya, lihat Memecahkan masalah memori rendah di server fleksibel Azure Database for MySQL.
Memperbarui
Status ini biasanya berarti bahwa utas melakukan operasi tulis. Periksa metrik terkait IO di Pemantauan Performa untuk mendapatkan pemahaman yang lebih baik tentang apa yang dilakukan sesi saat ini.
Menunggu <kunci lock_type>
Status ini menunjukkan bahwa utas sedang menunggu kunci kedua. Dalam kebanyakan kasus, mungkin kunci metadata. Anda harus meninjau semua utas lain dan melihat siapa yang mengambil kunci.
Memahami dan menganalisis peristiwa tunggu
Penting untuk memahami peristiwa tunggu yang mendasar di mesin MySQL, karena menunggu lama atau sejumlah besar waktu tunggu dalam database dapat menyebabkan peningkatan pemanfaatan CPU. Contoh berikut menunjukkan perintah dan output sampel yang sesuai.
SELECT event_name AS wait_event,
count_star AS all_occurrences,
Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_wait_time,
Concat(Round(avg_timer_wait / 1000000000, 2), ' ms') AS
avg_wait_time
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE count_star > 0 AND event_name <> 'idle'
ORDER BY sum_timer_wait DESC LIMIT 10;
+--------------------------------------+-----------------+-----------------+---------------+
| wait_event | all_occurrences | total_wait_time | avg_wait_time |
+--------------------------------------+-----------------+-----------------+---------------+
| wait/io/file/sql/binlog | 7090 | 255.54 s | 36.04 ms |
| wait/io/file/innodb/innodb_log_file | 17798 | 55.43 s | 3.11 ms |
| wait/io/file/innodb/innodb_data_file | 260227 | 39.67 s | 0.15 ms |
| wait/io/table/sql/handler | 5548985 | 11.73 s | 0.00 ms |
| wait/io/file/sql/FRM | 1237 | 7.61 s | 6.15 ms |
| wait/io/file/sql/dbopt | 28 | 1.89 s | 67.38 ms |
| wait/io/file/myisam/kfile | 92 | 0.76 s | 8.30 ms |
| wait/io/file/myisam/dfile | 271 | 0.53 s | 1.95 ms |
| wait/io/file/sql/file_parser | 18 | 0.32 s | 17.75 ms |
| wait/io/file/sql/slow_log | 2 | 0.05 s | 25.79 ms |
+--------------------------------------+-----------------+-----------------+---------------+
10 rows in set (0.00 sec)
Membatasi waktu eksekusi Pernyataan SELECT
Jika Anda tidak tahu tentang biaya eksekusi dan waktu eksekusi untuk operasi database yang melibatkan kueri SELECT, SELECT yang berjalan lama dapat menyebabkan ketidakpastian atau volatilitas di server database. Ukuran pernyataan dan transaksi, serta pemanfaatan sumber daya terkait, terus tumbuh tergantung pada pertumbuhan himpunan data yang mendasar. Karena pertumbuhan yang tidak terbatas ini, pernyataan dan transaksi pengguna akhir membutuhkan waktu yang jauh lebih lama, mengonsumsi semakin banyak sumber daya sampai membuat server database kewalahan. Saat menggunakan kueri SELECT yang tidak terbatas, disarankan untuk mengonfigurasi parameter max_execution_time sehingga kueri apa pun yang melebihi durasi ini akan dibatalkan.
Rekomendasi
- Pastikan database Anda memiliki cukup sumber daya yang dialokasikan untuk menjalankan kueri. Terkadang, Anda mungkin perlu meningkatkan ukuran instans untuk mendapatkan lebih banyak inti CPU untuk mengakomodasi beban kerja Anda.
- Hindari transaksi besar atau jangka panjang dengan memecahnya menjadi transaksi yang lebih kecil.
- Jalankan pernyataan SELECT pada server replika baca jika memungkinkan.
- Gunakan pemberitahuan pada "Host CPU Percent" sehingga Anda mendapatkan pemberitahuan jika sistem melebihi salah satu ambang yang ditentukan.
- Gunakan Wawasan Performa Kueri atau Buku Kerja Azure untuk mengidentifikasi kueri yang bermasalah atau berjalan lambat, lalu optimalkan.
- Untuk server database produksi, kumpulkan diagnostik secara berkala untuk memastikan bahwa semuanya berjalan dengan lancar. Jika tidak, pecahkan masalah dan atasi masalah apa pun yang Anda identifikasi.
Langkah berikutnya
Untuk menemukan jawaban serekan atas pertanyaan Anda yang paling penting atau memposting atau menjawab pertanyaan, kunjungi Stack Overflow.