Drupal on MySql, Things to Know

February 10, 2010

Drupal supports MySQL as it’s primary database. Postgres is also supported, but core development usually lags behind MySQL and contributed modules rarely support it. We experimented with converting to DB2, since IBM was showing some signs of supporting it, but after being swarmed by IBM reps with dollar signs in their eyes this started to seem like a bad idea.

The reason we started looking at the database was because it represents the first and most fundamental bottleneck for large Drupal sites (as well as any dynamic site for that matter). A single, uncached Drupal page can easily make over 100 calls to the database. Dries has a good blogpost about this. With a bunch of sloppy modules this can easily run into several hundred calls per page. Even a cached page has 10 or more database queries (though with memcached you can do it with no database calls).

This bottleneck can be handled first and foremost by limiting your queries with good coding practices. Scrutinize contributed modules carefully for bad practice like querying the db in a loop instead of using a JOIN. Seems obvious I know, but you’d be surprised. Beyond the basics, there are a few other fundamental strategies.

Slave Labor

MySQL supports setting up databases in a master-slave relationship called replication. This entails one database (the master) sending all it’s INSERT and UPDATE statements to a second (the slave), which (mostly) keeps them in sync all the time, so you are then free to do SELECT statements from either database.

It isn’t that difficult to redirect a single read from the Master to a Slave, and you can make short work out of redirecting your most frequently called SELECT statements to the Slaves. You can even grep SQL statements and dynamically send them to the slave, but this is ill advised. The gotcha is the replication lag.

The typical replication lag is well under a second. Here’s a graph of a 4 day period:

Typical MySQL Replication Lag

Typical MySQL Replication Lag (in milliseconds)

As you can see, the lag never went longer than 1/2 second. While 1/2 second is not long, it is far longer than the time it takes to make a typical request on a healthy site. It is not uncommon for Drupal modules to write to the database and then read the data back in a single request. It is very common to write the data in a POST then redirect the response to a GET which immediately reads the data back. If you are writing to the master and reading from the slave, either of these scenarios will look like a site failure. If user’s don’t see their comments, pictures, hopes and dreams show up on your site immediately they will post repeatedly or leave in disgust.

With this in mind, you need to be certain that you know under what conditions a SELECT statement is made before you send it to a slave.

One Bad Apple

In a high volume setup, it only takes one bad query to bring your site down. We recently had a situation where a query generated automatically from a view (more on that later) might take as long as a minute to run. This query was not run very frequently, and most of the time, even in high traffic periods, every thing was fine. However, if too many of these queries where called under moderately heavy traffic it would cause the MySQL threads to start piling up. Once you have a stressed database, queries that normally take 10 milliseconds or less can start taking several seconds, multiply that by 1000 and you’ve quickly used all your database connections and your site is down.

The most effective way to find the bad queries is with the slow query log, which records queries that take longer than a set threshold. We keep this at it’s lowest threshold (1 second), and leave it on all the time. I have a DBA go through this log at least once a month and after every major release. Queries that were fine last month can become slow once a table gets too big, and queries that were fine in QA can collapse under a real world load. A great tool for trolling through these logs is mysqlsla, it will very quickly bring issues to light.

Hazy Views

A View is an automated way of creating queries in Drupal. The powerful thing about them is that many modules use Views to allow for flexible data sets. So for instance if a image gallery module gets its it’s data from a View, you can create galleries of photos, blog posts, videos, or anything with an image. They are great and we use them all the time.

Of course, whenever you are automating query creation you are asking for trouble. Views will not create the best query all the time, and they are frequently the source of queries that show up in the slow query log. Sometimes you can fix it by adding an index, but more often you need to throw away the view entirely and write a specific query for a specific type of content. So it goes.

Sharding Possibilities

Really, really big sites have sometimes dealt with database bottlenecks by sharding. Sharding means breaking up your data across several databases. Facebook, Friendster, Flickr and LiveJournal all do this in one form or another. I don’t know of anyone doing it (truly) in Drupal. We do a modest form of sharding by breaking up different sections of the site into different Drupal instances running off of different databases. To the user it looks like one site, but in the background it is several sites with apache handling the request distribution based on url paths.

We’ve occasionally considered the effort involved in truly sharding Drupal, breaking up the node table or something along those lines, but in the end we’ve always fallen back on simpler strategies which have proven to be effective.

My Favorite Tune

There are many more common MySQL tuning techniques that I didn’t mention but you should look into. Things like MyISAM vs. InnoDB, adding RAM and config tuning. All this is good for any site using MySQL and not Drupal specific.

Where Do You Go From Here?

If all this isn’t doing it for you there are a few other things we’ve considered but not tried. Commercial replication products are available for scaling your Master databases horizontally but they have limits. MySQL does support Master-Master replication out of the box, but as far as I know everyone who’s tried this with Drupal has failed to get it to work as the databases quickly get out of sync. There are also hardware solutions tuned to get the most from a single MySQL master.

If you’re considering any of these however you should probably take another look at your architecture, because in the end your site will only be truly ready to scale if it’s built that way, and with a little effort (ok, more than a little) you can build it in Drupal.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: