1

I have a Drupal 7.2 + CentOS 5.6/64 bit + PHP 5.3 + PostgreSQL 8.4.8 (servicing unix domain sockets only) web site which often goes down on peak times because of too many postmaster processes started.

On the advice from PostgreSQL mailing list I have installed pgbouncer at "port" 6432 with the following config:

[databases]
pref = host=/tmp user=pref password=XXX dbname=pref

[pgbouncer]
logfile = /var/log/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_port = 6432
unix_socket_dir = /tmp
auth_type = md5
auth_file = /var/lib/pgsql/data/global/pg_auth
pool_mode = transaction
server_check_delay = 10
max_client_conn = 200
default_pool_size = 16
log_connections = 0
log_disconnections = 0
log_pooler_errors = 1

And forced Drupal 7.2 to use it by changing sites/default/settings.php to:

$databases = array (
  'default' =>
  array (
    'default' =>
    array (
      'database' => 'pref',
      'username' => 'pref',
      'password' => 'XXX',
      'host' => '/tmp',
      'port' => '6432',
      'driver' => 'pgsql',
      'prefix' => 'drupal_',
    ),
  ),
);

Here an excerpt from /var/log/pgbouncer.log during peak times:

2011-06-20 17:32:48.591 23883 LOG File descriptor limit: 1024 (H:1024), max_client_conn: 200, max fds possible: 230
2011-06-20 17:32:48.591 23883 LOG File descriptor limit: 1024 (H:1024), max_client_conn: 200, max fds possible: 230
2011-06-20 17:32:48.592 23886 LOG listening on unix:/tmp/.s.PGSQL.6432
2011-06-20 17:33:48.593 23886 LOG Stats: 36 req/s, in 3346 b/s, out 57238 b/s,query 74605 us
2011-06-20 17:34:48.593 23886 LOG Stats: 43 req/s, in 3167 b/s, out 54435 b/s,query 68969 us
2011-06-20 17:35:48.593 23886 LOG Stats: 25 req/s, in 3391 b/s, out 42060 b/s,query 95764 us
2011-06-20 17:36:48.593 23886 LOG Stats: 25 req/s, in 3370 b/s, out 103636 b/s,query 72986 us

Also I've been advised to disable persistent database connections and have done it for my own PHP scripts.

But I don't know how to do it for Drupal, what would be the best spot for that?

Thank you! Alex

2

Drupal does not use persistent connections. See https://drupal.org/node/1079892. You may need to lower the number of web server processes (e.g. Apache MaxClients).

  • Thank you and I've also put pgsql.allow_persistent = Off into /etc/php.ini just to be sure – Alexander Farber Jun 29 '11 at 16:00
1

Persistent connections are not be the only issue for pgBouncer transactional mode. You need to make sure that Drupal doesn't use SET/RESET, WITH HOLD CURSOR, etc.

Actually, I see that Drupal issues SET NAMES 'UTF8', but maybe this is superfluous (so it doesn't break pooling).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.