A MySQL host in a slave configuration is in the testing phase before being put into production as a primary server for a specific workload. To make an estimate about how it is going to perform, the idea was born to simply use the current productive master's queries and replay them on the slave. The imminent issues that arise are the following:

  1. the slave already is replaying the master's binlogs, so only SELECT statements should be considered for execution
  2. it needs to happen multi-threaded and in near-realtime (or at least in the same pacing as at the master) to get meaningful figures out of the whole process

Although I know about the general log, I do not see an easy way to meet both requirements. The first would require a more-or-less sophisticated parser to prevent execution of edgy things changing data like a SELECT [...] INTO. As for the second, I know about Percona Playback which is capable of parallel execution and query-pacing, but it only would take the slow query log format (at least for the pacing feature), which is even harder to parse. It also won't process in real time (e.g. the mysql-slow log piped from cat or nc).

As this does not sound too far-fetched as a requirement, I wonder how the other DBAs are managing this.

  • We did this running custom code from our application (record statements there) But we also considered percona playback: you can also use a tcpdump trace, have you checked that ?
    – phil_w
    Dec 3 '14 at 22:19
  • @phil_w I have seen the tcpdump capture option in the docs, but the pacing-relevant option is saying This option ensures that each query takes at least Query_time (from slow query log) to execute, which, I assume, means that it won't be able to make use of tcpdump caps. Also, pcap data is even more difficult to pre-process than the slow query log. Dec 4 '14 at 6:53

I would collect queries from Master via slow log and then reply them on the slave using http://www.percona.com/doc/percona-toolkit/2.1/pt-log-player.html to do this you will need:

  • Enable full slow query logging. You need to be careful with the IO load if your server is really busy. You may want to do it during low time (say at night)
  • Collect good amount of queries to play with
  • Replay them using pt-log-player on slave using filter (you don't want to replay INSERTs/UPDATEs/DELETEs as it will ruin data consistency on slave and you'll have to recreate it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.