mysql performance tuning innodb_buffer_pool_size and warming the cache (select * vs select count(*)) I have a couple of questions about tuning a MySQL (5.5) database. It seems the clients database server has never had any performance tuning done. Upon reading around, it seems the one thing is to set innodb_buffer_pool_size to "80% of the available RAM". Now, the physical size of the database is about 5Gig, and the available RAM on the server is 32Gigs. It seems to make no sense to me to make the innodb_buffer_pool_size = 25G.

At the moment I have set


innodb_buffer_pool_size: "8G"
innodb_buffer_pool_instances: "8"
innodb_log_files_in_group: "2"
innodb_log_file_size: "1G"


Which, reading around (and running mysql-tuner script) seems about sane. 

My question is: Is it a bad idea to have a buffer_pool_size greater than the size of the database itself. Is it bad to effectively have all the data in the buffer_pool ? 

Warming the cache

The other part is warming the cache. Reading other posts on the net, the suggestion is to run a bunch of select count (*) from &lt;table&gt; to warm it. If I do that, I get the amount of RAM at point (1) in the picture below.

If instead I run select * from &lt;table&gt; I get a heap more RAM used. Which, I assume means that I have more data loaded into the cache. Which seems good to me.



Is it better to run select * or select count(*) for warming the cache?
Warmup

If you have a secondary index on the table, SELECT COUNT(*)... will use that.  So the first "warming" fetched that index.  The second "warming" fetched the data; note that the hump is higher, since the data is bigger?

That is, COUNT(*) works only for an InnoDB table with no secondary keys.

This might work:  SELECT COUNT(*) FROM t FORCE INDEX(PRIMARY);

You are overdue for an upgrade.  With 5.6.3, there is innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup, which is better than your warmup script; it efficiently restores the buffer pool to whatever was in it.

Buffer pool size

5GB of data; 32GB of RAM --> 8GB sounds like a good buffer_pool_size:


Less than 5GB probably leads to extra I/O (depending on "working set size").
8GB leaves enough room on top of the 5GB for the "Change Buffer" and other overhead.
8-25GB -- the rest would be idle.
over 25GB -- threaten to do swapping, which would be really bad for performance.