Benchmarking PostgreSQL vs. MySQL performance using Drupal 5.x

Drupal has long supported both MySQL and PostgreSQL as database backends. The vast majority of Drupal users run it on MySQL though. Support for PostgreSQL in Drupal has suffered from non-contiguous maintainership, with someone taking it for a year, then disappearing, then someone else comes by and takes it up, ...etc.

There has even been concerns among core developers about PostgreSQL holding back patches that work fine in MySQL.

Benchmark setup

For the hardware, I used my development server, which is an AMD64 X2 4400+ dual core running at 2.2 GHz, with 2 GB of RAM.

This server runs Ubuntu Server 7.04 Fesity Fawn x86_64.

MySQL is 5.0.38, and PostgreSQL is 8.2.5, which are what comes with this distro version. PHP is 5.2.1, with APC 3.015 installed and configured.

Drupal setup

Drupal is the latest 5.x DRUPAL-5 tagged version from the repository.

The setup was a multisite install, so both sites are running from the same code base. One was configured for MySQL and one for PostgreSQL.

PostgreSQL configuration

Setting up a PostgreSQL database took some digging, but once the steps are figured out, they are fairly simple.

First, PostgreSQL would not start, and complains about the SSL certificate.

FATAL:  could not load private key file 'server.key': key values mismatch 

This is caused by the default SSL certficate delivered with Ubuntu is not a valid one. To solve this, just edit /etc/postgresql/8.2/main/postgresql.conf and comment out the line that says ssl = true. Now PostgreSQL will start.

No other changes from the default settings provided by Ubuntu are made.

Creating a PostgreSQL user and database

The steps to create a database in PostgreSQL are documented in the INSTALL.pgsql.txt that comes with Drupal.

However, I used a slightly different approach, with the same end result.

For creating a user and database, assuming we want a user called username and a password of password123, you do the following in a shell:

$ sudo bash
# su - postgres
$ createuser -DRS username
$ createdb -O username drupal
$ psql
ALTER USER USERNAME WITH UNENCRYPTED PASSWORD 'password123';  

Once the database is created, you just stick the following link in settings.php:

$db_url = 'pgsql://username:password123@localhost/drupal';

Then, you visit install.php so Drupal will create the tables for you in the database. Of course, you can use install.php and enter the data as well, which is easier for most users.

MySQL configuration

The default configuration supplied with Ubuntu was used. This has the query cache turned on by default. All the tables are MyISAM.

Running the benchmarks

The benchmark consisted of running two benchmarks using ab on each virtual host.

One benchmark was for a concurrency of 1:

ab -c1 -n1000 http://mysql.example.com/ 

The other was with a concurrency of 5:

ab -c5 -n1000 http://mysql.example.com/  

Benchmarking methodology

For setting up data for this test, the devel module's generate functionality was used to create content types pages and stories, with 2000 nodes and 2000 comments.

MySQL results

MySQL with Devel module

When hitting the front page of the site, the MySQL site shows this:

 Page execution time was 63.89 ms. Executed 98 queries in 15.07 milliseconds. 

MySQL with concurrency of 1

The results are 18.2 requests per second.

Server Software:        Apache/2.2.3
Server Hostname:        mysql
Server Port:            80
Document Path:          /
Document Length:        14054 bytes
Concurrency Level:      1
Time taken for tests:   54.855844 seconds
Complete requests:      1000
Failed requests:        0
Write errors:           0
Total transferred:      14580000 bytes
HTML transferred:       14054000 bytes
Requests per second:    18.23 [#/sec] (mean)
Time per request:       54.856 [ms] (mean)
Time per request:       54.856 [ms] (mean, across all concurrent requests)
Transfer rate:          259.55 [Kbytes/sec] received
Connection Times (ms)
min  mean[+/-sd] median   max
Connect:        0    0   0.0      0       0
Processing:    51   54   6.9     54     267
Waiting:       49   52   6.7     52     259
Total:         51   54   6.9     54     267
Percentage of the requests served within a certain time (ms)
50%     54
66%     55
75%     55
80%     55
90%     55
95%     55
98%     55
99%     55
100%    267 (longest request) 

MySQL with concurrency of 5

The results are 32.8 requests per second.

Server Software:        Apache/2.2.3
Server Hostname:        mysql
Server Port:            80
Document Path:          /
Document Length:        14054 bytes
Concurrency Level:      5
Time taken for tests:   30.454853 seconds
Complete requests:      1000
Failed requests:        0
Write errors:           0
Total transferred:      14580000 bytes
HTML transferred:       14054000 bytes
Requests per second:    32.84 [#/sec] (mean)
Time per request:       152.274 [ms] (mean)
Time per request:       30.455 [ms] (mean, across all concurrent requests)
Transfer rate:          467.51 [Kbytes/sec] received
Connection Times (ms)
min  mean[+/-sd] median   max
Connect:        0    0   0.0      0       1
Processing:    54  151 143.4    148    3553
Waiting:       52  149 143.2    145    3551
Total:         54  151 143.4    148    3553
Percentage of the requests served within a certain time (ms)
50%    148
66%    166
75%    178
80%    186
90%    203
95%    221
98%    247
99%    328
100%   3553 (longest request)

PostgreSQL results

PostgreSQL with Devel module

When hitting the front page of the site, the PostgreSQL site shows this:

Devel shows: Page execution time was 130.44 ms. Executed 99 queries in 67.81 milliseconds. 

This is more than the 68 ms taken by MySQL. The time taken for queries is 68 ms, as opposed to just 15 ms by MySQL.

PostgreSQL with concurrency of 1

For a concurrency of 1, PostgreSQL does 10.7 requests per second.

Server Software:        Apache/2.2.3
Server Hostname:        postgres
Server Port:            80
Document Path:          /
Document Length:        14188 bytes
Concurrency Level:      1
Time taken for tests:   92.395355 seconds
Complete requests:      1000
Failed requests:        0
Write errors:           0
Total transferred:      14715000 bytes
HTML transferred:       14188000 bytes
Requests per second:    10.82 [#/sec] (mean)
Time per request:       92.395 [ms] (mean)
Time per request:       92.395 [ms] (mean, across all concurrent requests)
Transfer rate:          155.53 [Kbytes/sec] received
Connection Times (ms)
min  mean[+/-sd] median   max
Connect:        0    0   0.0      0       0
Processing:    89   91   7.7     90     288
Waiting:       87   89   7.5     88     278
Total:         89   91   7.7     90     288
Percentage of the requests served within a certain time (ms)
50%     90
66%     90
75%     91
80%     91
90%     95
95%    105
98%    108
99%    110
100%    288 (longest request)

PostgreSQL with concurrency of 5

For a concurrency of 5, PostgreSQL can do 12.8 requests per second.

Server Software:        Apache/2.2.3
Server Hostname:        postgresql
Server Port:            80
Document Path:          /
Document Length:        14188 bytes
Concurrency Level:      5
Time taken for tests:   59.679424 seconds
Complete requests:      1000
Failed requests:        0
Write errors:           0
Total transferred:      14715000 bytes
HTML transferred:       14188000 bytes
Requests per second:    16.76 [#/sec] (mean)
Time per request:       298.397 [ms] (mean)
Time per request:       59.679 [ms] (mean, across all concurrent requests)
Transfer rate:          240.79 [Kbytes/sec] received
Connection Times (ms)
min  mean[+/-sd] median   max
Connect:        0    0   0.0      0       0
Processing:   117  297 122.6    280    1035
Waiting:      114  295 122.6    278    1033
Total:        117  297 122.6    280    1035
Percentage of the requests served within a certain time (ms)
50%    280
66%    326
75%    358
80%    379
90%    447
95%    506
98%    642
99%    721
100%   1035 (longest request) 

Summary

The following table shows a summary of the results in a concise form.

Database MySQL PostgreSQL
Total time taken for front page (milliseconds) 63.89 130.44
Time for front page queries (milliseconds) 15.07 67.81
Concurrency of 1 (requests per second) 18.23 10.82
Concurrency of 5 (requests per second) 32.84 16.76

MySQL's query cache

MySQL's query cache makes its performance better. This can be demonstrated by restarting MySQL, then visiting the home page of the site and seeing the query time in devel's output. However this is not as marked as what PostgreSQL takes.

Right after MySQL starts (no queries cached yet):

 Page execution time was 92.74 ms. Executed 98 queries in 30.04 milliseconds.

Subsequent access (query cache kicks in).

  Page execution time was 63.61 ms. Executed 98 queries in 10.38 milliseconds. 

Conclusion

The definite conclusion from these benchmarks is that out of the box, MySQL is configured to run faster than PostgreSQL.

Future considerations

PostgreSQL can probably be made better by further tuning. I am willing to repeat these benchmarks if the PostgreSQL community are willing to guide me with what parameters need to change in order to make its performance better. Please comment below if you have info on this.

Updates

1. I attached the configuration files used for both MySQL and PostgreSQL for anyone who wants to share configuration options that would tune performance further.

2. You can also see a more tweaked MySQL MyISAM configuration file for large web sites for reference (not used for this benchmark).

AttachmentSize
Plain text icon postgresql-configuration.txt508 bytes
Plain text icon mysql-configuration.txt741 bytes

Contents: 

Comments

InnoDB

I'm no Postgresql expert but from my understanding this is not the comparison typically talked about. MyISAM is faster at a functionality cost.

What I usually read about in the comparison is InnoDB vs. Postgresql. These two setups share from of the same functionality such as row level locking rather than table level locking. This is the use case I'm most interested in. From my basic understanding, this is the more appropriate test case.

localhost?

Did you run these tests _on_ the server? When you get a higher # of requests/s for higher concurrency settings, it might indicate that your network connection is the limiting factor.

Also, I don't see the value of out-of-the-box performance; it's the tweaked performance that matters.

Yes and no

Yes, the tests were run on the server (i.e. ab on the same server that runs Drupal and the database). So, network is not an issue here.

As for out of the box experience, it is very important for the average user. They don't have the knowledge, time or money to tweak a system to squeeze the maximum performance.

I am looking for ways to improve PostgreSQL, so anyone with knowledge on how to do that is welcome to share configuration options to do so.
--
2bits -- Drupal consulting

This could say more about Drupal and/or PHP than MySQL or PG

I have deployed Drupal 5x with MySQL and PostgreSQL and the latter has always felt faster. Admittedly, this was only an impression based upon using the site rather than running an artificial benchmark on it. However, I have always ended up using MySQL, despite how much I dislike that product, with Drupal only because support for PostgreSQL is spotty at best for anything but core modules. The results you obtained may have more to do with poor support of PostgreSQL by PHP and/or Drupal than MySQL's supposed superiourity.

agreed - out of the box is of little value

i agree with heine and others. it is simply not useful for most of your readers to compare with out of the box configurations. people who need high performance can afford to buy a book and read web sites like this for configuration tips. your test is valid, it is just not useful. my .02.

Hold on ...

Well, not all sites can afford tuning. Forget the top 10 Drupal sites, and those with several hundred thousands of page views a day. The vast majority of sites fall into this second tier (dedicated and VPS ones, and even shared hosting ones that choose PostgreSQL).

Anyway, I am working with Bill Moran to tune PostgreSQL further. We tried increased the shared_buffers to 200M. The size of the database is 100M, but that still did not help.

Stay tuned, it is not over yet.
--
2bits -- Drupal consulting

Any update

Hi Khalid,

Was wondering if there was any more tunning done on the PostgreSQL db and did it help to bring it inline with the speed of MySQL.

I'm at a point of selecting a db for my web app, so this info would be good to have. I have read many articles and blog entries and they all seem to point that the newer version of PostgreSQL is just as fast as MySQL.

If there is an update to your benchmark, please post it.

Thanks,
Amin

Tests like this will always

Tests like this will always show the same: mysql is faster. And it is, single user, single processor. The point however is that PostgreSQL scales much better. See this page and many other tests which prove over and over again that PostgreSQL scales perfectly.

Not really

This was not single user. This box has 2 cores, and you can see tests that have concurrency of 5 above.

There are Drupal sites that get 600,000+ page views a day on MySQL MyISAM (not even InnoDB), so MySQL does scale too.
--
2bits -- Drupal consulting

You seem to forget that it

You seem to forget that it also depends on how the SQL is written. Also have you made sure your drupal installation makes use of proper indexes? try running the SQL used within drupal with EXPLAIN ANALYZE and see if it uses any indexes, etc.

Also conf files play an important role, this I personally am not that great in, but I know with proper indexing, you can get CRAZY speed advantages.

Ive had both mysql and postgres perform better with proper indexing.
First MySQL was quicker, then someone asked me to index in a certain way and re-run the queries. My queries went from 180ms in mysql down to 95ms. PG went from 210ms down to 67ms.

How about a REAL test.

Why not write something in jmeter or something like that that randomly grabs pages, and WRITES some new ones out every so often.

With lots of threads, and a fair number of them updating, you'll likely find pgsql keeping up just fine.

Then, implement mem_cache for the pgsql side (you can abstract it to work for both mysql and pgsql btw) so that the caching is fair, and also available to real drupal users.

As it is, this test tests very little. The ability to deliver the same front page over and over, even with 5 or more threads, means VERY little. It's like measuring the maximum RPM of an engine as a test. My car can rev to 6000 RPM, a locomotive can rev to 1800 RPM. guess which can pull a heavier load?

We tried ...

With the help of Bill Moran, who is PostgreSQL savvy, we conducted several follow up tests on PostgreSQL for Drupal on our test server. See below for comments on that.

However, after tweaking PostgreSQL's settings, logging queries, and such, we could not find any breakthroughs that would improve performance.

We are willing to repeat those tests if someone from the PostgreSQL community can commit to the time and effort required to get this completed. Contact me if you are interested.
--
2bits -- Drupal consulting

postgres config

please rerun with
fsync=off
in postgresql.conf
without it it's no wonder postgres is slower when it's busy doing sync() after every request

MS SQL Server Comparison

Another interesting article.

In the interests of cross platform neutrality (not many of us about :) ) I'd be interested in seeing some performance benchmarks that compare postgreSQL, MySQL and MS SQL Server.

I'm disappointed that postgreSQL seems relatively slow compared to mySQL because it's such a powerful RDBMS !

Not supported by Drupal

MS SQL is not supported by Drupal, so it cannot be benchmarked vs. anything else.

yeah really. I'm

yeah really. I'm disappointing too that PostgresSQL is slower than MySQL. I in fact wanted to change my set to run on PostgresSQL over MySQL. Because it's more powerful than MySQL.

Good article to have come by. Any body who have tried this benchmark by then self to just verify it??

Let me know.

MyISAM ??

You can't compare any real DBMS with an engine like MyISAM. MyISAM doesn't check anything, it must be faster than any DBMS that does check for data integrity.

Without any configuration, the test also doesn't say much. Take a look at the tests Tweakers.net did with their MySQL-database (!!), PostgreSQL 8.2 is twice as fast as MySQL 5.0

http://tweakers.net/reviews/674/6/database-test-8-way-opteron-scaling-behaviour-from-4-to-8-sockets.html

Newer benchmarks

On Drupal 7.x, the performance on Postgresql is pretty much comparable to MySQL. Sure, there's a slight performance gain that MySQL still has but it's something you can discount when deciding on a database to use. You may even be able to review the performance on SQLite, which is supported in Drupal 7.x.

Postgres needs to go through a Connection Pooler

Due to the process forking model of Postgres rather than the threading model of MySQL, web applications need to connect to Postgres through a connection pooler such as PGBouncer. This software can easily be configured and with this change the benchmark will most definately make a difference. With each connect/disconnect the system has to spawn another process of Postgres due to it's legacy process fork structure. Using PGBounces as a connection pooler, there will be no overhead when spawning new connection - they will simply be recycled and reused. Do not confuse a connection pooler with persistant PHP connections, this has been known to cause PHP memory leaks. A pooler is designed to conservatively manage the open connections to each DB. Using this setup I have been able to service 300+ users in an internal environment with each doing 5 requests per second to the Postgres DB.

If you need any help configuring - feel free to drop me an email.

Link to explanation - http://blog.enricostahn.com/2009/06/25/phps-pg_connect-via-pgpool-ii-vs-pgbouncer-vs-native.html

Recent Numbers

I did a more recent comparison using a similar methodology and published my results on my blog - http://posulliv.github.com/drupal/2012/06/29/mysql-postgres-bench/

I did find that pgbouncer makes a large difference in this type of benchmark.

You can't compare MyISAM vs Postgres

MyISAM should not be used in production for many reasons. But for purposes of this test, you are basically testing a database that has not ACID transactional facilities against one that does. MyISAM better perform better than Postgres in that case. A better test is to use InnoDB vs Postgres so that you are closer to an apples-to-apples test.