How to capture all queries on a very busy MySQL server without adding further strain.

We recently had capacity problems where too large a percentage of read queries where going to our master MySQL server instead of the read-only slaves.What I needed to do was capture the queries on the very busy server without consuming more resources to the disk.I started at first using tcpdump to capture the inbound queries.

sudo tcpdump -s0 -A dst port 3306 and src host app11| strings | grep SELECT| sed 's/^.*SELECT/SELECT/'

This worked really well but I needed to run this for an hour or so to get a decent sample size and couldn’t use the local disks on because they were already at capacity. What I ended up doing was piping the output from tcpdump through ssh.

ssh $TO_HOST cat -  ">" $OUT_FILE

The whole process looks like this.

sudo tcpdump -s0 -A dst port 3306 and src host app11| strings | grep SELECT| sed 's/^.*SELECT/SELECT/' | ssh log11 cat -  ">" db11m_query_log.02AUG2011_3:30-4:00

This allowed my to capture ~100MB file without consuming more IO resources on the local disk.

Advertisements

2 thoughts on “How to capture all queries on a very busy MySQL server without adding further strain.

  1. Hey, something else that is really cool is the maatkit tool chain and in particular my-query-digest. You can use this to capture live queries on one server and write them out or replay them against another server. This is useful for warming up a slave, for instance. Or doing what you are here. Here’s a link to the resource, much more good stuff as well.http://www.maatkit.org/doc/mk-query-digest.htmlHave a look.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s