Re: High-end PG database configuration help

On Fri, Jul 22, 2016 at 1:57 AM, Gupta, Amar Nath <angupta(at)corbus(dot)com> 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.