postgres 9.0.4 configuration and performance issue
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
Attachments:
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.4Some 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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:
Show quoted text
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 DBAhttp://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 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
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:
Show quoted text
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 bitversion
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<http://www.postgresql.org/mailpref/pgsql-general>
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
thanks for the advice..
Regards
On Sun, Oct 13, 2013 at 6:10 PM, Sergey Konoplev <gray.ru@gmail.com> wrote:
Show quoted text
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 totake
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 DBAhttp://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com