1

Warming up a cold MySQL instance is moving all data or the data with the highest probability of getting used to main memory. And, yes, by main memory, I mean InnoDB Buffer Pool. The Buffer Pool is configured as a large chunk of main memory, usually. Getting all the data from disk to main memory will take time. Rather than leaving it for the future, can the buffer pool be loaded in advance with the data most expected to be used at the earliest?

5

Assuming, you have a 7200 rpm HDD, if you perform random reads and expect the data to be loaded whenever it is required by the application, it will slow down the whole process. Random reads are way slower than sequential reads in case of HDD. So, rather the records must be loaded in sequential access.

It may take a while under a regular workload to store your entire working set data into the buffer pool, after restarting MySQL for instance. You can go with one of these two options:

  • Manual Warmup - Running SELECT queries against your InnoDB tables will load necessary pages into the memory, i.e., the buffer pool. SELECT COUNT(*) may be very useful, as it will load the whole clustered index into memory (actually as much as available). Secondary indexes may be loaded into the memory with simple queries, for instance by adding a catch-all, i.e., <> 0 WHERE clause on the first column of an index. Using a given index could be forced if needed.
  • Automatic Warmup - From 5.6, automatic buffer pool content dump at shutdown and restore on startup can be enabled - innodb_buffer_pool_dump_at_shutdown = ON, innodb_buffer_pool_load_at_startup = ON.

Reference - From Dual's article on InnoDB Warmup

Marcelo Altmann's article on MySQL Warmup

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.