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.
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 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.
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.
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/
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 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 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)
The following table shows a summary of the results in a concise form.
|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.
The definite conclusion from these benchmarks is that out of the box, MySQL is configured to run faster than PostgreSQL.
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.
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).
Visitor (not verified)
You seem to forget that itThu, 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.
Visitor (not verified)
How about a REAL test.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?
We tried ...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
Visitor (not verified)
postgres configFri, 2008/11/14 - 15:45
please rerun with
without it it's no wonder postgres is slower when it's busy doing sync() after every request
Visitor (not verified)
MS SQL Server ComparisonTue, 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 !
Not supported by DrupalTue, 2009/08/18 - 11:33
MS SQL is not supported by Drupal, so it cannot be benchmarked vs. anything else.
Visitor (not verified)
yeah really. I'mThu, 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.
Visitor (not verified)
MyISAM ??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
Nitin Reddy Katkam (not verified)
Newer benchmarksWed, 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.
Richard Carnes (not verified)
Postgres needs to go through a Connection PoolerFri, 2011/11/11 - 17:04
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