Re: PostgreSQL 8.4.8 bringing my website down every evening

2011/6/19 Alexander Farber <alexander(dot)farber(at)gmail(dot)com>:
> 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?

Ooops, I would have been more explicit here!
You need to add begin/commit to build a transaction.
>From PHP::PDO doc:
http://www.php.net/manual/en/pdo.begintransaction.php
<?
/* Begin a transaction, turning off autocommit */
$dbh->beginTransaction();

/* Change the database schema */
$sth = $dbh->exec("DROP TABLE fruit");

/* Commit the changes */
$dbh->commit();

/* Database connection is now back in autocommit mode */
?>

An alternative can be to use pre_prepare:
https://github.com/dimitri/preprepare

Please read the README carefully for this one if you intend to use it.

>
> 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)
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support