Mysql – optimal size of innodb buffer pool

buffer-poolinnodbmy.cnfMySQL

I read a lot of optimal number of innodb_buffer_pool_size and people say it must be larger with 10% than the innodb tables size, what is 60M. Accordyngly this the optimal size of the buffer is about 66M im my case.
Should I set this value more than 66M, for example 256M or 1G to achieve better performance? I have enough memory to add more to innodb buffer pool if that improve the performance.
Thanks

Best Answer

If you expect your InnoDB data to grow, you should prepare for it by having a larger InnoDB Buffer Pool.

Where does the idea of having additional 10% extra space for the Buffer Pool come from ???

Please note the following diagram of the InnoDB Architecture

InnoDB Architecture

Look in the upper left hand corner where you see the buffer pool. There is a section of it called the "Insert Buffer Part". The purpose of the Insert Buffer is to populate changes to non-unique indexes into the Insert Buffer inside the system tablespace file (a.k.a. ibdata1).

From the diagram, up to 50% of the Buffer Pool can be used to manage the Insert Buffer. That being the case, it would be in your best interests to assign a lot more memory to the Buffer Pool. With a data set of 60M, 256M would be a great place to start,

Related Solutions

Mysql – How much memory do I need for innodb buffer pool

If you go strictly by that rule of accommodating an addition 10%, here is my suggestion:

SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
Recommended_InnoDB_Buffer_Pool_Size FROM
(
    SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
    FROM
    (
        SELECT SUM(data_length+index_length)*1.1*growth RIBPS
        FROM information_schema.tables AAA,
        (SELECT 1 growth) BBB
        WHERE ENGINE='InnoDB'
    ) AA
) A;

This will produce exactly what you need to set innodb_buffer_pool_size in /etc/my.cnf. If you want to account for 25% increase in data and indexes over time, please change (SELECT 1 growth) BBB to (SELECT 1.25 growth) BBB

Recently, I answered another question like this in the DBA StackExchange.

Mysql – rule of thumb regarding the size and number of a buffer pool instances

BUFFER POOL INSTANCES

The rule of thumb I usually use is based on a special program in in the Linux environment

numactl --hardware

When I run this, I get the following output

sh-4.1# numactl --hardware
available: 4 nodes (0-3)
node 0 cpus: 0 1 2 3
node 0 size: 49151 MB
node 0 free: 241 MB
node 1 cpus: 4 5 6 7
node 1 size: 32768 MB
node 1 free: 39 MB
node 2 cpus: 8 9 10 11
node 2 size: 49152 MB
node 2 free: 49 MB
node 3 cpus: 12 13 14 15
node 3 size: 32752 MB
node 3 free: 32 MB
node distances:
node   0   1   2   3
  0:  10  16  16  16
  1:  16  10  16  16
  2:  16  16  10  16
  3:  16  16  16  10

This quickly tells me how many CPUs and cores I have on my DB Server.

In general, I set the innodb_buffer_pool_instances to the number of physical CPUs or the number of cores. In your particular, I set the innodb_buffer_pool_instances to 4 or 16.

BUFFER POOL SIZE

If you make the InnoDB Buffer Pool bigger than 50% of the installed RAM, mysqld causes the OS to start swapping ... BADLY !!!! If you need a Buffer Pool that big, then tuning innodb_buffer_pool_instances becomes even more critical.

EPILOGUE

As soon as innodb_buffer_pool_instances was first introduced, I immediately experimented with it. I had a client that had 192GB DB Server, dual hexacore with a 162GB Buffer Pool. I simply set it to 2 and everything worked out just fine. Please see my old post from Feb 12, 2011 on this : How do you tune MySQL for a heavy InnoDB workload?