[php-maint] Bug#630569: Bug#630569: php5-pgsql and postgresql-9.0 seem to collude never to close idle persistent connections
Ondřej Surý
ondrej at sury.org
Wed Jun 15 10:32:35 UTC 2011
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 <joy at 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-maint at lists.alioth.debian.org
> http://lists.alioth.debian.org/cgi-bin/mailman/listinfo/pkg-php-maint
More information about the pkg-php-maint
mailing list