pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedperformance problems ... 100 cpu utilization
7+ messages / 3 participants
[nested] [flat]
* performance problems ... 100 cpu utilization
@ 2005-07-13 19:13 Dennis <dennis@works4me.com>
0 siblings, 1 reply; 7+ messages in thread
From: Dennis @ 2005-07-13 19:13 UTC (permalink / raw)
To: pgsql-performance
Hi,
I've got a java based web application that uses PostgreSQL 8.0.2.
PostgreSQL runs on its own machine with RHEL 3, ia32e kernel, dual Xeon
processor, 4 Gb ram.
The web application runs on a seperate machine from the database. The
application machine has three tomcat instances configured to use 64
database connections each using DBCP for pooling. Most of the data
access is via Hibernate. The database itself is about 100 meg in size.
We're perf testing the application with Loadrunner. At about 500 virtual
users hitting the web application, the cpu utilization on the database
server is at 100%, PostgreSQL is on its knees. The memory usage isn't
bad, the I/O isn't bad, only the CPU seems to be maxed out.
checking the status of connections at this point ( ps -eaf | grep
"postgres:") where the CPU is maxed out I saw this:
127 idle
12 bind
38 parse
34 select
Hibernate is used in the application and unfortunately this seems to
cause queries not to get logged. (see
http://archives.postgresql.org/pgsql-admin/2005-05/msg00241.php)
I know there has been discussion about problems on Xeon MP systems. Is
this what we are running into? Or is something else going on? Is there
other information I can provide that might help determine what is going on?
Here are the postgresql.conf settings:
# The maximum number of connections.
max_connections = 256
# Standard performance-related settings.
shared_buffers = 16384
max_fsm_pages = 200000
max_fsm_relations = 10000
fsync = false
wal_sync_method = fsync
wal_buffers = 32
checkpoint_segments = 6
effective_cache_size = 38400
random_page_cost = 2
work_mem = 16384
maintenance_work_mem = 16384
# TODO - need to investigate these.
commit_delay = 0
commit_siblings = 5
max_locks_per_transaction = 512
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: performance problems ... 100 cpu utilization
@ 2005-07-14 03:11 Qingqing Zhou <zhouqq@cs.toronto.edu>
parent: Dennis <dennis@works4me.com>
0 siblings, 1 reply; 7+ messages in thread
From: Qingqing Zhou @ 2005-07-14 03:11 UTC (permalink / raw)
To: pgsql-performance
"Dennis" <dennis@works4me.com> writes
>
> checking the status of connections at this point ( ps -eaf | grep
> "postgres:") where the CPU is maxed out I saw this:
>
> 127 idle
> 12 bind
> 38 parse
> 34 select
>
Are you sure 100% CPU usage is solely contributed by Postgresql? Also, from
the ps status you list, I can hardly see that's a problem because of problem
you mentioned below.
>
> I know there has been discussion about problems on Xeon MP systems. Is
> this what we are running into? Or is something else going on? Is there
> other information I can provide that might help determine what is going
on?
>
Here is a talk about Xeon-SMP spinlock contention problem:
http://archives.postgresql.org/pgsql-performance/2005-05/msg00441.php
Regards,
Qingqing
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: performance problems ... 100 cpu utilization
@ 2005-07-14 03:28 Dennis <dennis@works4me.com>
parent: Qingqing Zhou <zhouqq@cs.toronto.edu>
0 siblings, 1 reply; 7+ messages in thread
From: Dennis @ 2005-07-14 03:28 UTC (permalink / raw)
To: Qingqing Zhou <zhouqq@cs.toronto.edu>; +Cc: pgsql-performance
Qingqing Zhou wrote:
>Are you sure 100% CPU usage is solely contributed by Postgresql? Also, from
>the ps status you list, I can hardly see that's a problem because of problem
>you mentioned below.
>
>
The postgreSQL processes are what is taking up all the cpu. There aren't
any other major applications on the machine. Its a dedicated database
server, only for this application.
It doesn't seem to make sense that PostgreSQL would be maxed out at this
point. I think given the size of the box, it could do quite a bit
better. So, what is going on? I don't know.
Dennis
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: performance problems ... 100 cpu utilization
@ 2005-07-14 05:07 David Mitchell <david.mitchell@telogis.com>
parent: Dennis <dennis@works4me.com>
0 siblings, 1 reply; 7+ messages in thread
From: David Mitchell @ 2005-07-14 05:07 UTC (permalink / raw)
To: Dennis <dennis@works4me.com>; +Cc: Qingqing Zhou <zhouqq@cs.toronto.edu>; pgsql-performance
What is the load average on this machine? Do you do many updates? If you
do a lot of updates, perhaps you haven't vacuumed recently. We were
seeing similar symptoms when we started load testing our stuff and it
turned out we were vacuuming too infrequently.
David
Dennis wrote:
> Qingqing Zhou wrote:
>
>> Are you sure 100% CPU usage is solely contributed by Postgresql? Also,
>> from
>> the ps status you list, I can hardly see that's a problem because of
>> problem
>> you mentioned below.
>>
>>
> The postgreSQL processes are what is taking up all the cpu. There aren't
> any other major applications on the machine. Its a dedicated database
> server, only for this application.
>
> It doesn't seem to make sense that PostgreSQL would be maxed out at this
> point. I think given the size of the box, it could do quite a bit
> better. So, what is going on? I don't know.
>
> Dennis
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
--
David Mitchell
Software Engineer
Telogis
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: performance problems ... 100 cpu utilization
@ 2005-07-14 06:54 Dennis <dennis@works4me.com>
parent: David Mitchell <david.mitchell@telogis.com>
0 siblings, 1 reply; 7+ messages in thread
From: Dennis @ 2005-07-14 06:54 UTC (permalink / raw)
To: David Mitchell <david.mitchell@telogis.com>; +Cc: Qingqing Zhou <zhouqq@cs.toronto.edu>; pgsql-performance
David Mitchell wrote:
> What is the load average on this machine? Do you do many updates? If
> you do a lot of updates, perhaps you haven't vacuumed recently. We
> were seeing similar symptoms when we started load testing our stuff
> and it turned out we were vacuuming too infrequently.
The load average at the 100% utilization point was about 30! A vacuum
analyze was done before the test was started. I believe there are many
more selects than updates happening at any one time.
Dennis
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: performance problems ... 100 cpu utilization
@ 2005-07-15 00:43 David Mitchell <david.mitchell@telogis.com>
parent: Dennis <dennis@works4me.com>
0 siblings, 1 reply; 7+ messages in thread
From: David Mitchell @ 2005-07-15 00:43 UTC (permalink / raw)
To: Dennis <dennis@works4me.com>; +Cc: Qingqing Zhou <zhouqq@cs.toronto.edu>; pgsql-performance
If your table has got into this state, then vacuum analyze won't fix it.
You will have to do a vacuum full to get it back to normal, then
regularly vacuum (not full) to keep it in good condition. We vacuum our
critical tables every 10 minutes to keep them in good nick.
David
Dennis wrote:
> David Mitchell wrote:
>
>> What is the load average on this machine? Do you do many updates? If
>> you do a lot of updates, perhaps you haven't vacuumed recently. We
>> were seeing similar symptoms when we started load testing our stuff
>> and it turned out we were vacuuming too infrequently.
>
>
> The load average at the 100% utilization point was about 30! A vacuum
> analyze was done before the test was started. I believe there are many
> more selects than updates happening at any one time.
>
> Dennis
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>
--
David Mitchell
Software Engineer
Telogis
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: performance problems ... 100 cpu utilization
@ 2005-07-15 16:03 Dennis <dennis@works4me.com>
parent: David Mitchell <david.mitchell@telogis.com>
0 siblings, 0 replies; 7+ messages in thread
From: Dennis @ 2005-07-15 16:03 UTC (permalink / raw)
To: David Mitchell <david.mitchell@telogis.com>; +Cc: Qingqing Zhou <zhouqq@cs.toronto.edu>; pgsql-performance
David Mitchell wrote:
> If your table has got into this state, then vacuum analyze won't fix
> it. You will have to do a vacuum full to get it back to normal, then
> regularly vacuum (not full) to keep it in good condition. We vacuum
> our critical tables every 10 minutes to keep them in good nick.
So should I have vacuum run during the load test? At what level of
updates should it run every ten minutes?
Dennis
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-07-15 16:03 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-13 19:13 performance problems ... 100 cpu utilization Dennis <dennis@works4me.com>
2005-07-14 03:11 ` Qingqing Zhou <zhouqq@cs.toronto.edu>
2005-07-14 03:28 ` Dennis <dennis@works4me.com>
2005-07-14 05:07 ` David Mitchell <david.mitchell@telogis.com>
2005-07-14 06:54 ` Dennis <dennis@works4me.com>
2005-07-15 00:43 ` David Mitchell <david.mitchell@telogis.com>
2005-07-15 16:03 ` Dennis <dennis@works4me.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox