Yesterday one of my colleagues pointed me to the innodb_lru_dump_restore variable available in the Percona MySQL Server distribution. See more about it here:
innodb_lru_dump_restore
LRU meaning that the list is in Least Recently Used order to make it easier to remove old items off the pages. Reading from the description it can be a very nice option for already running servers to maintain this list after startup, but it did not solve my immediate problem: I had to warm up two new freshly cloned read slaves on one of our busiest databases.
Normally I use mk-query-digest with the –processlist option on the (active) master, but in our setup this is seriously flawed because you will only replay the slow running queries and not directly served from the innodb_buffer_pool. I’ll explain in depth in a later posting.
Anyway, the LRU Dump restore could work very nice if:
1. If (and I say if) the ib_lru_dump file would/could be initiated by- and included in the xtrabackup used by the clone it could be beneficial by not needing an extensive warmup.
2. If the LRU dump could be transferred from one host to another, but this probably won’t work because the pages could be different between hosts. Only cloned hosts should be an identical copy of the machine while applying the binlogs after extraction alone could already make it different.
Now why doesn’t the LRU dump solve the warmup problems in general?
In our setup we mostly use the second master as a failover of the master, meaning it is an inactive slave and not running any read and/or write queries. Meaning once we flip over the masters the LRU becomes stale and while flipping back we effectively have at most a lukewarm buffer pool.
Apart from this the LRU Dump restore is a sane thing to enable on MySQL start up. 
Discussion
No comments yet.