Have you ever tuned the MySQL instance underlying your content management system (CMS)? For most users of Drupal, Joomla!, XOOPS, Alfresco, or any of the other CMS, the answer is no.
However, I’m seeing an increasing number of mission-critical corporate sites running on open source CMS. Take a look at Alfresco’s customer listing, http://www.alfresco.com/customers , which includes H&R Block, Boise Cascade and Harvard Business School Publishing. If your CMS site has become a crucial part of your enterprise, it’s time to take a closer look at the database.
The MySQL Enterprise Monitor is designed to help developers configure, tune and administer their MySQL Servers. After watching a Drupal presentation in a local meetup, I decided to evaluate the Monitor with Drupal.
Test Scenario
1. Install Drupal and MySQL
2. Install the MySQL Enterprise Monitor ( A trial can be downloaded from http://www.mysql.com/ent-trial-reg-2007 )
3. Add a load to the site using DieselTest ( http://www.dieseltest.com )
MySQL Enterprise Monitor
The Monitor has two components: the agent, which collects statistics (600+) from the production server; and the dashboard, a web-based interface, which is installed on a non-production machine within the firewall. The dashboard can be used to determine what gets monitored (70+ options), to configure email notifications and to view 20+ graphs.
Figure 1.
Heat Chart
By default, the Monitor includes 12 rules that comprise the heat chart (figure 1). The heat chart is on the front page of the web-interface and provides a one-glance status report on your MySQL server. It monitors if the server is up or down; the I/O, CPU and RAM usage; database connections; key and query cache; table scans; temporary tables-to-disk ratio and lock contention.
Critical Alert! -Query Cache Has a Sub-Optimal Hit Rate
The Monitor instantly threw a critical alert that the Query Cache Has a Sub-Optimal Hit Rate. The query cache holds not only the SQL statement, but the data as well. Results for identical statements can be instantly retrieved from cache. However, it also means than any updates to the underlying table will force the cache to be invalidated. Since my load test was primarily duplicate reads, I should probably follow the advice of the alert and increase query_cache_size.
Security and Administration
With version 1.2, there are 18 security rules and 16 Administration rules. These rules will provide email notification for issues ranging from the existence of anonymous user accounts to an excessive number of attempted connections (DOS attack?). I actually received 3 administrative alerts and 7 security alerts. I had covered the basics, but it still triggered several warnings including advice to disable symlinks and to disable older (weaker) 4.1 password authentications.
Adding a Load to Drupal
I browsed the internet and selected Dieseltest (http://www.dieseltest.com) for load generation. Dieseltest looked easy, and it was. I simply recorded some blog and forum updates and some browsing on my drupal site. Then, I re-played the script with 50 virtual users for 10 minutes. Of course, this was a fairly rudimentary process, but it did add load to my database server (see figure 2).
Figure 2. 
Performance
In all, I received nine performance events. I outlined the two most critical below:
Critical Alert! -Indexes Not Being Used Efficiently
As I suspected, the default database could use some indexing. See figure 3 for rows read via indexes versus rows read with a full table scan. As the alert suggested, I need to enable –log-queries-not-using-indexes.
Figure 3.
Critical Alert! – Excessive Disk Temporary Table Usage Detected
I may need to do more research on this one. I suspect that the temp tables are a result of using a lot of TEXT data types for storing blog entries. Perhaps I can change some data types to varchar so that disk temporary tables aren’t automatically used. The alert provides plenty of detailed information for adjusting parameters and links to more online references.
Conclusion
A web site developer recently told me that he designed his sites without ever changing the default MySQL installation. While this speaks volumes to the ease-of-use of MySQL, if your site becomes mission-critical or has grown to handle more traffic than you anticipated, consider installing the MySQL Enterprise Monitor for configuring, monitoring and tuning your server.
