Slow query log adalah tool diagnostic yang sangat valuable untuk performance tuning. Setelah menganalisis slow queries untuk berbagai production workloads, saya akan berbagi workflow untuk identify dan optimize query lambat.

Setup Slow Query Log

1. Enable Slow Query Log

# my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1

2. Dynamic Enable/Disable

-- Enable
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';
-- Disable
SET GLOBAL slow_query_log = 'OFF';

3. Output ke Table

-- Log ke mysql.slow_log table
SET GLOBAL log_output = 'TABLE';
SET GLOBAL slow_query_log = 'ON';
-- Query slow log
SELECT * FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10;

Analisis dengan Percona Toolkit

1. Install Percona Toolkit

# Ubuntu/Debian
sudo apt install percona-toolkit
CentOS/RHEL
sudo yum install percona-toolkit

2. Analisis dengan pt-query-digest

# Basic analysis
pt-query-digest /var/log/mysql/slow.log
Save ke table
pt-query-digest \
--review h=localhost,D=percona,t=query_review \
--history h=localhost,D=percona,t=query_history \
--no-report \
/var/log/mysql/slow.log
Analisis dengan filter
pt-query-digest \
--filter '$event->{arg} =~ m/^SELECT/i' \
/var/log/mysql/slow.log

Query Analysis

1. Identifikasi Patterns

-- Most frequent slow queries
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT/1000000000 as avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY COUNT_STAR DESC
LIMIT 10;
-- Slowest queries
SELECT 
DIGEST_TEXT,
MAX_TIMER_WAIT/1000000000 as max_ms,
AVG_TIMER_WAIT/1000000000 as avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

2. Analisis dengan EXPLAIN

-- Analisis slow query
EXPLAIN SELECT * FROM orders 
WHERE user_id = 123 
  AND created_at > '2024-01-01';
-- Extended EXPLAIN
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 123;

Common Slow Query Patterns

1. Missing Index

-- Sebelum: Full table scan
SELECT * FROM users WHERE email = '[email protected]';
-- Query_time: 5.234123  Lock_time: 0.000123 Rows_sent: 1  Rows_examined: 1000000
-- Solusi
CREATE INDEX idx_email ON users(email);
-- Setelah: Index lookup
SELECT * FROM users WHERE email = '[email protected]';
-- Query_time: 0.001234  Lock_time: 0.000012 Rows_sent: 1  Rows_examined: 1

2. Large Offset Pagination

-- Slow (large offset)
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- Query_time: 10.234123
-- Fast (keyset pagination)
SELECT * FROM orders 
WHERE id > 1000000 
ORDER BY id 
LIMIT 20;
-- Query_time: 0.012345

3. Complex Joins tanpa Indexes

-- Sebelum: Joins tanpa FK indexes
EXPLAIN SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'US';
-- type: ALL pada kedua tables
-- Solusi
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_customers_country ON customers(country);

Optimasi Strategies

1. Add Indexes

-- Single column index
CREATE INDEX idx_status ON orders(status);
-- Composite index
CREATE INDEX idx_user_date ON orders(user_id, created_at);
-- Covering index
CREATE INDEX idx_covering ON orders(user_id, status, total_amount);

2. Query Rewriting

-- Sebelum
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- Query_time: 5.123456 (full scan)
-- Sesudah
SELECT * FROM orders 
WHERE created_at >= '2024-01-01' 
AND created_at < '2025-01-01';
-- Query_time: 0.012345 (index range scan)

3. Limit Result Sets

-- Always use LIMIT
SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;
-- Untuk pagination, gunakan keyset
SELECT * FROM logs 
WHERE created_at < '2024-01-01' 
ORDER BY created_at DESC 
LIMIT 20;

Monitoring dan Alerting

1. Automated Analysis

#!/bin/bash
# analyze_slow_queries.sh
LOG_FILE="/var/log/mysql/slow.log"
ALERT_EMAIL="[email protected]"
THRESHOLD=10  # queries slower than 10 seconds
Count slow queries
SLOW_COUNT=$(pt-query-digest --no-report --output=json "$LOG_FILE" 2>/dev/null | grep -c 'Query_time')
if [ $SLOW_COUNT -gt $THRESHOLD ]; then
echo "ALERT: $SLOW_COUNT slow queries detected" | \
mail -s "[WARNING] High Slow Query Count" $ALERT_EMAIL
fi
Top 5 slow queries
pt-query-digest --limit=5 "$LOG_FILE" | mail -s "Top 5 Slow Queries" $ALERT_EMAIL

2. Grafana Dashboard

-- Query untuk Grafana
SELECT 
    COUNT(*) as slow_query_count,
    AVG(query_time) as avg_query_time
FROM mysql.slow_log
WHERE start_time > DATE_SUB(NOW(), INTERVAL 1 HOUR);

Best Practices

1. Enable slow query log di semua production
2. Set reasonable threshold: 1-2 seconds untuk OLTP
3. Monitor regularly: Weekly atau daily review
4. Act promptly: Fix sebelum users complain
5. Track improvements: Verify fixes work

Kesimpulan

Slow query log adalah tool essential untuk:
– Identifikasi query lambat
– Analisis patterns
– Track improvements
– Prevent performance degradation

Dengan analisis regular, Anda selalu tahu bottlenecks sebelum mereka impact users.