Drupal Database Development

Most information that you see displayed on a Drupal site, other than images and files, lives in the Drupal database.  Drupal code is executed by the webserver which requests whatever information it needs from the database.  When Developing Drupal custom modules, you will write many database queries: reading, writing, modifying, and deleting data in the Database.  When you do this, it is easy to accidentally write bad queries.  These bad queries can be the source of painfully slow website response times, and in some cases, site outages.  The way to avoid this very common problem is to employ industry standard tools like Anemometer from the very beginning of your development.

Anemometer is a tool for visualizing collected data from the MySQL Slow Query Log. The name comes from the instrument in a weather station that measures wind speed. SQL queries are like the wind — ephemeral and hard to get a hold of. Anemometer helps you get a handle on your queries; it makes it easier to figure out what to optimize and how to track performance over time.

Anemometer relies on the Percona Toolkit to do the slow query log collection. Specifically you can run pt-query-digest. To parse your slow logs and insert them into a database for reporting.

The above image is borrowed from the percona blog on the subject demonstrating query time sum graphically

As your development team grows, you will have many people writing queries to the database.  You can endeavor to teach proper database query writing skills through the proper use of indexes, but this alone is not enough.  There is no substitute for constant discipline where it comes to database development.  When a developer can see that there new query is causing a qurey lock time spike in the graph on the drupal database, they know there's a problem and they can fix it before it gets pushed to production.  Peaceful and non-judge mental. 

The above image is borrowed from the percona blog on the subject showing a bad query

Once your Development team gets into the habit of using Anemometer, as a way of validating there database queries, your obstacles to scaling will be removed.  It's a really really good habit to start from the very beginning.  This way when you achieve your goal of 1,000,000 new users, your Drupal custom site will be well positioned to handle the load.



Add new comment