Master MySQL Ops: From Config Tuning to Query Performance Boost

MySQL Operations Practical Guide: From Configuration Tuning to SQL Performance Optimization

Author Bio: Senior operations engineer with 8 years of database performance optimization experience, having optimized MySQL clusters for systems with tens of millions of users.

🔥 Opening: A Real Production Incident

Last month, our core business system experienced massive timeouts; investigation revealed MySQL CPU at 99% and many slow queries. Through configuration and SQL optimization, service was restored within 30 minutes.

This incident taught me that MySQL tuning is a core competency for ops engineers.

📊 Part 1: MySQL Configuration Tuning – Make Your Database Fly

1.1 Memory Configuration: Proper Allocation Is Key

# my.cnf core memory configuration
[mysqld]
# Buffer pool size: usually 70-80% of physical memory
innodb_buffer_pool_size = 8G

# Buffer pool instances: improve concurrency
innodb_buffer_pool_instances = 8

# Log buffer: reduce disk I/O
innodb_log_buffer_size = 64M

# Query cache (important for read‑intensive apps, removed in MySQL 8.0)
query_cache_size = 256M
query_cache_type = 1

Practical tip: How to determine the optimal innodb_buffer_pool_size?

-- View buffer pool usage
SELECT ROUND(A.num*100.0/ B.num, 2) AS buffer_pool_hit_rate
FROM (SELECT variable_value AS num FROM performance_schema.global_status WHERE variable_name='Innodb_buffer_pool_read_requests') A,
     (SELECT variable_value AS num FROM performance_schema.global_status WHERE variable_name='Innodb_buffer_pool_reads') B;

Buffer pool hit rate should stay above 99%.

1.2 Connection and Thread Optimization

# Connection related settings
max_connections = 2000
max_connect_errors = 10000
connect_timeout = 60
wait_timeout = 28800
interactive_timeout = 28800

# Thread cache
thread_cache_size = 64
thread_concurrency = 16

Ops experience: max_connections is not “the larger the better”; set according to server capacity.

Monitor Threads_connected and Threads_running to avoid connection spikes.

1.3 InnoDB Core Parameter Tuning

# InnoDB core settings
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 75
innodb_io_capacity = 2000
innodb_read_io_threads = 8
innodb_write_io_threads = 8

Performance improvement case: Changing innodb_flush_log_at_trx_commit from 1 to 2 increased TPS by 40% (trade‑off with durability).

🚀 Part 2: SQL Performance Optimization – Eliminate Slow Queries

2.1 Index Optimization: The Ops Engineer’s Secret Weapon

Golden rule for composite indexes:

-- Wrong: separate indexes
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_create_time ON orders(create_time);

-- Correct: composite index based on query pattern
CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time);

Index usage analysis:

-- Find unused indexes
SELECT object_schema, object_name, index_name, count_star, count_read,
       count_insert, count_update, count_delete
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL AND count_star = 0
  AND object_schema = 'your_database'
ORDER BY object_schema, object_name;

2.2 Query Optimization Cases

Case 1: Pagination on a table with tens of millions of rows

-- Traditional pagination (slow)
SELECT * FROM user_logs
WHERE user_id = 12345
ORDER BY created_at DESC
LIMIT 1000000, 20;

-- Optimized pagination (100× faster)
SELECT * FROM user_logs
WHERE user_id = 12345
  AND id < (
    SELECT id FROM user_logs
    WHERE user_id = 12345
    ORDER BY created_at DESC
    LIMIT 1000000, 1
  )
ORDER BY created_at DESC
LIMIT 20;

Case 2: Subquery to JOIN conversion

-- Slow subquery
SELECT * FROM orders o
WHERE o.user_id IN (
  SELECT u.id FROM users u WHERE u.level = 'VIP'
);

-- Optimized JOIN
SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.level = 'VIP';

2.3 Slow Query Log Analysis

Enable slow query log:

slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON

Analyze with pt‑query‑digest:

# Install percona‑toolkit
sudo apt-get install percona-toolkit

# Analyze slow log
pt-query-digest /var/log/mysql/slow.log > slow_query_report.txt

⚡ Part 3: Advanced Tuning Techniques

3.1 Read‑Write Splitting Configuration

# Python example using PyMySQL for read‑write splitting
import pymysql, random

class MySQLPool:
    def __init__(self):
        # Master (write)
        self.master = {
            'host': '192.168.1.10',
            'user': 'root',
            'password': 'password',
            'database': 'mydb'
        }
        # Slaves (read)
        self.slaves = [
            {'host': '192.168.1.11', 'user': 'root', 'password': 'password', 'database': 'mydb'},
            {'host': '192.168.1.12', 'user': 'root', 'password': 'password', 'database': 'mydb'}
        ]

    def get_read_connection(self):
        slave_config = random.choice(self.slaves)
        return pymysql.connect(**slave_config)

    def get_write_connection(self):
        return pymysql.connect(**self.master)

3.2 MySQL Monitoring Script

#!/bin/bash
# MySQL performance monitoring script

mysql_status() {
    mysql -e "SHOW GLOBAL STATUS;" | grep -E "(Connections|Questions|Threads_running|Slow_queries)"
}

innodb_status() {
    mysql -e "SHOW ENGINE INNODB STATUS\G" | grep -A 10 "BUFFER POOL AND MEMORY"
}

current_queries() {
    mysql -e "SELECT * FROM information_schema.processlist WHERE COMMAND != 'Sleep' ORDER BY TIME DESC;"
}

echo "=== MySQL Performance Monitor ===="

echo "1. Connection Status:"
mysql_status

echo ""

echo "2. InnoDB Buffer Pool:"
innodb_status

echo ""

echo "3. Current Running Queries:"
current_queries

3.3 Sharding Strategy

Horizontal sharding examples:

-- Time‑based tables
CREATE TABLE user_logs_202501 LIKE user_logs;
CREATE TABLE user_logs_202502 LIKE user_logs;

-- Hash‑based tables
CREATE TABLE user_data_0 LIKE user_data;
CREATE TABLE user_data_1 LIKE user_data;

-- Routing logic (Python)
def get_table_name(user_id, table_count=10):
    return f"user_data_{user_id % table_count}"

📈 Part 4: Production Experience

4.1 MySQL Fault Diagnosis Process

# 1. Check MySQL service status
systemctl status mysql

# 2. View error log
tail -f /var/log/mysql/error.log

# 3. Check disk space
df -h

# 4. Show current connections
mysql -e "SHOW PROCESSLIST;"

# 5. Analyze slow queries
mysql -e "SELECT * FROM information_schema.processlist WHERE TIME > 10;"

4.2 Backup and Recovery Best Practices

# Hot backup script
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backup/mysql"
DB_NAME="production_db"

# Logical backup with mysqldump
mysqldump --single-transaction --routines --triggers \
  --master-data=2 --databases $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

# Physical backup with xtrabackup (recommended)
xtrabackup --backup --target-dir=$BACKUP_DIR/full_${DATE}

4.3 High‑Availability Architecture Deployment

MySQL master‑slave replication configuration:

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
gtid-mode = ON
enforce-gtid-consistency = ON

[mysqld]
server-id = 2
relay-log = relay-bin
read-only = 1

💡 Summary: Core Ideas of MySQL Tuning

Monitoring first: Build a complete monitoring system to detect issues early.

Configuration foundation: Proper parameter settings are the basis of performance.

Index is king: Good index design solves 80% of performance problems.

Architecture matters: Read‑write splitting and sharding address high concurrency.

Continuous optimization: Performance tuning is an ongoing process.