On Fri, Oct 11, 2013 at 10:08 AM, akp geek <akpgeek@xxxxxxxxx> wrote: > We have 4 applications ( 2 transactional , 2 ( transactional + reporting )) > on postgres 32 bit 9.0.4 > > Some of the queries are extreemly taking time ( 10 seconds). I can the > explain for that also. > > I just want to get your thoughts on the conf file values we have are good. > Really appreciate your help. Start with the changes below. Later tuning will depend on further observations. I also recommend you to install pgbouncer, and configure it as transaction polling if you don't use prepared statements or as statement pooling if you don't use transactions either. I also suggest you to to perform VACUUM FULL or use https://github.com/reorg/pg_repack or https://github.com/grayhemp/pgtoolkit because your autovacuum was configured inappropriately and you might have a lot of bloat in your database. shared_buffers = 16GB temp_buffers = 16MB work_mem = 128MB maintenance_work_mem = 512GB vacuum_cost_delay = 5ms bgwriter_delay = 10ms bgwriter_lru_maxpages = 500 bgwriter_lru_multiplier = 5.0 effective_io_concurrency = 4 # put here a number of disks in your RAID checkpoint_segments = 128 checkpoint_timeout = 1h checkpoint_completion_target = 0.9 checkpoint_warning = 10min wal_keep_segments = 256 seq_page_cost = 1.0 random_page_cost = 2.0 # put 1.0 if you have SSD , 2.0 in other cases effective_cache_size = 56GB track_activity_query_size = 4096 log_autovacuum_min_duration = 1000 autovacuum_max_workers = 5 autovacuum_naptime = 5s autovacuum_vacuum_scale_factor = 0.05 autovacuum_analyze_scale_factor = 0.05 autovacuum_vacuum_cost_delay = 5ms -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray.ru@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
