[mysqld]
slow_query_log                         = ON
slow_query_log_file                    = /var/log/mysql/mysql-slow.log
long_query_time                        = 1       # log queries taking > 1 second
log_queries_not_using_indexes          = ON      # also log full-table scans
log_slow_admin_statements              = ON      # log slow ALTER TABLE, ANALYZE, etc.
log_throttle_queries_not_using_indexes = 10      # max per minute (avoid log flood)
min_examined_row_limit                 = 100     # skip trivially small queries

SET GLOBAL slow_query_log                         = ON;
SET GLOBAL slow_query_log_file                    = '/var/log/mysql/mysql-slow.log';
SET GLOBAL long_query_time                        = 1;
SET GLOBAL log_queries_not_using_indexes          = ON;
SET GLOBAL log_slow_admin_statements              = ON;
SET GLOBAL min_examined_row_limit                 = 100;

-- MySQL 8.0+: persist so the setting survives restart
SET PERSIST slow_query_log     = ON;
SET PERSIST long_query_time    = 1;

# Time: 2024-06-15T14:32:01.234567Z
# User@Host: app[app] @ web-server [10.0.0.5]  Id: 1234
# Query_time: 4.231567  Lock_time: 0.000102  Rows_sent: 1  Rows_examined: 2500000
# SET timestamp=1718461921;
SELECT * FROM orders WHERE status = 'pending' AND created_at > '2024-01-01';

-- Capture everything in this session (threshold = 0 seconds)
SET SESSION long_query_time = 0;

-- Run the query you want to profile
SELECT * FROM products WHERE category_id = 5 ORDER BY created_at DESC;

-- Restore the global threshold
SET SESSION long_query_time = 1;

# -s t  : sort by total time (best for finding biggest offenders)
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

# -s at : sort by average time per execution
mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log

# -s c  : sort by call count (high frequency moderate queries)
mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log

# -s r  : sort by rows examined
mysqldumpslow -s r -t 10 /var/log/mysql/mysql-slow.log

# -g    : grep filter (regex)
mysqldumpslow -s t -t 10 -g 'orders' /var/log/mysql/mysql-slow.log

# Pipe through less for large output
mysqldumpslow -s t -t 20 /var/log/mysql/mysql-slow.log | less

# Install Percona Toolkit
apt-get install percona-toolkit          # Debian/Ubuntu
yum install percona-toolkit              # RHEL/CentOS

# Analyze a slow query log (full output)
pt-query-digest /var/log/mysql/mysql-slow.log

# Top 10 queries only
pt-query-digest --limit 10 /var/log/mysql/mysql-slow.log

# Only queries from the last hour
pt-query-digest --since 1h /var/log/mysql/mysql-slow.log

# Only queries for a specific database
pt-query-digest --filter '$event->{db} eq "myapp"' /var/log/mysql/mysql-slow.log

# Save report to a file
pt-query-digest /var/log/mysql/mysql-slow.log > analysis.txt

-- Top 10 slowest queries by average execution time
SELECT
  DIGEST_TEXT,
  COUNT_STAR                               AS executions,
  ROUND(AVG_TIMER_WAIT / 1e12, 3)         AS avg_sec,
  ROUND(SUM_TIMER_WAIT / 1e12, 3)         AS total_sec,
  ROUND(MAX_TIMER_WAIT / 1e12, 3)         AS max_sec,
  SUM_ROWS_EXAMINED                       AS total_rows_examined,
  SUM_ROWS_SENT                           AS total_rows_sent,
  SUM_NO_INDEX_USED                       AS no_index_used,
  SUM_NO_GOOD_INDEX_USED                  AS no_good_index_used
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = DATABASE()
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- Queries doing the most full table scans
SELECT DIGEST_TEXT, SUM_NO_INDEX_USED, COUNT_STAR
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_NO_INDEX_USED > 0
ORDER BY SUM_NO_INDEX_USED DESC
LIMIT 10;

-- Show current lock waits
SELECT r.trx_id waiting_trx_id,
       r.trx_mysql_thread_id waiting_thread,
       r.trx_query waiting_query,
       b.trx_id blocking_trx_id,
       b.trx_mysql_thread_id blocking_thread,
       b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

-- Check for long-running transactions
SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS seconds_open,
       trx_query
FROM information_schema.innodb_trx
ORDER BY seconds_open DESC;

# Option 1: Use logrotate (Linux)
# Create /etc/logrotate.d/mysql-slow:
# /var/log/mysql/mysql-slow.log {
#     daily
#     rotate 7
#     missingok
#     compress
#     delaycompress
#     create 640 mysql adm
#     postrotate
#         /bin/kill -HUP $(cat /var/run/mysqld/mysqld.pid 2>/dev/null) 2>/dev/null || true
#     endscript
# }

# Option 2: Flush the log at runtime (MySQL creates a new file)
mysqladmin -u root -p flush-logs

# Option 3: Rename and flush
mv /var/log/mysql/mysql-slow.log /var/log/mysql/mysql-slow.log.bak
# Then tell MySQL to reopen the file:
mysql -u root -p -e "FLUSH SLOW LOGS;"

-- Step 1: pt-query-digest identifies this as the top offender:
-- SELECT * FROM orders WHERE status = 'S' AND created_at > 'S'
-- Rows_examined: 2,500,000  Rows_sent: 1

-- Step 2: run EXPLAIN on the actual query
EXPLAIN SELECT * FROM orders
WHERE status = 'pending' AND created_at > '2024-01-01'G
-- type: ALL, key: NULL  <-- missing index confirmed

-- Step 3: check existing indexes
SHOW INDEX FROM orders;

-- Step 4: add the right index
CREATE INDEX idx_orders_status_date ON orders (status, created_at);

-- Step 5: verify with EXPLAIN
EXPLAIN SELECT * FROM orders
WHERE status = 'pending' AND created_at > '2024-01-01'G
-- type: range, key: idx_orders_status_date  <-- fixed

-- Step 6 (MySQL 8.0): confirm actual performance
EXPLAIN ANALYZE SELECT * FROM orders
WHERE status = 'pending' AND created_at > '2024-01-01'G

# Enable slow log in Docker MySQL without restarting the container
docker exec -it mysql-container mysql -u root -psecret   -e "SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1;"

# View the slow log inside the container
docker exec -it mysql-container tail -f /var/lib/mysql/$(hostname)-slow.log

# AWS RDS / Aurora: slow log is in CloudWatch Logs
# Enable via Parameter Group: slow_query_log = 1, long_query_time = 1
# View in AWS Console > CloudWatch > Log Groups > /aws/rds/instance/*/slowquery

-- On the replica: check if replication is applying statements slowly
SHOW SLAVE STATUSG
-- Look at: Seconds_Behind_Master -- if this grows, replication is lagging

-- Enable slow log on replica to capture slow applied statements
SET GLOBAL log_slow_replica_statements = ON;  -- MySQL 8.0
-- SET GLOBAL log_slow_slave_statements = ON;  -- MySQL 5.7

-- After enabling, analyze the replica's slow log separately
pt-query-digest /var/log/mysql/replica-slow.log