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. -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
