Is this pgbouncer configuration suitable for a production environment with hundreds of databases?
Hi there!
I'm using pgbouncer in front of a PostgreSQL 9.3 instance.
I have hundreds of databases (almost 200 and counting).
The clients connect to pgbouncer always with the same user (there is one
only user).
Currently I have *postgresql max_connections=200*.
My pgbouncer configuration is this (I put only the directives regarding my
question):
[pgbouncer]
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 2
min_pool_size = 0
reserve_pool_size = 2
reserve_pool_timeout = 5
max_db_connections = 5
server_idle_timeout = 30
[databases]
db1 = host=localhost port=6543 dbname=db1
db2...
db3...
Notice that I don't set a pool_size for every database, but instead I use
the general default_pool_size.
Our project is growing, and we are constantly adding new databases, so it's
a matter of time that we reach the number of 300 or 400 databases. Most of
the databases have low activity (websites with very few traffic).
So, considering that we have set postgresql max_connections=200, then
(correct me if I'm wrong) we would be reaching the postgresql limit of
max_connections soon.
The question is: can I set default_pool_size=0 to avoid reaching the
postgres limit? Is that suitable?
*My goal is to find a pgbouncer configuration that allows me to add
databases without worring about reaching the postgresql max_connection=200
limit. How can I achieve that?*
Thanks in advance!
Regards,
Lisandro.
--
View this message in context: http://www.postgresql-archive.org/Is-this-pgbouncer-configuration-suitable-for-a-production-environment-with-hundreds-of-databases-tp5954479.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Apr 5, 2017 at 3:29 PM, Lisandro <rostagnolisandro@gmail.com> wrote:
The question is: can I set default_pool_size=0
ISTM parameter would be better named (i.e., mentally remembered as)
"default_maximum_pool_size" ... and a zero for that seems like you'll
render your system inoperable since:
"Notice that I don't set a pool_size for every database, but instead I use
the general default_pool_size.
*My goal is to find a pgbouncer configuration that allows me to add
databases without worring about reaching the postgresql max_connection=200
limit. How can I achieve that?*
The presence of open issue # 103 on GitHub implies that what you want to do
is not possible.
https://github.com/pgbouncer/pgbouncer/issues/103
David J.
Thank you very much David for your quick reply, I understand better now.
For now, I'll let default_pool_size=2 and I guess I will have to monitor the
total number of databases and adjust configuration when needed, in order to
avoid reaching the postgres max_connection limit.
I think I can play a bit with some pgbouncer configuration directives, like
pool_size (per database), server_idle_timeout, server_connect_timeout and
idle_transaction_timeout.
Thanks for the help!
Regards,
Lisandro.
--
View this message in context: http://www.postgresql-archive.org/Is-this-pgbouncer-configuration-suitable-for-a-production-environment-with-hundreds-of-databases-tp5954479p5954499.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general