Auto-tuning the InnoDB Buffer Pool

Out-of-Memory (OOM) events are a frequent culprit of downtimes in MySQL databases in cloud environments. The innodb_buffer_pool is the largest consumer of memory in MySQL and can be reduced in times of memory pressure in order to free up memory at the cost of performance.

Cloud SQL for MySQL developed Managed Buffer Pool, a MySQL daemon plugin designed to provide proactive memory management and take the guesswork out of finding the optimal innodb_buffer_pool_size. The plugin continuously tracks the real-time memory footprint of the mysqld process relative to its container memory limit. When memory usage breaches a configurable critical threshold, such as 95%, the plugin immediately executes a reduction of the innodb_buffer_pool_size. The plugin re-increases the innodb_buffer_pool_size if usage stabilizes at a lower value to improve performance. Attendees will learn about the detailed design, how the plugin manages performance optimization, and how auto-tuning other flags and parameters can follow a similar framework.