Re: [MASSMAIL]Re: [TIPS] Tuning PostgreSQL 9.2

Hello

Use pgtuning in you server.

> On Wed, Feb 17, 2016 at 6:03 PM, drum(dot)lucas(at)gmail(dot)com
> <drum(dot)lucas(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

--
Saludos,
Gilberto Castillo
ETECSA, La Habana, Cuba