Resolving High SQL‑Kill Rate Caused by Buffer‑Pool Dirty‑Page Saturation in MySQL

The author, a MySQL DBA at Youzan, observed a critical alarm that reported more than 200 killed SQL statements per minute, typically occurring at 2 am or during daytime delete‑heavy workloads.

Investigation revealed that the killed statements were all DELETE queries from a scheduled cleanup job spanning many tables, which triggered the internal sql‑killer tool.

Initial diagnostics showed low overall QPS, no long‑running transactions, but the SHOW ENGINE INNODB STATUS output repeatedly displayed latch waits related to the buffer pool.

Further monitoring linked the spikes of killed SQLs to the rate of dirty‑page flushing; the dirty‑page percentage surged to almost 90 %.

MySQL’s relevant parameters were examined:

# yzsql    3306 param  dirty
Variable_name    Value
innodb_max_dirty_pages_pct         75.000000
innodb_max_dirty_pages_pct_lwm     50.000000

Documentation explains that innodb_max_dirty_pages_pct caps the dirty‑page ratio (default 75 %) and innodb_max_dirty_pages_pct_lwm defines the low‑water mark that triggers pre‑flushing (default 0, disabled).

When the dirty‑page ratio exceeded the upper limit, InnoDB aggressively flushed pages, but ongoing DELETE operations generated dirty pages faster than they could be written, causing write stalls, slowed deletes, and eventual kills.

Three mitigation ideas were considered: increase innodb_io_capacity (rejected due to high I/O usage), throttle delete speed (rejected), and enlarge the buffer pool. Using MySQL 5.7’s online buffer‑pool resizing, the pool size was doubled.

After the change, the dirty‑page ratio dropped sharply, the number of killed SQLs fell, and average query response time improved significantly.

In summary, the open‑source nature of MySQL allowed pinpointing the code path responsible for the issue; monitoring buffer‑pool hit rate and dirty‑page ratio is essential for maintaining SQL performance.