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?
Short Answer: Rule of Thumb: If you have more than 4GB of RAM, then set
innodb_buffer_pool_size to about 70% of available RAM. Set and forget.
“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.
To be perfectly honest with you, allocating 5GB for innodb_buffer_pool_size may not hold 5GB. Why not ??? Please take a look at the following diagram from Vadim Tkachenko
Look in the upper left-hand corner. The Buffer Pool has a section dedicated to the Insert Buffer (memory structure dedicated to holding changes to secondary indexes).
If you attempt to load all your InnoDB data and index pages into RAM, the moment you start changing columns that are part of a secondary (non-unique) index, 16K pages will start getting kicked out of the Buffer Pool to make room for such changes.
The more write-intensive your database, the more Insert Buffer changes need to be recorded in the Buffer Pool.
If you look inside the yellow box under the title “Insert buffer part of buffer_pool”, the first line says “InnoDB-std: may take 1/2 of buffer_pool”.
This means the most write-heavy application that has 5GB would need a 10GB Buffer Pool. Sounds ludicrous, but it is possible that can be the case.
Personally, I would make the Buffer Pool 7GB to start. I would also make sure I have these configured in
[mysqld] innodb_buffer_pool_load_at_startup innodb_buffer_pool_dump_at_shutdown innodb_read_io_threads = 8
That way, any restart of mysqld would preserve the contents of the Buffer Pool and would reload it (won’t be instantaneous but will load in a few minutes)
I doubled the number of read I/O threads to increase the speed data is read into the Buffer Pool.
This is covered in the docs, basically yes:
set the size of the buffer pool to as large a value as practical, leaving enough memory for other processes on the server to run without excessive paging. The larger the buffer pool, the more InnoDB acts like an in-memory database, reading data from disk once and then accessing the data from memory