Buffer Pool Size limited by mysql kernel?

11-26-2009, 01:01 PM

Hello,

Our database size is 16 GB, and contains almost exclusively InnoDB tables. Our Buffer Pool size is set to 2 GB. I read that larger Pool size yields better performance and it should be set to at least the size of the database or even 20 % larger to accommodate for database growth.

We are using Linux Kernel 2.6.18. It is a 64 Bit machine. MySQL 5.0.44 is installed.

Is there a limit (by Linux Kernel?) how large the Buffer Pool Size can be set to?

The memory on our machine is currently 7.5 GB, but we are considering to increase it (to over 16GB?) [and subsequently increase Buffer Pool Size), hoping it will improve our database performance.

Comment

A related question: If the database size is ~16 GB (all tables are InnoDB) and InnoDB Buffer Pool Size is set to let's say 20 GB, can we assume that the whole database would be loaded into memory, greatly increasing performance (i.e. by totally eliminating disk access)?

Thank you!

Comment

It depends on how much of your data is accessed frequently. If you are constantly accessing every row in all your tables with equal frequency, then the increase you've suggested should have significant impact. However, if only a small portion of that data is really being used, you may notice little to no gain in performance, because the most often used data is already in the buffer pool.

If your current system's CPU usage is spending most of its time waiting for I/O, then chances are the buffer pool may need to be expanded. The SHOW STATUS command has a few items that can help you figure out how well the buffer pool is working. Read the docs and check your values for the following:

This will let you know how the buffer pool is being utilized. Just be aware that the buffer pool is not the only thing that needs memory in MySQL. There are per connection buffers for sorting and joining, and the OS filesystem cache will help out a bit if you find yourself writing temp tables to disk very often.