Re: [MASSMAIL]Re: High-end PG database configuration help

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



> On Fri, Jul 22, 2016 at 1:57 AM, Gupta, Amar Nath <angupta@xxxxxxxxxx>
> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin



[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux