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

Comments

Thu, 2008/01/03 - 12:48

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.

Thu, 2008/05/08 - 22:11

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?

Tue, 2008/05/13 - 19:51

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 and Backdrop CMS consulting

Fri, 2008/11/14 - 15:45

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

Tue, 2009/08/18 - 07:48

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 !

Tue, 2009/08/18 - 11:33

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

Thu, 2009/09/17 - 03:26

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.

Fri, 2009/10/02 - 06:05

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

Wed, 2011/05/18 - 11:50

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.

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

Pages

Is your Drupal or Backdrop CMS site slow?
Is it suffering from server resources shortages?
Is it experiencing outages?
Contact us for Drupal or Backdrop CMS Performance Optimization and Tuning Consulting