[TIPS] Tuning PostgreSQL 9.2

From: "drum(dot)lucas(at)gmail(dot)com" <drum(dot)lucas(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: [TIPS] Tuning PostgreSQL 9.2
Date: 2016-02-18 01:03:31
Message-ID: CAE_gQfXcw=qOTSWiTsuJR3Z3ujM2kPfGTEjgDQcmgODnH4B1Zw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Shreeyansh Dba 2016-02-18 01:28:03 Re: oracle db reads from postgresql
Previous Message Shreeyansh Dba 2016-02-18 00:27:29 Re: Extracting data into .csv format