Message-ID: <47625.192.168.207.54.1455819001.squirrel@webmail.etecsa.cu>
In-Reply-To: 
     <CAOR=d=067_jGrkSQvxjx5YpJOaRLi+zhOX2EbdvKzUw75R3Xfw@mail.gmail.com>
References: 
    <CAE_gQfXcw=qOTSWiTsuJR3Z3ujM2kPfGTEjgDQcmgODnH4B1Zw@mail.gmail.com>
    <CAOR=d=067_jGrkSQvxjx5YpJOaRLi+zhOX2EbdvKzUw75R3Xfw@mail.gmail.com>
Date: Thu, 18 Feb 2016 13:10:01 -0500 (GMT+5)
Subject: Re: [MASSMAIL]Re: [TIPS] Tuning PostgreSQL 9.2
From: "Gilberto Castillo" <gilberto.castillo@etecsa.cu>
To: "Scott Marlowe" <scott.marlowe@gmail.com>
Cc: "drum.lucas@gmail.com" <drum.lucas@gmail.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


Hello

Use pgtuning in you server.


> On Wed, Feb 17, 2016 at 6:03 PM, drum.lucas@gmail.com
> <drum.lucas@gmail.com> wrote:
>> Hi all,
>>
>> Trying to improve the performance, it would be great hear from you some
>> tips
>> to it...
>>
>> My current scenario is:
>> 128 GB Ram - Raid 10 - PostgreSQL 9.2 in a Centos 6.6 64 Bits
>>
>> How could I measure a nice parameter to it?
>> Can you guys tell your experience and how did you get nice results with
>> your
>> changes?
>>
>> But biggest problem nowadays are slow queries and I/O (In some spikes I
>> get
>> 100% I/O usage)
>>
>> Thank you!
>>
>> Current confs:
>> 1 - Newrelic
>> 2 - pg_stat_statements = on
>> 3 - log_min_duration_statement = 1000
>> 4 - log_statement = 'ddl'
>> 5 - Munin
>> 6 - Vaccum:
>>>
>>> vacuum_cost_delay = 20ms
>>> vacuum_cost_page_hit = 1
>>> vacuum_cost_page_miss = 10
>>> vacuum_cost_page_dirty = 20
>>> vacuum_cost_limit = 100
>>> autovacuum = on
>>> log_autovacuum_min_duration = 30000
>>> autovacuum_max_workers = 2
>>> autovacuum_naptime = 1min
>>> autovacuum_vacuum_threshold = 500
>>> autovacuum_analyze_threshold = 500
>>> autovacuum_vacuum_scale_factor = 0.1
>>> autovacuum_analyze_scale_factor = 0.1
>>> autovacuum_freeze_max_age = 200000000
>>> autovacuum_vacuum_cost_delay = -1
>>> autovacuum_vacuum_cost_limit = -1
>>> vacuum_freeze_min_age = 50000000
>>> vacuum_freeze_table_age = 150000000
>>
>>
>> shared_buffers = 51605MB
>> work_mem = 32MB
>> maintenance_work_mem = 128 MB
>> effective_cache_size = 96760MB
>
> There are a lot of things you can do to improve performance, but we
> don't know your usage patterns or underlying IO subsystem. What does
> your IO sybsystem look like? How fast can you get something like
> pgbench to go on this machine?
>
> Might I ask where the idea for shared_buffers being 51GB came from?
> Generally speaking shared_buffers don't work well that big, except in
> some very specific circumstances maybe.
>
> So when you say IO is 100% utilized, is that being used by sorts, the
> background writer, reads?
>
> How many active and idle connections do you typically have on this
> machine? If you have a lot of connections have you considered pooling?
>
> What are max_connections, effective_io_concurrency, ramdom_page_cost,
> wal_writer_delay, commit_delay, commit_siblings, checkpoint_segments,
> temp_buffers, set to?
>
> Turn on things like log_temp_files, log_checkpoints.
>
> Also got a slow query and an explain analyze output?
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>


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