I had the opportunity to review a client’s production Drupal installation recently. This is a new site and traffic is just starting to pick up. Drupal is a popular LAMP stack open source CMS system using the MySQL Database.
Unfortunately I don’t always have the chance to focus on one product when consulting, sometimes the time can be minutes to a few hours. Some observations from looking at Drupal.
Disk footprint
Presently, volume and content is of a low volume, but expecting to ramp up. I do however find 90% of disk volume in one table called ‘watchdog’;
+--------------+--------------+--------------+-------------+--------+
| table_schema | total_mb | data_mb | index_mb | tables |
+--------------+--------------+--------------+-------------+--------+
| xxxxx | 812.95555878 | 745.34520721 | 67.61035156 | 191 |
+--------------+--------------+--------------+-------------+--------+
+-------------------------------------------+--------+------------+------------+----------------+--------------+--------------+-------------+
| table_name | engine | row_format | table_rows | avg_row_length | total_mb | data_mb | index_mb |
+-------------------------------------------+--------+------------+------------+----------------+--------------+--------------+-------------+
| watchdog | MyISAM | Dynamic | 63058 | 210 | 636.42242813 | 607.72516251 | 28.69726563 |
| cache_menu | MyISAM | Dynamic | 145 | 124892 | 25.33553696 | 25.32577133 | 0.00976563 |
| search_index | MyISAM | Dynamic | 472087 | 36 | 23.40134048 | 16.30759048 | 7.09375000 |
| comments | MyISAM | Dynamic | 98272 | 208 | 21.83272934 | 19.58272934 | 2.25000000 |
Investigating the content of the ‘watchdog’ table shows detailed logging. Drilling down just on the key ‘type’ records shows the following.
mysql> select message,count(*) from watchdog where type='page not found' group by message order by 2 desc limit 10;
+--------------------------------------+----------+
| message | count(*) |
+--------------------------------------+----------+
| content/images/loadingAnimation.gif | 17198 |
| see/images/loadingAnimation.gif | 6659 |
| images/loadingAnimation.gif | 6068 |
| node/images/loadingAnimation.gif | 2774 |
| favicon.ico | 1772 |
| sites/all/modules/coppa/coppa.js | 564 |
| users/images/loadingAnimation.gif | 365 |
| syndicate/google-analytics.com/ga.js | 295 |
| content/img_pos_funny_lowsrc.gif | 230 |
| content/google-analytics.com/ga.js | 208 |
+--------------------------------------+----------+
10 rows in set (2.42 sec)
Some 25% of rows is just the reporting one missing file. Correcting this one file cuts down a pile of unnecessary logging.
Repeating Queries
Looking at just 1 random second of SQL logging shows 1200+ SELECT statements.
355 are SELECT changed FROM node
$ grep would_you_rather drupal.1second.log
7 Query SELECT changed FROM node WHERE type='would_you_rather' AND STATUS=1 ORDER BY created DESC LIMIT 1
5 Query SELECT changed FROM node WHERE type='would_you_rather' AND STATUS=1 ORDER BY created DESC LIMIT 1
3 Query SELECT field_image_textarea_value AS value FROM content_type_would_you_rather WHERE vid = 24303 LIMIT 0, 1
4 Query SELECT changed FROM node WHERE type='would_you_rather' AND STATUS=1 ORDER BY created DESC LIMIT 1
6 Query SELECT changed FROM node WHERE type='would_you_rather' AND STATUS=1 ORDER BY created DESC LIMIT 1
10 Query SELECT changed FROM node WHERE type='would_you_rather' AND STATUS=1 ORDER BY created DESC LIMIT 1
9 Query SELECT changed FROM node WHERE type='would_you_rather' AND STATUS=1 ORDER BY created DESC LIMIT 1
8 Query SELECT changed FROM node WHERE type='would_you_rather' AND STATUS=1 ORDER BY created DESC LIMIT 1
9 Query SELECT field_image_textarea_value AS value FROM content_type_would_you_rather WHERE vid = 24303 LIMIT 0, 1
There is plenty of information regarding monitoring the Slow Queries in MySQL, but I have also promoted that’s it not the slow queries that ultimately slow a system down, but the 1000’s of repeating fast queries.
MySQL of course has the Query Cache to assist, but this is a course grade solution, and a high volume read/write environment this is meaningless.
There is a clear need for either a application level caching, or a database redesign to pull rather then poll this information, however without more in depth review of Drupal I can not make any judgment calls.
This month will see the release of the book Expert PHP and MySQL which I was a co-author of. Initially this will be available for purchase in PDF format from the Wrox website and I am hopeful this will be available in print format for the MySQL Users Conference.
