[php-maint] Bug#630569: php5-pgsql and postgresql-9.0 seem to collude never to close idle persistent connections
Josip Rodin
joy at debbugs.entuzijast.net
Wed Jun 15 10:01:32 UTC 2011
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.
More information about the pkg-php-maint
mailing list