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