Intermediate LevelSQL & MySQL
How do you configure and analyze the MySQL Slow Query Log?
❓ Interview Prompt: Explain MySQL Slow Query Log, `slow_query_log`, `long_query_time`, `log_queries_not_using_indexes`, and analyzing output with `pt-query-digest`.
The Slow Query Log records queries whose execution time exceeds a specified threshold ('long_query_time'):
Configuration Steps:
1
Set 'slow_query_log = 1'.
2
Set 'long_query_time = 0.5' (Logs queries taking $> 500\text{ms}$).
3
Set 'log_queries_not_using_indexes = 1'.
4
Analyze log output using 'pt-query-digest' to rank queries by cumulative lock and execution time.
Code Implementation Example
bash
# Analyzing MySQL Slow Query Log with Percona Toolkit pt-query-digest:
pt-query-digest /var/log/mysql/mysql-slow.log > slow_report.txt💡Analyzing MySQL slow query log output using pt-query-digest.
🎯 Interview Key Takeaway
Enable the Slow Query Log and analyze reports with pt-query-digest to identify and optimize query bottlenecks.
#MySQL#Slow Query Log#Performance#pt-query-digest#Intermediate
Back to all SQL & MySQL Questions→