Multi-threaded replay of master activity for load/performance testing 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.
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 &lt; db1traffic.sql &amp;
mysql -hdb4 -u... -p... -A -Ddb2 &lt; db2traffic.sql &amp;
mysql -hdb4 -u... -p... -A -Ddb3 &lt; db3traffic.sql &amp;

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!!