Drupal database performance: MySQL and PostgreSQL compared.
As a long time open source user and a chronic challenger of own assumptions, I often test out the alternatives to my current prefrences. While I used Linux, I tended to switch desktop environments every two months or so. I tried out all the e-mail clients, all the browsers, all the VCSes, and even (almost) all the text editors. Before settling with Drupal, I tried out all the CMSes I could find.
I have long felt compelled by PostgreSQL, although it is in many ways a bastard in the Drupal community. Through officially supported, it is used by a very little minority of Drupal users, and it has proven exceedingly difficult to find someone to maintain Drupal’s compatibility with PostgreSQL.
Still, I’ve tried it out. When I first started using it, I marvelled at how fast everything felt, but such impressions can be (and often are) misleading.
So, I’ve put it to the test with a customer site. I’ve created to sites as identical as I can make them, one on MySQL 5.0.51a with InnoDB and one on PostgreSQL 8.3.3.
This was the test: 5 concurrent logged in users hammering the front page. I know this could be much more scientific, but here’s the configuration:
siege -H "Cookie: drupalsessid" -c 5 "http://drupal-site.local/" -b -t30s
I ran it thrice for each database server. This is all running on the same webserver - in fact, the same server that’s powering this blog. APC was enabled in all these tests. Here are the numbers:
MySQL
Transactions: 325 hits
Availability: 100.00 %
Elapsed time: 29.94 secs
Data transferred: 6.19 MB
Response time: 0.46 secs
Transaction rate: 10.86 trans/sec
Throughput: 0.21 MB/sec
Concurrency: 4.97
Successful transactions: 325
Failed transactions: 0
Longest transaction: 5.81
Shortest transaction: 0.23
Transactions: 245 hits
Availability: 100.00 %
Elapsed time: 30.46 secs
Data transferred: 4.67 MB
Response time: 0.61 secs
Transaction rate: 8.04 trans/sec
Throughput: 0.15 MB/sec
Concurrency: 4.93
Successful transactions: 245
Failed transactions: 0
Longest transaction: 1.27
Shortest transaction: 0.27
Transactions: 273 hits
Availability: 100.00 %
Elapsed time: 30.03 secs
Data transferred: 5.20 MB
Response time: 0.54 secs
Transaction rate: 9.09 trans/sec
Throughput: 0.17 MB/sec
Concurrency: 4.95
Successful transactions: 273
Failed transactions: 0
Longest transaction: 1.34
Shortest transaction: 0.25
PostgreSQL
Transactions: 171 hits
Availability: 100.00 %
Elapsed time: 29.51 secs
Data transferred: 3.62 MB
Response time: 0.85 secs
Transaction rate: 5.79 trans/sec
Throughput: 0.12 MB/sec
Concurrency: 4.92
Successful transactions: 171
Failed transactions: 0
Longest transaction: 2.19
Shortest transaction: 0.44
Transactions: 178 hits
Availability: 100.00 %
Elapsed time: 30.33 secs
Data transferred: 3.77 MB
Response time: 0.84 secs
Transaction rate: 5.87 trans/sec
Throughput: 0.12 MB/sec
Concurrency: 4.95
Successful transactions: 178
Failed transactions: 0
Longest transaction: 2.18
Shortest transaction: 0.44
Transactions: 163 hits
Availability: 100.00 %
Elapsed time: 30.06 secs
Data transferred: 3.45 MB
Response time: 0.90 secs
Transaction rate: 5.42 trans/sec
Throughput: 0.11 MB/sec
Concurrency: 4.88
Successful transactions: 163
Failed transactions: 0
Longest transaction: 1.56
Shortest transaction: 0.46
Conclusion
I played a bit with these numbers, even throwing together a snazzy little spreadsheet.
And however much I play with these numbers, there’s nothing to hide the fact that MySQL outperforms PostgreSQL by a wide margin, even though MySQL in this case is configured to use InnoDB, although that is rumoured to be slower than InnoDB.
So, yes, MySQL is the faster database to run Drupal on (for this particular load, at any rate).
What is the cause of this? Is PostgreSQL just inherently slower than MySQL, or is it the that Drupal fails to take advantage of Postgres. I know that we in many cases could conserve resources in the database by using a more specific column type, but I have no clear idea how much that would gain us.
It might also be because my PostgreSQL is improperly configured. It it more less as it came out of the Ubuntu Server box, whereas MySQL is set up to my own liking, though not with a whole lot of performance tweaks.
I will probably continue to use and play with PostgreSQL, but until proven otherwise, I’m going to assume that MySQL is the best performing database for my Drupal sites. I’m looking forward to see how these numbers would come out with Drupal 7.
Update
Thanks to the nice guys on pgsql-general, I found both that Drupal can’t figure out how to use sockets with PostgreSQL and that Ubuntu enables SSL on all PostgreSQL HTTP-connections including localhost out-of-the-box. That obviously skews the benchmark to the point where it becomes completely unreliable.
I have now changed PostgreSQL not to use SSL for localhost, and it changes the picture quite a bit – these are the…
new numbers for PostgreSQL
Transactions: 283 hits
Availability: 100.00 %
Elapsed time: 29.63 secs
Data transferred: 6.08 MB
Response time: 0.52 secs
Transaction rate: 9.55 trans/sec
Throughput: 0.21 MB/sec
Concurrency: 4.93
Successful transactions: 283
Failed transactions: 0
Longest transaction: 1.31
Shortest transaction: 0.32
Transactions: 291 hits
Availability: 100.00 %
Elapsed time: 30.19 secs
Data transferred: 6.25 MB
Response time: 0.51 secs
Transaction rate: 9.64 trans/sec
Throughput: 0.21 MB/sec
Concurrency: 4.96
Successful transactions: 291
Failed transactions: 0
Longest transaction: 1.29
Shortest transaction: 0.31
Transactions: 288 hits
Availability: 100.00 %
Elapsed time: 30.51 secs
Data transferred: 6.18 MB
Response time: 0.52 secs
Transaction rate: 9.44 trans/sec
Throughput: 0.20 MB/sec
Concurrency: 4.93
Successful transactions: 288
Failed transactions: 0
Longest transaction: 1.19
Shortest transaction: 0.32
And for MySQL
Transactions: 429 hits
Availability: 100.00 %
Elapsed time: 30.16 secs
Data transferred: 8.18 MB
Response time: 0.35 secs
Transaction rate: 14.22 trans/sec
Throughput: 0.27 MB/sec
Concurrency: 4.98
Successful transactions: 429
Failed transactions: 0
Longest transaction: 1.17
Shortest transaction: 0.22
Transactions: 403 hits
Availability: 100.00 %
Elapsed time: 30.16 secs
Data transferred: 7.68 MB
Response time: 0.37 secs
Transaction rate: 13.36 trans/sec
Throughput: 0.25 MB/sec
Concurrency: 4.98
Successful transactions: 403
Failed transactions: 0
Longest transaction: 1.32
Shortest transaction: 0.23
Transactions: 406 hits
Availability: 100.00 %
Elapsed time: 30.42 secs
Data transferred: 7.74 MB
Response time: 0.37 secs
Transaction rate: 13.35 trans/sec
Throughput: 0.25 MB/sec
Concurrency: 4.95
Successful transactions: 406
Failed transactions: 0
Longest transaction: 1.28
Shortest transaction: 0.22
And an updated comparison table:
As you can see, that brings the difference down to about 30%. I suppose we could reduce it further, if I could make Drupal use sockets for connecting to PostgreSQL.