From Table Design to Scalable MySQL: A Complete Optimization Roadmap

1. Database Table Design

Designing table schemas early based on product requirements is critical. Poor design leads to slow queries, missing indexes, and lock contention. Early performance testing can expose design flaws before traffic grows.

2. Database Deployment

A single MySQL instance can handle roughly 1500 QPS. For high availability, use master‑slave replication combined with an active‑passive failover solution such as Keepalived or Heartbeat.

Reference URL: http://lizhenliang.blog.51cto.com/7876557/1362313

3. Database Performance Optimization

On a typical x86 server MySQL processes ~2000 QPS out‑of‑the‑box; with tuning it can reach ~2500 QPS. Optimization includes running multiple MySQL instances per server, adjusting OS defaults, and increasing connection limits.

3.1 Configuration Tuning

Storage engines :

MyISAM – table‑level lock, fast reads, no transactions.

InnoDB – row‑level lock, ACID transactions, default since MySQL 5.5.

Lock types :

Table lock – low overhead, high contention.

Row lock – higher overhead, better concurrency.

Key parameters (default values shown):

MyISAM defaults: key_buffer_size = 16M # 30‑40% of RAM read_buffer_size = 128K # recommended 16‑32M

InnoDB defaults: innodb_buffer_pool_size = 128M # 60‑70% of RAM innodb_buffer_pool_instances = 1 # usually set to 4‑8 innodb_flush_log_at_trx_commit = 1 # durability vs. performance trade‑off innodb_file_per_table = OFF # enable for separate tablespaces

innodb_log_buffer_size = 8M

3.2 Kernel Optimization

Linux kernel parameters affect MySQL. Increase the maximum number of open file descriptors (default 1024) to avoid “too many files open” errors.

Adjust /etc/security/limits.conf and relevant sysctl settings.

3.3 Hardware Configuration

Increase physical memory to enlarge OS cache, use SSDs instead of SAS drives, and configure RAID 1+0 for higher I/O throughput.

4. Database Architecture Scaling

When a single server can no longer meet demand, adopt clustering, sharding, caching, and partitioning.

4.1 Master‑Slave Replication & Read‑Write Splitting

Deploy one master for writes and multiple slaves for reads. Use load balancers such as LVS, HAProxy, or Nginx. Application‑level read‑write splitting is common; proxy‑based solutions include MySQL Proxy and Amoeba.

Example: five slaves each handling 2000 QPS can collectively serve ~10 k QPS.

For active‑active scenarios, MySQL‑MMM (Master‑Master replication manager) ensures a single write node to avoid data inconsistency.

Reference URLs:

http://lizhenliang.blog.51cto.com/7876557/1290431

http://lizhenliang.blog.51cto.com/7876557/1305083

http://lizhenliang.blog.51cto.com/7876557/1354576

4.2 Adding Cache

Introduce a caching layer (memcached or Redis) to store hot data in memory, dramatically increasing read QPS (memcached up to 80 k QPS, Redis adds persistence).

Workflow diagram:

4.3 Sharding (Database Splitting)

Separate business domains into distinct databases (e.g., web, forum, blog) and optionally apply master‑slave replication per shard.

4.4 Table Splitting

When a table grows to millions of rows, split it:

Vertical splitting – move rarely used columns to separate tables.

Horizontal splitting – duplicate schema across multiple tables and distribute rows.

4.5 Partitioning

Use MySQL partitioning to divide a table’s data across multiple physical locations, improving I/O parallelism without changing the logical schema.

Note: Implementing cache, sharding, table splitting, and partitioning typically requires application code changes.

5. Database Maintenance

5.1 Key Performance Metrics

Important status variables (retrieved via SHOW GLOBAL STATUS) include: Uptime – server running time (seconds) Questions – total queries received Com_select, Com_insert, Com_update, Com_delete – operation counts Com_commit, Com_rollback – transaction counts

Calculate QPS and TPS:

QPS = Questions / Uptime

TPS = (Com_commit + Com_rollback) / Uptime

Alternative QPS calculation uses the sum of Com_select, Com_insert, Com_update, and Com_delete over a one‑second interval.

5.2 Slow Query Log

Enable the slow query log in my.cnf, set long_query_time, and restart MySQL. Analyze logs with:

# mysqldumpslow -t 3 /var/log/mysql/mysql-slow.log

# pt-query-digest /var/log/mysql/mysql-slow.log

5.3 Backup Strategies

Choose between logical backups ( mysqldump) for small databases and physical backups ( mysqlhotcopy, xtrabackup) for large, production‑grade systems. Incremental backups are recommended for multi‑hundred‑GB datasets.

Reference URL: http://lizhenliang.blog.51cto.com/7876557/1612800

5.4 Repair Tools

When tables become corrupted after crashes, use myisamchk (MyISAM only) or mysqlcheck (both MyISAM and InnoDB).

Common myisamchk usage: # myisamchk -r -q *.MYI Common mysqlcheck usage:

# mysqlcheck -r -q -uroot -p123 weibo

5.5 Monitoring CPU & I/O

Use iostat and mpstat to view CPU usage and I/O statistics. Key fields: %util – I/O device utilization (100% means saturated) await – average wait time per I/O request

IOPS – read/write operations per second (typical SAS ~1200, SSD ~6000)