Archive for the ‘microslow logging’ tag
Here’s a little trivia that you might find helpful. Suppose that I have the following in the MySQL slow query log (abbreviated for clarity):
# User@Host: root[root] @ localhost [127.0.0.1] # Time: 100919 17:58:52 # Query_time: 9.648427 Lock_time: 8.648039 select sleep(1) from t limit 1;
To get this into the slow query log, I set the long_query_time to 0 and opened two sessions. In one session I ran
LOCK TABLES t WRITE, and in the other I tried to select from that table. As you can see above, 1) LOCK TABLES contributes to the Lock_time number, and 2) the Query_time is the sum of execution time and lock time.
Now, I’ll set long_query_time = 2 and run the same test. What happens? Nothing shows up in the slow query log, because 3) the time spent waiting for table locks doesn’t count towards the slow query time threshold.
A final note: it was rumored that the LOCK TABLES query itself is somehow a special-case that is never logged to the slow query log. However, this is not true; if long_query_time is set to zero, the LOCK TABLES query will appear in the log.
The December release is here! There are some goodies in this release, but the major one is an initial version of mk-log-parser, a slow log analysis tool that is carefully designed (with lots of input from Percona consultants) to make slow log analysis as productive and easy as possible. It’s based on a lot of work Daniel and I have done on log analysis over the years (I actually wrote the initial version a long time ago but never released it). Our goal is to finally put all the mostly-good-enough log parsing tools to rest. There are bugs, we know that; but we’d like you to use the tool and give us feedback anyway.
We also made mk-audit a little more robust, although we didn’t really start to touch the massive TODO list for it. And we fixed a few bugs in mk-table-checksum and mk-table-sync, as well as making them more efficient.
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|
|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 slave SQL thread|
|Measures slave SQL thread utilization|
|Provides per-database stats|
|Provides per-table stats|
|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.
 From the article: “You basically have to redirect your application to connect to the Proxy port.”
 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.
 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.