Master MySQL: From Table Design to Scalable, High‑Performance Architecture

1. Database Table Design

After a project is approved, developers design the table schema based on product requirements. Poor design leads to slow queries, missing indexes, deadlocks, and overall bad user experience. Early testing can catch issues, but many teams postpone design considerations until traffic grows, making later changes difficult.

2. Database Deployment

In the early stage, a single MySQL instance can handle about 1500 QPS. For high availability, master‑slave replication combined with Keepalived (or Heartbeat) provides dual‑node hot standby.

3. Database Performance Optimization

On a typical x86 server, MySQL can handle roughly 2000 QPS out‑of‑the‑box; with tuning it may reach 2500 QPS. When concurrency approaches 1500 connections, performance degrades, indicating the need for software‑level optimization.

3.1 Configuration Optimization

MySQL offers two main storage engines: MyISAM (table‑level lock, no transactions) and InnoDB (row‑level lock, ACID transactions). InnoDB is the default since MySQL 5.5 and generally provides better concurrency.

Table lock: low overhead, large granularity, higher deadlock probability, lower concurrency.

Row lock: higher overhead, fine granularity, lower deadlock probability, higher concurrency.

Key configuration parameters (default values) that affect performance include:

max_connections = 151 sort_buffer_size = 2M query_cache_limit = 1M query_cache_size = 16M open_files_limit = 1024

MyISAM defaults:

key_buffer_size = 16M read_buffer_size = 128K

InnoDB defaults:

innodb_buffer_pool_size = 128M innodb_buffer_pool_instances = 1 innodb_flush_log_at_trx_commit = 1 innodb_file_per_table = OFF innodb_log_buffer_size = 8M

3.2 System Kernel Optimization

Linux kernel parameters also impact MySQL performance. Typical tunings include:

net.ipv4.tcp_fin_timeout = 30 net.ipv4.tcp_tw_reuse = 1 net.ipv4.tcp_tw_recycle = 1 net.ipv4.tcp_max_tw_buckets = 4096 net.ipv4.tcp_max_syn_backlog = 4096

Increase the maximum number of open file descriptors to avoid “too many files open” errors:

* soft nofile 65535 * hard nofile 65535

3.3 Hardware Configuration

More RAM allows larger buffer pools, reducing disk I/O. SSDs replace SAS drives, and RAID 1+0 improves read/write performance.

4. Database Architecture Expansion

4.1 Master‑Slave Replication & Read‑Write Splitting

Deploy a master‑slave cluster: the master handles writes, multiple slaves handle reads via load balancers such as LVS, HAProxy, or Nginx. Read‑write splitting can be implemented in application code or via proxy tools (MySQL Proxy, Amoeba). Tools like MySQL‑MMM ensure only one master writes at a time to avoid data inconsistency.

4.2 Adding Cache

Introduce a caching layer (local or distributed) to store hot data in memory, dramatically improving read performance. Distributed caches such as Memcached or Redis can handle tens of thousands of QPS.

4.3 Sharding (Database Splitting)

Split related tables into separate databases (e.g., web, bbs, blog) and optionally apply master‑slave replication to each shard.

4.4 Table Partitioning

When a single table grows to millions of rows, split it into multiple smaller tables. Vertical partitioning separates infrequently used columns; horizontal partitioning creates identical tables with subsets of rows.

4.5 Partitioning

Partition a table into multiple physical segments, possibly across different disks, to improve I/O parallelism.

5. Database Maintenance

5.1 Key Performance Indicators

Important metrics include QPS (Queries Per Second) and TPS (Transactions Per Second). They can be derived from status variables such as Questions, Uptime, Com_commit, and Com_rollback.

QPS = Questions / Uptime TPS = (Com_commit + Com_rollback) / Uptime

5.2 Enabling Slow Query Log

Activate the slow‑query log to identify inefficient SQL statements.

set global slow_query_log=on; set global slow_query_log_file='/var/log/mysql/mysql-slow.log'; set global log_queries_not_using_indexes=on; set global long_query_time=1;

5.3 Backup

Use logical backup (mysqldump) for small databases or physical tools (mysqlhotcopy, XtraBackup) for large, high‑availability environments. Incremental backups are recommended for massive datasets.

5.4 Repair

When tables become corrupted, use myisamchk (MyISAM only) or mysqlcheck (both MyISAM and InnoDB) with appropriate flags.

myisamchk -r -q *.MYI mysqlcheck -r -q -uroot -p123 weibo

5.5 Monitoring CPU and I/O

CPU and I/O performance can be inspected with standard Linux tools; typical I/O limits are around 1200 IOPS, with SSDs delivering up to 600 MB/s.