Innodb buffer pool size

[LostInTheWoods]

Hi,

I know this is a well worn subject and I have read Percona blog posts about the 80% of RAM rule of thumb. This is exactly what I followed, because I thought the relatively small databases I manage (~30GB total) could easily sit on a server with 80% of 8GB of RAM. But I have read that if possible you should have a buffer pool size that equals your data set size plus a little more. Is this right?

Our systems run fine, no problems and of course the database may run quicker with more RAM, but is it really necessary to store the complete dataset in cache?

Anyone got any thoughts or advice on this?

Thanks,
Stephen

[scott.nemes]

Hi LostInTheWoods;

It really comes down to your workload. Would storing all data plus indexes in memory at all times be great? Yes. Is it necessary? Probably not (but sometimes it is). If your application is currently running fine, then chances are you are set. That may change as your data size and/or usage grows/changes however, so on-going trending and tuning is key.

The real goal is to at least get your most commonly accessed data and indexes into memory. That could mean only 10% of your data, or 90% of your data (or somewhere in between). A good way to track how you are doing is by monitoring the “evicted without access” value:

mysql> pager grep “evicted without access”
PAGER set to ‘grep “evicted without access”’
mysql> show engine innodb status \G
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
1 row in set (0.00 sec)

Ideally the “evicted without access” value should always be zero. If this value is not zero, then it means data is getting read into the buffer pool and then pushed out again before it is accessed (a.k.a. buffer pool churn). If you track this value over time, and you start to see the value go up (even only temporarily), then it may be time to increase your buffer pool size. Note that no matter how large your buffer pool is, bad queries can (and will) destroy it. So always keep an eye towards query optimization and tuning when looking into this area, because even one bad query that gets run often can destroy performance for every other query (i.e. imagine a query that scans all your largest tables into memory each time it is ran, pushing out everything else).

-Scott

[LostInTheWoods]

Scott,

That is great advice, thanks. The application mainly consistently saves data and there is a web application that also saves and reads. But I don’t think we have any large queries that could fill up the buffer pool.

This is backed up by checking the evicted without access value:

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
1 row in set (0.16 sec)

I don’t know why it is repeated 8 times, but anyway.

We do have more RAM available but not the 70GB (30 x 1.1 x 2) that I have read is ideal. I will monitor the evicted without access value like you suggested and keep an eye out for bad queries.

Thanks again,
Stephen

[scott.nemes]

Hi LostInTheWoods;

You will see multiple lines there when you have a value of innodb_buffer_pool_instances greater-than 1. That setting splits your buffer pool into multiple chunks, which can help reduce contention in larger buffer pools. So the first line you see is for the entire buffer pool, and each line following that is for the individual chunks. So since you have 9 lines there, you must have innodb_buffer_pool_instances set to 8 in your config.

-Scott