innodbMySQLperformanceperformance-tuningquery-performance
For example, when using mysqltuner.pl, it told me to increase the InnoDB Buffer Size to > 5GB as my data is 5.6GB
Does it mean: if I allocate enough for the InnoDB Buffer, data will be loaded from the memory instead of disk, and will greatly improve QPS for my database?
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.
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
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,
Best Answer
Short Answer: Rule of Thumb: If you have more than 4GB of RAM, then set
innodb_buffer_pool_sizeto about 70% of available RAM. Set and forget.Long Answer:
"Greatly improve QPS?" -- It depends.
The buffer_pool is a "cache", maintained roughly as "least-recently-used".
If your data + indexes are bigger than the buffer_pool, there may be some slowdown. Either live with the speed or get more RAM.
I have seen terabyte datasets run just fine on 32GB servers. It depends on the "working set". If the application only rummages through a portion of the rows (eg, "recent" rows), then a small buffer_pool works just as good as a big one.
If you have UUIDs, you are hosed. These lead to random access that leads to slow-due-to-IO versus buy-more-RAM.
mysqltuner has some good points and some bad points. Don't increase your buffer_pool to "big enough" unless you have 8GB or more of RAM. Swapping is much worse for performance than shrinking the buffer_pool.
With a big enough buffer_pool, no it won't automatically load everything. As a "cache", things (16KB blocks) are loaded as needed, then left there. If it is not big enough and your "working set size" is big, those blocks will be bumped out to make room for other blocks. Automatically. The only cost is speed.