We have released two free tools that will help MySQL and PostgreSQL DBAs understand the queries their database servers execute. As you probably know, we have spent nearly 3 years building the most advanced and efficient network traffic capture and decoding tools for MySQL and PostgreSQL. With the release of these free tools, we’re placing all the power of our traffic analysis libraries in your hands.
In our initial release, the tools sniff the network traffic and print out queries, with microsecond-resolution timing information, in a format that pt-query-digest understands natively. This means you can just pipe the tools into pt-query-digest and you’ll get a report of top queries by time. If you’re not familiar with pt-query-digest, it’s a powerful and flexible query analysis tool.
Here’s a quickstart, assuming you’re using the MySQL tool, but it works just the same for PostgreSQL.
wget percona.com/get/pt-query-digest ./vc-mysql-sniffer > log.txt perl pt-query-digest log.txt
Let the network analyzer run as long as you care, and cancel it with CTRL-C to stop capturing data. Run pt-query-digest to see the top-queries report.
Why build and share these free tools with the MySQL and PostgreSQL communities? From many years of experience we know that network analysis is often one of the most powerful ways to understand what a server is doing. Ask any sysadmin if they’ve ever used tcpdump or Wireshark to inspect a server, and you’ll probably hear an enthusiastic yes.
Although tcpdump and Wireshark are fantastic for debugging, they’re not very usable for large-scale query analysis of the type you need to do as a DBA:
- Finding top queries by total time
- Performing audits of the server
- And a range of other tasks
Unfortunately, no really good options exist for this. The ones that do exist just aren’t built for performance or scale, either in the machine sense or in the human usability factor.
SolarWinds® Database Performance Monitor’s (DPM) network decoding libraries are second to none in performance, accuracy, and ability to handle the kinds of bizarre stuff you see when you’re sniffing a network with libpcap. These libraries form the backbone of our battle-tested agents for theDPM service itself. Now they’re available to you, in a form that’s designed to play well with popular tools of all sorts.
Our initial release offers the most important functionality. Later we’ll add functionality to do things like generate metrics from the query traffic in a form suitable to send to StatsD, for example. We also support only 64-bit Linux in this release, but later we’ll support more operating systems such as FreeBSD and Windows. We’ll also add tools for Redis and any other databases we support in our agents. (Ask us about our MongoDB beta if you’re interested.)
The tools require root access to capture network traffic. They are barebones wrappers around our TCP and MySQL/PostgreSQL decoding libraries. They do nothing but decode and print. They don’t try to communicate with our APIs, for example. They’re secure and private to use.
