We often encounter customers who have partitioned their applications among a number of databases within the same instance of MySQL (think application service providers who have a separate database per customer organization … or wordpress-mu type of apps). For example, take the following single MySQL instance with multiple (identical) databases:
SHOW DATABASES;
+----------+
| Database |
+----------+
| db1 |
| db2 |
| db3 |
| db4 |
| mysql |
+----------+
Separating the data in this manner is a great setup for being able to scale by simply migrating a subset of the databases to a different physical host when the existing host begins to get overloaded. But MySQL doesn’t allow us to examine statistics on a per-database basis.
Enter Maatkit.
There is an often-ignored gem in Maatkit’s mk-query-digest, and that is the –group-by argument. This can be used to aggregate information by tables, hosts, users, or databases (full documentation is available via perldoc).
%> perl mk-query-digest --limit 100% --group-by db slow.log
...
# Rank Query ID Response time Calls R/Call Item
# ==== ======== ============= ===== ====== ====
# 1 0x 6000 60.0% 6000 0.5124 db3
# 2 0x 2000 20.0% 2000 0.0112 db1
# 3 0x 1500 15.0% 1500 0.1665 db2
# 4 0x 500 5.0% 500 0.0022 db4
So here, we can see that the majority (60%, to be exact) of execution time is spent in db3. If the server is reaching it’s capacity and the next most useful performance optimization is to migrate a database to a different server, you know exactly which database to move (db3) and how much room that will give you on the original host (60% growth) and on the new host (40% growth), which may have a direct bearing on your hardware selection.
Let Baron know how awesome you think this is by getting him a gift from his Amazon Wish List!
Entry posted by Ryan Lowe |
No comment
Add to: 
PlanetMySQL Voting:
Vote UP /
Vote DOWN"
Posts
No comments:
Post a Comment