MySQL Database Architecture Evolution, Design, Deployment, Optimization, and Maintenance Guide

A mature database architecture does not start with high availability and scalability; it evolves as user traffic grows and the underlying infrastructure is refined. This article discusses common problems and optimization solutions throughout the MySQL lifecycle.

1. Database Table Design

Proper table design is crucial for query speed and user experience. Poor design leads to slow queries, missing indexes, and deadlocks. Early-stage projects often overlook design, causing hidden issues once traffic increases.

2. Database Deployment

Initial deployments can use a single server handling ~1500 QPS. For high availability, MySQL master‑slave replication with Keepalived or Heartbeat provides hot standby.

3. Database Performance Optimization

Optimizing MySQL on standard x86 servers can raise QPS from ~2000 to ~2500. Optimization includes running multiple MySQL instances, adjusting OS and MySQL defaults, and tuning hardware.

3.1 Configuration Optimization

Key MySQL parameters:

max_connections = 151
# recommended ~80% of max connections
sort_buffer_size = 2M
# increase to 16M for large ORDER BY/GROUP BY
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 Kernel Optimization

Linux kernel tweaks for MySQL:

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 file descriptor limits:

# vi /etc/security/limits.conf
* soft nofile 65535
* hard nofile 65535
# ulimit -SHn 65535

3.3 Hardware Configuration

Increase RAM, use SSDs, and RAID10 for better I/O performance.

4. Database Architecture Expansion

4.1 Master‑Slave Replication & Read/Write Separation

Deploy one master for writes and multiple slaves for reads, using load balancers like LVS, HAProxy, or Nginx.

4.2 Caching

Introduce local or distributed caches (memcached, Redis) to keep hot data in memory and boost read QPS.

4.3 Sharding (分库)

Split business‑related tables into separate databases to distribute load.

4.4 Partitioning (分区)

Use MySQL partitioning (RANGE, LIST, HASH) to spread a single table’s data across multiple disks.

5. Database Maintenance

5.1 Key Performance Metrics

QPS = Questions / Uptime; TPS = (Com_commit + Com_rollback) / Uptime. Metrics can be obtained via SHOW GLOBAL STATUS commands.

5.2 Slow Query Log

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;

Analyze with mysqldumpslow or pt-query-digest.

5.3 Backup

Use logical backup (mysqldump) for small databases or physical tools (mysqlhotcopy, xtrabackup) for large, high‑availability environments.

5.4 Repair

Repair MyISAM tables with myisamchk and any table type with mysqlcheck using options like -r, -q, -a, -o.

5.5 CPU and I/O Monitoring

Use iostat and mpstat to view CPU and I/O stats; watch %util and await values to identify bottlenecks.

Overall, these practices provide a solid foundation for building and scaling MySQL databases in small to medium enterprises, while acknowledging the rise of NoSQL solutions for massive data workloads.