From: Gilberto Castillo <gilberto.castillo@etecsa.cu>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Gupta, Amar Nath <angupta@corbus.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: [MASSMAIL]Re: High-end PG database configuration help
Date: Fri, 22 Jul 2016 12:41:28 -0500 (GMT+5)
Message-ID: <60070.192.168.207.54.1469209288.squirrel@webmail.etecsa.cu> (raw)
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: gilberto.castillo@etecsa.cu, scott.marlowe@gmail.com, angupta@corbus.com
Subject: Re: [MASSMAIL]Re: High-end PG database configuration help
In-Reply-To: <60070.192.168.207.54.1469209288.squirrel@webmail.etecsa.cu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox