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