2011/6/19 Alexander Farber <alexander.farber@xxxxxxxxx>: > 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@xxxxxxxxxxxxxx) > 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
