Optimizing MySQL Performance with Read/Write Splitting, Columnar Storage, and Dynamic Scheduling

The author, a senior DBA with extensive Oracle and MySQL experience, describes a sudden 100× surge in write traffic on a business receipt‑tracking database, which caused severe CPU and I/O bottlenecks on the primary server.

The workload consists of dense, non‑transactional inserts and periodic statistical queries (every 7 minutes) that scan the whole table on a simple master‑slave MHA setup, leading to both read and write pressure on the primary.

As a first mitigation step, the statistical queries were moved to the replica, instantly dropping the primary CPU load from >90% to <10% and I/O from near 100% to ~25%.

Further improvements showed limited benefit from additional indexes, so two architectural options were explored: (1) adding two data nodes and using middleware to build a distributed layer, and (2) implementing application‑level data routing where different business groups are mapped to specific nodes, enabling more controllable scaling.

To address the core statistical‑query performance, a columnar storage solution (Infobright) was introduced. After loading 35 million rows per table into Infobright, a set of five statistical queries that previously took ~18 minutes were reduced to ~14 seconds, and batch processing time fell from 18 minutes to under 3 minutes.

A custom script was written to extract data from MySQL and load it into Infobright. The script accepts start and end timestamps, performs full‑dump exports, and can be wrapped in a dynamic scheduler that calculates the next execution window automatically. Example DDL and export commands are shown below:

CREATE TABLE `receipt_12149_428` (
  `id` int(11) NOT NULL COMMENT 'auto‑increment primary key',
  `userid` int(11) NOT NULL DEFAULT '0' COMMENT 'user ID',
  `action` int(11) NOT NULL DEFAULT '0' COMMENT 'action type',
  `readtimes` int(11) NOT NULL DEFAULT '0' COMMENT 'read count',
  `create_time` datetime NOT NULL COMMENT 'creation time'
);

SELECT * FROM ${tab_name} WHERE create_time BETWEEN xxx AND xxxx INTO OUTFILE '/data/dump_data/${tab_name}.csv' FIELDS TERMINATED BY ' ' ENCLOSED BY '"';

Performance tests on Infobright demonstrated query times of 6–10 seconds for count operations on tables with hundreds of millions of rows, confirming that columnar storage effectively eliminates the previous bottleneck.

The final architecture retains the original master‑slave MySQL topology, adds a dedicated columnar warehouse node for statistical workloads, and employs dynamic scripts for periodic data sync, resulting in a stable system where primary load is low, replica I/O is manageable, and statistical latency is reduced from hours to seconds.