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...@gmail.com> wrote: > On Fri, Oct 11, 2013 at 10:08 AM, akp geek <akpg...@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 > > http://www.linkedin.com/in/grayhemp > +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 > gray...@gmail.com >
