Re: High-end PG database configuration help

On Wed, Jul 20, 2016 at 4:45 AM, Gupta, Amar Nath <angupta(at)corbus(dot)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 older 2.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 about Centos 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 80 core 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 it comes 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 keep it on the list so
others can benefit)