Databases Reference
In-Depth Information
which might differ in a parameter to the
WHERE
clause, for example.) Noticing this
could have stimulated our intuition and directed us to the solution more quickly.
There were hundreds of
SELECT
queries per second, but only five
UPDATE
s. What's to say
that these five weren't really heavy queries?
They could indeed have been responsible for a lot of load on the server. We didn't
show the actual queries because it would clutter things too much, but it's a valid
point that the absolute number of each type of query isn't necessarily meaningful.
Isn't the “proof” about the origin of the I/O storms still pretty weak?
Yes, it is. There could be many explanations for why a small database would write
a huge amount of data to disk, or why the disk's free space decreased quickly. This
is something that's ultimately pretty hard to measure (though not impossible) on
the versions of MySQL and GNU/Linux in question. Although it's possible to play
devil's advocate and come up with lots of scenarios, we chose to balance the cost
and potential benefit by pursuing what seemed like the most promising leads first.
The harder it is to measure and be certain, the higher the cost/benefit ratio climbs,
and the more willing we are to accept uncertainty.
We said “the database was never the problem in the past.” Wasn't that a bias?
Yes, that was a bias. If you caught it, great—if not, well, then hopefully it serves
as a useful illustration that we all have biases.
We'd like to finish this troubleshooting case study by pointing out that this issue
probably could have been solved (or prevented) without our involvement by using an
application profiling tool such as New Relic.
Other Profiling Tools
We've shown a variety of ways to profile MySQL, the operating system, and queries.
We've demonstrated those that we think you'll find most useful, and of course, we'll
show more tools and techniques for inspecting and measuring systems throughout this
book. But wait, there's more!
Using the USER_STATISTICS Tables
Percona Server and MariaDB include additional
INFORMATION_SCHEMA
tables for object-
level usage statistics. These were originally created at Google. They are extremely useful
for finding out how much or little the various parts of your server are actually used. In
a large enterprise, where the DBAs are responsible for managing the databases and have
little control over the developers, they can be vital for measuring and auditing database
activity and enforcing usage policies. They're similarly useful for multitenant applica-
tions such as shared hosting environments. When you're hunting for performance
problems, on the other hand, they can be great for helping you figure out who's spend-
ing the most time in the database or what tables and indexes are most or least used.
Here are the tables:
