MySQL Performance Optimization: Complete Guide for DBAs

-- Check current connections and processes
SHOW PROCESSLIST;

-- View performance schema settings
SELECT * FROM performance_schema.setup_instruments 
WHERE NAME LIKE '%statement%' AND ENABLED = 'YES';

-- Check slow query log status
SHOW VARIABLES LIKE 'slow_query_log%';

-- Check buffer pool statistics
SELECT 
    pool_id,
    pool_size,
    free_buffers,
    database_pages,
    old_database_pages,
    modified_database_pages,
    pending_decompress,
    pending_reads,
    pending_flush_lru,
    pending_flush_list
FROM information_schema.innodb_buffer_pool_stats;

-- Basic EXPLAIN
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- Extended EXPLAIN with JSON format
EXPLAIN FORMAT=JSON 
SELECT u.name, p.title 
FROM users u 
JOIN posts p ON u.id = p.user_id 
WHERE u.created_at > '2024-01-01';

-- EXPLAIN ANALYZE (MySQL 8.0+)
EXPLAIN ANALYZE 
SELECT * FROM orders 
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

-- Bad: Function on column prevents index usage
SELECT * FROM orders WHERE YEAR(order_date) = 2024;

-- Good: Rewrite to use index
SELECT * FROM orders 
WHERE order_date >= '2024-01-01' 
AND order_date < '2025-01-01';

-- Ensure proper JOIN order and indexing
SELECT 
    u.username,
    COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.active = 1
GROUP BY u.id, u.username
HAVING order_count > 5;

-- Create supporting indexes
CREATE INDEX idx_users_active ON users(active);
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Convert correlated subqueries to JOINs when possible
-- Bad: Correlated subquery
SELECT * FROM users u 
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id AND o.status = 'completed'
);

-- Good: JOIN alternative
SELECT DISTINCT u.* 
FROM users u 
INNER JOIN orders o ON u.id = o.user_id 
WHERE o.status = 'completed';

-- Primary key (automatically indexed)
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    username VARCHAR(100) NOT NULL
);

-- Composite unique index
CREATE UNIQUE INDEX idx_user_email_status 
ON users(email, status);

-- Order matters in composite indexes
CREATE INDEX idx_order_date_status_customer 
ON orders(order_date, status, customer_id);

-- This index supports these queries efficiently:
-- WHERE order_date = '2024-01-01'
-- WHERE order_date = '2024-01-01' AND status = 'completed'
-- WHERE order_date = '2024-01-01' AND status = 'completed' AND customer_id = 123

-- Include all needed columns in the index
CREATE INDEX idx_user_lookup 
ON users(email, username, first_name, last_name);

-- This query can be satisfied entirely from the index
SELECT username, first_name, last_name 
FROM users 
WHERE email = '[email protected]';

-- Check index usage
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    INDEX_NAME,
    SEQ_IN_INDEX,
    COLUMN_NAME,
    CARDINALITY
FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

-- Find unused indexes
SELECT 
    s.TABLE_SCHEMA,
    s.TABLE_NAME,
    s.INDEX_NAME,
    s.COLUMN_NAME
FROM information_schema.STATISTICS s
LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage t
    ON s.TABLE_SCHEMA = t.OBJECT_SCHEMA
    AND s.TABLE_NAME = t.OBJECT_NAME
    AND s.INDEX_NAME = t.INDEX_NAME
WHERE t.INDEX_NAME IS NULL
    AND s.TABLE_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema');

# my.cnf optimization for InnoDB
[mysqld]
# Buffer Pool (70-80% of available RAM)
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8

# Log Files
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_log_buffer_size = 64M

# I/O Configuration
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# Transaction Log
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# Table Configuration
innodb_file_per_table = 1
innodb_open_files = 4000

# Connection settings
max_connections = 500
max_connect_errors = 10000
connect_timeout = 10
wait_timeout = 300
interactive_timeout = 300

# Query cache (disabled in MySQL 8.0+)
query_cache_type = 0
query_cache_size = 0

# Thread handling
thread_cache_size = 100
thread_pool_size = 16

-- Check current configuration
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'max_connections';

-- Monitor buffer pool usage
SELECT 
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status 
     WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data') /
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status 
     WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total') * 100 
AS buffer_pool_utilization_percent;

-- Check current engine usage
SELECT 
    ENGINE,
    COUNT(*) as table_count,
    SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 as size_mb
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema')
GROUP BY ENGINE;

-- Convert MyISAM to InnoDB
ALTER TABLE your_table ENGINE = InnoDB;

-- Top 10 slowest queries
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT/1000000000 as avg_time_seconds,
    SUM_TIMER_WAIT/1000000000 as total_time_seconds
FROM performance_schema.events_statements_summary_by_digest 
ORDER BY SUM_TIMER_WAIT DESC 
LIMIT 10;

-- Table I/O statistics
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    COUNT_READ,
    COUNT_WRITE,
    COUNT_FETCH,
    SUM_TIMER_WAIT/1000000000 as total_time_seconds
FROM performance_schema.table_io_waits_summary_by_table 
WHERE OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema')
ORDER BY SUM_TIMER_WAIT DESC;

#!/bin/bash
# MySQL health check script

MYSQL_USER="monitor"
MYSQL_PASS="password"
MYSQL_HOST="localhost"

# Check replication lag
mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST -e "SHOW SLAVE STATUS\G" | grep Seconds_Behind_Master

# Check connection count
CONNECTIONS=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST -e "SHOW STATUS LIKE 'Threads_connected'" | awk '{print $2}' | tail -1)
echo "Current connections: $CONNECTIONS"

# Check slow queries
SLOW_QUERIES=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST -e "SHOW STATUS LIKE 'Slow_queries'" | awk '{print $2}' | tail -1)
echo "Slow queries: $SLOW_QUERIES"

-- Check I/O statistics
SELECT 
    FILE_NAME,
    EVENT_NAME,
    COUNT_READ,
    COUNT_WRITE,
    SUM_NUMBER_OF_BYTES_READ,
    SUM_NUMBER_OF_BYTES_WRITE
FROM performance_schema.file_summary_by_instance 
WHERE FILE_NAME LIKE '%.ibd'
ORDER BY (COUNT_READ + COUNT_WRITE) DESC
LIMIT 10;

-- Calculate optimal buffer pool size
SELECT 
    SUM(data_length + index_length) / 1024 / 1024 / 1024 as total_size_gb,
    SUM(data_length + index_length) * 1.2 / 1024 / 1024 / 1024 as recommended_buffer_pool_gb
FROM information_schema.tables 
WHERE engine = 'InnoDB';

-- Range partitioning example
CREATE TABLE sales (
    id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, sale_date)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Check partition statistics
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    PARTITION_NAME,
    TABLE_ROWS,
    DATA_LENGTH / 1024 / 1024 as data_size_mb
FROM information_schema.PARTITIONS 
WHERE TABLE_NAME = 'sales';

-- Force index usage
SELECT /*+ USE_INDEX(users, idx_email) */ * 
FROM users 
WHERE email = '[email protected]';

-- Join order hints
SELECT /*+ STRAIGHT_JOIN */ u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.active = 1;

-- Disable specific optimizations
SELECT /*+ NO_MERGE(sub) */ *
FROM (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    GROUP BY user_id
) sub
WHERE sub.order_count > 10;

# Python connection pool example
import mysql.connector.pooling

config = {
    'user': 'your_user',
    'password': 'your_password',
    'host': 'localhost',
    'database': 'your_database',
    'pool_name': 'mypool',
    'pool_size': 20,
    'pool_reset_session': True,
    'autocommit': True,
    'charset': 'utf8mb4',
    'use_unicode': True,
    'get_warnings': True,
    'sql_mode': 'TRADITIONAL'
}

pool = mysql.connector.pooling.MySQLConnectionPool(**config)

# Use connection from pool
def get_user_data(user_id):
    conn = pool.get_connection()
    try:
        cursor = conn.cursor(dictionary=True)
        cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
        return cursor.fetchone()
    finally:
        conn.close()  # Returns connection to pool

# Prepare test data
sysbench oltp_read_write \
    --mysql-host=localhost \
    --mysql-user=test \
    --mysql-password=password \
    --mysql-db=testdb \
    --tables=10 \
    --table-size=100000 \
    prepare

# Run benchmark
sysbench oltp_read_write \
    --mysql-host=localhost \
    --mysql-user=test \
    --mysql-password=password \
    --mysql-db=testdb \
    --tables=10 \
    --table-size=100000 \
    --threads=16 \
    --time=300 \
    --report-interval=10 \
    run

-- Create a stored procedure for performance testing
DELIMITER //
CREATE PROCEDURE BenchmarkQuery()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE start_time BIGINT;
    DECLARE end_time BIGINT;
    
    SET start_time = UNIX_TIMESTAMP(NOW(6)) * 1000000 + MICROSECOND(NOW(6));
    
    WHILE i < 1000 DO
        SELECT COUNT(*) FROM users WHERE active = 1;
        SET i = i + 1;
    END WHILE;
    
    SET end_time = UNIX_TIMESTAMP(NOW(6)) * 1000000 + MICROSECOND(NOW(6));
    
    SELECT CONCAT('Execution time: ', (end_time - start_time), ' microseconds') AS result;
END //
DELIMITER ;

-- Run the benchmark
CALL BenchmarkQuery();