thanks for the advice. One question I have is if I increase the shared_buffers to 16GB, then it won't restart because for the 32 bit version of postgres , we can't have shared buffers more than 3.2 GB right ? ( this from various blogs that I have read )
Thanks again for helping me out
On Fri, Oct 11, 2013 at 7:03 PM, Sergey Konoplev <gray.ru@gmail.com> wrote:
On Fri, Oct 11, 2013 at 10:08 AM, akp geek <akpgeek@gmail.com> 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