There are several monitoring tools for MySQL that show you useful statistics about your server, draw graphs, store historical information and generate trends. All are using the same approach – querying MySQL status variables – this provides basic information about server health. But is that information enough to find and trace weak spots? Unfortunately no. You can get a high level report showing that there is definitely something wrong with your database, but not the root cause.
The only way to find the root cause is to know what your database is doing during stress periods. This requires deeper analysis into the MySQL queries.
While that’s something typically found for other DBMSs (like Query Profiler in MS SQL Server) MySQL was missing this important component for a long time. Until MySQL Proxy was born.
MySQL Proxy is a simple program that sits between your client and MySQL server(s) that can monitor, analyze or transform their communication. This is the ultimate tool for query analysis and is a part of MySQL Enterprise Monitor – the most sophisticated, solid and user friendly tool to monitor your MySQL servers I have ever seen.
MySQL Enterprise Monitor – Features
The overview page providing you with a quick summary of the servers, their status, events, availability and load.
Shows the various advisors configured in your installation and allows you to schedule their execution on different servers, apply and manage rules and manage the advisor installation itself.
Provides an interface into the event system that highlights specific issues and problems on your monitored servers.
Enables you to view and configure a number of individual graphics covering a range of different statistics.
Interfaces to the query monitoring system that can be used to monitor and track the individual queries that are being executed on a system and help to highlight problem queries that may need optimization or that may be affecting server load.
Provides information on the status and structure of your servers that are using replication.
Getting MySQL Enterprise Monitor
MEM is a proprietary tool from MySQL/Sun and is available from a paid commercial subscription. Fortunately there’s a trial license to get started seeing how powerful this tool is.
Mysql Enterprise Trial sign-up page is here http://www.mysql.com/trials/. You will receive an email with login instructions after signing up for an account.
Setting up MySQL Enterprise Monitor
Installation is exhaustively covered in Chapter 2.
Query Analysis Powered by MySQL Proxy
Query Analyzer is the most valuable part of MySQL Enterprise Monitor. It allows you to see what is exactly happening at a particular time and find those nasty slow queries. Sorting by Exec Count, Exec Time, Rows and Bytes will give you a better idea on what’s happening when the site gets slow.
Let’s analyze 1h worth of monitoring statistics and look at some examples.
Order by Exec Count
Looking at top 5 executed queries we have an obvious leader.
SELECT dst FROM url_alias WHERE src = ?
This one is executed 40 times more frequently, thus can be a very good candidate for optimization. It’s originating from drupal_lookup_path() function which is executed every time l() function is called. The more links you have on your Drupal site the more time l() function will be called and the query executed.
What can we do about this? We do not have to do anything actually. This query is very lightweight and will not have much impact on site performance:
Execution Time Statistics:
Max Time: 2.622
Min Time: 0.000
Avg Time: 0.000
Total Time: 7:00.714
Standard Deviation: 0.003
However it may be reasonable to store the whole url_alias table in memcache in order to reduce network traffic generated by the query:
Total Rows: 105,769
Total Size: 5.25 MB
It really depends on the site size and backend configuration:
- If you are reading this, chances are your site is already hitting some performance bottlenecks and your url_alias table has tens/hundreds of thousands of records. Otherwise you can skip this section.
- If you host both Web server and DB server on the same box – you don’t need to do anything – MySQL will do all the work for you.
- If you have a Web server and DB server on separate boxes – you may get benefit from caching url aliases in memcached (there is a module for Drupal 6 that takes care of this– Path Cache [http://drupal.org/project/pathcache]). In this case memcache bin used for path cache should be running on the same box with the Web server. Otherwise this is being optimized here – going back to the same network traffic and delays.
Order by Exec Time
Queries in this Top Chart are best candidates to get the “Slow Query” prize.
Order by Total Exec Time
Our leader here is:
SELECT n.nid, r.vid, n.type, r.title, r.body, r.uid, r.timestamp FROM node n INNER JOIN node_revisions r ON n.nid = r.nid WHERE r.vid > n.vid ORDER BY r.vid DESC LIMIT ?,?
Execution Time Statistics:
Max Time: 18.467
Min Time: 3.876
Avg Time: 7.252
Total Time: 13:32.209
Standard Deviation: 2.965
Executions: 112 Rows
Yick! This is bad. This query is running for 13+ minutes in an hour, though being executed only 112 times. Wins the prize!
Where does it come from? That’s Revision Moderation module [http://drupal.org/project/revision_moderation].
Any query that has a JOIN statement requires involved tables locking. Revision Moderation query is joining node and node_revisions tables (which Drupal is heavily utilizing). As a result we get a global lock on the whole site 20% of time!
Good time to make a decision if it’s really worth having the module on.
Order by Rows/Bytes
Queries in this Top Chart most likely return more rows/date than needed.
Usually there are missing filtering conditions or conditions are applied on the application side.
Do you really need all those rows? Maybe there are only few important while you’re selecting all.
The same story with selecting more fields than needed (SELECT *). You should always specify fields explicitly. This limits your results set to only useful data and also helps to keep your application integrity.
MySQL Proxy – filling a gap in DB performance analysis
MySQL Proxy use cases go way beyond monitoring and analysis. Load balancing, failover, read/write splitting and many more – it provides you with flexibility to do whatever you want using LUA scripting engine. Most likely it will soon become an integral part of any med-to-large size MySQL back end.