Databases Reference
In-Depth Information
We'll stop lecturing you now, and explain the approach and tools we use for solving
intermittent problems.
Single-Query Versus Server-Wide Problems
Do you have any evidence of the problem? If so, try to determine whether the problem
is with a single isolated query, or if it's server-wide. This is important to point you in
the right direction. If everything on the server is suffering, and then everything is okay
again, then any given query that's slow isn't likely to be the problem. Most of the slow
queries are likely to be victims of some other problem instead. On the other hand, if
the server is running nicely as a whole and a single query is slow for some reason, you
have to look more closely at that query.
Server-wide problems are fairly common. As more powerful hardware has become
available in the last several years, with 16-core and bigger servers becoming the norm,
MySQL's scalability limitations on SMP systems have become more noticeable. Most
of these problems are in older versions, which are unfortunately still widely used in
production. MySQL still has some scalability problems even in newer versions, but
they are much less serious, and much less frequently encountered, because they're edge
cases. This is good news and bad news: good because you're much less likely to hit
them, and bad because they require more knowledge of MySQL internals to diagnose.
It also means that a lot of problems can be solved by simply upgrading MySQL.
12
How do you determine whether the problem is server-wide or isolated to a single query?
If the problem occurs repeatedly enough that you can observe it in action, or run a
script overnight and look at the results the next day, there are three easy techniques
that can make it obvious in most cases. We'll cover those next.
Using SHOW GLOBAL STATUS
The essence of this technique is to capture samples of
SHOW GLOBAL STATUS
at high
frequency, such as once per second, and when the problem manifests, look for “spikes”
or “notches” in counters such as
Threads_running
,
Threads_connected
,
Questions
, and
Queries
. This is a simple method that anyone can use (no special privileges are required)
without impacting the server, so it's a great way to learn more about the nature of the
problem without a big investment of time. Here's a sample command and output:
$
mysqladmin ext -i1 | awk '
/Queries/{q=$4-qp;qp=$4}
/Threads_connected/{tc=$4}
/Threads_running/{printf "%5d %5d %5d\n", q, tc, $4}'
2147483647 136 7
798 136 7
767 134 9
828 134 7
12. Again, don't do that without a good reason to believe that it's the solution.
