Master MySQL Performance: Practical Optimization Strategies & FAQs

MySQL Performance Optimization

Common optimization strategies include:

Read‑write separation

Sharding

Parameter tuning

Index optimization

System optimization

Hardware optimization

Read‑Write Separation

Separating reads from writes isolates workloads, reduces interference, and suits the typical internet pattern of many reads and few writes.

Sharding

Sharding redistributes data to alleviate write pressure or capacity limits on a single instance. It introduces operational complexity, so it should be applied judiciously and combined with vertical and horizontal splits when appropriate.

Master‑Slave Replication Lag Optimization

Replication lag often stems from the master’s heavy write load or the slave’s limited single‑threaded SQL execution. Parallel replication in MySQL 5.6 helps but remains coarse‑grained.

Primary mitigation is using high‑performance I/O storage, followed by parallel replication and, if needed, sharding.

Schema Optimization

Effective schema design focuses on appropriate column types, minimal size, simple structures, and sensible indexing. Recommended practices include using integer keys, limiting InnoDB indexes per table to five, choosing UTF‑8/UTF8mb4 character sets, and avoiding redundant indexes.

Parameter Optimization

Key InnoDB parameters that impact performance include:

innodb_file_per_table innodb_buffer_pool_size innodb_flush_log_at_trx_commit (0/1/2, data‑safety trade‑off) innodb_log_file_size innodb_page_size sync_binlog (data‑safety trade‑off)

For high data‑safety requirements, set

innodb_flush_log_at_trx_commit

and

sync_binlog

to 1.

Hardware and SSD Optimization

NUMA, large memory, and SSD usage can improve performance, though NUMA benefits are limited on modern multi‑core servers. SSDs are especially effective; disabling the filesystem barrier and tuning the I/O scheduler yield noticeable gains.

echo noop > /sys/block/[device]/queue/scheduler echo 2 > /sys/block/[device]/queue/rq_affinity (CentOS 6.4+) echo 0 > /sys/block/[device]/queue/add_random (disable barrier)

For SSD‑focused MySQL tuning (5.5+), increase

innodb_write_io_threads

,

innodb_read_io_threads

, and

innodb_io_capacity

, place redo logs on HDD, undo logs on SSD, disable double‑write, compress InnoDB buffers, and consider multi‑instance + cgroup deployment.

FAQ

Q: Difficulty of implementing Amazon Aurora? A: Aurora diverges significantly from native MySQL, requiring extensive rewrites at the storage and file‑system layers, making implementation challenging.

Q: Do you use PCI‑E cards now? A: Not currently; they were used at Sina for high IOPS but offer limited benefit unless workloads are extremely heavy.

Q: Besides I/O improvements, how to reduce replication lag? A: Use parallel replication and sharding; pre‑reading relay logs provides modest gains.

Q: Why place redo logs on HDD? A: Redo logs are sequential writes; HDDs handle sequential I/O well, while SSDs excel at random writes, so mixing can be acceptable.

Q: When is sharding unnecessary? A: If data volume and access patterns are modest; keep a single shard under 200 million rows and consider online schema change tools for minor changes.

Q: What modifications were made to MHA? A: Core failover logic was rewritten in Python; Galera is rarely used directly, with most teams preferring Percona XtraDB Cluster.

Q: Can you share your MHA changes? Do you use MariaDB or triggers? A: Modifications are Python‑based; MariaDB supports parallel replication but is not used in our production environment; triggers are avoided.

Q: Tips for SQL optimization? A: Prefer equality queries, leverage indexes, and use EXPLAIN and pt‑query‑digest for analysis.

Q: Issues with MHA data loss and recovery? A: MHA aims to prevent loss; if gaps remain after failover, recover from business logs. Choose between PXC and MHA based on IDC distribution and operational cost.

Q: Status of official Fabric? A: Still in lab stage, not production‑ready.

Q: How is read‑write separation implemented? A: Via DNS‑based routing or custom middleware; similar to TDDL at Weibo.

Q: Backup tools recommendation? A: Use xtrabackup for hot backups; resort to mysqldump for logical backups when needed.