MySQL just released their new MySQL Query Analyzer (link to a trial), and recently wrote up an interview with Mark Matthews about it. If you haven’t read that article, go ahead and do it. I have not used this software, but I fully believe its functionality is quite nice.
But there is at least one alternative, which has been available for a long time. That is the Percona patch-set, plus analysis tools such as mysqlsla or Maatkit’s query analysis tools. This is a compelling alternative, if you can live without a point-and-click interface.
Percona’s patches put the metrics-gathering where it should be: in the server. That’s why Percona’s builds are able to measure a lot of statistics that a Proxy-based solution can’t capture. This information is not possible to get outside of the server. For example, you cannot use the MySQL Query Analyzer to measure the I/O caused by a query. Externally to the server, about all you can do is time queries and measure their size. Percona’s patches have no such limitations; they measure and expose an ever-richening set of meta-data about queries.
Guessing is not enough. You need to be able to measure what your queries are doing. The MySQL Query Analyzer’s way to know which queries cause I/O usage is to “…graph I/O usage on the system as a whole, and when you see a spike in I/O you can see what queries were running at the time.” So you’re essentially reduced to lining up graphs, picking time intervals, running EXPLAIN and guessing. If you use Percona’s patches, you can measure directly which queries cause I/O.
The article claims that “…With MySQL Query Analyzer we are watching from the sideline and capturing things that the MySQL server does not give you,” but the irony is that since Proxy-based solutions are outside the MySQL server, they actually can’t measure things the server already exposes internally. While would be possible to do so by running SHOW STATUS after each query, ask Mark Callaghan what he thinks of that idea.
If you’ve ever administered Microsoft SQL Server, you know what kind of insight you can get into a running server. Other databases have similar functionality. MySQL has decided not to build metrics into the server, and is now trying to build it outside the server—an effort that’s ultimately doomed to failure because the information is only available inside.
Let’s see a feature comparison. I’ve chosen features that were promoted in the tech article linked above, plus key features I know are in the Percona patches:
| Percona patches | MySQL Query Analyzer | |
|---|---|---|
| Has a point-and-click interface | ✓ | |
| Freely available | ✓ | |
| License | Free (GPL) | Proprietary |
| Integrated into the server | ✓ | |
| Requires a separate server | ✓ | |
| Requires an agent on monitored servers | ✓ | |
| Requires MySQL proxy with extra scripts loaded | ✓ | |
| Relays queries through a single-threaded proxy | ✓ | |
| Requires changing where your application connects | ✓ | |
| Captures total execution time of all queries | ✓ | ✓ |
| Measures query execution time in microseconds | ✓ | ✓ |
| Permits sampling of only a fraction of sessions | ✓ | |
| Abstracts queries into similar forms | ✓ | ✓ |
| Aggregates similar queries together | ✓ | ✓ |
| Aggregates across multiple servers | ✓ | |
| Automatically generates EXPLAIN plans | ✓ | ✓ |
| Filters by query type (SELECT, UPDATE, etc) | ✓ | ✓ |
| Calculates statistical metrics (min, max, 95th percentile etc) | ✓ | ✓ |
| Measures per-query execution time | ✓ | ✓ |
| Measures per-query execution count | ✓ | ✓ |
| Measures per-query row counts | ✓ | ✓ |
| Measures per-query update counts | ✓ | ✓ |
| Measures per-query result set sizes | ✓ | |
| Measures per-query table lock waits | ✓ | |
| Measures per-query InnoDB lock waits | ✓ | |
| Measures per-query InnoDB read operations | ✓ | |
| Measures per-query InnoDB write operations | ✓ | |
| Measures per-query InnoDB I/O wait | ✓ | |
| Measures per-query InnoDB queue waits | ✓ | |
| Measures per-query InnoDB pages touched | ✓ | |
| Measures per-query filesorts caused | ✓ | |
| Measures per-query temp tables caused | ✓ | |
| Measures per-query temp tables on disk | ✓ | |
| Measures per-query table usage | ✓ | |
| Measures per-query index usage | ✓ | |
| Measures per-query query cache hits | ✓ | |
| Measures per-query full scans | ✓ | |
| Measures per-query full joins | ✓ | |
| Measures per-query sort merge passes | ✓ | |
| Measures queries executed by replica SQL thread | ✓ | |
| Measures replica SQL thread utilization1 | ✓ | |
| Provides per-database stats | ✓ | ✓ |
| Provides per-table stats2 | ✓ | |
| Provides per-index stats | ✓ | |
| Provides per-user stats | ✓ | ✓ |
| Is deployed and tested in large social network sites | ✓ | ? |
| Is demonstrated stable by years of real-world testing | ✓ | |
| Requires understanding MySQL source code |
Stay tuned. More is coming.
- The replica SQL thread’s utilization is the amount of time it stays busy. This is different from measuring the queries the replica SQL thread executes. The Percona patches can do both; MySQL Query Analyzer does neither, since replication doesn’t go through a proxy. Both are extremely useful in predicting and measuring a replication replica’s workload. [return]
- Aggregating queries and then filtering by table isn’t the same thing as measuring how many Handler operations are performed against the table. The Percona patches include SHOW
TABLE_STATISTICS, SHOWINDEX_STATISTICS, and SHOWUSER_STATISTICS, which are functionality ported from Google’s patches. These let you know exactly how much work is done. This is what I call per-object statistics. [return]
