Capturing MySQL Data with tcpdump

Percona Toolkit has a great tool, pt-query-digest, that can use tcpdump data. Capturing raw tcp data can be taxing on a server, however, when you see the following message:

64000 packets received by filter
12000 packets dropped by kernel

When there is a significant amount of user cpu% being used, the kernel will drop packets you are trying to capture, leading to a partial picture and missing data. I’ve found that if you write it using the native tcpdump format, it’s more efficient and you drop less. There are also recommendations on Stack Overflow on how to help prevent this.

To perform a capture for a specific length of time, here’s the trick I use:

export REPLICA="" # Adjust for a replica you want to ignore
export INTERFACE="bond0" # Adjust for the interface you want to capture 
export CAPLEN="60" # seconds to capture
/usr/sbin/tcpdump -i ${INTERFACE} \
\( tcp port 3306 \) \
and host not ${REPLICA} \
-s 65535 -X -q -tttt -w tcpdump.out & sleep ${CAPLEN} ; kill %%

This example will capture 60 seconds of data to tcpdump.out in your current directory. Remember that you’re capturing the tcp traffic from an active server and you could easily fill the disk with a long capture time.

To break it down further, you’re executing tcpdump in the background, sleeping ${CAPLEN} and then killing the most recent backgrounded task.

When you need to use this data, you can extract it from the tcpdump format with the following:

/usr/sbin/tcpdump -s 65535 -X -nn -q -tttt -r tcpdump.out > tcpdump.tcp

This file will be suitable to use as data for pt-query-digest:

pt-query-digest --type=tcpdump tcpdump.tcp

Leave a Reply

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

You are commenting using your 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