Memecahkan masalah performa kueri di Azure Database for MySQL - Server Fleksibel

Artikel
08/15/2024

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?

Performa kueri dapat terpengaruh oleh beberapa faktor, jadi pertama-tama penting untuk melihat cakupan gejala yang Anda alami di instans server fleksibel Azure Database for MySQL Anda. Misalnya, apakah performa kueri lambat untuk:

Semua kueri yang berjalan di instans server fleksibel Azure Database for MySQL?
Sekumpulan kueri tertentu?
Kueri tertentu?

Perlu diingat juga bahwa setiap perubahan terbaru pada struktur atau data dasar tabel yang Anda kueri dapat memengaruhi performa.

Mengaktifkan fungsionalitas pengelogan

Sebelum menganalisis masing-masing kueri, Anda perlu menentukan tolok ukur kueri. Dengan informasi ini, Anda dapat menerapkan fungsionalitas pengelogan di server database untuk melacak kueri yang melebihi ambang yang ditentukan berdasarkan kebutuhan aplikasi.

Server fleksibel Azure Database for MySQL, disarankan untuk menggunakan fitur log kueri lambat untuk mengidentifikasi kueri yang membutuhkan waktu lebih lama dari N detik untuk dijalankan. Setelah mengidentifikasi kueri dari log kueri lambat, Anda dapat menggunakan diagnostik MySQL untuk memecahkan masalah kueri tersebut.

Sebelum dapat mulai melacak kueri yang berjalan lama, Anda perlu mengaktifkan parameter slow_query_log dengan menggunakan portal Azure atau Azure CLI. Dengan mengaktifkan parameter ini, Anda juga harus mengonfigurasi nilai parameter long_query_time untuk menentukan jumlah detik yang dapat dijalankan kueri sebelum diidentifikasi sebagai kueri yang "berjalan lambat". Nilai default parameter adalah 10 detik, tetapi Anda dapat menyesuaikan nilai tersebut untuk mengatasi kebutuhan SLA aplikasi Anda.

Meskipun log kueri lambat adalah alat yang bagus untuk melacak kueri yang berjalan lama, ada skenario tertentu yang mungkin membuatnya tidak efektif. Misalnya, log kueri lambat:

Berdampak negatif pada performa jika jumlah kueri sangat tinggi atau jika pernyataan kueri sangat besar. Sesuaikan nilai parameter long_query_time yang sesuai.
Mungkin tidak membantu jika Anda juga mengaktifkan parameter log_queries_not_using_index, yang menetapkan untuk mencatat kueri yang diharapkan mengambil semua baris. Kueri yang menjalankan pemindaian indeks penuh memanfaatkan indeks, tetapi akan dicatat karena indeks tidak membatasi jumlah baris yang ditampilkan.

Mengambil informasi dari log

Log tersedia hingga tujuh hari sejak pembuatannya. Anda dapat membuat daftar dan mengunduh log kueri lambat melalui portal Azure atau Azure CLI. Di portal Azure, navigasikan ke server Anda, di bagian Pemantauan, pilih Log server, lalu pilih panah ke bawah di samping entri untuk mengunduh log yang terkait dengan tanggal dan waktu yang diselidiki.

Selain itu, jika log kueri lambat Anda terintegrasi dengan log Azure Monitor melalui Log diagnostik, Anda dapat menjalankan kueri di editor untuk menganalisisnya lebih lanjut:

AzureDiagnostics
| where Resource == '<your server name>'
| where Category == 'MySqlSlowLogs'
| project TimeGenerated, Resource , event_class_s, start_time_t , query_time_d, sql_text_s
| where query_time_d > 10

Catatan

Contoh selengkapnya untuk membantu Anda memulai mendiagnosis log kueri lambat melalui log Diagnostik dapat dilihat di Menganalisis log di Azure Monitor Logs.

Snapshot berikut menggambarkan sampel kueri lambat.

# Time: 2021-11-13T10:07:52.610719Z
# User@Host: root[root] @  [172.30.209.6]  Id: 735026
# Query_time: 25.314811  Lock_time: 0.000000 Rows_sent: 126  Rows_examined: 443308
use employees;
SET timestamp=1596448847;
select * from titles where DATE(from_date) > DATE('1994-04-05') AND title like '%senior%';;

Perhatikan bahwa kueri berjalan dalam 26 detik, memeriksa lebih dari 443 ribu baris, dan menampilkan 126 baris hasil.

Biasanya, Anda harus fokus pada kueri dengan nilai tinggi untuk Query_time dan Rows_examined. Namun, jika Anda melihat kueri dengan Query_time tinggi tetapi hanya beberapa Rows_examined, ini sering kali menunjukkan adanya penyempitan sumber daya. Untuk kasus ini, periksa apakah ada pembatasan IO atau penggunaan CPU.

Membuat profil kueri

Setelah mengidentifikasi kueri tertentu yang berjalan lambat, Anda bisa menggunakan perintah EXPLAIN dan pembuatan profil untuk mengumpulkan detail selengkapnya.

Untuk memeriksa rencana kueri, jalankan perintah berikut:

EXPLAIN <QUERY>

Catatan

Untuk informasi selengkapnya tentang menggunakan pernyataan EXPLAIN, lihat Cara menggunakan EXPLAIN untuk memprofilkan performa kueri di server fleksibel Azure Database for MySQL.

Selain membuat paket EXPLAIN untuk kueri, Anda dapat menggunakan perintah SHOW PROFILE, yang memungkinkan Anda mendiagnosis eksekusi pernyataan yang telah dijalankan dalam sesi saat ini.

Untuk mengaktifkan pembuatan profil dan membuat profil kueri tertentu dalam sesi, jalankan kumpulan perintah berikut:

SET profiling = 1;
<QUERY>;
SHOW PROFILES;
SHOW PROFILE FOR QUERY <X>;

Catatan

Pembuatan profil masing-masing kueri hanya tersedia dalam sesi dan pernyataan historis tidak dapat diprofilkan.

Mari lihat lebih dekat menggunakan perintah ini untuk membuat profil kueri. Pertama, aktifkan pembuatan profil untuk sesi saat ini, jalankan perintah SET PROFILING = 1:

mysql> SET PROFILING = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Selanjutnya, jalankan kueri suboptimal yang menjalankan pemindaian tabel penuh:

mysql> select * from sbtest8 where c like '%99098187165%';
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k       | c                                                                                                                       | pad                                                         |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 10 | 5035785 | 81674956652-89815953173-84507133182-62502329576-99098187165-62672357237-37910808188-52047270287-89115790749-78840418590 | 91637025586-81807791530-84338237594-90990131533-07427691758 |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
1 row in set (27.60 sec)

Kemudian, tampilkan daftar semua profil kueri yang tersedia dengan menjalankan perintah SHOW PROFILES:

mysql> SHOW PROFILES;
+----------+-------------+----------------------------------------------------+
| Query_ID | Duration    | Query                                              |
+----------+-------------+----------------------------------------------------+
|        1 | 27.59450000 | select * from sbtest8 where c like '%99098187165%' |
+----------+-------------+----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Terakhir, untuk menampilkan profil kueri 1, jalankan perintah SHOW PROFILE FOR QUERY 1.

mysql> SHOW PROFILE FOR QUERY 1;
+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000102 |
| checking permissions |  0.000028 |
| Opening tables       |  0.000033 |
| init                 |  0.000035 |
| System lock          |  0.000018 |
| optimizing           |  0.000017 |
| statistics           |  0.000025 |
| preparing            |  0.000019 |
| executing            |  0.000011 |
| Sending data         | 27.594038 |
| end                  |  0.000041 |
| query end            |  0.000014 |
| closing tables       |  0.000013 |
| freeing items        |  0.000088 |
| cleaning up          |  0.000020 |
+----------------------+-----------+
15 rows in set, 1 warning (0.00 sec)

Membuat daftar kueri yang paling sering digunakan di server database

Setiap kali Anda memecahkan masalah performa kueri, sangat berguna untuk memahami kueri mana yang paling sering dijalankan pada instans server fleksibel Azure Database for MySQL Anda. Anda dapat menggunakan informasi ini untuk mengukur apakah ada kueri teratas yang membutuhkan waktu lebih lama dari biasanya untuk dijalankan. Selain itu, pengembang atau DBA dapat menggunakan informasi ini untuk mengidentifikasi apakah ada kueri yang tiba-tiba meningkat dalam jumlah dan durasi eksekusi kueri.

Untuk mencantumkan 10 kueri teratas yang paling banyak dijalankan terhadap instans server fleksibel Azure Database for MySQL Anda, jalankan kueri berikut:

SELECT digest_text AS normalized_query,
 count_star AS all_occurrences,
 Concat(Round(sum_timer_wait / 1000000000000, 3), ' s') AS total_time,
 Concat(Round(min_timer_wait / 1000000000000, 3), ' s') AS min_time,
 Concat(Round(max_timer_wait / 1000000000000, 3), ' s') AS max_time,
 Concat(Round(avg_timer_wait / 1000000000000, 3), ' s') AS avg_time,
 Concat(Round(sum_lock_time / 1000000000000, 3), ' s') AS total_locktime,
 sum_rows_affected AS sum_rows_changed,
 sum_rows_sent AS sum_rows_selected,
 sum_rows_examined AS sum_rows_scanned,
 sum_created_tmp_tables,
 sum_select_scan,
 sum_no_index_used,
 sum_no_good_index_used
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

Catatan

Gunakan kueri ini sebagai tolok ukur kueri teratas yang dijalankan di server database Anda dan tentukan apakah ada perubahan pada kueri teratas atau apakah kueri yang ada di tolok ukur awal telah meningkat dalam durasi eksekusi.

Mencantumkan 10 kueri termahal menurut total waktu eksekusi

Output dari kueri berikut ini menyediakan informasi tentang 10 kueri teratas yang berjalan terhadap server database dan jumlah eksekusinya di server database. Ini juga menyediakan informasi berguna lainnya seperti latensi kueri, waktu kuncinya, jumlah tabel sementara yang dibuat sebagai bagian dari runtime kueri, dll. Gunakan output kueri ini untuk melacak kueri teratas pada database dan perubahan pada faktor-faktor seperti latensi, yang mungkin menunjukkan kesempatan untuk menyempurnakan kueri lebih lanjut untuk membantu menghindari risiko di masa mendatang.

SELECT REPLACE(event_name, 'statement/sql/', '') AS statement, 
 count_star AS all_occurrences , 
 Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_latency, 
 Concat(Round(avg_timer_wait / 1000000000000, 2), ' s') AS avg_latency, 
 Concat(Round(sum_lock_time / 1000000000000, 2), ' s') AS total_lock_time  , 
 sum_rows_affected AS sum_rows_changed, 
 sum_rows_sent AS  sum_rows_selected, 
 sum_rows_examined AS  sum_rows_scanned, 
 sum_created_tmp_tables,  sum_created_tmp_disk_tables, 
 IF(sum_created_tmp_tables = 0, 0, Concat( Truncate(sum_created_tmp_disk_tables / 
 sum_created_tmp_tables * 100, 0))) AS 
 tmp_disk_tables_percent, 
 sum_select_scan, 
 sum_no_index_used, 
 sum_no_good_index_used 
FROM performance_schema.events_statements_summary_global_by_event_name 
WHERE event_name LIKE 'statement/sql/%' 
 AND count_star > 0 
ORDER BY sum_timer_wait DESC 
LIMIT 10;

Memantau pengumpulan sampah InnoDB

Ketika pengumpulan sampah InnoDB diblokir atau tertunda, database dapat mengembangkan jeda pembersihan substansial yang dapat berdampak negatif pada pemanfaatan penyimpanan dan performa kueri.

Panjang daftar riwayat segmen putar kembali InnoDB (HLL) mengukur jumlah rekaman perubahan yang disimpan dalam log pembatalan. Nilai HLL yang berkembang menunjukkan bahwa utas pengumpulan sampah InnoDB (utas pembersihan) tidak mengikuti beban kerja tulis atau pembersihan diblokir oleh kueri atau transaksi yang berjalan lama.

Penundaan yang berlebihan dalam pengumpulan sampah dapat memiliki konsekuensi negatif yang parah:

Tabel sistem InnoDB akan diperluas, sehingga mempercepat pertumbuhan volume penyimpanan yang mendasarinya. Terkadang, ruang tabel sistem dapat membengkak oleh beberapa terabyte sebagai akibat dari pembersihan yang diblokir.
Rekaman yang ditandai dengan penghapusan tidak akan dihapus tepat waktu. Ini dapat menyebabkan tabel InnoDB tumbuh dan mencegah mesin menggunakan kembali penyimpanan yang ditempati oleh rekaman tersebut.
Performa semua kueri mungkin menurun, dan pemanfaatan CPU dapat meningkat karena pertumbuhan struktur penyimpanan InnoDB.

Akibatnya, penting untuk memantau nilai, pola, dan tren HLL.

Menemukan nilai HLL

Anda dapat menemukan nilai HLL dengan menjalankan perintah tampilkan status innodb mesin. Nilai akan tercantum dalam output, di bawah judul TRANSACTIONS:

mysql> show engine innodb status\G 
*************************** 1. row *************************** 
 
(...) 
 
------------ 
TRANSACTIONS 
------------ 
Trx id counter 52685768 
Purge done for trx's n:o < 52680802 undo n:o < 0 state: running but idle 
History list length 2964300 
 
(...)

Anda juga dapat menentukan nilai HLL dengan mengkueri tabel information_schema.innodb_metrics:

mysql> select count from information_schema.innodb_metrics  
    -> where name = 'trx_rseg_history_len'; 
+---------+ 
|  count  | 
+---------+ 
| 2964300 | 
+---------+ 
1 row in set (0.00 sec)

Menginterpretasikan nilai HLL

Saat menginterpretasikan nilai HLL, pertimbangkan panduan yang tercantum dalam tabel berikut:

Nilai	Catatan
Kurang dari ~10.000	Nilai normal, menunjukkan bahwa pengumpulan sampah tidak ketinggalan.
Antara ~10.000 hingga ~1.000.000	Nilai tersebut menunjukkan jeda kecil dalam pengumpulan sampah. Nilai tersebut mungkin dapat diterima jika tetap stabil dan tidak meningkat.
Lebih besar dari ~1.000.000	Nilai-nilai ini harus diselidiki dan mungkin memerlukan tindakan korektif

Mengatasi nilai HLL yang berlebihan

Jika HLL menunjukkan lonjakan besar atau menunjukkan pola pertumbuhan berkala, selidiki kueri dan transaksi yang berjalan di instans server fleksibel Azure Database for MySQL Anda segera. Kemudian Anda dapat menyelesaikan masalah beban kerja apa pun yang mungkin mencegah kemajuan proses pengumpulan sampah. Meskipun database tidak diharapkan bebas dari lag pembersihan, Anda tidak boleh membiarkan lag tumbuh tak terkendali.

Untuk mendapatkan informasi transaksi dari tabel information_schema.innodb_trx, misalnya, jalankan perintah berikut:

select * from information_schema.innodb_trx  
order by trx_started asc\G

Detail dalam kolom trx_started akan membantu Anda menghitung usia transaksi.

mysql> select * from information_schema.innodb_trx  
    -> order by trx_started asc\G 
*************************** 1. row *************************** 
                    trx_id: 8150550 
                 trx_state: RUNNING 
               trx_started: 2021-11-13 20:50:11 
     trx_requested_lock_id: NULL 
          trx_wait_started: NULL 
                trx_weight: 0 
       trx_mysql_thread_id: 19 
                 trx_query: select * from employees where DATE(hire_date) > DATE('1998-04-05') AND first_name like '%geo%';
(…)

Untuk informasi tentang sesi database saat ini, termasuk waktu yang dihabiskan dalam status sesi saat ini, periksa tabel information_schema.processlist. Output berikut, misalnya, memperlihatkan sesi yang telah aktif menjalankan kueri selama 1462 detik terakhir:

mysql> select user, host, db, command, time, info  
    -> from information_schema.processlist  
    -> order by time desc\G 
*************************** 1. row *************************** 
   user: test 
   host: 172.31.19.159:38004 
     db: employees 
command: Query 
   time: 1462 
   info: select * from employees where DATE(hire_date) > DATE('1998-04-05') AND first_name like '%geo%';
 
(...)

Rekomendasi

Pastikan database Anda memiliki cukup sumber daya yang dialokasikan untuk menjalankan kueri. Terkadang, Anda mungkin perlu meningkatkan ukuran instans untuk mendapatkan lebih banyak core CPU dan memori tambahan untuk mengakomodasi beban kerja Anda.
Hindari transaksi besar atau jangka panjang dengan memecahnya menjadi transaksi yang lebih kecil.
Konfigurasikan innodb_purge_threads sesuai beban kerja Anda untuk meningkatkan efisiensi terhadap operasi pembersihan latar belakang.

Catatan

Uji setiap perubahan pada variabel server ini untuk setiap lingkungan guna mengukur perubahan perilaku mesin.
Gunakan peringatan pada "Persentase CPU Host", "Persentase Memori Host" dan "Total Koneksi" agar 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.

Bagikan melalui

Memecahkan masalah performa kueri di Azure Database for MySQL - Server Fleksibel

Mengaktifkan fungsionalitas pengelogan

Mengambil informasi dari log

Membuat profil kueri

Membuat daftar kueri yang paling sering digunakan di server database

Mencantumkan 10 kueri termahal menurut total waktu eksekusi

Memantau pengumpulan sampah InnoDB

Menemukan nilai HLL

Menginterpretasikan nilai HLL

Mengatasi nilai HLL yang berlebihan

Rekomendasi

Langkah berikutnya

Saran dan Komentar

Sumber Daya Tambahan: