Thread: postgres 9.0.4 configuration and performance issue

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

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com

Hello

Why? There are no multilevels structures in pg. Variables should be joined
with schemas or extensions. Other levels are messy.




-----
"M Better"

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/postgres-9-0-4-configuration-and-performance-issue-tp5774309p5774369.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 10/13/2013 8:35 AM, akp geek wrote:
> 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 )


um, on a 32 bit system, no application can have over ~ 2-3GB of user
space, with the top 1-2GB taken by the kernel (this varies with
operating system).    out of that, I wouldn't use more than about 1GB of
shared_buffers.



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On Sun, Oct 13, 2013 at 8:35 AM, akp geek <akpgeek@gmail.com> wrote:
> 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 )

Hm.. looks like I missed this fact. Is it possible to install the 64bit one?

--
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@gmail.com

On 10/13/2013 1:45 PM, Sergey Konoplev wrote:
> On Sun, Oct 13, 2013 at 8:35 AM, akp geek<akpgeek@gmail.com>  wrote:
>> >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 )
> Hm.. looks like I missed this fact. Is it possible to install the 64bit one?

indeed, I note the original post also states he has 64GB ram.  Its crazy
to run a 32bit kernel even with PAE with that large of a physical
memory.   the PAE page tables all have to fit in 1GB kernel address
space, and 32bit style PAE page tables sufficient to utilize 64gb
physical memory will about kill you.   Even without the PAE page table
size issue, no process can see more than 3GB of this memory, making it
quite hard to fully utilize the system.

updating this system to a 64bit kernel and 64bit postgres will result in
much higher performance overall.   if the OP is in fact already running
a 64bit kernel, he should upgrade postgres to 64bit.  note, this will
require a dump/initdb/restore as they aren't binary compatible.





--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On Sun, Oct 13, 2013 at 2:40 PM, akp geek <akpgeek@gmail.com> wrote:
> thank you all. We will upgrade to 64bit postgres 9.2.5 and take all your
> suggestions for that.  Mean while will run the pg_reorg or pg_repack to take
> the bloat of the DB. Also pg_repack not installing on the solaris . I will
> try pg_reorg.

When you move your cluster to the 64bit version you need to do
dump/restore, because it is the only way to migrate between
architectures. In this case you don't need to use pg_reorg, as your
cluster will be recreated "from scratch".

--
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@gmail.com