you're reading...

LRU Dump restore

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:
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. 😉


About Art van Scheppingen

Head of Database Engineering at Spil Games


No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 446 other followers

%d bloggers like this: