MK-SLAVE-PREFETCH(1)

📦

version 7540 · 3 versions

🌸 MK-SLAVE-PREFETCH(1) — the perl doc page, but make it cute 🌸

okay bestie, this is mk-slave-prefetch, straight out of the User Contributed Perl Documentation section. she's a lil pipeline girlie and she's about to pre-warm ur caches. let's get into it 💅

✨ NAME ✨

mk-slave-prefetch — pipelines relay logs on a MySQL slave to pre-warm caches.

🎀 SYNOPSIS 🎀

Usage: mk-slave-prefetch [OPTION...] [FILE]

mk-slave-prefetch pipelines relay logs to pre-warm the slave's caches. no cap, that's the whole job description.

Example:

mk-slave-prefetch

mk-slave-prefetch --statistics > /path/to/saved/statistics

mk-slave-prefetch /path/to/saved/statistics

💀 RISKS 💀

okay so before we get too excited — this section exists to tell u about the potential risks, whether known or unknown, of using this tool. the two main categories of risks are those created by the nature of the tool (e.g. read-only tools vs. read-write tools) and those created by bugs. not me being responsible for once 🙈

mk-slave-prefetch is read-only by default, and is generally low-risk. it does execute SQL statements, but these should be SELECT only. despite that, it might be a good idea to make it connect to MySQL with a user account that has minimal privileges. here's an example of how to grant the necessary privileges:

GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.*
   TO 'prefetch'@'%' IDENTIFIED BY 'sp33dmeup!';

at the time of this release, we know of no bugs that could cause serious harm to users. slay.

the authoritative source for updated information is always the online issue tracking system. issues that affect this tool will be marked as such. you can see a list of such issues at the following URL: http://www.maatkit.org/bugs/mk-slave-prefetch.

see also "BUGS" for more information on filing bugs and getting help.

🦋 DESCRIPTION 🦋

mk-slave-prefetch reads the slave's relay log slightly ahead of where the slave's SQL thread is reading, converts statements into SELECT, and executes them. in theory, this should help alleviate the effects of the slave's single-threaded SQL execution. it will help take advantage of multiple CPUs and disks by pre-reading the data from disk, so the data is already in the cache when the slave SQL thread executes the un-modified version of the statement. it's giving prepared. it's giving ahead of the game. 👑

mk-slave-prefetch learns how long it takes statements to execute, and doesn't try to execute those that take a very long time. you can ask it to print what it has learned after it executes. you can also specify a filename on the command line. the file should contain the statistics printed by a previous run. these will be used to pre-populate the statistics so it doesn't have to re-learn. bestie is a fast learner but she's not gonna redo the whole semester if she doesn't have to 💅

this program is based on concepts i heard Paul Tuckfield explain at the November 2006 MySQL Camp un-conference. however, the code is my own work. i have not seen any other implementation of Paul's idea.

🌈 DOES IT WORK? 🌈

does it work? does it actually speed up the slave?

that depends on your workload, hardware, and other factors. it might work when the following are true:

the slave's data is much larger than memory, and the workload is mostly randomly scattered small (single-row is ideal) changes.
there are lots of high-concurrency UPDATE and DELETE statements on the master.
the slave SQL thread is I/O-bound, but the slave overall has plenty of spare I/O capacity (definitely more than one disk spindle).
the slave uses InnoDB or another storage engine with row-level locking.

it does not speed up replication on my slaves, which mostly have large queries like INSERT .. SELECT .. GROUP BY. in my benchmarks it seemed to make no difference at all, positive or negative. 😭

on the wrong workload or slave configuration, this technique might actually make the slaves slower. your mileage will vary.

user-contributed benchmarks are welcome. 🫶

✨ OPTIONS ✨

specify at least one of --print, --execute or --stop. like, pick a lane bestie.

this tool accepts additional command-line arguments. refer to the "SYNOPSIS" and usage information for details.

--ask-pass — prompt for a password when connecting to MySQL.
--charset — short form: -A; type: string.

default character set. if the value is utf8, sets Perl's binmode on STDOUT to utf8, passes the mysql_enable_utf8 option to DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. any other value sets binmode on STDOUT without the utf8 layer, and runs SET NAMES after connecting to MySQL.

--check-interval — type: Array; default: 16,1,1024.

how often to check the slave: init,min,max. this many relay log events should pass before checking the output of SHOW SLAVE STATUS. the syntax is a three-number range: initial, minimum, and maximum. you should be able to leave this at the defaults.

mk-slave-prefetch varies the check interval in powers of two, depending on whether it decides the check was necessary. she's adaptive like that ✨

--config — type: Array.

read this comma-separated list of config files; if specified, this must be the first option on the command line. first or it doesn't count, periodt.

--[no]continue-on-error — default: yes.

continue parsing even if there is an error.

--daemonize — fork to the background and detach from the shell. POSIX operating systems only.
--database — short form: -D; type: string.

the database to use for the connection. the initial connection will be to this database, but mk-slave-prefetch will issue USE statements as required by the binary log events.

this database is also used as the default database for --secondary-indexes if the database cannot automatically be determined from the query.

--defaults-file — short form: -F; type: string.

only read mysql options from the given file. you must give an absolute pathname. absolute. no shortcuts. 💅

--dry-run — ignore replication checks and just read and rewrite the relay log events.

this option makes mk-slave-prefetch ignore all relay log related checks for position, slave lag, etc. and simply causes the tool to read and rewrite all the events in the relay log. a connection to the slave server is still required.

--errors — cumulative: yes.

print queries that caused errors. if specified once, at exit; if twice, in realtime.

if you specify this option once, you will see a report at the end of the script execution, showing the normalized queries and the number of times they were seen. if you specify this option twice, you will see the errors printed out as they occur, but no normalized report at the end of execution.

--execute — execute the transformed queries to warm the caches.
--help — show help and exit.
--host — short form: -h; type: string.

connect to host.

--[no]inject-columns — default: yes.

inject (columns) into INSERT/REPLACE that don't specify them.

normally this query cannot be rewritten because mk-slave-prefetch doesn't know which columns the values refer to: INSERT INTO tbl VALUES (1,2). this option causes mk-slave-prefetch to SHOW CREATE TABLE the table from the query, get its columns and inject these columns into the query, like: INSERT INTO tbl (\col1`,`col2`) VALUES (1,2)`. this allows the query to be written as a SELECT and prefetched. she's filling in the blanks bestie 🧚

columns for each unique database.table are cached, so this operation may fail if an ALTER TABLE statement changes the order or name of any columns.

--io-lag — type: size; default: 1k.

how many bytes to lag the slave I/O thread. this helps avoid mysqlbinlog reading right off the end of the relay log file.

--log — type: string.

print all output to this file when daemonized.

--max-query-time — type: float; default: 1.

do not run queries longer than this many seconds; fractions allowed. if mk-slave-prefetch predicts the query will take longer to execute, it will skip the query. this is based on the theory that pre-warming the cache is most beneficial for short queries.

mk-slave-prefetch learns how long queries require to execute. it keeps an average over the last --query-sample-size samples of each query. the averages are based on an abstracted version of the query, with specific parameters replaced by placeholders. the result is a sort of "fingerprint" for the query, not executable SQL. you can see the learned statistics with the --statistics option.

you can pre-load query fingerprints, and average execution times, from a file. this way you don't have to wait for mk-slave-prefetch to learn all over every time you start it. just specify the file on the command line. the format should be the same as the output from --statistics.

you might also want to filter out some statements completely, or let only some statements through. see the --reject-regexp and --permit-regexp options.

if mk-slave-prefetch hasn't seen a query's fingerprint before, and thus doesn't know how long it will take to execute, it wraps it in a subquery, like this:

SELECT 1 FROM ( <query> ) AS X LIMIT 1;

this helps avoid fetching a lot of data back to the client when a query is very large. it requires a version of MySQL that supports subqueries (version 4.1 and newer). if yours doesn't, the subquery trick can't be used, so the query might fetch a lot of data back to the client.

once a query's fingerprint has been seen, so it's known that the query isn't enormously slow, mk-slave-prefetch just rewrites the SELECT list for efficiency. (avoiding the subquery reduces the query's overhead for short queries). the rewritten query will then look like the following;

SELECT ISNULL(COALESCE(<columns>)) FROM ...

--num-prefix — abstract away numeric table name prefixes. this causes the following two queries to "fingerprint" to the same thing:

select from 1_2_users;
select from 2_3_users;

--offset — type: size; default: 128.

how many bytes mk-slave-prefetch will try to stay in front of the slave SQL thread. it will not execute log events it doesn't think are at least this far ahead of the SQL thread. see also --window.

--password — short form: -p; type: string.

password to use when connecting.

--permit-regexp — type: string.

permit queries matching this Perl regexp. this is a filter for log events. the regular expression is matched against the raw log event, before any transformations are applied. if specified, this option will permit only log events matching the regular expression.

--pid — type: string

The PID file gets deleted when the daemonized instance exits. And when it starts up, it peeks to see if the PID file's already there — if it is, and the process with that matching PID is actually alive, the program bails out. No double-dipping 💅

--port — short form: -P; type: int. The port number you wanna use for the connection. 🔌
--print — Prints the transformed relay log events straight to standard output.
--print-nonrewritten — Prints the queries that could NOT be transformed into SELECT. Look at them, they're just sitting there, unrewritten 😭
--progress — type: int. Prints progress info every X events. What's in it? The current log file and position, plus a lil summary of all the statistics gathered. 💫
--query-sample-size — type: int; default: 4. Average query exec time over this many queries. Basically the last N queries with a given fingerprint get averaged together to produce the average query execution time (peep --max-query-time for the drama). 📊
--reject-regexp — type: string. Rejects queries matching this Perl regexp. It's like --permit-regexp's evil twin — opposite effect: log events must not match the regular expression.
--relay-log — type: string. Reads only the specified relay log file; - means read from STDIN. 📥

By default mk-slave-prefetch reads the Relay_Log_File reported by SHOW SLAVE STATUS. This option lets you point it at a relay log file that's already been converted to text by mysqlbinlog. Once it's read and parsed that file, the tool exits.

Honestly this one is a whole vibe with --dry-run and --print if you just wanna see how the tool would rewrite the relay log's events — without executing them, and without having to wait around for a lagged slave. 👑

--relay-log-dir — type: string. Opens the slave's Relay_Log_File relative to this directory.

Unless you specify this, mk-slave-prefetch figures out the relay log directory all by itself: first it checks the relay_log system variable to see if it specifies a path. If yes → that path gets used. If no → the datadir variable value gets used.

Heads up bestie: this option is ignored if you specified an explicit --relay-log.

--run-time — type: time. How long mk-slave-prefetch should run before dipping out. Default? Run forever. 🕰️✨
--secondary-indexes — Prefetch secondary indexes for pipelined queries.
--sentinel — type: string; default: /tmp/mk-slave-prefetch-sentinel. Exit if this file exists.
--set-vars — type: string; default: wait_timeout=10000. Sets these MySQL variables. Immediately after connecting to MySQL, this string gets appended to SET and executed.
--sleep — type: time; default: 1s. Sleep time before checking for new events.

When mk-slave-prefetch has read all the events in a relay log, it naps for this long before checking for new events. 😴

Fun fact: this option is automatically set to zero if both --relay-log and --dry-run are specified.

--socket — short form: -S; type: string. Socket file to use for connection.
--statistics — Prints execution statistics after exiting. The stats come in two sections: counters, and queries. The counters just count how many times events happen. You might see these counters:
- mysqlbinlog — Executed mysqlbinlog to read log events.
- events — The total number of relay log events.
- not_far_enough_ahead — An event was not at least --offset bytes ahead of the SQL thread.
- too_far_ahead — An event was more than --offset + --window bytes ahead of the SQL thread.
- too_close_to_io_thread — An event was less than --io-lag bytes away from the I/O thread's position.
- event_not_allowed — An event wasn't a SET, USE, INSERT, UPDATE, DELETE or REPLACE query.
- event_filtered_out — An event was filtered out because of --permit-regexp or --reject-regexp.
- same_timestamp — A SET TIMESTAMP event was ignored because it had the same timestamp as the last one.
- do_query — A transformed event was executed or printed.
- query_error — An executed query had an error.
- query_too_long — An event was not executed because its average query length exceeded --max-query-time.
- query_not_rewritten — A query could not be rewritten to a SELECT.
- master_pos_wait — The tool waited for the SQL thread to catch up.
- show_slave_status — The tool queried SHOW SLAVE STATUS.
- load_data_infile — The tool found a LOAD DATA INFILE query and unlinked (deleted) the temp file.
- could_not_unlink — The tool failed to unlink a temp file.
- sleep — The tool slept for a second because the slave's SQL thread was not running, or because it read past the end of the log.

After the counters, mk-slave-prefetch prints info about every query fingerprint it's seen — two lines per fingerprint. Line one is the query's fingerprint. Line two has the number of times the fingerprint was seen, the number of times executed, the sum of the execution times, and the average execution time over the last --query-sample-size samples. 🧾

--stop — Stops running instances by creating the --sentinel file. ✋
--threads — type: int; default: 2. Number of concurrent threads to use for pipelining queries.
--tmpdir — type: string; default: /dev/null. Where to create temp files for LOAD DATA INFILE queries. The default will make mysqlbinlog skip the file and the associated LOAD DATA INFILE command entirely. 🙈

If mk-slave-prefetch sees a LOAD DATA INFILE command (which, reminder, it won't if this is left at the default), it'll try to remove the temporary file, then skip the event.

--user — short form: -u; type: string. User for login if not current user.
--version — Show version and exit.
--window — type: size; default: 4k. The max bytes ahead of the slave that mk-slave-prefetch should get. This defines the window within which mk-slave-prefetch considers a query OK to execute. The window begins at the slave SQL thread's last known position plus --offset bytes, and extends for the specified number of bytes. 🪟

If mk-slave-prefetch sees a log event that's too far in the future, it increments the too_far_ahead counter and waits for the slave SQL thread to catch up (which increments the master_pos_wait counter). If an event isn't far enough ahead of the SQL thread, it gets discarded and the not_far_enough_ahead counter increments.

Watching those statistics can help you figure out how to tune the window. What you want is mk-slave-prefetch running just ahead of the SQL thread — not throwing out a ton of events for being too far ahead or not far enough ahead. Periodt. 💅

🌈 DSN OPTIONS 🌈

These DSN options are used to create a DSN. Each option is given like option=value. The options are case-sensitive, so P and p are NOT the same option, bestie. There cannot be whitespace before or after the =, and if the value contains whitespace it must be quoted. DSN options are comma-separated. See the maatkit manpage for full details. 📝

A — dsn: charset; copy: yes. Default character set.
D — dsn: database; copy: yes. Default database.
F — dsn: mysql_read_default_file; copy: yes. Only read default options from the given file.
h — dsn: host; copy: yes. Connect to host.
p — dsn: password; copy: yes. Password to use when connecting.
P — dsn: port; copy: yes. Port number to use for connection.
S — dsn: mysql_socket; copy: yes. Socket file to use for connection.
u — dsn: user; copy: yes. User for login if not current user.

⬇️ DOWNLOADING ⬇️

You can download Maatkit from Google Code at <http://code.google.com/p/maatkit/>, or you can grab any of the tools easily with a command like this:

wget http://www.maatkit.org/get/toolname
or
wget http://www.maatkit.org/trunk/toolname

Where toolname can be swapped out for the name (or fragment of a name) of any of the Maatkit tools. Once downloaded, they're ready to run — no installation needed, we love a low-maintenance king 👑. The first URL gets you the latest released version of the tool, and the second gets the latest trunk code from Subversion.

🌸 ENVIRONMENT 🌸

The environment variable MKDEBUG enables verbose debugging output in all of the Maatkit tools:

MKDEBUG=1 mk-....

🧚 SYSTEM REQUIREMENTS 🧚

You need Perl, DBI, DBD::mysql, and some core packages that ought to be installed in any reasonably new version of Perl.

💀 BUGS 💀

For a list of known bugs, go look at <http://www.maatkit.org/bugs/mk-slave-prefetch>.

Please use Google Code Issues and Groups to report bugs or request support: <http://code.google.com/p/maatkit/>. You can also join #maatkit on Freenode to discuss Maatkit. 🫶

Please include the complete command-line used to reproduce the problem you're seeing, the version of all MySQL servers involved, the complete output of the tool when run with --version, and if possible, debugging output produced by running with the MKDEBUG=1 environment variable. That's the whole care package, ty bestie 💖

📜 COPYRIGHT, LICENSE AND WARRANTY 📜

THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.

fr fr, no warranty, none, zero, nada. if it eats your replicas that's between you and the void 😭

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, version 2; OR the Perl Artistic License. On UNIX and similar systems, you can issue man perlgpl or man perlartistic to read these licenses — go read them, they're giving ✨legally binding✨ 💅

You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.

👑 AUTHOR 👑

Baron Schwartz
Daniel Nichter

two icons, one tool. periodt 🫶

🌈 ABOUT MAATKIT 🌈

This tool is part of Maatkit, a toolkit for power users of MySQL. Maatkit was created by Baron Schwartz; Baron and Daniel Nichter are the primary code contributors. Both are employed by Percona. Financial support for Maatkit development is primarily provided by Percona and its clients.

so like… it's a whole toolkit, and the toolkit has a whole squad behind it. no cap, that's the lore 🌸

⭐ VERSION ⭐

This manual page documents:

Ver 1.0.21 Distrib 7540 $Revision: 7531 $

that's the one, that's the version, write it down bestie 📝

aaaand that's the whole page, we made it 🎀 this one came from the User Contributed Perl Documentation collection, built for perl v5.42.0 and dated 2025-07-24 — and yes, the footer is literally just MK-SLAVE-PREFETCH(1) again because manpages love a little encore 🦋

go forth and prefetch responsibly, bestie 💖✨

📦 where this page comes from

fedora 43: maatkit 7540-39.fc43 · GPL-2.0-or-later OR Artistic-1.0-Perl
💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash on 2026-10-18: a language model's rewrite of the original, under its terms