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.

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).