Re: [TIPS] Tuning PostgreSQL 9.2

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?