Hello Use pgtuning in you server. > 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 > -- Saludos, Gilberto Castillo ETECSA, La Habana, Cuba -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
