MySQL Parameter Optimization Using sysbench oltp_read_write Benchmark

The article is part of the "MySQL Column" series produced by the Aikesheng operations team, sharing practical experience from frontline database administration, covering MySQL features, optimization cases, architecture, HA, and monitoring.

It introduces a benchmark using sysbench's oltp_read_write workload to simulate business pressure and generate a reasonable MySQL configuration for the given hardware.

The environment description lists the hardware and software setup, accompanied by screenshots of the system.

Optimization is organized into three layers—SQL, parameter, and architecture—with the focus of this article on parameter optimization.

The guiding principles include log‑first strategy, bottleneck analysis via SHOW GLOBAL STATUS and system tools, and balancing read/write performance.

During the optimization process, the initial configuration (key parts of my.cnf ) is shown, and monitoring data reveals high Innodb_data_pending_fsyncs and I/O utilization peaks.

Analysis attributes the bottleneck to a small buffer_pool_size causing frequent dirty‑page flushing and redo‑log reuse; the first step is to enlarge innodb_buffer_pool_size , which reduces pending fsyncs and I/O peaks, raising throughput roughly threefold.

Next, the redo log is tuned by increasing innodb_log_files_in_group and innodb_log_file_size to about 5 GB, eliminating log reuse and further smoothing performance, achieving about a two‑fold gain.

Additional optimizations address high CPU usage (≈95 %) by enlarging table_open_cache , table_open_cache_instances , and thread_cache_size to reduce thread creation overhead.

The article concludes that, given the CPU ceiling, parameter tuning is halted, presenting the final performance results and the complete optimized my.cnf configuration.

Tips are provided, including a link to a MySQL configuration generation site for further experimentation.