The common wisdom with database performance management is that a “cold” database server has poor performance. Then, as it “warms up”, performance improves until finally you reach a completely warmed up state with peak database performance. In other words, that to get peak performance from MySQL you need to wait for database warm up.

This thinking comes from the point of view of database cache warmup. Indeed from the cache standpoint, you start with an empty cache and over time the cache is filled with data. Moreover the longer the database runs, the more statistics about data access patterns it has, and the better it can manage database cache contents.

Over recent years with the rise of SSDs, cache warmup has become less of an issue. High Performance NVMe Storage can do more than 1GB/sec read, meaning you can warm up a 100GB database cache in less than 2 minutes. Also, SSD IO latency tends to be quite good so you’re not …

In the past… One of the typical problems you have when restarting mysqld is that the InnoDB buffer pool (buffer pool from now on) is empty and consequently access to the database requires reading directly from disk. Performance suffers dramatically as a consequence.

So the common solution is to artificially warm upthe server by doing queries which will fill the buffer pool. Typical solutions might be to do: SELECT COUNT(*) FROM some_table FORCE INDEX (PRIMARY) LIMIT ... on a number of tables to fill up the pool on startup. Fitting this into the standard mysql init start script is somewhat tricky as no hooks are provided for this sort of post-start action. (It would be nice to have this for other tasks too.)

Of course choosing the right parameters here can be tricky as workload changes over time, and as the ratio of the size of the database to the size of the buffer pool increases, you need to be more selective …

This comment is in regard to this InnoDB blog page. Trying to post a message says you need to be logged in, but there’s no register/login page I could see…

Anyway the page talks about a new feature which allows you to save on shutdown an InnoDB buffer pool and to load this on startup, this ensuring that once loaded the database will perform with this “hot” cache.

That sounds interesting as I have seen on numerous occasions that if the buffer pool is not warm then performance can be a magnitude worse.

This looks like a very welcome feature. However, a couple of things are not clear to me.

1. Having some example benchmark times of using this process and comparing it to trying to warm up the buffer pool by hand would be useful.  While this may heavily dependent on database …