Обсуждение: Is this pgbouncer configuration suitable for a productionenvironment 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.
Re: Is this pgbouncer configuration suitable for aproduction environment with hundreds of databases?
От
"David G. Johnston"
Дата:
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.
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.