Re: postgres 9.0.4 configuration and performance issue

On Fri, Oct 11, 2013 at 10:08 AM, akp geek <akpgeek(at)gmail(dot)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(dot)ru(at)gmail(dot)com