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. -- To UNSUBSCRIBE, email to debian-bugs-rc-requ...@lists.debian.org with a subject of "unsubscribe". Trouble? Contact listmas...@lists.debian.org
