[pgpool-hackers: 105] Re: pgpool does not accept connections after postgres hits the max_connections limit
Tatsuo Ishii
ishii at postgresql.org
Wed Aug 1 07:49:38 JST 2012
Did you follow the guide in the doc?
In summary, max_pool, num_init_children, max_connections,
superuser_reserved_connections must satisfy the following formula:
max_pool*num_init_children <= (max_connections - superuser_reserved_connections) (no query canceling needed)
max_pool*num_init_children*2 >= (max_connections - superuser_reserved_connections) (query canceling needed)
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
> Hi,
>
> As per the docs:
>
> - *Limiting Exceeding Connections*
>
> There is a limit on the maximum number of concurrent connections with
> PostgreSQL, and connections are rejected after this many connections.
> Setting the maximum number of connections, however, increases resource
> consumption and affect system performance. pgpool-II also has a limit on
> the maximum number of connections, but *extra connections will be queued
> instead of returning an error immediately.*
>
> So one would expect the application to wait indefinitely if the connection
> parameter pair (user, database) that it is requesting is not yet available,
> but I see that the application connections are terminated immediately.
> Here's how I tested this on version 3.1.3 and master branch ( 3.2.0 RC3,
> commit id: 1c584a84).
> .) Start a single Postgres database instance (version PostgreSQL 9.1.4)
> .) Create 2 databases : test and test2
> .) Start pgpool as follows: (config files attached)
>
> inst/bin/pgpool -f ~/Downloads/pgpool.conf -F ~/Downloads/pcp.conf -a
> ~/Downloads/pool_hba.conf
>
> .) In one terminal, run psql clients, one at a time, to connect to 'test'
> database, over pgpool:
>
> export PGHOST=localhost PGPORT=9999 PGDATABASE=test PGUSER=postgres
> while sleep 1; do psql -c "select datname, now()::timestamp(0), count(*)
> from pg_stat_activity group by datname"; done
>
> .) In other terminal, run psql clients, one at a time,, to connect to
> 'test2' database over pgpool:
>
> export PGHOST=localhost PGPORT=9999 PGDATABASE=test2 PGUSER=postgres
> while sleep 1; do psql -c "select datname, now()::timestamp(0), count(*)
> from pg_stat_activity group by datname"; done
>
> After a few seconds, in the second terminal, when the total connections on
> Postgres server have all been exhausted, I see the psql connections failing:
>
>
> datname | now | count
> ---------+---------------------+-------
> test | 2012-07-31 11:17:49 | 75
> test2 | 2012-07-31 11:17:49 | 25
> (2 rows)
>
> psql: psql: psql: psql: psql: psql: psql: psql: psql: psql: psql: psql:
> psql: psql: psql: psql: psql: psql: psql: psql: ^C
> gurjeet at work:~/dev/POSTGRES T111809 (pg_9.1_stable)$
>
> Given that pgpool advertises that 'extra connections will be queued instead
> of returning an error immediately", I think we should treat this as a bug,
> unless there's something wrong with my configuration.
>
> Best regards,
> --
> Gurjeet Singh
> EnterpriseDB Corporation
> The Enterprise PostgreSQL Company
More information about the pgpool-hackers
mailing list