Thread: postgres 9.0.4 configuration and performance issue

postgres 9.0.4 configuration and performance issue

From
akp geek
Date:
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.



Attachment has the postgresql.conf file ( too big to put this email ) . Following is the detail from the top command

The top command gave us the following

            pci, instance #5
    pci, instance #5
load averages:  17.5,  18.1,  18.6;                    up 673+23:00:23                                                                                                   16:33:58
156 processes: 140 sleeping, 16 on cpu
CPU states: 76.5% idle, 22.8% user,  0.7% kernel,  0.0% iowait,  0.0% swap
Memory: 64G phys mem, 19G free mem, 63G total swap, 63G free swap

   PID USERNAME LWP PRI NICE  SIZE   RES STATE    TIME    CPU COMMAND
  7041 postgres   1   0    0 3180M 3174M cpu/26 113:08  1.59% postgres
 22787 postgres   1   0    0 3181M 3174M cpu/32 185:09  1.59% postgres
 28199 postgres   1   0    0 3178M 3172M cpu/11  41:52  1.59% postgres
 20361 postgres   1   0    0 3180M 3174M cpu/46 201:25  1.59% postgres
  3768 postgres   1   0    0 3179M 3172M cpu/51  42:47  1.59% postgres
  5410 postgres   1   0    0 3180M 3174M cpu/19 129:45  1.58% postgres
  8183 postgres   1   0    0 3181M 3175M cpu/63 397:59  1.58% postgres
 20472 postgres   1   0    0 3181M 3174M cpu/38 191:32  1.57% postgres
 22793 postgres   1   0    0 3181M 3175M sleep  155:57  1.54% postgres
 27811 postgres   1   0    0 3179M 3173M cpu/7   61:28  1.50% postgres
  3770 postgres   1   0    0 3176M 3170M cpu/59  26:25  1.29% postgres
  9473 postgres   1   0    0 3167M 3162M cpu/14   6:44  1.16% postgres
 19994 postgres   1  50    0 3179M 3173M sleep   81:53  1.10% postgres
  2773 postgres   1   0    0 3162M 3157M cpu/1   32:07  1.08% postgres
  6356 postgres   1   0    0 3180M 3174M cpu/2  130:16  0.64% postgres



Appreciate your help
regards

Attachment

Re: postgres 9.0.4 configuration and performance issue

From
Sergey Konoplev
Date:
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


Re: postgres 9.0.4 configuration and performance issue

From
mbetter95
Date:

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.


Re: postgres 9.0.4 configuration and performance issue

From
akp geek
Date:
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

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

Re: postgres 9.0.4 configuration and performance issue

From
John R Pierce
Date:
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



Re: postgres 9.0.4 configuration and performance issue

From
Sergey Konoplev
Date:
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


Re: postgres 9.0.4 configuration and performance issue

From
John R Pierce
Date:
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



Re: postgres 9.0.4 configuration and performance issue

From
akp geek
Date:
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.

Regards


On Sun, Oct 13, 2013 at 4:58 PM, John R Pierce <pierce@hogranch.com> wrote:
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



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: postgres 9.0.4 configuration and performance issue

From
Sergey Konoplev
Date:
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


Re: postgres 9.0.4 configuration and performance issue

From
akp geek
Date:
thanks for the advice..

Regards



On Sun, Oct 13, 2013 at 6:10 PM, Sergey Konoplev <gray.ru@gmail.com> wrote:
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".