Stay Curious!

An alternative to the MySQL Query Analyzer

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[1]
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 slave SQL thread[2]
Measures slave SQL thread utilization
Provides per-database stats
Provides per-table stats[3]
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.

Footnotes

[1] From the article: "You basically have to redirect your application to connect to the Proxy port."

[2] The slave SQL thread's utilization is the amount of time it stays busy. This is different from measuring the queries the slave 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 slave's workload.

[3] 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, SHOW INDEX_STATISTICS, and SHOW USER_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.

Posted on Thu, Nov 20, 2008. Approximately 900 Words.

Databases Open Source