Re: [TIPS] Tuning PostgreSQL 9.2

Echoing (other) Scott's comments, specifically, when he says "what does your IO subsystem looks like," what's the hardware? RAID 10 is all well and good, but how many drives, what type are they, and how is your RAID-10 configured? Specific model numbers would be useful.

________________________________________
From: pgsql-admin-owner(at)postgresql(dot)org <pgsql-admin-owner(at)postgresql(dot)org> on behalf of Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Sent: Thursday, February 18, 2016 11:52 AM
To: drum(dot)lucas(at)gmail(dot)com
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] [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?

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

Journyx, Inc.
7600 Burnet Road #300
Austin, TX 78757
www.journyx.com

p 512.834.8888
f 512-834-8858

Do you receive our promotional emails? You can subscribe or unsubscribe to those emails at http://go.journyx.com/emailPreference/e/4932/714/