Re: PostgreSQL 8.4.8 bringing my website down every evening

Hello everyone,

after the suggestion from this mailing list,
I have installed pgbouncer at my
CentOS 5.6 / 64 bit server and
activated its transaction mode:

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

[pgbouncer]
logfile = /var/log/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
;listen_addr = 127.0.0.1
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 = 20

log_connections = 0
log_disconnections = 0
log_pooler_errors = 1

Now the server stopped crashing even
at peak times and "pg_top -I" only shows
few simultaneous commands active:

last pid: 13476; load avg: 4.03, 4.02, 4.29; up 2+22:57:32
19:37:05
16 processes: 3 running, 13 sleeping
CPU states: 67.8% user, 0.0% nice, 0.7% system, 27.0% idle, 4.5% iowait
Memory: 3363M used, 561M free, 374M buffers, 2377M cached
Swap: 7812M free

PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND
13018 postgres 20 0 1173M 179M run 4:08 11.39% 99.99%
postgres: pref pref [local] SELECT
13144 postgres 18 0 1172M 179M run 3:38 6.11% 84.15%
postgres: pref pref [local] SELECT
1636 postgres 16 0 1170M 152M run 186:34 4.67% 20.79%
postgres: pref pref [local] SELECT
12761 postgres 16 0 1173M 180M sleep 3:16 20.22% 5.94%
postgres: pref pref [local] idle

And in the /var/log/pgbouncer.log shows:

2011-06-19 19:28:05.772 3031 LOG Stats: 29 req/s, in 4087 b/s, out
116615 b/s,query 106024 us
2011-06-19 19:29:05.772 3031 LOG Stats: 29 req/s, in 3665 b/s, out
39510 b/s,query 71303 us
2011-06-19 19:30:05.772 3031 LOG Stats: 32 req/s, in 4318 b/s, out
90909 b/s,query 115946 us
2011-06-19 19:31:05.773 3031 LOG Stats: 33 req/s, in 4686 b/s, out
79397 b/s,query 84436 us
2011-06-19 19:32:05.774 3031 LOG Stats: 31 req/s, in 4478 b/s, out
108103 b/s,query 104060 us

But I have a new (not so bad problem) -

My php script displaying player stats:
http://preferans.de/user.php?id=OK493430777441
will sometimes exit with the PDO error:

SQLSTATE[26000]: Invalid sql statement name:
7 ERROR: prepared statement
"pdo_stmt_00000016" does not exist

When I reload it, it works ok.

The SQL statements called by the script are:

try {
# enable persistent connections and throw exception on any errors
$options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_PERSISTENT => true);
$db = new PDO('pgsql:host=' . DBHOST . ' port=6432; dbname=' .
DBNAME, DBUSER, DBPASS, $options);

$sth = $db->prepare('
select first_name, city, avatar, login > logout as online
from pref_users where id=?
');
$sth->execute(array($id));

and so on - a dozen SELECT statements.

I wonder, if this "prepared statement not found"
problem is caused by transaction mode of pgbouncer
and if there is a way to workaround that?

And I can't switch to pgbouncer session mode,
because it will hang at peak time - tried that already.

Thank you
Alex

P.S. Here again my specs:

pgbouncer-1.3.4-1.rhel5
postgresql-libs-8.4.8-1PGDG.rhel5
compat-postgresql-libs-4-1PGDG.rhel5
postgresql-8.4.8-1PGDG.rhel5
postgresql-server-8.4.8-1PGDG.rhel5
postgresql-devel-8.4.8-1PGDG.rhel5
php53-pgsql-5.3.3-1.el5_6.1
php53-pdo-5.3.3-1.el5_6.1
php53-5.3.3-1.el5_6.1

I have 4GB RAM and postgresql.conf contains:
max_connections = 50
shared_buffers = 1024MB
#listen_addresses = 'localhost' (i.e. unix socket only)