Message-ID: <60070.192.168.207.54.1469209288.squirrel@webmail.etecsa.cu>
In-Reply-To: 
     <CAOR=d=2X=kvuaGU1NoA5qdsd+0nQvQoLjVx=buCthWce=ESLjA@mail.gmail.com>
References: 
    <D8FBDBAB4D0ECF4FB63140011F7D21A201ADB495B2@CRUSEXMBX02.corbus.com>
    <CAOR=d=0dOcPia5HVuiZNGf0SE4rpYDJ0WNR-1A1Zyf07JoTbog@mail.gmail.com>
    <D8FBDBAB4D0ECF4FB63140011F7D21A201ADB4A37C@CRUSEXMBX02.corbus.com>
    <CAOR=d=2X=kvuaGU1NoA5qdsd+0nQvQoLjVx=buCthWce=ESLjA@mail.gmail.com>
Date: Fri, 22 Jul 2016 12:41:28 -0500 (GMT+5)
Subject: Re: [MASSMAIL]Re: High-end PG database configuration help
From: "Gilberto Castillo" <gilberto.castillo@etecsa.cu>
To: "Scott Marlowe" <scott.marlowe@gmail.com>
Cc: "Gupta, Amar Nath" <angupta@corbus.com>,
 "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Reply-To: gilberto.castillo@etecsa.cu
User-Agent: SquirrelMail/1.4.8-5.el5_4.10
MIME-Version: 1.0
Content-Type: text/plain;charset=utf-8
Content-Transfer-Encoding: 8bit
Importance: Normal
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org


> On Fri, Jul 22, 2016 at 1:57 AM, Gupta, Amar Nath <angupta@corbus.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.
>
>
Maybe may use pg_tunning in you server, following your recomendation.

Other Case, Use pgbounce for those conection.
-- 
Saludos,
Gilberto Castillo
ETECSA, La Habana, Cuba


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin