Monitoring your Drupal MySQL server performance with MySQL Enterprise Monitor and MySQL Proxy

Monitoring your Drupal MySQL server performance with MySQL Enterprise Monitor and MySQL Proxy

FFW Marketing
Thought byFFW Marketing
August 10, 2009
Laptop with a bullseye on the screen

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.

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

MySQL Enterprise Monitor Features

Monitor

The overview page providing you with a quick summary of the servers, their status, events, availability and load.

Advisors

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.

Events

Provides an interface into the event system that highlights specific issues and problems on your monitored servers.

Graphs

Enables you to view and configure a number of individual graphics covering a range of different statistics.

Query Analyzer

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.

Replication

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.

MySQL Proxy Query Analysis

 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:

Row Statistics:

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.