How to “warm up” a cold DB server?

Question

1st setup was a DB slave that was used by customers. That was sufficiently fast. 2nd setup (now) is replication of a DB slave that is just used by me. The current setup is slower on query execution (even though I don't have to share CPU and memory) and the theory is that this is because the DBMS is not "warm" by use but f.x. has to reload the index every time I use it. This leads to weaker performance. The conclusion would be to run scheduled queries that keep the DB "warm".

I would like to know three things which are directly connected:

1. What is the primary reason for this effect? Is it because after every small write access to the slave which gets replicated onto my server, indexes are loaded into the memory first before a query is executed? My guts tell me there something more complex going on.

2. How would a query have to look like that optimally uses the DB (scheduled) to keep it warm and ready? I am aware of this post: MySQL warm procedure - but I am not so much interested in a generic query but in understanding the logic behind it.

3. What determines how frequently such a keep-warm-query (or set of) should be run?

---

SHOW VARIABLES LIKE "%version%";

innodb_version          5.6.13-rel61.0
protocol_version        10
slave_type_conversions  -
version                 5.6.13-rc61.0
version_comment         Percona Server (GPL), Release 61.0
version_compile_machine x86_64
version_compile_os      Linux

Answer 1

Database "warm up" is a real thing... but then again, people making excuses for under-performing systems is also a real thing. An important take-away here is that once a server is warmed up, it doesn't generally spontaneously cool back down again.

Warm-up is needed because the best performance of a database is achieved when its various caches are populated with the most relevant data, since reading from memory is generally substantially faster than reading from disk.

InnoDB has the InnoDB buffer pool. MyISAM has the key cache and the OS cache. Then there's also the MySQL query cache. MySQL has the open table cache. Some or all of these structures contribute to the ideal performance of a server. When a server is handling traffic that is characteristic of its workload, it tends to have the most frequently used -- and therefore presumably the most relevant data -- already cached.

Take the load off of an active MySQL server and you will not see it start closing tables and evicting data from its caches. If you leave it running, then come back to it tomorrow, you should find that it's still as warm as you left it.

The need for a warm-up is not typically something that is a relevant consideration needed unless the server process is restarted. Percona Server has a feature that can pre-warm a server's InnoDB buffer pool on restart, by storing the pointers to the pages that were in-memory before shutdown and reloading those pages again into the pool on startup.

However, you can also undo a well-warmed system to some extent by taking a full backup from the client-side of the server, such as with mysqldump and most graphical tools, but not backup utilities that understand native tablespaces, like innobackupex (if my understanding of how that utility works is correct), since a large portion of the data being backed up may be data that is older or irrelevant to the primary workload but may still cause the eviction of other data from the various buffers and caches as it is loaded in, on its way back out for the backup.

The logic in the question you referenced is that reading from the tables and their indexes brings them into memory -- warms things up -- before the "real" queries jump in to start doing their work.

Generally speaking, though... a single query, run once, will have warmed up whatever structures were needed to execute that particular query. If the same query, executed again immediately, is still slow, then warm-up is not the issue. Indexes are not reloaded every time you use them, unless there is competition for space in the buffer and cache structures.

Answer 2

I made up a perl script that load index cache for myisam table and full table for InnoDB including InnoDB secondary index. Feel free to use it.

https://github.com/benjaminlin/mysql_cache_prewarm

:)