Cara menggunakan mysql terabyte database

Lompati ke konten utama

Browser ini sudah tidak didukung.

Mutakhirkan ke Microsoft Edge untuk memanfaatkan fitur, pembaruan keamanan, dan dukungan teknis terkini.

Pemecahan masalah performa kueri di Azure Database for MySQL

• Artikel
• 09/27/2022
• 9 menit untuk membaca

Dalam artikel ini

BERLAKU UNTUK:

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

• Semua kueri yang berjalan di server 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.

Dengan Azure Database for MySQL, sebaiknya gunakan fitur log kueri lambat untuk mengidentifikasi kueri yang memakan 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 == ''
| where Category == 'MySqlSlowLogs'
| project TimeGenerated, Resource , event_class_s, start_time_t , query_time_d, sql_text_s
| where query_time_d > 10

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 dapat menggunakan perintah EXPLAIN dan pembuatan profil untuk mengumpulkan detail tambahan.

Untuk memeriksa rencana kueri, jalankan perintah berikut:

EXPLAIN 

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;
;
SHOW PROFILES;
SHOW PROFILE FOR QUERY ;

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, akan sangat membantu untuk memahami kueri mana yang paling sering dijalankan di server 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 membuat daftar 10 kueri yang paling sering dijalankan terhadap server 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.

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:

Mengatasi nilai HLL yang berlebihan

Jika HLL menunjukkan lonjakan besar atau menunjukkan pola pertumbuhan berkala, segera selidiki kueri dan transaksi yang berjalan di instans Azure Database for MySQL Anda. 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.

Bagaimana cara membuat database di MySQL?

Langkah membuat database phpMyAdmin?

Amazon menggunakan database apa?

Apa saja software database yang terbaik?