Warm up for database cache

Updated at by

For a better mysql client experience a properly buffed up server might turn a total I/O slur into nothing happened. Here's a couple of ways to warm up your database caches on MariaDB / MySQL server before throwing it to the wolves.

Warmup with SELECTs from general_log

Let's say you're bringing online another binlog slave and you can grab general_log from an already busy slave. The log format is pretty horrible, so it needs some tr|sed|grep-magic. A more proficient cli-toolist can probably achieve the same output with one command but I need a few more pipes :)

Snippet of the general log might look like this

150917 11:42:21    83 Quit  
150917 11:43:39    84 Connect   admin@localhost as anonymous on woofwoof
       84 Query SELECT * FROM muchdata WHERE content LIKE '%derp'
150917 11:43:51    85 Connect   admin@localhost as anonymous on woofwoof
       85 Query SELECT * FROM bigtable WHERE
content LIKE '%derp'
       87 Query SELECT * FROM bigtable WHERE id = 1
AND content LIKE '%meh%'
ORDER BY content DESC
       87 Quit

After ^beginning of the line there's an optional timestamp, might be some \tabs and \spaces, a running thread [0-9]number, \space, command and input for the command spanning over multiple lines. I ended up with a monster like this :

sed -E 's/^(\t|[0-9]{6}\s[0-9:]{8})\s+([0-9]+)/~~\2/g'|tr '\n' ' '|tr '\t' ' '|grep -oP '~~[0-9]+\s+Query\s+\KSELECT.*?(?=~~[0-9]+)'|sed 's/ $/;/g'

This will normalize the line beginnings to two tildes and a thread number, replace tabs and newlines with space, grep the SELECT between two ~~[thread-number] patterns and slap a semicolon before eol. One assumption is that in multiline queries, new query lines won't start with \space(s) and a number(s), so it might need a bit more work on your logs. Anyway the output on the log snippet above is:

SELECT * FROM muchdata WHERE content LIKE '%derp';
SELECT * FROM bigtable WHERE content LIKE '%derp';
SELECT * FROM bigtable WHERE id = 1 AND content LIKE '%meh%' ORDER BY content DESC;

Warmup with SELECT count(*) and SHOW INDEX

Using information from SHOW INDEX and executing SELECT count(*) on the tables. Plus a little awful awk calculus to limit queries to top ~30% of IDs after reaching parameter max_rows. A short script :

#!/bin/bash
database="woofwoof"
mysql_options="-uadminuser -pderp"
max_rows=100000
mysql $mysql_options $database -Bn -e "SHOW TABLES" 2>/dev/null|sed '1,1d'|xargs -I{} mysql $mysql_options $database -Bn -e "SHOW INDEX FROM $database.\`{}\` WHERE Key_name='PRIMARY'" 2>/dev/null|grep -v '^Table.*'|awk -v max_rows=$max_rows '{limit=($7<max_rows)?$7:int(max_rows+($7-max_rows)/5); print "SELECT count(*) FROM `" $1 "` ORDER BY `"  $5 "` DESC LIMIT " limit ";" }'

Spills out:

SELECT count(*) FROM `another_table` ORDER BY `id` DESC LIMIT 7;
SELECT count(*) FROM `bigtable` ORDER BY `id` DESC LIMIT 204482;

another_table is fully buffered with cardinality of 7. bigtable has cardinality of ~600k, so the awk is limiting.

Warmup with innodb_buffer_pool_load_at_startup

A server already hard at work can dump it's innodb_buffer_pool to disk on exit and load the dump on startup. Depending on how much your active buffer pool changes over time and for how long the server is out, this might work or be a total waste of I/O. Enable dump by adding the following variables to my.cnf (and SET GLOBAL as they're dynamic).

innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
innodb_buffer_dump_pct=25

innodb_buffer_dump_pct is the percentage 0-100 of most-recently-used-pages. 100 meaning the whole buffer pool is dumped and 0 is just being weird.

Leave a comment