Re: High-end PG database configuration help - Mailing list pgsql-admin

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
complexqueries. 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

Regards,
Amar

-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: 21 July 2016 22:36
To: Gupta, Amar Nath <angupta@corbus.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] High-end PG database configuration help

On Wed, Jul 20, 2016 at 4:45 AM, Gupta, Amar Nath <angupta@corbus.com> wrote:
> Hello,
>
> I am working to configure high end PostgreSQL database, and need some help.
>
> Please can you suggest me best and optimal configuration for
> “postgresql.conf” I should use.
>
> DB server details:
>
> ·         PostgreSQL Version: 9.5.3
>
> ·         Disc: RAID 10 (1 TB)
>
> ·         DB Size: 250GB
>
> ·         RAM: 256GB
>
> ·         CPU(s): 48
>
> ·         Max Connection: 2000
>
> ·         OS: CentOS release 6.6 (Final)

A few quick pointers.

1: describe what your workload looks like.  How you configure a server can vary quite a bit depending on what it's
doing.
2: If that RAID-10 is spinning disks pull them, throw them in the trash, and buy some SSDs. A pair of 800GB Intels
costs$1000 total, and you don't even need the RAID controller with them to be fast.
 
3: Pick a distro that can run the 3.11 or higher kernels. IO is MUCH MUCH faster in the later model kernels than in the
older2.6.32 kernel Centos 6 uses. Ubuntu 12.04 can easily run a 3.11 or 3.13 kernel from the standard repos. Not sure
aboutCentos 6, but I'm pretty sure it takes more than a simple rpm command to get a later model kernel into it.
 
4: Look into connection pooling. NO database is gonna be fast if it has 2,000 connections all active at once. I've got
80core machines with 3 super fast 1TB SSD cards in them that would fall over under the load of 2,000 active
connections.
5: Don't go crazy on shared_buffers. A few gig is usually plenty, let the OS do the majority of the heavy lifting when
itcomes to caching data.
 

There's more to discuss, but we don't know what you're trying to do yet, so I'll wait to hear back from you (Please
keepit on the list so others can benefit)
 
Confidentiality Notice

This message is intended exclusively for the individual or entity to which it is addressed. This communication may
containinformation 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
anypart 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.