Kueri lambat yang tidak terduga adalah salah satu masalah kinerja MySQL yang paling umum. Kueri yang berkinerja baik dalam pengembangan dapat goyah saat ditekankan oleh beban kerja produksi. Aplikasi
Besar mungkin menjalankan ratusan kueri database unik setiap kali titik akhir tercapai. Hal ini menyulitkan untuk memilih kueri yang menyebabkan penundaan dalam respons server. Log kueri lambat MySQL adalah opsi debug yang dapat membantu Anda mengidentifikasi pernyataan SQL yang dicurigai, memberikan titik awal untuk penyelidikan Anda.
Mengaktifkan Log Kueri Lambat
Log adalah mekanisme bawaan untuk merekam kueri SQL yang berjalan lama. Kueri yang tidak selesai dalam waktu yang dikonfigurasi akan ditulis ke dalam log. Membaca konten log menunjukkan kepada Anda SQL yang dijalankan dan waktu yang dibutuhkan.
Logging kueri yang lambat dinonaktifkan secara default. Anda dapat mengaktifkannya di server Anda dengan menjalankan perintah berikut dari shell MySQL administratif:
SET GLOBAL slow_query_log_file=`/var/log/mysql/mysql-slow.log`; SET GLOBAL slow_query_log=1;
Perubahan langsung berlaku. Kueri lambat sekarang akan masuk ke /var/log/mysql/mysql-slow.log. Anda dapat meninjau file ini secara berkala untuk mengidentifikasi kueri yang berkinerja buruk.
MySQL menghitung kueri sebagai “lambat” jika dibutuhkan lebih dari 10 detik untuk diselesaikan. Batas ini biasanya terlalu longgar untuk aplikasi web yang dihadapi pengguna di mana respons yang hampir instan diharapkan. Anda dapat mengubah batas dengan menyetel variabel long_query_time:
SET GLOBAL long_query_time=1;
Nilai menetapkan durasi minimum untuk kueri lambat. Penting untuk menemukan keseimbangan yang sesuai dengan aplikasi Anda sendiri. Ambang yang terlalu tinggi akan mengecualikan kueri yang benar-benar memengaruhi kinerja. Sebaliknya, nilai yang sangat rendah dapat menyebabkan terlalu banyak kueri yang ditangkap, membuat log yang terlalu berisik.Menggunakan File Konfigurasi MySQL
Anda harus mengaktifkan log kueri lambat di file konfigurasi MySQL Anda jika Anda berencana untuk menggunakannya dalam jangka panjang. Ini akan memastikan logging dilanjutkan secara otomatis setelah server MySQL dimulai ulang.
Lokasi file konfigurasi dapat bervariasi menurut distribusi platform. Biasanya di /etc/mysql/my.cnf atau /etc/mysql/mysql.conf.d/mysqld.cnf. Tambahkan baris berikut untuk mereplikasi pengaturan yang diaktifkan secara dinamis di atas:
slow_query_log=1 slow_query_log_file=/var/log/mysql/mysql-slow.log long_query_time=1
Mulai ulang MySQL untuk menerapkan perubahan Anda:
$ sudo service mysql restart
Log kueri lambat akan sekarang aktif setiap kali server MySQL dimulai.
Menyesuaikan Konten Log
Log biasanya hanya menyertakan kueri SQL yang mengecualikan ambang batas “lambat” dan telah dikirimkan oleh aplikasi klien. Ini tidak termasuk operasi administratif lambat yang mungkin terjadi, seperti pembuatan indeks dan pengoptimalan tabel, serta kueri yang berpotensi lambat di masa mendatang.
Anda dapat memperluas log untuk menyertakan informasi ini dengan membuat perubahan berikut pada konfigurasi Anda file:
log_slow_admin_statements = 1
– Termasuk pernyataan SQL administratif seperti ALTER TABLE, CREATE INDEX, DROP INDEX, dan OPTIMIZE TABLE. Ini jarang diinginkan karena operasi ini biasanya dijalankan selama skrip pemeliharaan dan migrasi. Meskipun demikian, pengaturan ini dapat membantu jika aplikasi Anda juga secara dinamis melakukan tugas-tugas ini.
log_slow_replica_statements = 1
– Pengaturan ini memungkinkan pencatatan kueri lambat untuk kueri yang direplikasi di server replika. Ini dinonaktifkan secara default. Gunakan log_slow_slave_statements sebagai gantinya untuk MySQL versi 8.0.26 dan yang lebih lama.
log_queries_not_using_indexes = 1
– Saat pengaturan ini diaktifkan, kueri yang diharapkan untuk mengambil semua catatan dari tabel atau tampilan target akan dicatat, meskipun mereka tidak mengecualikan yang lambat ambang batas kueri. Ini dapat membantu mengidentifikasi ketika kueri kehilangan indeks atau tidak dapat menggunakannya. Kueri yang memiliki indeks yang tersedia akan tetap dicatat jika tidak memiliki batasan yang membatasi jumlah baris yang diambil.
Kueri pencatatan yang tidak menggunakan indeks dapat meningkatkan verbositas secara signifikan. Mungkin ada situasi di mana pemindaian indeks penuh diharapkan atau diperlukan. Kueri ini akan terus muncul di log meskipun tidak dapat diselesaikan.
Anda dapat menilai kueri batas tanpa indeks dengan menyetel variabel log_throttle_queries_not_using_indexes. Ini menentukan jumlah maksimum log yang akan ditulis dalam periode 60 detik. Nilai 10 berarti hingga 10 kueri akan direkam per menit. Setelah peristiwa kesepuluh, tidak ada lagi kueri yang akan dicatat hingga jendela 60 detik berikutnya terbuka.
Menafsirkan Log Kueri Lambat
Setiap kueri yang masuk ke log kueri lambat akan menampilkan serangkaian baris yang terlihat seperti berikut:
# Waktu : 2022-07-12T19:00:00.000000Z # Pengguna@Host: demo[demo] @ mysql [] Id: 51 # Query_time: 3.514223 Lock_time: 0.000010 Rows_sent: 5143 Rows_examined: 322216 SELECT * FROM slow_table KIRI GABUNG another_table …
Baris yang dikomentari di atas kueri berisi waktu saat kueri dijalankan, pengguna MySQL yang terhubung dengan klien, dan statistik yang menyediakan durasi dan jumlah baris yang dikirim. Contoh di atas membutuhkan waktu 3,5 detik untuk diselesaikan dan melihat lebih dari 320.000 baris, sebelum hanya mengirim 5.143 ke klien. Ini bisa menjadi indikasi bahwa indeks yang hilang menyebabkan MySQL memeriksa terlalu banyak catatan.
Anda dapat secara opsional memasukkan lebih banyak informasi dalam log dengan mengatur variabel sistem log_slow_extra = 1 di file konfigurasi Anda. Ini akan menambahkan ID utas, jumlah byte yang diterima dan dikirim, dan jumlah baris yang dipertimbangkan untuk diurutkan, serta jumlah permintaan khusus pernyataan yang memberikan visibilitas tentang cara MySQL menangani kueri.
File log harus diperlakukan dengan hati-hati sebagai isinya akan sensitif. Kueri ditampilkan secara penuh, tanpa penyembunyian nilai parameter. Ini berarti data pengguna akan ada jika Anda menggunakan log kueri lambat di server produksi. Akses harus dibatasi untuk pengembang dan administrator basis data yang menyetel pernyataan SQL. Pencatatan dan Pencadangan Kueri Lambat
Salah satu frustrasi umum dengan log kueri lambat muncul ketika Anda juga menggunakan MySQLDump untuk membuat cadangan basis data. Kueri SELECT * FROM … yang berjalan lama akan dieksekusi untuk mengambil data dari tabel Anda dan memasukkannya ke dalam cadangan Anda. Mereka akan dimasukkan dalam log kueri lambat seperti pernyataan SQL lainnya. Ini dapat mencemari log jika Anda membuat cadangan secara teratur.
Anda dapat mengatasinya dengan menonaktifkan sementara log kueri lambat sebelum menjalankan mysqldump. Anda dapat mengaktifkan kembali log setelah pencadangan selesai. Sesuaikan skrip cadangan Anda sehingga terlihat seperti berikut:
#!/bin/bash # Nonaktifkan sementara logging kueri lambat mysql -uUser -pPassword -e “SET GLOBAL slow_query_log=0”; # Jalankan mysqldump mysqldump -uUser -pPassword –single-transaction databaseName | gzip > backup.bak # Aktifkan lagi log kueri lambat mysql -uUser -pPassword -e “SET GLOBAL slow_query_log=1”
Ini akan menjaga aktivitas MySQLDump keluar dari log kueri lambat, sehingga lebih mudah untuk fokus pada SQL yang dijalankan oleh Anda application.
Summary
Log kueri lambat MySQL adalah salah satu cara paling efektif untuk menentukan penyebab masalah kinerja. Mulailah dengan memperkirakan penundaan yang Anda alami dan gunakan nilai ini sebagai long_query_time Anda. Kurangi nilainya jika tidak ada yang muncul di log setelah Anda mereproduksi masalah.
Log kueri lambat tidak akan memberi tahu Anda cara memperbaiki perlambatan. Namun kemampuan untuk melihat SQL tepat yang diterima oleh server memungkinkan Anda mengulangi pernyataan berkinerja buruk dan kemudian mengukur efek pengoptimalan. Menambahkan indeks atau batasan yang hilang dapat menjadi perbedaan antara kueri yang menyentuh ribuan baris dan kueri yang berfungsi dengan segelintir.
Itulah berita seputar Cara Mengaktifkan Log Kueri Lambat MySQL, semoga bermanfaat. Disadur dari HowToGeek.com.