> On Fri, Jul 22, 2016 at 1:57 AM, Gupta, Amar Nath <angupta@xxxxxxxxxx> > wrote: >> Many thanks for your reply Scott, >> >> Point#1: we have two DB servers (web app and reporting database). Web >> app does simple read and insert query + some complex queries. And >> reporting DB is used for heave queries >> Point#2: will do in next update ;) >> Point#3: I'll check for that. >> Point#4: Agreed, opening 2000 connection is not a good idea. Somehow my >> application is setup in this way I cannot use pgpool, but working on it >> in long term. >> Point#5: I have setup 32GB for that. >> >> Server current configuration is: >> max_connections = 2000 >> shared_buffers = 32GB >> work_mem = 128MB >> synchronous_commit = off >> effective_cache_size = 192GB >> >> rest settings are on default > > Which of your two dbs has 2,000 connections and needs tuning, the > reporting db or the web db? > > Two points: > > 1: Rarely if ever is 32GB needed for shared_buffers. There's a cost to > have more shared_buffers and it's pretty rare that anything over 1 or > 2GB is needed. If you have a performance test showing you need more > than a few G then yes, go for it. > > 2: You've got to get max_connections down to something reasonable. > Look at pgbouncer. It's super easy to setup and will allow you to drop > the # connections to 100 or so even for busy dbs with lots of users > etc. > > Maybe may use pg_tunning in you server, following your recomendation. Other Case, Use pgbounce for those conection. -- Saludos, Gilberto Castillo ETECSA, La Habana, Cuba -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
