Multi-threaded replay of master activity for load/performance testing

Question

Does anyone know of any existing tools/products that accomplish what I'm trying to do?

After searching around for a bit the only thing I could find surrounding what I'm trying to accomplish is someone else looking for the same thing http://forums.mysql.com/read.php?24,192250,192250

The idea is I'd like to capture all the traffic to my master to save for a replay log against a snapshot of the entire database taken when the monitoring started. Bin logs won't serve what I want since they only include writes. I want read activity to realistically view the effects of proposed changes with "real" production traffic.

Real production traffic meaning everything from all applications that are hitting the database to be modified. If there's some application level change, tests running just that app don't account for other activity going on in the system at the time. Running all applications in a test environment aren't guaranteed to have the same state of the database. I could take a test snapshot as a starting point as I fire them up but the applications don't have the ability to do an exact replay of their own activity.

I've use the tcpdump script from http://www.mysqlperformanceblog.com/2008/11/07/poor-mans-query-logging/ to monitor activity but this doesn't tell me which queries are coming from which connections. Part of the playback I'm wanting is a multi threaded approach that replays the activity from the same number of threads that were actually in use.

I can't afford to turn on general query logging b/c my production master wouldn't be able to handle the performance hit for that.

The whole snapshot part of the process is to have a golden start point database to ensure everything is the same during the start of each test run.

Answer 1

I believe that pt-query-digest from the Percona Toolkit (http://www.percona.com/doc/percona-toolkit/2.0/pt-query-digest.html) is what you are looking for.

Answer 2

I actually have a suggestion that will take some work but is doable.

THEORETICAL SCENARIO

• You have 3 databases: db1, db2, db3
• Production DB had binary logging enabled

What you need are the following to create enough traffic:

• mysqldump of Production DB two weeks old (TwoWeekOldData.sql) of db1, db2 and db3
• Binary logs on Production DB that are two or more weeks old
• Four DB Servers running MySQL
  • DBServers 1-3 are Slaves to Production DB
  • DBServers 1-4 have Binary Logging Enabled
  • DBServer1 has replicate-do-db=db1 and has only db1 data loaded
  • DBServer2 has replicate-do-db=db2 and has only db2 data loaded
  • DBServer3 has replicate-do-db=db3 and has only db3 data loaded
  • DBServer4 has all data from TwoWeekOldData.sql loaded

You will need the following:

• Run mysqlbinlog against all binary logs on Slave1 and collect SQL into db1traffic.sql
• Run mysqlbinlog against all binary logs on Slave2 and collect SQL into db2traffic.sql
• Run mysqlbinlog against all binary logs on Slave3 and collect SQL into db3traffic.sql

• Run these three traffic SQL files in parallel like this

  • mysql -hdb4 -u... -p... -A -Ddb1 < db1traffic.sql &
  • mysql -hdb4 -u... -p... -A -Ddb2 < db2traffic.sql &
  • mysql -hdb4 -u... -p... -A -Ddb3 < db3traffic.sql &

• You can use DBServer4 to measure

  • server load
  • query performance
  • I/O utilization
  • Whatever you want to measure

As I mentioned in the outset, this takes some setup work along with access to old data and all binary log entriers since the old data's last backup.

I hope this gives you some guidance on producing real traffic rather that simulating ordinary load.

GIVE IT A TRY!!