# 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

-- 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';

-- 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;

# Ubuntu/Debian
sudo apt install percona-toolkit

# CentOS/RHEL
sudo yum install percona-toolkit

# 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

-- 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;

-- 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;

-- Sebelum: Full table scan
SELECT * FROM users WHERE email = 'test@example.com';
-- 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 = 'test@example.com';
-- Query_time: 0.001234  Lock_time: 0.000012 Rows_sent: 1  Rows_examined: 1

-- 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

-- 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);

-- 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);

-- 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)

-- 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;

#!/bin/bash
# analyze_slow_queries.sh

LOG_FILE="/var/log/mysql/slow.log"
ALERT_EMAIL="dba@example.com"
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

-- 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);