Databases Reference
In-Depth Information
$
mysql -e 'SHOW PROCESSLIST\G' | grep State: | sort | uniq -c | sort -rn
744 State:
67 State: Sending data
36 State: freeing items
8 State: NULL
6 State: end
4 State: Updating
4 State: cleaning up
2 State: update
1 State: Sorting result
1 State: logging slow query
Just change the
grep
pattern if you want to examine a different column. The
State
column is a good one for a lot of cases. Here we can see that there are an awful lot of
threads in states that are part of the end of query execution: “freeing items,” “end,”
“cleaning up,” and “logging slow query.” In fact, in many samples on the server from
which this output came, this pattern or a similar one occurred. The most characteristic
and reliable indicator of a problem was a high number of queries in the “freeing items”
state.
You don't have to use command-line techniques to find problems like this. You can
query the
PROCESSLIST
table in the
INFORMATION_SCHEMA
if your server is new enough, or
use
innotop
with a fast refresh rate and watch the screen for an unusual buildup of
queries. The example we just showed was of a server with InnoDB internal contention
and flushing problems, but it can be far more mundane than that. The classic example
would be a lot of queries in the “Locked” state. That's the unlovable trademark of
MyISAM with its table-level locking, which quickly escalates into server-wide pileups
when there's enough write activity on the tables.
Using query logging
To find problems in the query log, turn on the slow query log and set
long_query_time
to
0
globally, and make sure that all of the connections see the new setting. You might
have to recycle connections so they pick up the new global value, or use Percona Server's
feature to force it to take effect instantly without disrupting existing connections.
If you can't enable the slow query log to capture all queries for some reason, use
tcpdump
and
pt-query-digest
to emulate it. Look for periods in the log where the
throughput drops suddenly. Queries are sent to the slow query log at completion time,
so pileups typically result in a sudden drop of completions, until the culprit finishes
and releases the resource that's blocking the other queries. The other queries will then
complete. What's helpful about this characteristic behavior is that it lets you blame the
first query that completes after a drop in throughput. (Sometimes it's not quite the first
query; other queries might be running unaffected while some are blocked, so this isn't
completely reliable.)
Again, good tools can help with this. You can't be looking through hundreds of giga-
bytes of queries by hand. Here's a one-liner that relies on MySQL's pattern of writing
the current time to the log when the clock advances one second:
