Posted by mitchelljj on April 13, 2010 at 8:45pm
A co-worker informed me that he read on the web that if you are running Drupal with Postgres as the database it runs at 1/2 the speed compared to running Drupal with MySQL as the database.
Any comments?
Thanks,
John
Comments
This was true in the past,
This was true in the past, here is greatly improved since in my opinion. Also manages much better Drupal Postgresql.
What the experts say?
I've only dabbled with
I've only dabbled with Postgres + Drupal. Performance has improved greatly since those 1/2 speed days but they are still probably not equal. The primary reason is probably that 98% of Drupal developers are running MySQL. Thus queries get optimized on MySQL and query optimization is often specific to the DB. Then there's the Pressflow flavour of Drupal that says bye-bye to cross-DB-compatibility and does further optimizations that make queries only able to run on MySQL.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
Tuning?
The default setting of postgresql.conf is not optimized. Users could enjoy much faster postgresql performece with a little optimization.
Few tips. Check out the difference.
Increase shared_buffer. if the server is dedicated for db, around 1/2 of memory.
Increase_commit_delay. Around 10000. It won't hart system like drupal.
Decrease random_page_cost. Around 2.0.
Recent PostgreSQL very fast and one can use replication on backend.
I agree using abstract DB lib is killing performance. Use of prepared statement with proper manner will achieve much better result.
Pressflow is one example that only work with MySQL. Generally speaking, PostgreSQL has more features than MySQL(e.g. developers even suporrt 4.x). If a module don't work with PostgreSQL, it would be a bug.
大垣 靖男 / Yasuo OHGAKI / yohgaki@ohgaki.net
http://www.ohgaki.net/ / http://www.es-i.jp//http://www.provephp.com/
Drupal 7 uses prepared
Drupal 7 uses prepared statements and it brings no performance gain, perhaps even a loss. But the benefits of using Drupal's DB abstraction layer more than outweigh the costs, especially with the new features available in D7. I don't think I agree that it is killing performance. Sure there's a penalty, but bypassing it is not going to cure all your performance woes.
Yes the default PgSQL config is not not good for real-world situations, but the same can be said for MySQL.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
Prepared statement is not a real one
There are many database access abstraction library that uses fake prepared statement. "Fake" means libs that do not use databases system's prepared statement feature. D7 uses PDO's prepared statement and PDO uses both fake one and real one. If PDO was linked againt libpq(PostgreSQL lib) or libmysql(MySQL lib) which do not have prepare statement feature, it emulates prepared statement even if server is supporting it.
In order to enjoy prepared statement performance, app MUST use persistent db connection. User should manage web server processes so that db connection is kept, too. This is more likely the reason for slower performance with prepared statement.
These are reasons why I pointed out like "Use of prepared statement with proper manner will achieve much better result."
I did a quick search on D7 alpha3 source and it does not have PDO::ATTR_PERSISTENT option. So current D7 cannot enjoy prepared statement performance.
大垣 靖男 / Yasuo OHGAKI / yohgaki@ohgaki.net
http://www.ohgaki.net/ / http://www.es-i.jp//http://www.provephp.com/
Use of proxy
Even if app does not support persistent db connection, one may use pgpool-II for PostgreSQL or mysql-proxy to cache db connections. It should give better result, but I haven't tried it out with D7.
大垣 靖男 / Yasuo OHGAKI / yohgaki@ohgaki.net
http://www.ohgaki.net/ / http://www.es-i.jp//http://www.provephp.com/
Benchmark on PostgresSQL 8.4 - default vs. tuned
I took simple benchmark using pgbench to show real difference and how fast PostgreSQL could be.
PostgreSQL 8.4.3 build from tar ball and running under x86_64 Linux.
DB Server - C2Q Q6600(4 core, 2.4Ghz) CPU, 8GB RAM, 1TB SATA HDD
DB Client - Core2Duo MacBook
Server and client are connected by 1Gbps LAN
[Default postgresql.conf]
yohgaki@yohgaki-macbook-2$ pgbench -h 10.10.xxx.xxx -U yohgaki -p 5555 -c 20 -t 200
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 20
query mode: simple
number of clients: 20
number of transactions per client: 200
number of transactions actually processed: 4000/4000
tps = 41.206994 (including connections establishing)
tps = 41.237810 (excluding connections establishing)
yohgaki@yohgaki-macbook-2$ pgbench -h 10.10.xxx.xxx -U yohgaki -p 5555 -c 20 -t 200
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 20
query mode: simple
number of clients: 20
number of transactions per client: 200
number of transactions actually processed: 4000/4000
tps = 41.014274 (including connections establishing)
tps = 41.047273 (excluding connections establishing)
[Turned postgresql.conf]
yohgaki@yohgaki-macbook-2$ pgbench -h 10.10.xxx.xxx -U yohgaki -p 5555 -c 20 -t 200
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 20
query mode: simple
number of clients: 20
number of transactions per client: 200
number of transactions actually processed: 4000/4000
tps = 2912.893559 (including connections establishing)
tps = 3078.429139 (excluding connections establishing)
yohgaki@yohgaki-macbook-2$ pgbench -h 10.10.xxx.xxx -U yohgaki -p 5555 -c 20 -t 200
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 20
query mode: simple
number of clients: 20
number of transactions per client: 200
number of transactions actually processed: 4000/4000
tps = 2982.221487 (including connections establishing)
tps = 3146.506866 (excluding connections establishing)
yohgaki@yohgaki-macbook-2$
I turned off fsync for turned version, but you'll see how fast it could be. Even if I turned on fsync, it's about 10 times faster than default setting.
大垣 靖男 / Yasuo OHGAKI / yohgaki@ohgaki.net
http://www.ohgaki.net/ / http://www.es-i.jp//http://www.provephp.com/
Good info but largely irrelevant
All this info is well and good, and shows what PostgreSQL can do in transactional environments.
The issue here is not PostgreSQL vs. MySQL in an abstract sense, but rather PostgreSQL WITH Drupal vs. MySQL WITH Drupal
We have a now dated article detailing benchmarking of PostgreSQL vs. MySQL performance using Drupal 5.x, using defaults from the distro, and the results were not good. A discussion ensued and someone offered steps to tune PostrgreSQL. We tried various things within PostgreSQL's configuration, and still could not get it to scale.
What we need is a repeat of the above benchmark with Drupal 6 with several key modules installed, and perhaps Drupal 7 as well. It also has to use more current versions of PostgreSQL as well.
Another datapoint: we had a client approach us for tuning a large site that they happened to build on top of PostgreSQL, and it was REALLY slow. Part of the blame was overuse and misuse of certain Drupal features, but PostgreSQL did not help the situation for sure, but made it worse.
Again, an up to date benchmark using Drupal should be the starting point here.
Drupal performance tuning, development, customization and consulting: 2bits.com, Inc..
Personal blog: Baheyeldin.com.
D7 with PostgreSQL and MySQL
Installed 2 D7, one for PostgreSQL and another for MySQL. Almost all settings are the default.
Druapl: 7.0 alpha3
MySQL: 5.1.42
PostgreSQL: 8.4.3
Apache: 2.2.15
PHP: 5.2.13
OS: Linux 2.6.30 x86_64
As I expected, there aren't much differences. Please note that I did this benchmark on my dev PC and it's not a dedicated machine for benchmarking.
Server: C2Q Q6600, 8GB RAM, 1TB SATA HDDs (I have many HDDs on this PC)
Client: C2Duo MacBook
Network: 1Gbps LAN
There aren't much difference between PostgreSQL backend and MySQL backend. During the benchmark, httpd processes are using up CPU time and not much HDD nor DB process. Therefore, CPU is the bottleneck obviously. It could be more than 10x faster even with simple tuning, but the result would be enough to give an idea.
BTW, if I used tuned PostgreSQL, there aren't much difference. It shouldn't matter, since CPU is the bottleneck. i.e. Installing APC, Cache, etc would give better performance.
PostgreSQL
yohgaki@yohgaki-macbook-2$ ab -c 10 -n 1000 http://yohgaki/drupal-7.0-alpha3/
This is ApacheBench, Version 2.3 <$Revision: 655654 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/
Benchmarking yohgaki (be patient)
Completed 100 requests
Completed 200 requests
Completed 300 requests
Completed 400 requests
Completed 500 requests
Completed 600 requests
Completed 700 requests
Completed 800 requests
Completed 900 requests
Completed 1000 requests
Finished 1000 requests
Server Software: Apache/2.2.15
Server Hostname: yohgaki
Server Port: 80
Document Path: /drupal-7.0-alpha3/
Document Length: 5345 bytes
Concurrency Level: 10
Time taken for tests: 53.907 seconds
Complete requests: 1000
Failed requests: 0
Write errors: 0
Total transferred: 5783000 bytes
HTML transferred: 5345000 bytes
Requests per second: 18.55 [#/sec] (mean)
Time per request: 539.070 [ms] (mean)
Time per request: 53.907 [ms] (mean, across all concurrent requests)
Transfer rate: 104.76 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 0.7 0 11
Processing: 249 537 93.2 532 1004
Waiting: 249 536 93.1 531 1004
Total: 250 537 93.2 533 1004
Percentage of the requests served within a certain time (ms)
50% 533
66% 563
75% 585
80% 601
90% 639
95% 691
98% 760
99% 910
100% 1004 (longest request)
MySQL
yohgaki@yohgaki-macbook-2$ ab -c 10 -n 1000 http://yohgaki/drupal-7.0-alpha3-mysql/
This is ApacheBench, Version 2.3 <$Revision: 655654 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/
Benchmarking yohgaki (be patient)
Completed 100 requests
Completed 200 requests
Completed 300 requests
Completed 400 requests
Completed 500 requests
Completed 600 requests
Completed 700 requests
Completed 800 requests
Completed 900 requests
Completed 1000 requests
Finished 1000 requests
Server Software: Apache/2.2.15
Server Hostname: yohgaki
Server Port: 80
Document Path: /drupal-7.0-alpha3-mysql/
Document Length: 5429 bytes
Concurrency Level: 10
Time taken for tests: 50.014 seconds
Complete requests: 1000
Failed requests: 0
Write errors: 0
Total transferred: 5867000 bytes
HTML transferred: 5429000 bytes
Requests per second: 19.99 [#/sec] (mean)
Time per request: 500.143 [ms] (mean)
Time per request: 50.014 [ms] (mean, across all concurrent requests)
Transfer rate: 114.56 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 0.5 0 7
Processing: 241 499 123.5 483 1473
Waiting: 240 498 123.6 483 1472
Total: 241 499 123.5 484 1473
Percentage of the requests served within a certain time (ms)
50% 484
66% 516
75% 536
80% 549
90% 599
95% 658
98% 790
99% 1302
100% 1473 (longest request)
yohgaki@yohgaki-macbook-2$
大垣 靖男 / Yasuo OHGAKI / yohgaki@ohgaki.net
http://www.ohgaki.net/ / http://www.es-i.jp//http://www.provephp.com/
This is a better methodology
This is a better methodology for sure.
Use the devel module to generate lots of content (nodes, comment, taxonomy, users). Also add several blocks on the 2 sidebars.
It would be best if the data is identical, but it would be non-trivial to dump the data from one database and import it in the other.
Now make sure that the page cache is off, as well as block cache too, and rerun tests on the front page which has many nodes.
Drupal performance tuning, development, customization and consulting: 2bits.com, Inc..
Personal blog: Baheyeldin.com.
More Recent Test
I performed similar tests recently on AWS and documented my findings here. For the simple benchmark of testing anonymous users hitting the front page, I found PostgreSQL and MySQL are pretty much on par (assuming both are configured reasonably). For more complex queries that can result from views, I found PostgreSQL to be much better.
Your link doesn't work. I
Your link doesn't work. I assume this is the correct page: http://posulliv.github.com/drupal/2012/06/29/mysql-postgres-bench.html
what about now in drupal 8,
what about now in drupal 8, is better we used postgres ?