10/15/07 update: On persistent connections, see http://us2.php.net/manual/en/function.pg-pconnect.php#78476 As a correction to this post and on db connection speed, see http://www.nabble.com/Re%3A-ZF-performance-advice-p13186947s16154.html
Abstract: Postgres is my preferred db but it’s running slow; should I use MySQL instead?
Answer: Nope, Postgres can be made to connect just as fast as MySQL with a small change supported by the Zend Framework’s Zend_Db.
Good news: I got my ZF Postgres db connection up and running
Bad news: it was taking 1.5 seconds just for my zf bootstrap environment to run with no db queries.
I was concerned: maybe Postgres is too slow and I should use MySQL.
So I did a little study.
The result: I got Postgres connections to be just as fast as MySQL by using persistent connections.
My setup:
Laptop: 1.2 gb ram, 1.6 ghz single core, 7200 rpm disk, Windows XP
Php 5.2
Postgres 8.2
Apache 2.2
Firefox 2.0
No query; just connect to db using Zend_Db: $db->getConnection();
With about 10 samples:
Postgres:
0.7 - 0.9 sec with occassional peaks of 1.2 sec for 1 db connect
1.4 – 1.6 sec for 2 db connects to the same db.
MySQL:
0.6 - 0.7 sec for 1 db connect
1.1 – 1.2 sec for 2 db connects to the same db.
Now, add persistent connections via:
$driver_options = array(PDO::ATTR_PERSISTENT => true);
Postgres:
0.6 - 0.6 sec with occassional peaks of 0.8 sec for 1 db connect
1.1 – 1.2 sec for 2 db connects to the same db.
So it’s working and speeding connections up!
MySQL:
0.6 - 0.7 sec for 1 db connect
1.1 – 1.2 sec for 2 db connects to the same db.
No difference…
Does not seem to be working
Postgres is just as fast as MySQL for connections now!
So, switching to MySQL won’t help.
But the whole thing is still pretty slow…
I wonder if pgpool would help.
Anyone have any advice on pgpool?
Subscribe to:
Post Comments (Atom)
4 comments:
Definitely, database connection time is not major time leak in general web/php application. Personally, I'm strongly fighting against persistent connections because they does not provide significant time improvement while gives unnecessary complexity. It is better to reuse connection during script execution, e.g. open one connection per request then share connection between requests. Data caching is also a key to good application.
First, if you're thinking about choosing mysql or postgresql basing your opinion in the connection time it means to me you're not aware about the 'real' differences between two databases. If you don't need transactions, mvcc, triggers, strong type checking and high fiability go to mysql, but do not base your application only in the connection time.
Second, using persistent connections is dangerous, because reusing the same connection can lead to uncommited transactions, shared session vars and states, and other potential problems, so it's better to isolate the connections unless you're using different usernames to connect to the database.
my 0.02 cents
Try FirebirdSQL.
Nice post as for me. It would be great to read something more concerning that topic. The only thing I would like to see here is a few pics of any devices.
David Stepman
Phone jammers
Post a Comment