Is one large innodb buffer better and multiple smaller ones?

Question

I have a MySQL Server with roughly 1.8TB of data and 128GB of installed RAM. Would I be better off with one large innodb_buffer_pool that's say 80% of available RAM or would say 16 that are 8GB each be better? I understand that it's likely usage dependent but am looking for generic "rule of thumb" / close enough answers. The server has six different databases running on it with a few dozen tables each. Each individual DB ranges from 50GB up to 860GB for the largest.

Answer 1

I would just leave it at the default. That is 1 if the buffer pool size is < 1GB, or in your case, the default number of instances is 8 since you have a much larger buffer pool. That should be fine for most types of workload.

The reason to scale the number of buffer pools is to relieve contention on the buffer pool mutex if you have a high number of concurrent active sessions in MySQL. So the size of your RAM and the size of the database is less important than the level of concurrent demand.

To monitor the concurrency, watch how the Threads_running status variable changes over time (it helps if you have a monitoring system to graph this, for example Percona Monitoring and Management). That status variable shows the number of sessions that are actually executing queries at any given time. It's more relevant than Threads_connected, which includes all the idle connections.

Even the top experts at MySQL performance have a hard time deciding what is the optimal number of buffer pool instances.

https://www.percona.com/blog/2020/08/14/part-two-how-many-innodb_buffer_pool_instances-do-you-need-in-mysql-8-with-a-cpu-bound-workload/

For this particular case, innodb_buffer_pool_instances=64 was the best choice, but I still can’t recommend a reliable way to find what the optimal value is.

The optimal tuning is heavily dependent on factors specific to your case:

• Server hardware, including CPU cores, CPU speed, RAM
• Patterns of concurrent requests, i.e. Threads_running
• Patterns of data

So the only way to really tune this is to try different settings in your environment, and measure performance like shown in that Percona blog.