MySQL Performance Optimization: Complete Tuning and Monitoring Guide

📅 Published on September 30, 2025 👤 by ⏱️ 10 min read
Database

Master advanced MySQL performance optimization techniques for high-traffic applications. Learn query optimization, indexing strategies, and server configuration tuning to maximize database performance.

Understanding MySQL Performance Fundamentals

Database performance is critical for application responsiveness and scalability. This comprehensive guide covers MySQL optimization strategies from query-level improvements to server-wide configuration tuning.

1. Query Optimization and Analysis

Identify and optimize slow-performing queries using MySQL analysis tools:

Query Analysis with EXPLAIN

# Enable slow query log
SET GLOBAL slow_query_log = "ON";
SET GLOBAL slow_query_log_file = "/var/log/mysql/slow.log";
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = "ON";

# Analyze query execution plan
EXPLAIN SELECT u.id, u.name, p.title, p.created_at
FROM users u
JOIN posts p ON u.id = p.user_id
WHERE u.status = "active"
AND p.published_at > "2023-01-01"
ORDER BY p.created_at DESC
LIMIT 10;

# Extended explain for more details
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = "user@example.com";

# Analyze query performance
SELECT
    ROUND(SUM(timer_end - timer_start) / 1000000000000, 6) as query_time_seconds,
    sql_text
FROM performance_schema.events_statements_history_long
WHERE sql_text LIKE "%SELECT%"
GROUP BY sql_text
ORDER BY query_time_seconds DESC
LIMIT 10;

Query Optimization Techniques

# Inefficient query example
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date >= "2023-01-01"
AND c.country = "USA";

# Optimized version
SELECT o.id, o.total_amount, o.order_date, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date >= "2023-01-01"
AND c.country = "USA"
AND o.status = "completed";

# Use covering indexes when possible
CREATE INDEX idx_orders_covering ON orders (order_date, status, customer_id, total_amount);

# Optimize WHERE clauses
-- Bad: function on column
SELECT * FROM orders WHERE YEAR(order_date) = 2023;

-- Good: range condition
SELECT * FROM orders WHERE order_date >= "2023-01-01" AND order_date = "2023-01-01"
);

# Pagination optimization
-- Inefficient for large offsets
SELECT * FROM products ORDER BY id LIMIT 10000, 10;

-- Efficient cursor-based pagination
SELECT * FROM products WHERE id > 10000 ORDER BY id LIMIT 10;

2. Index Strategy and Optimization

Design and implement effective indexing strategies for optimal query performance:

Index Types and Usage

# Primary and unique indexes
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

# Composite indexes for multiple column queries
CREATE INDEX idx_user_status_created ON users (status, created_at);
CREATE INDEX idx_order_customer_date ON orders (customer_id, order_date, status);

# Partial indexes for specific conditions
CREATE INDEX idx_active_users ON users (email) WHERE status = "active";

# Functional indexes (MySQL 8.0+)
CREATE INDEX idx_email_domain ON users ((SUBSTRING_INDEX(email, "@", -1)));

# Full-text indexes for search
CREATE FULLTEXT INDEX idx_product_search ON products (title, description);
SELECT * FROM products
WHERE MATCH(title, description) AGAINST("laptop computer" IN NATURAL LANGUAGE MODE);

Index Monitoring and Maintenance

# Analyze index usage
SELECT
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_FETCH,
    COUNT_INSERT,
    COUNT_UPDATE,
    COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = "your_database"
ORDER BY COUNT_FETCH DESC;

# Find unused indexes
SELECT
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    s.INDEX_NAME,
    s.COLUMN_NAME
FROM information_schema.TABLES t
LEFT JOIN information_schema.STATISTICS s ON t.TABLE_SCHEMA = s.TABLE_SCHEMA
    AND t.TABLE_NAME = s.TABLE_NAME
LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage p
    ON s.TABLE_SCHEMA = p.OBJECT_SCHEMA
    AND s.TABLE_NAME = p.OBJECT_NAME
    AND s.INDEX_NAME = p.INDEX_NAME
WHERE t.TABLE_SCHEMA = "your_database"
AND s.INDEX_NAME IS NOT NULL
AND p.INDEX_NAME IS NULL
AND s.INDEX_NAME != "PRIMARY";

# Index cardinality analysis
SELECT
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    CARDINALITY,
    SUB_PART,
    PACKED,
    NULLABLE,
    INDEX_TYPE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = "your_database"
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

3. Server Configuration Optimization

Configure MySQL server parameters for optimal performance:

Memory Configuration

# /etc/mysql/conf.d/performance.cnf
[mysqld]
# InnoDB Buffer Pool (most important setting)
# Set to 70-80% of available RAM for dedicated database server
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4

# InnoDB Log Configuration
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# Query Cache (deprecated in MySQL 8.0)
# query_cache_type = 1
# query_cache_size = 128M
# query_cache_limit = 4M

# Connection Settings
max_connections = 200
max_user_connections = 50
connect_timeout = 10
wait_timeout = 300
interactive_timeout = 300

# Thread Settings
thread_cache_size = 16
thread_stack = 256K

# Table Settings
table_open_cache = 4000
table_definition_cache = 2000

# Temporary Tables
tmp_table_size = 256M
max_heap_table_size = 256M

# MyISAM Settings (if using MyISAM tables)
key_buffer_size = 128M
myisam_sort_buffer_size = 128M

# Binary Logging
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 500M
sync_binlog = 1

# Slow Query Log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 1000

Performance Monitoring Configuration

# Enable Performance Schema
performance_schema = ON
performance_schema_max_table_instances = 12500
performance_schema_max_table_handles = 4000

# Enable specific instruments
UPDATE performance_schema.setup_instruments
SET ENABLED = "YES"
WHERE NAME LIKE "stage/sql/%";

UPDATE performance_schema.setup_instruments
SET ENABLED = "YES", TIMED = "YES"
WHERE NAME LIKE "statement/sql/%";

# Monitor key metrics
SELECT
    EVENT_NAME,
    COUNT_STAR,
    SUM_TIMER_WAIT/1000000000000 AS TOTAL_TIME_SEC,
    AVG_TIMER_WAIT/1000000000000 AS AVG_TIME_SEC,
    MAX_TIMER_WAIT/1000000000000 AS MAX_TIME_SEC
FROM performance_schema.events_statements_summary_global_by_event_name
WHERE COUNT_STAR > 0
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

4. Advanced Optimization Techniques

Implement advanced strategies for high-performance MySQL systems:

Partitioning for Large Tables

# Range partitioning by date
CREATE TABLE orders_partitioned (
    id INT AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10,2),
    status VARCHAR(20),
    PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

# Hash partitioning for even distribution
CREATE TABLE user_activities (
    id BIGINT AUTO_INCREMENT,
    user_id INT NOT NULL,
    activity_type VARCHAR(50),
    activity_data JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, user_id)
) PARTITION BY HASH(user_id) PARTITIONS 8;

# Partition pruning example
EXPLAIN PARTITIONS
SELECT * FROM orders_partitioned
WHERE order_date >= "2023-01-01" AND order_date < "2023-12-31";

Read Replica Configuration

# Master configuration
# /etc/mysql/conf.d/master.cnf
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
binlog-do-db = production_db

# Create replication user
CREATE USER "replicator"@"%" IDENTIFIED BY "secure_replication_password";
GRANT REPLICATION SLAVE ON *.* TO "replicator"@"%";
FLUSH PRIVILEGES;

# Get master status
SHOW MASTER STATUS;

# Slave configuration
# /etc/mysql/conf.d/slave.cnf
[mysqld]
server-id = 2
relay-log = mysql-relay-bin
read-only = 1
replicate-do-db = production_db

# Configure slave connection
CHANGE MASTER TO
    MASTER_HOST="master-server-ip",
    MASTER_USER="replicator",
    MASTER_PASSWORD="secure_replication_password",
    MASTER_LOG_FILE="mysql-bin.000001",
    MASTER_LOG_POS=154;

START SLAVE;
SHOW SLAVE STATUSG

5. Performance Monitoring and Alerting

Implement comprehensive monitoring for MySQL performance:

Key Performance Metrics

# Connection monitoring
SHOW STATUS LIKE "Connections";
SHOW STATUS LIKE "Max_used_connections";
SHOW STATUS LIKE "Threads_connected";
SHOW STATUS LIKE "Threads_running";

# Query performance metrics
SHOW STATUS LIKE "Questions";
SHOW STATUS LIKE "Queries";
SHOW STATUS LIKE "Slow_queries";
SHOW STATUS LIKE "Select_scan";
SHOW STATUS LIKE "Select_full_join";

# InnoDB metrics
SHOW STATUS LIKE "Innodb_buffer_pool_read_requests";
SHOW STATUS LIKE "Innodb_buffer_pool_reads";
SHOW STATUS LIKE "Innodb_buffer_pool_pages_dirty";
SHOW STATUS LIKE "Innodb_log_waits";

# Lock monitoring
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
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

Automated Performance Analysis Script

#!/bin/bash
# mysql-performance-check.sh

MYSQL_USER="monitor"
MYSQL_PASS="monitor_password"
MYSQL_HOST="localhost"
LOG_FILE="/var/log/mysql-performance.log"
ALERT_EMAIL="admin@example.com"

log() {
    echo "[$(date "+%Y-%m-%d %H:%M:%S")] $*" | tee -a "$LOG_FILE"
}

# Check connection usage
check_connections() {
    local max_conn=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST -e "SHOW VARIABLES LIKE "max_connections";" | tail -1 | awk "{print $2}")
    local current_conn=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST -e "SHOW STATUS LIKE "Threads_connected";" | tail -1 | awk "{print $2}")
    local usage_percent=$((current_conn * 100 / max_conn))

    log "Connection usage: $current_conn/$max_conn ($usage_percent%)"

    if [ $usage_percent -gt 80 ]; then
        log "WARNING: High connection usage detected"
        echo "High MySQL connection usage: $usage_percent%" | mail -s "MySQL Alert" "$ALERT_EMAIL"
    fi
}

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

    # Check if slow queries increased significantly in last hour
    local prev_slow=$(tail -n 100 "$LOG_FILE" | grep "Slow queries:" | tail -2 | head -1 | awk "{print $4}" || echo "0")
    local increase=$((slow_queries - prev_slow))

    if [ $increase -gt 10 ]; then
        log "WARNING: Slow query increase detected: +$increase"
        echo "MySQL slow query increase: +$increase queries" | mail -s "MySQL Slow Query Alert" "$ALERT_EMAIL"
    fi
}

# Check InnoDB buffer pool efficiency
check_buffer_pool() {
    local reads=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST -e "SHOW STATUS LIKE "Innodb_buffer_pool_reads";" | tail -1 | awk "{print $2}")
    local read_requests=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST -e "SHOW STATUS LIKE "Innodb_buffer_pool_read_requests";" | tail -1 | awk "{print $2}")

    if [ $read_requests -gt 0 ]; then
        local hit_rate=$(echo "scale=2; (($read_requests - $reads) / $read_requests) * 100" | bc)
        log "InnoDB buffer pool hit rate: $hit_rate%"

        if (( $(echo "$hit_rate < 95" | bc -l) )); then
            log "WARNING: Low buffer pool hit rate: $hit_rate%"
            echo "Low MySQL buffer pool hit rate: $hit_rate%" | mail -s "MySQL Buffer Pool Alert" "$ALERT_EMAIL"
        fi
    fi
}

# Main function
main() {
    log "Starting MySQL performance check"
    check_connections
    check_slow_queries
    check_buffer_pool
    log "MySQL performance check completed"
}

main "$@"
Performance Tips: Always test configuration changes in a development environment first, monitor performance metrics continuously, and keep regular backups before making significant changes.

Conclusion

MySQL performance optimization requires a comprehensive approach covering query optimization, proper indexing, server configuration tuning, and continuous monitoring. Regular analysis and maintenance ensure optimal database performance as your application scales.

Remember that optimization is an iterative process – measure performance, identify bottlenecks, implement improvements, and measure again to ensure positive impact.

Add Comment