Master MySQL Monitoring, Tuning, and Schema Design for Optimal Performance

1. Monitoring

Tool: sp on mysql series can monitor various databases.

2. Tuning

2.1 DB Layer Operations and Tuning

2.1.1 Enable Slow Query Log

Add the following to the my.cnf file (locate it with find / -name my.cnf if unknown):

Log_slow_queries = ON
Log-slow-queries = /var/log/slowqueries.log
Long_query_time = 1

Restart MySQL with service mysqld restart and verify with show variables like '%slow%'. Both slow_query_log and log_slow_queries should be ON .

2.1.2 Analyze Slow Queries with mysqldumpslow

Navigate to the slow‑query log directory (e.g., /var/log) and inspect slowqueries.log using cat, more, less or vi. Use mysqldumpslow to summarize the log.

Key options: -s sort by count, time, lock time, or rows examined (c, t, l, r). -t limit the number of statements displayed. -g filter with a regular expression.

2.1.3 Use EXPLAIN for Execution Plan Analysis

Run EXPLAIN followed by the SELECT statement. Pay special attention to the type column (values from worst to best: ALL, index, range, ref, eq_ref, const, system, NULL) and the extra column, which may show Using filesort or Using temporary —both indicate potential performance problems.

2.1.4 Optimize After Analysis – Index Tuning

Add indexes on columns used in WHERE clauses or join conditions. The goal is to let MySQL use the most selective index for each query.

2.2 Cache Layer Operations and Tuning

2.2.1 Enable Query Cache

In the my.cnf under mysqld, set:

Query_cache_size = 268435456
Query_cache_type = 1
Query_cache_limit = 1048576

Query_cache_type

values: 0 = off, 1 = cache all SELECTs, 2 = cache only statements with SQL_CACHE. Ensure that identical queries are issued to benefit from caching.

2.2.2 Query Cache Status

Check status with show status like '%qcache%'. Important fields: qcache_free_blocks: number of free blocks (high value indicates fragmentation). qcache_free_memory: free memory in the cache. qcache_hits: number of cache hits. qcache_inserts: number of queries added to the cache. qcache_lowmem_prunes: number of times MySQL had to prune due to low memory. qcache_total_blocks: total number of blocks.

2.2.3 Cache Metrics

Cache hit rate = qcache_hits / (qcache_hits + qcache_inserts). Fragmentation rate = qcache_free_blocks / qcache_total_blocks * 100%. If fragmentation exceeds 20 %, run FLUSH QUERY CACHE. Utilization =

(query_cache_size - qcache_free_memory) / query_cache_size * 100%

.

2.2.4 Cache Optimization

Consolidate identical SQL statements (e.g., via MyBatis XML mappings) so that the same text is cached.

2.3 MySQL Configuration Optimization

back_log : size of the TCP/IP connection queue; increase from default 50 to, e.g., 500 for high‑traffic servers.

interactive_timeout : seconds a server waits for activity on an interactive connection; default 28800, often reduced to 7200.

key_buffer_size : buffer for index blocks; raise from 8 MB to an appropriate value (e.g., 400 MB) based on RAM.

max_connections : maximum simultaneous client connections; raise from 100 to 1024 if needed.

read_buffer (record_buffer): per‑thread buffer for sequential scans; increase from 128 KB to 16 MB for heavy scans.

sort_buffer_size : per‑thread buffer for ORDER BY / GROUP BY; increase from 2 MB to 16 MB.

table_open_cache (table_cache): number of open tables; raise from 64 to 512.

thread_cache_size : number of reusable threads; set to 80 after observing Connections vs Threads_created.

wait_timeout : seconds the server waits before closing an idle connection; default 28800, often reduced to 7200.

All changes are made by editing /etc/my.cnf and restarting MySQL.

2.4 Database Design Models

2.4.1 Normalization

1NF : Ensure each column holds atomic values (e.g., split phone numbers into country code, area code, number).

2NF : Must satisfy 1NF and have a primary key; all non‑key columns must depend on the whole primary key.

3NF : Must satisfy 2NF and eliminate transitive dependencies; non‑key columns must depend directly on the primary key.

Benefits: reduces redundancy, saves storage, and simplifies integrity maintenance.

Drawbacks: excessive splitting can increase join complexity and degrade performance.

2.4.2 Denormalization

In scenarios with frequent read access, deliberately introduce redundancy to avoid costly joins and improve query speed.