Cara Mengaktifkan Slow Query Log di MySQL dan MariaDB

Log kueri lambat adalah fitur diagnostik bawaan MySQL dan MariaDB yang mencatat setiap pernyataan SQL yang waktu eksekusinya melebihi ambang batas yang dapat dikonfigurasi. Fitur ini merekam durasi kueri, waktu kunci, baris yang diperiksa, baris yang dikirim, dan teks SQL lengkap — memberikan administrator database dan pengembang jejak audit berbasis file yang tepat dari setiap kueri yang menurunkan performa aplikasi.

Mengaktifkannya adalah salah satu tindakan paling efektif yang dapat Anda lakukan selama penyetelan performa database. Tidak seperti alat pemantauan generik, log kueri lambat menunjukkan pernyataan yang tepat yang bertanggung jawab atas latensi, menjadikannya sangat diperlukan untuk optimasi indeks, restrukturisasi kueri, dan perencanaan kapasitas di server mana pun — dari lingkungan VPS Hosting penyewa tunggal hingga kluster database dedicated multi-node.

Mengapa Log Kueri Lambat Penting Melampaui Pemantauan Dasar

Sebagian besar tim menggunakan EXPLAIN atau SHOW PROCESSLIST secara reaktif, setelah pengguna melaporkan kelambatan. Log kueri lambat bekerja secara proaktif: ia mengumpulkan bukti selama berjam-jam atau berhari-hari dari lalu lintas nyata, menangkap pelanggar intermiten yang tidak pernah muncul selama jendela inspeksi manual.

Manfaat operasional utama meliputi:

• Isolasi bottleneck — membedakan pemindaian tabel penuh yang terikat CPU dari masalah pertentangan kunci menggunakan rasio Query_time vs. Lock_time
• Analisis kesenjangan indeks — flag log_queries_not_using_indexes menampilkan setiap kueri yang melakukan pemindaian penuh, terlepas dari waktu eksekusi mentahnya
• Deteksi regresi — membandingkan snapshot log sebelum dan sesudah deployment mengungkapkan apakah kode baru memperkenalkan pola kueri yang lebih lambat
• Bukti perencanaan kapasitas — nilai Rows_examined yang jauh lebih tinggi daripada Rows_sent mengindikasikan indeks yang hilang atau salah digunakan, yang semakin parah di bawah beban

MySQL vs. MariaDB: Perbandingan Fitur Log Kueri Lambat

Kedua mesin berbagi infrastruktur log kueri lambat inti yang sama yang diwarisi dari MySQL 5.1, tetapi MariaDB telah memperluasnya dalam beberapa cara yang berarti.

Opsi log_slow_verbosity dan log_slow_rate_limit di MariaDB sangat berharga di lingkungan produksi dengan throughput tinggi di mana pencatatan setiap kueri lambat itu sendiri akan menjadi beban performa.

Langkah 1: Temukan File Konfigurasi

MySQL dan MariaDB membaca konfigurasi mereka dari jalur default yang berbeda tergantung pada distribusi dan metode instalasi.

MySQL:

/etc/my.cnf (berbasis RPM: RHEL, CentOS, AlmaLinux, Rocky Linux)
/etc/mysql/my.cnf (Debian/Ubuntu)
/etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu dengan paket mysql-server)

MariaDB:

/etc/my.cnf.d/server.cnf (berbasis RPM)
/etc/mysql/mariadb.conf.d/50-server.cnf (Debian/Ubuntu)
/etc/mysql/mariadb.cnf (tata letak Debian lama)

Jika Anda tidak yakin file mana yang aktif, kueri proses yang sedang berjalan:
mysqld --verbose --help 2>/dev/null | grep -A1 "Default options"
Ini mencetak daftar file yang dipesan secara tepat yang dibaca daemon saat startup, termasuk direktori !includedir mana pun.
Buka file konfigurasi utama dengan editor pilihan Anda:
sudo nano /etc/my.cnf
Langkah 2: Tambahkan Direktif Log Kueri Lambat ke [mysqld]
Semua parameter log kueri lambat termasuk dalam bagian [mysqld]. Jika bagian tersebut tidak ada, buat di bagian atas file.
[mysqld]
# Core slow query log settings
slow_query_log          = 1
slow_query_log_file     = /var/log/mysql/slow-query.log
long_query_time         = 1

# Log queries that skip index usage entirely
log_queries_not_using_indexes = 1

# Avoid flooding the log with index warnings on low-traffic tables
min_examined_row_limit  = 100

# Log slow administrative statements (ALTER TABLE, OPTIMIZE TABLE, etc.)
log_slow_admin_statements = 1
Penjelasan parameter:

slow_query_log = 1 — mengaktifkan fitur; atur ke 0 untuk menonaktifkan tanpa menghapus blok
slow_query_log_file — jalur absolut ke file log; pengguna proses MySQL/MariaDB (mysql) harus memiliki akses tulis ke direktori induk
long_query_time = 1 — ambang batas dalam detik, menerima nilai desimal (misalnya, 0.5 untuk 500 ms); default 10 detik hampir selalu terlalu permisif untuk aplikasi web
log_queries_not_using_indexes — mencatat kueri pemindaian penuh terlepas dari long_query_time; kombinasikan dengan min_examined_row_limit untuk menekan noise dari tabel kecil
min_examined_row_limit — kueri harus memeriksa setidaknya sejumlah baris ini sebelum memenuhi syarat untuk dicatat di bawah log_queries_not_using_indexes; mencegah pencarian satu baris sepele dari mencemari log
log_slow_admin_statements — menangkap operasi tingkat skema yang memblokir tabel dan sering diabaikan sebagai sumber latensi

Tambahan khusus MariaDB yang layak diaktifkan di produksi:
# MariaDB only — extended per-query statistics in the log
log_slow_verbosity      = query_plan,explain

# MariaDB only — log only 1 in every N qualifying queries (rate limiting)
log_slow_rate_limit     = 10
log_slow_verbosity = query_plan,explain menambahkan rencana eksekusi optimizer langsung ke setiap entri log, menghilangkan kebutuhan untuk menjalankan ulang EXPLAIN secara manual setelahnya — penghemat waktu yang signifikan saat mendiagnosis kueri yang hanya muncul di bawah pola beban produksi.
Langkah 3: Buat File Log dan Atur Izin
Jika direktori target tidak ada, buat dan tetapkan kepemilikan sebelum memulai ulang layanan. Melewati langkah ini adalah salah satu alasan paling umum mengapa log kueri lambat gagal diaktifkan secara diam-diam.
sudo mkdir -p /var/log/mysql
sudo touch /var/log/mysql/slow-query.log
sudo chown mysql:mysql /var/log/mysql/slow-query.log
sudo chmod 640 /var/log/mysql/slow-query.log
Pada sistem yang menerapkan SELinux (RHEL, CentOS, AlmaLinux), konteks file juga harus diatur dengan benar:
sudo semanage fcontext -a -t mysqld_log_t "/var/log/mysql(/.*)?"
sudo restorecon -Rv /var/log/mysql
Kegagalan mengatur konteks SELinux yang benar menyebabkan daemon berhasil dimulai tetapi secara diam-diam melewati penulisan ke file log — kasus tepi yang membuat frustrasi yang tidak menghasilkan kesalahan jelas di /var/log/messages.
Langkah 4: Mulai Ulang Layanan Database
Terapkan perubahan konfigurasi dengan memulai ulang layanan. Pada distribusi berbasis systemd (standar di server Linux modern mana pun):
# MySQL
sudo systemctl restart mysqld

# MariaDB
sudo systemctl restart mariadb
Pada sistem berbasis init.d yang lebih lama:
# MySQL
sudo service mysqld restart

# MariaDB
sudo service mariadb restart
Setelah restart, periksa bahwa layanan berjalan dengan baik:
sudo systemctl status mysqld    # or mariadb
sudo journalctl -u mysqld -n 50 --no-pager
Kesalahan konfigurasi apa pun di my.cnf akan mencegah startup dan muncul di output journal.
Langkah 5: Aktifkan Log Kueri Lambat saat Runtime (Tanpa Restart)
Untuk server produksi di mana restart mengganggu, MySQL dan MariaDB mendukung pengaktifan log kueri lambat secara dinamis melalui SET GLOBAL. Perubahan yang dibuat dengan cara ini langsung berlaku tetapi tidak bertahan setelah restart layanan kecuali juga ditulis ke my.cnf.
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 1;
SET GLOBAL min_examined_row_limit = 100;
Ini adalah pendekatan yang tepat untuk diagnostik darurat pada sistem langsung — aktifkan, ambil sampel 15–30 menit selama lalu lintas puncak, lalu nonaktifkan lagi tanpa menyentuh file konfigurasi atau memulai ulang daemon.
Langkah 6: Verifikasi Konfigurasi
Hubungkan ke klien MySQL atau MariaDB:
mysql -u root -p
Kemudian jalankan pencocokan pola terhadap tabel variabel sistem:
SHOW VARIABLES LIKE '%slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
Output yang diharapkan untuk instance yang dikonfigurasi dengan benar:
+-------------------------------+-------------------------------+
| Variable_name                 | Value                         |
+-------------------------------+-------------------------------+
| slow_query_log                | ON                            |
| slow_query_log_file           | /var/log/mysql/slow-query.log |
+-------------------------------+-------------------------------+

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| long_query_time | 1.000 |
+-----------------+-------+

+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+
Anda juga dapat mengonfirmasi bahwa log sedang ditulis dengan memeriksa penghitung kueri lambat:
SHOW GLOBAL STATUS LIKE 'Slow_queries';
Penghitung ini bertambah setiap kali kueri melebihi long_query_time, terlepas dari apakah pencatatan file aktif — berguna untuk mengonfirmasi bahwa kueri lambat benar-benar terjadi sebelum Anda menghabiskan waktu menganalisis file log yang kosong.
Langkah 7: Membaca dan Menginterpretasikan Log Mentah
Gunakan tail untuk memantau log secara real time selama uji beban atau jendela lalu lintas puncak:
sudo tail -f /var/log/mysql/slow-query.log
Entri log tipikal terlihat seperti ini:
# Time: 2024-10-11T12:45:23.489187Z
# User@Host: app_user[app_user] @ 10.0.1.45 []  Id: 1042
# Query_time: 4.561529  Lock_time: 0.000115  Rows_sent: 1  Rows_examined: 847293
# Bytes_sent: 512
SET timestamp=1697030723;
SELECT * FROM orders WHERE customer_email = 'user@example.com' ORDER BY created_at DESC;
Apa yang diceritakan setiap field:

Query_time — total waktu eksekusi jam dinding dalam detik
Lock_time — waktu yang dihabiskan menunggu kunci tabel atau baris; rasio tinggi Lock_time terhadap Query_time menunjukkan pertentangan, bukan indeks yang hilang
Rows_sent — baris yang dikembalikan ke klien
Rows_examined — baris yang dipindai mesin penyimpanan untuk menghasilkan hasil; rasio Rows_examined / Rows_sent di atas 100:1 adalah sinyal kuat dari indeks yang hilang atau kurang selektif
Bytes_sent — hadir dalam verbositas diperluas MariaDB; berguna untuk mengidentifikasi kueri yang mengembalikan kumpulan hasil yang terlalu besar

Dalam contoh di atas, kueri memeriksa 847.293 baris untuk mengembalikan 1 baris. Menambahkan indeks pada customer_email akan mengurangi Rows_examined menjadi sekitar 1, memotong waktu eksekusi dari 4,5 detik menjadi sub-milidetik.
Langkah 8: Analisis Log dengan mysqldumpslow dan pt-query-digest
Membaca file log mentah tidak praktis dalam skala besar. Dua alat mengagregasi dan mengurutkan kueri lambat berdasarkan dampak total.
Menggunakan mysqldumpslow (Dibundel dengan MySQL/MariaDB)
# Top 10 queries by total execution time
sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

# Top 10 queries by average execution time
sudo mysqldumpslow -s at -t 10 /var/log/mysql/slow-query.log

# Top 10 queries by rows examined
sudo mysqldumpslow -s r -t 10 /var/log/mysql/slow-query.log
mysqldumpslow menormalkan parameter kueri (mengganti nilai literal dengan N atau S) sehingga kueri yang identik secara struktural dengan nilai parameter berbeda dikelompokkan bersama — penting untuk mengidentifikasi pola frekuensi tinggi.
Menggunakan pt-query-digest (Percona Toolkit — Direkomendasikan untuk Produksi)
# Install Percona Toolkit (Debian/Ubuntu)
sudo apt-get install percona-toolkit

# Install Percona Toolkit (RHEL/CentOS/AlmaLinux)
sudo yum install percona-toolkit

# Generate a full digest report
sudo pt-query-digest /var/log/mysql/slow-query.log

# Show only the top 5 queries by total time
sudo pt-query-digest --limit 5 /var/log/mysql/slow-query.log

# Output to a file for later review
sudo pt-query-digest /var/log/mysql/slow-query.log > /tmp/slow_query_report.txt
pt-query-digest menghasilkan laporan berperingkat yang menampilkan fingerprint setiap kueri, total waktu eksekusi, waktu rata-rata, jumlah panggilan, dan distribusi persentil. Ini jauh lebih kuat dari mysqldumpslow dan merupakan alat standar yang digunakan oleh DBA profesional untuk analisis log lambat.
Langkah 9: Konfigurasi Rotasi Log dengan logrotate
Tanpa rotasi, log kueri lambat tumbuh tanpa batas. Pada server sibuk dengan long_query_time diatur ke 1 detik, file dapat mencapai beberapa gigabyte dalam beberapa hari.
Buat konfigurasi logrotate khusus:
sudo nano /etc/logrotate.d/mysql-slow
/var/log/mysql/slow-query.log {
    daily
    rotate 14
    missingok
    notifempty
    compress
    delaycompress
    sharedscripts
    postrotate
        /usr/bin/mysqladmin flush-logs 2>/dev/null || true
    endscript
}
Penjelasan direktif utama:

rotate 14 — menyimpan 14 hari arsip terkompresi; sesuaikan berdasarkan anggaran disk dan persyaratan audit Anda
compress / delaycompress — mengompresi file yang dirotasi dengan gzip, tetapi menunda kompresi satu siklus untuk menghindari kompresi file yang mungkin masih terbuka oleh daemon
postrotate — menjalankan mysqladmin flush-logs setelah rotasi, yang memberi sinyal kepada daemon untuk menutup handle file log saat ini dan membuka yang baru; tanpa ini, MySQL/MariaDB terus menulis ke file yang diganti namanya hingga restart berikutnya

Paksa rotasi manual untuk menguji konfigurasi:
sudo logrotate -f /etc/logrotate.d/mysql-slow
Langkah 10: Nonaktifkan Log Kueri Lambat Saat Tidak Lagi Diperlukan
Pencatatan log kueri lambat yang terus-menerus pada ambang batas rendah (misalnya, 0,5 detik) pada server lalu lintas tinggi menambah overhead I/O yang terukur. Nonaktifkan setelah Anda mengumpulkan data yang cukup:
Melalui file konfigurasi (persisten):
[mysqld]
slow_query_log = 0
Kemudian mulai ulang layanan:
sudo systemctl restart mysqld   # or mariadb
Melalui variabel runtime (segera, tidak persisten):
SET GLOBAL slow_query_log = 'OFF';
Metode runtime lebih disukai selama jam produksi — berlaku dalam milidetik tanpa downtime.
Lanjutan: Menggunakan performance_schema sebagai Pelengkap
Log kueri lambat menangkap kueri yang melebihi ambang batas waktu. Tabel performance_schema events_statements_summary_by_digest menangkap statistik agregat untuk setiap pola kueri yang berbeda, terlepas dari waktu eksekusi. Menggunakan keduanya bersama memberikan gambaran lengkap.
SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    ROUND(SUM_TIMER_WAIT / 1e12, 3)     AS total_time_sec,
    ROUND(AVG_TIMER_WAIT / 1e12, 6)     AS avg_time_sec,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
Kueri ini menampilkan 10 pola kueri yang paling memakan waktu di seluruh riwayat pernyataan — termasuk kueri cepat yang berjalan jutaan kali dan secara kolektif mendominasi waktu CPU, yang tidak pernah akan ditangkap oleh log kueri lambat.
Pertimbangan Lingkungan Hosting
Ambang batas long_query_time yang optimal sangat bergantung pada peran server dan profil sumber daya:

Lingkungan shared hosting — biasanya tidak ada akses langsung ke my.cnf; gunakan SET GLOBAL jika penyedia hosting memberikan hak istimewa SUPER atau SYSTEM_VARIABLES_ADMIN, atau minta akses log lambat melalui panel kontrol
Lingkungan VPS — akses root penuh berarti kontrol penuh atas semua parameter konfigurasi; instalasi VPS dengan cPanel mengekspos pengaturan log kueri lambat melalui MySQL Configuration Editor WHM, yang menulis langsung ke my.cnf

Jika tumpukan aplikasi Anda menyertakan frontend web yang dikelola melalui Panel Kontrol VPS, menghubungkan timestamp log kueri lambat dengan timestamp log akses HTTP aplikasi Anda adalah metode efektif untuk melacak latensi database kembali ke permintaan yang dihadapi pengguna tertentu.

Matriks Keputusan Praktis: Memilih Ambang Batas yang Tepat

Daftar Periksa Teknis dan Poin Penting

Sebelum menutup investigasi kueri lambat, verifikasi masing-masing hal berikut:

• Bagian [mysqld] di my.cnf berisi slow_query_log = 1, jalur slow_query_log_file yang valid, dan long_query_time yang sesuai untuk profil lalu lintas Anda
• File log dan direktori induknya dimiliki oleh pengguna sistem mysql dengan izin tulis; pada sistem SELinux, konteks file diatur ke mysqld_log_t

SHOW VARIABLES LIKE '%slow_query%' mengonfirmasi slow_query_log = ON dan jalur file yang benar setelah restart layanan
SHOW GLOBAL STATUS LIKE 'Slow_queries' menampilkan penghitung yang bukan nol dan bertambah, mengonfirmasi bahwa kueri yang memenuhi syarat benar-benar terjadi
log_queries_not_using_indexes diaktifkan dan dipasangkan dengan min_examined_row_limit untuk mencegah pencarian satu baris sepele membanjiri log
log_slow_admin_statements diaktifkan untuk menangkap ALTER TABLE, OPTIMIZE TABLE, dan operasi DDL serupa yang merupakan sumber umum kunci tabel yang tidak terduga
Konfigurasi logrotate sudah ada dengan hook postrotate yang memanggil mysqladmin flush-logs

FAQ

Apakah mengaktifkan log kueri lambat mempengaruhi performa database?

Pada ambang batas 1 detik atau lebih tinggi pada beban kerja produksi tipikal, overhead-nya dapat diabaikan — biasanya di bawah 1% dari total waktu eksekusi kueri. Overhead menjadi terukur hanya ketika long_query_time diatur di bawah 0,1 detik atau ketika log_queries_not_using_indexes diaktifkan pada skema dengan banyak tabel kecil yang tidak terindeks. Gunakan log_slow_rate_limit (MariaDB) atau naikkan min_examined_row_limit untuk mengurangi hal ini.

Bisakah saya mengaktifkan log kueri lambat tanpa memulai ulang MySQL atau MariaDB?

Ya. Gunakan SET GLOBAL slow_query_log = 'ON' dan SET GLOBAL long_query_time = 1 dari sesi klien MySQL mana pun dengan hak istimewa SUPER atau SYSTEM_VARIABLES_ADMIN. Perubahan langsung berlaku. Tulis nilai yang sama ke my.cnf agar persisten setelah restart.

Apa perbedaan antara Query_time dan Lock_time dalam log kueri lambat?

Query_time adalah total waktu jam dinding yang berlalu dari saat server menerima kueri hingga mengirim baris terakhir ke klien. Lock_time adalah bagian dari total tersebut yang dihabiskan menunggu untuk mendapatkan kunci tabel atau baris. Kueri dengan Lock_time mendekati Query_time adalah masalah pertentangan kunci, bukan masalah indeks — perbaikannya melibatkan desain transaksi atau pengurangan cakupan kunci, bukan penambahan indeks.

Mengapa file log kueri lambat saya kosong meskipun slow_query_log = ON?

Penyebab paling umum adalah: (1) tidak ada kueri yang benar-benar melebihi long_query_time — verifikasi dengan SHOW GLOBAL STATUS LIKE 'Slow_queries'; (2) jalur file log tidak ada atau pengguna mysql tidak memiliki izin tulis; (3) pada sistem SELinux, konteks file salah; (4) variabel slow_query_log_file menunjuk ke jalur yang berbeda dari file yang Anda periksa — konfirmasi dengan SHOW VARIABLES LIKE 'slow_query_log_file'.

Bagaimana cara menemukan kueri paling merusak dalam log kueri lambat?

Jalankan pt-query-digest dan urutkan berdasarkan R/Call (baris yang diperiksa per panggilan) atau Response time (total waktu kumulatif). Kueri di bagian atas peringkat Response time mengonsumsi waktu database agregat terbanyak dan harus menjadi target pertama untuk analisis EXPLAIN dan optimasi indeks. Jika pt-query-digest tidak tersedia, gunakan mysqldumpslow -s t -t 1 untuk mengekstrak kueri dengan total waktu tertinggi tunggal.