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 MyISAM.
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.
Complexity
I don’t have a lot of experience with Postgres, but John VanDyk and I recently worked with a client that needed it for compatibility with their existing infrastructure. Drupal has a lot of inherent mysqlisms that are slowly but surely being smoothed away. Subtle things like SELECT COUNT(1) to determine whether there are any rows in a table are slow in Postgres, but speedy in MySQL.
In addition, certain things like establishing a connection to postgres are more expensive than mySQL – most of the PG gurus we talked to explained that connection pooling processes are considered “baseline” configuration for scalable postgres. In our testing, that kind of tweaking doubled performance.
The lesson, I think, is that Postgres can be competitive, and a lot of its features are compelling for “enterprise” installations, but it also takes a lot of careful polishing and tweaking to work through Drupal’s Mysql-centric assumptions.
Too simple a test to tell you anything.
Your testing methodology is far to simple to tell you anything really useful. You need real world tests that simulate real world usage and load, and you need to test with far more than 5 client threads at once.
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.
Making such a decision with so little information is premature. I’m assuming you’re using a stock untuned pgsql, and have not used any form of connection pooling. If that’s the case then you don’t really know. You’ve explored 1% of the possible use cases and declared a winner based on that.
If you did not tweak the
If you did not tweak the postgresql config files at all performance will be lousy. The postgreql.conf file is set up so postgresql will start on machines with very limited hardware. There are many good articles out there on how to do this.
Alright, my benchmarks might
Alright, my benchmarks might have been a bit naïve. When it comes to hardware, my webserver is a SunFire X2100 with an Opteron 1210 Dual Core and 4 GB DDR2 RAM, running 64-bit Ubuntu Linux Server 8.04 LTS.
When it comes to the resource usage section of my postgresql.conf, the only thing that are not commented out are:
I freely admit that the reason I haven’t messed with these values is that I have next to no clue what the different things do and how they affect performance, so perhaps an apology is in order. As Scott wrote, “Without a realistic test scenario and with no connection pooling and with no performance tuning, I don’t think you should make any decisions right now about which is faster”. My apologies.
PostgreSQL tuning
The default values in the postgresql.conf are aimed so the server can start even on systems with very little memory. You’ll likely need to adjust one Linux kernel parameter before you can allocate it more than the 24MB of dedicated memory (shared_buffers) it’s using right now for example. An introduction to the most important values to change is at Tuning Your PostgreSQL Server.
On a relatively simple test like you ran, I’m not sure whether a tuning effort will really buy you much though. I doubt you really need more than the default allocation until there’s significantly more of your site’s content involved in the test. If you want a more interesting comparison with a simple change, use a lot more users than you did. 5 people banging on the site is not enough to demonstrate which database scales better under what happens in the real world; I think you’d see a more useful result if you tried 100 instead.
InnoDB configuration
On a similar note like #3 and #5: the InnoDB default configuration isn’t worth anything - you need to adjust it to your server setup.
My experience is that with proper configuration InnoDB will be in most cases as fast as MyISAM.
Updated
I’ve updated the numbers, after I discovered an important configuration issue that was skewing them.
Post new comment