Re: InnoDB buffer pool setting of large Innodb database

[Rick James]

Short answer:

On a 16GB machine:
innodb_buffer_pool_size = 11G
(or perhaps 12G -- but don't let the OS swap)

Longer answer:

> 1 - As Database grows, do we have to set buffer pool accroding to size. If it is so, then do I need 50 GB Memory to cache Innodb data in memory in my case ?

The simple answer is to set innodb_buffer_pool_size to 70% of available RAM. Increasing beyond that would hurt because it would probably lead to swapping. Setting it to less than 70%, but still more than the dataset size, is OK.

> 2- In this situation, Mysql seeks the queried data from disk, instead of buffer pool and increase InnoDB I/O ?

The buffer_pool is a "cache". Every query needs some blocks of data and/or index. InnoDB first looks in the buffer pool to see if the blocks are there. If not, it reads them from disk. On writing, things are delayed, so a write (INSERT, UPDATE, DELETE) rarely is stalled waiting for the writes. There are a lot of variants on what I am saying; let's see a query that is running slower than you would like; I will analyze it for you. (As Luis points out.)

> 3- what is the recommanded solution for the same as database size is growing every day and we can not affore large memory on the server?

Use the 70% guideline. If you have a lot of I/O, then let's look at the slow queries to see what can be improved in them.

It is quite normal to have a dataset that is bigger than the available RAM.

Buffer_pool: bigger is better, but too big is really bad.