1

Environment: PostgreSQL 9.2 on separate server, PHP 5.3, Zend Framework 1

I execute on few servers every minute hundreds PHP scripts (launched from shell, not as WWW server request) which use in Zend Framework pdo_pgsql connection to PostgreSQL. Average script execution time is about 15s, most of the time they don't use database. Right now each script opens database connection at the beginning and closes it at the end. It is inefficient so I decided that script will close and reopen db connection few times during execution.

But reopening connection to database in also inefficient. PHP persistent connection is impossible to use because of my PHP scripts run as separate processes (not as WWW server worker). In my opinion the best solution will be using connection pooling system on each server, which will store connections to PostgreSQL for PHP scripts.

Am I right? If yes, which connection pooling system do you recommend to use with PHP, Zend Framework (pdo_pgsql adapter) and PostgreSQL?

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
Pajak Leon
  • 341
  • 4
  • 15
  • 1
    Have you tried using the internet? --> http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling – James Butler Mar 22 '13 at 10:43
  • 2
    I have experience with PgBouncer and Pgpool. As I used them for connection pooling only my preference goes to PgBouncer which is simpler. In my experience Pgpool is harder to setup and is somewhat buggy while PgBouncer is just setup and forget. It is client agnostic. It does not know if the client is a PHP script or whatever. It will serve connections regardless. – Clodoaldo Neto Mar 22 '13 at 11:36
  • Despite agreeing with James's comment, +1 thanks for including your Pg version and a clear description of your situation. Thankyou for making the effort to communicate clearly. – Craig Ringer Mar 22 '13 at 13:06

1 Answers1

3

PgBouncer is ideal for this job. It'll reduce connection overhead without adding other unnecessary complexity if used in session pooling mode (the default). The more aggressive pooling modes like transaction- or statement-pooling do affect the client application so you should only use them if you actually need them.

More information can be found on the PostgreSQL wiki as James points out.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778