[pgpool-hackers: 105] Re: pgpool does not accept connections after postgres hits the max_connections limit

[Tatsuo Ishii]

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