Thanks Scott, Which of your two dbs has 2,000 connections and needs tuning, the reporting db or the web db? Amar>> Web DB has 2000 connection. But would be great if you can also suggest tuning for reporting DB. 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. Amar>> reduced it to 16GB. Honestly speaking I haven’t noticed any difference in DB performance since I reduced memory from 64G to 32GB to 16GB. 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. Amar>> will look into this. Regards, Amar -----Original Message----- From: Scott Marlowe [mailto:scott.marlowe@xxxxxxxxx] Sent: 22 July 2016 17:13 To: Gupta, Amar Nath <angupta@xxxxxxxxxx> Cc: pgsql-admin@xxxxxxxxxxxxxx Subject: Re: High-end PG database configuration help 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. Confidentiality Notice This message is intended exclusively for the individual or entity to which it is addressed. This communication may contain information that is proprietary, privileged, confidential or otherwise legally exempt from disclosure. If you are not the named addressee, you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If you have received this message in error, please notify the sender immediately either by phone (937-226-7724) or reply to this e-mail and delete all copies of this message. -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
