Hi Josip, My first question would be if you can repeat same behaviour under unstable (and/or wheezy).
Ondřej Surý On 15.6.2011, at 12:01, Josip Rodin <j...@debbugs.entuzijast.net> wrote: > Package: php5-pgsql, postgresql-9.0 > Version: 5.3.3-7+squeeze1, 9.0.4-1~bpo60+1 > Severity: serious > > Hi, > > After the upgrade of one of our web servers to squeeze, coupled with > PostgreSQL 9 from squeeze-backports, the new pg_pconnect()-caused > connections seem to get created and left idling forever. When their number > eventually reaches PostgreSQL's max_connections limit, everything breaks. > > With the lenny PostgreSQL 8.3, the squeeze php5-pgsql seems to be able to > spool normally, foregoing excess connections over time. But not this one. > > (Obviously I brought this upon myself for trying a new non-Debian-stable > database version, but the new database version is in wheezy so it matters > for the next stable anyway.) > > The calling code at this end is literally unchanged for years, it's > basically a pg_pconnect() call to a set of two hostnames that both resolve > into 127.0.0.1 via /etc/hosts. All PHP children connect to a variety of > local databases using the same pgsql user (created as a normal, > non-privileged user with createuser). > > When I do: > > % for i in $(seq 1 12); do wget -q -O /dev/null > http://db1-connecting-website; done > > The near-immediate result is: > > % ps axfw | grep postgres | grep db1 > 18231 ? Ss 0:00 \_ postgres: web db1 127.0.0.1(55411) idle > 18255 ? Ss 0:00 \_ postgres: web db1 127.0.0.1(55416) idle > 18260 ? Ss 0:00 \_ postgres: web db1 127.0.0.1(55420) idle > 18263 ? Ss 0:00 \_ postgres: web db1 127.0.0.1(55424) idle > 18265 ? Ss 0:00 \_ postgres: web db1 127.0.0.1(55427) idle > 18271 ? Ss 0:00 \_ postgres: web db1 127.0.0.1(55432) idle > 18273 ? Ss 0:00 \_ postgres: web db1 127.0.0.1(55435) idle > 18282 ? Ss 0:00 \_ postgres: web db1 127.0.0.1(55439) idle > 18291 ? Ss 0:00 \_ postgres: web db1 127.0.0.1(55443) idle > 18317 ? Ss 0:00 \_ postgres: web db1 127.0.0.1(55446) idle > 18393 ? Ss 0:00 \_ postgres: web db1 127.0.0.1(55451) idle > 18396 ? Ss 0:00 \_ postgres: web db1 127.0.0.1(55455) idle > > And N minutes later, the output is identical - they just linger on. > > (Database names have been normalized to protect the innocent.) > > I tried setting: > > statement_timeout = 7000 > > in postgresql.conf, but it had no effect, the children kept piling on... > > 8059 ? Ss 0:00 \_ postgres: web db1 127.0.0.1(48255) idle > 8106 ? Ss 0:00 \_ postgres: web db2 127.0.0.1(48260) idle > 8119 ? Ss 0:00 \_ postgres: web db3 127.0.0.1(48262) idle > 8124 ? Ss 0:00 \_ postgres: web db3 127.0.0.1(48265) idle > 8126 ? Ss 0:00 \_ postgres: web db3 127.0.0.1(48267) idle > 8341 ? Ss 0:00 \_ postgres: web db1 127.0.0.1(57637) idle > 8357 ? Ss 0:00 \_ postgres: web db4 127.0.0.1(57640) idle > 8362 ? Ss 0:00 \_ postgres: web db5 127.0.0.1(57642) idle > > When I check how it looks from PostgreSQL's end: > > % sudo -u postgres psql template1 -c 'select * from pg_stat_activity;' > datid | datname | procpid | usesysid | usename | application_name | > client_addr | client_port | backend_start | > xact_start | query_start | waiting | > current_query > --------+-----------+---------+----------+----------+------------------+-------------+-------------+-------------------------------+-------------------------------+-------------------------------+---------+--------------------------------- > 205356 | db1 | 8863 | 16385 | web | | > 127.0.0.1 | 57764 | 2011-06-15 11:36:58.126871+02 | > | 2011-06-15 11:41:47.158675+02 | f | <IDLE> > 170288 | db2 | 8867 | 16385 | web | | > 127.0.0.1 | 57766 | 2011-06-15 11:36:58.227026+02 | > | 2011-06-15 11:36:58.389238+02 | f | <IDLE> > 170288 | db2 | 8872 | 16385 | web | | > 127.0.0.1 | 57769 | 2011-06-15 11:36:58.409101+02 | > | 2011-06-15 11:36:58.468774+02 | f | <IDLE> > 170288 | db2 | 8876 | 16385 | web | | > 127.0.0.1 | 57771 | 2011-06-15 11:36:58.491472+02 | > | 2011-06-15 11:36:58.549619+02 | f | <IDLE> > 157541 | db3 | 8888 | 16385 | web | | > 127.0.0.1 | 57772 | 2011-06-15 11:37:00.809331+02 | > | 2011-06-15 11:37:00.989411+02 | f | <IDLE> > 122630 | db4 | 9016 | 16385 | web | | > 127.0.0.1 | 57777 | 2011-06-15 11:37:04.802028+02 | > | 2011-06-15 11:37:05.050344+02 | f | <IDLE> > 106735 | db5 | 9018 | 16385 | web | | > 127.0.0.1 | 57779 | 2011-06-15 11:37:05.08563+02 | > | 2011-06-15 11:38:48.369282+02 | f | <IDLE> > 122630 | db4 | 9019 | 16385 | web | | > 127.0.0.1 | 57780 | 2011-06-15 11:37:05.096214+02 | > | 2011-06-15 11:37:05.30666+02 | f | <IDLE> > 106735 | db5 | 9020 | 16385 | web | | > 127.0.0.1 | 57784 | 2011-06-15 11:37:05.293885+02 | > | 2011-06-15 11:37:05.36558+02 | f | <IDLE> > 106735 | db5 | 9021 | 16385 | web | | > 127.0.0.1 | 57786 | 2011-06-15 11:37:05.414632+02 | > | 2011-06-15 11:37:05.488409+02 | f | <IDLE> > 202195 | db6 | 9041 | 16385 | web | | > 127.0.0.1 | 57788 | 2011-06-15 11:37:14.966001+02 | > | 2011-06-15 11:37:28.519769+02 | f | <IDLE> > 63812 | db7 | 9043 | 16385 | web | | > 127.0.0.1 | 57791 | 2011-06-15 11:37:15.276264+02 | > | 2011-06-15 11:41:50.756364+02 | f | <IDLE> > 240382 | db8 | 9044 | 16385 | web | | > 127.0.0.1 | 57792 | 2011-06-15 11:37:15.665498+02 | > | 2011-06-15 11:37:16.136575+02 | f | <IDLE> > 265733 | db9 | 9048 | 16385 | web | | > 127.0.0.1 | 57794 | 2011-06-15 11:37:18.065143+02 | > | 2011-06-15 11:37:18.597728+02 | f | <IDLE> > 265733 | db9 | 9050 | 16385 | web | | > 127.0.0.1 | 57796 | 2011-06-15 11:37:18.294857+02 | > | 2011-06-15 11:37:18.66658+02 | f | <IDLE> > 265733 | db9 | 9051 | 16385 | web | | > 127.0.0.1 | 57798 | 2011-06-15 11:37:18.401163+02 | > | 2011-06-15 11:37:21.890123+02 | f | <IDLE> > 265733 | db9 | 9052 | 16385 | web | | > 127.0.0.1 | 57801 | 2011-06-15 11:37:18.506822+02 | > | 2011-06-15 11:37:18.578728+02 | f | <IDLE> > 265733 | db9 | 9053 | 16385 | web | | > 127.0.0.1 | 57803 | 2011-06-15 11:37:18.560628+02 | > | 2011-06-15 11:37:18.650382+02 | f | <IDLE> > 265733 | db9 | 9054 | 16385 | web | | > 127.0.0.1 | 57805 | 2011-06-15 11:37:18.610887+02 | > | 2011-06-15 11:37:48.685439+02 | f | <IDLE> > 265733 | db9 | 9055 | 16385 | web | | > 127.0.0.1 | 57807 | 2011-06-15 11:37:18.674126+02 | > | 2011-06-15 11:38:16.490799+02 | f | <IDLE> > 265733 | db9 | 9062 | 16385 | web | | > 127.0.0.1 | 57808 | 2011-06-15 11:37:21.72951+02 | > | 2011-06-15 11:37:49.116284+02 | f | <IDLE> > 122630 | db4 | 9067 | 16385 | web | | > 127.0.0.1 | 57809 | 2011-06-15 11:37:25.26675+02 | > | 2011-06-15 11:37:25.415022+02 | f | <IDLE> > 202195 | db6 | 9069 | 16385 | web | | > 127.0.0.1 | 57811 | 2011-06-15 11:37:25.721735+02 | > | 2011-06-15 11:41:16.084124+02 | f | <IDLE> > [...] > 202195 | db6 | 28584 | 16385 | web | | > 127.0.0.1 | 35286 | 2011-06-15 11:41:50.39207+02 | > | 2011-06-15 11:41:50.605894+02 | f | <IDLE> > 202195 | db6 | 28583 | 16385 | web | | > 127.0.0.1 | 35283 | 2011-06-15 11:41:50.260415+02 | > | 2011-06-15 11:41:50.448556+02 | f | <IDLE> > 202195 | db6 | 28585 | 16385 | web | | > 127.0.0.1 | 35289 | 2011-06-15 11:41:50.554389+02 | > | 2011-06-15 11:41:50.771443+02 | f | <IDLE> > 163920 | db10 | 28588 | 16385 | web | | > 127.0.0.1 | 35293 | 2011-06-15 11:41:52.466221+02 | > | 2011-06-15 11:41:52.600124+02 | f | <IDLE> > 1 | template1 | 28593 | 10 | postgres | psql | > | -1 | 2011-06-15 11:41:53.724276+02 | 2011-06-15 > 11:41:53.729887+02 | 2011-06-15 11:41:53.729887+02 | f | select * from > pg_stat_activity; > (204 rows) > > So the normal queries subsequent to those connections all worked fine, > and then the connections just went into idle mode and never left it. > I know I said I want persistent connections, but not *this* persistent :) > > I tried setting: > > php_admin_value pgsql.max_persistent 3 > > in Apache config, and it did come into effect, but it just caused a bunch of > processes to explicitly fail: > > [15-Jun-2011 11:34:20] PHP Warning: pg_pconnect(): Cannot create new link. > Too many open persistent links (3) in /srv/app/dbinit.php on line 20 > > So I'm at a loss what to do to avoid this DoS, right now I'm resorting to > the silly route: > > % ps axfw | grep postgres -c > 383 > % sudo apache2ctl graceful > % ps axfw | grep postgres -c > 28 > > Until next time - in a bit... > > Please help! TIA. > > -- > 2. That which causes joy or happiness. > > > > _______________________________________________ > pkg-php-maint mailing list > pkg-php-ma...@lists.alioth.debian.org > http://lists.alioth.debian.org/cgi-bin/mailman/listinfo/pkg-php-maint -- To UNSUBSCRIBE, email to debian-bugs-dist-requ...@lists.debian.org with a subject of "unsubscribe". Trouble? Contact listmas...@lists.debian.org