Last week, we kicked off our three-part series highlighting the three ways that you can optimize your MySQL performance. Our first post covered indexing — which shared how you can leverage indexing to improve search query performance, as well as specific tips on how you can improve indexing across your MySQL environment. In case you missed it, you can read it here.
For our second post in the series, I’ll be sharing how you can maximize performance through buffer pool size.
WHY (BUFFER POOL) SIZE MATTERS
With solid indexes in place, you can move your focus to buffer pool size, which can work to your advantage by allowing you to increase the speed of your reads… as long as it’s configured correctly.
InnoDB, the storage engine behind MySQL, contains the storage area for the buffer pool, which has a primary responsibility of caching indexes and data in memory, instead of using your disk. As a result, your server has enough memory for processes to run without running into issues with excessive paging.
HOW TO CONFIGURE YOUR BUFFER POOL SIZE
To configure your buffer pool, head to the innodb_buffer_pool_size setting in your configuration and take the following into consideration:
Set the size of the buffer pool as large as you can. As a result, InnoDB acts more like an in-memory database — meaning it will read data from the disk once and then access it from the memory from that point forward.
Split your buffer pool into parts. If you are using a 64-bit system with a large memory size, you do have the ability to split it into parts to ensure that if you have processes happening at the same time, they are not fighting for resources.
Clean up dirty pages. To ensure maximum performance of your MySQL workloads, InnoDB will remove data from the buffer pool to reduce the number of dirty pages — or, as MySQL defines it, a page that has been updated in memory but the changes have not yet been written to the data files. You can set this number anywhere from 0 to 99, with the default value set at 75.
These are some of the top configurations that we recommend to ensure that your buffer pool serves as a tool to ensure optimal performance across your environment. For more tips, check out the InnoDB Buffer Pool section of the MySQL Reference Manual.
KEY BUFFER POOL METRICS
Now that you have your buffer pool size configured, it’s time to start monitoring it and pulling metrics to see how your MySQL environment performs. Buffer pool metrics give you the ability to investigate what may be causing performance issues (versus receiving indications that there is an issue with performance), giving you the ability to tweak your buffer pool size and set up to mitigate these problems.
There are a number of metrics that give you the insight you need into your buffer pool. Some of the top ones that we recommend monitoring include:
Buffer Pool Bytes Data
Buffer Pool Bytes Dirty
Buffer Pool Pages Data
Buffer Pool Pages Dirty
Buffer Pool Pages Free
Buffer Pool Pages Misc
Buffer Pool Pages Total
Buffer Pool Read Ahead
Buffer Pool Read Ahead Evicted
Buffer Pool Read Requests
Buffer Pool Reads
Buffer Pool Size
Buffer Pool Wait Free
Buffer Pool Wait Requests
In addition to visibility into key metrics for your buffer pool, understanding how data is trending over time can ensure that you have the necessary visibility you need to make decisions to improve performance.
Take this chart from SelectStar, a database and infrastructure monitoring tool, for example:
High spikes, like the one at 3:20 p.m., can mean nothing at all if they occur infrequently, but if there is a pattern and they tend to occur when you see a lapse in performance across your MySQL database, it indicates that you have a problem on your hands — and diving deeper into these metrics can give you the visibility that you need.
Interested in trying a SaaS database monitoring platform to evaluate MySQL performance? Try a free trial of SelectStar to track your MySQL buffer pools to drive better performance across your environment.