How to keep a passive-master MySQL database warm

When you have a passive master (in a master-master setup), there is usually no queries going to it. If there are no queries it will not have its buffers/caches at least semi-ready for a production workload. Maatkit (love this toolkit) has a nice little tool to help do this, mk-query-digest. Now this is only 1 of many uses for this tool. So visit the mk-query-digest doc to find out the rest of its many uses.

This assumes you’ve setup another MySQL already (possible doing dump/import or a hot backup of your current Master or a slave).

This can be run from active master, passive master or even a machine that has access to both. My example will be running from the active master machine.

mk-query-digest --processlist S=/tmp/mysql.sock \
	--filter '$event->{fingerprint} =~ m/^select/i' \
	--execute h=10.0.1.11,u=parrot,p=asdf \
	--report-format rusage,date,files,header,profile

This polls the process list of the local mysql server ten times a second (can be decreased by adding --interval <float>) via the DSN provided (in this case a local socket). It uses the filter to only use “select” queries and executes them on the DSN provided. When killing the script with Ctrl-C it will print out some statistics about the queries it polled (useful for comparing execution times). This can be disabled by removing the “–report-format” option and value and replicating it with “–no-report”.

If you have more than 1 MySQL database, and want to speed up or just increase the load, try replaying from all the databases in your cluster.

Some more technical detail about this can be found on the MySQL Performance Blog