pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgres: 100% CPU utilization
13+ messages / 9 participants
[nested] [flat]
* postgres: 100% CPU utilization
@ 2007-04-19 19:29 Sergey Tsukinovsky <Sergey.Tsukinovsky@cryptologic.com>
0 siblings, 4 replies; 13+ messages in thread
From: Sergey Tsukinovsky @ 2007-04-19 19:29 UTC (permalink / raw)
To: pgsql-performance
Hi,
I'm currently dealing with performance issues of postgres and looking
for some advice.
Platform
Postgres: 7.0.2
OS: FreeBSD4.4
DB: size - about 50M, most frequently updated tables are of an average
size of 1000-2000 rows and there are not many of them, about 15 in total
Description
My current system load keeps the postgres CPU utilization at the level
of 90-100%.
'vacuumdb' results in a sharp drop of the CPU usage down to 25-30%, but
not for a long period of time - it gets back to 100% within 30 minutes.
Disk IO ratio during the test keeps on about 0.5 MB/s
Questions:
1. When reading the 'vacuum analyze' output how to identify which one of
the actions had the most effect on reducing the CPU usage - garbage
cleaning or statistics recalculation for the analyzer?
2. What would be the recommended set of parameters to tune up in order
to improve the performance over the time, instead of considering an
option to vacuum every 30 minutes or so?
3. Is it safe to run 'vacuum' as frequently as every 15-30 minutes?
4. Suggestions?
I know that 7.0.2 is an old version and therefore ran the same test on
7.3.18 - the performance behavior was similar.
Thank you in advance,
Sergey
_________________________________________________
This message, including any attachments, is confidential and/or
privileged and contains information intended only for the person(s)
named above. Any other distribution, copying or disclosure is strictly
prohibited. If you are not the intended recipient or have received this
message in error, please notify us immediately by reply email and
permanently delete the original transmission from all of your systems
and hard drives, including any attachments, without making a copy.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgres: 100% CPU utilization
@ 2007-04-23 04:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: Sergey Tsukinovsky <Sergey.Tsukinovsky@cryptologic.com>
3 siblings, 0 replies; 13+ messages in thread
From: Tom Lane @ 2007-04-23 04:36 UTC (permalink / raw)
To: Sergey Tsukinovsky <Sergey.Tsukinovsky@cryptologic.com>; +Cc: pgsql-performance
"Sergey Tsukinovsky" <Sergey.Tsukinovsky@cryptologic.com> writes:
> I'm currently dealing with performance issues of postgres and looking
> for some advice.
> Postgres: 7.0.2
Stop right there. You have *no* business asking for help on an
installation you have not updated in more than six years.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgres: 100% CPU utilization
@ 2007-04-23 05:39 Andreas Tille <tillea@rki.de>
parent: Sergey Tsukinovsky <Sergey.Tsukinovsky@cryptologic.com>
3 siblings, 0 replies; 13+ messages in thread
From: Andreas Tille @ 2007-04-23 05:39 UTC (permalink / raw)
To: Sergey Tsukinovsky <Sergey.Tsukinovsky@cryptologic.com>; +Cc: pgsql-performance
On Thu, 19 Apr 2007, Sergey Tsukinovsky wrote:
> I know that 7.0.2 is an old version and therefore ran the same test on
> 7.3.18 - the performance behavior was similar.
Why have you choosen just another very old version for performance
comparison and not the latest stable release?
Kind regards
Andreas.
--
http://fam-tille.de
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgres: 100% CPU utilization
@ 2007-04-23 08:53 Mario Weilguni <mweilguni@sime.com>
parent: Sergey Tsukinovsky <Sergey.Tsukinovsky@cryptologic.com>
3 siblings, 1 reply; 13+ messages in thread
From: Mario Weilguni @ 2007-04-23 08:53 UTC (permalink / raw)
To: pgsql-performance; +Cc: Sergey Tsukinovsky <Sergey.Tsukinovsky@cryptologic.com>
Am Donnerstag, 19. April 2007 schrieb Sergey Tsukinovsky:
> 2. What would be the recommended set of parameters to tune up in order
> to improve the performance over the time, instead of considering an
> option to vacuum every 30 minutes or so?
>
> 3. Is it safe to run 'vacuum' as frequently as every 15-30 minutes?
No problem.
>
> 4. Suggestions?
Do yourself a favor and upgrade at least to 8.1.x and use autovacuum.
Best regards
Mario Weilguni
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgres: 100% CPU utilization
@ 2007-04-23 15:06 Ron <rjpeace@earthlink.net>
parent: Mario Weilguni <mweilguni@sime.com>
0 siblings, 1 reply; 13+ messages in thread
From: Ron @ 2007-04-23 15:06 UTC (permalink / raw)
To: Mario Weilguni <mweilguni@sime.com>; +Cc: pgsql-performance
At 04:53 AM 4/23/2007, Mario Weilguni wrote:
>Am Donnerstag, 19. April 2007 schrieb Sergey Tsukinovsky:
> > 2. What would be the recommended set of parameters to tune up in order
> > to improve the performance over the time, instead of considering an
> > option to vacuum every 30 minutes or so?
> >
> > 3. Is it safe to run 'vacuum' as frequently as every 15-30 minutes?
>No problem.
>
> >
> > 4. Suggestions?
>Do yourself a favor and upgrade at least to 8.1.x and use autovacuum.
In fact, I'll go one step further and say that pg improves so much
from release to release that everyone should make superhuman efforts
to always be running the latest stable release.
Even the differences between 8.1.x and 8.2.x are worth it.
(and the fewer and more modern the releases "out in the wild", the
easier community support is)
Cheers,
Ron Peacetree
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgres: 100% CPU utilization
@ 2007-04-23 16:09 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Sergey Tsukinovsky <Sergey.Tsukinovsky@cryptologic.com>
3 siblings, 2 replies; 13+ messages in thread
From: Scott Marlowe @ 2007-04-23 16:09 UTC (permalink / raw)
To: Sergey Tsukinovsky <Sergey.Tsukinovsky@cryptologic.com>; +Cc: pgsql-performance
On Thu, 2007-04-19 at 14:29, Sergey Tsukinovsky wrote:
> Hi,
>
>
>
> I’m currently dealing with performance issues of postgres and looking
> for some advice.
>
>
>
> Platform
>
> Postgres: 7.0.2
>
> OS: FreeBSD4.4
>
> DB: size - about 50M, most frequently updated tables are of an average
> size of 1000-2000 rows and there are not many of them, about 15 in
> total
SNIP
> I know that 7.0.2 is an old version and therefore ran the same test on
> 7.3.18 – the performance behavior was similar.
So, are you running this on an Intel 486DX2-50 with a Seagate ST-4096
with an ISA based RLL encoding controller, or are you using the more
advanced AMD 586 CPU running at 90MHz with an Adaptec ARC2090 PCI based
SCSI card?
And do you have 32 or 64 Megs of memory in that machine?
Cause honestly, that's the kinda hardware I was running 7.0.2 on, so you
might as well get retro in your hardware department while you're at it.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgres: 100% CPU utilization
@ 2007-04-23 20:00 Vivek Khera <vivek@khera.org>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
1 sibling, 1 reply; 13+ messages in thread
From: Vivek Khera @ 2007-04-23 20:00 UTC (permalink / raw)
To: pgsql-performance
On Apr 23, 2007, at 12:09 PM, Scott Marlowe wrote:
> And do you have 32 or 64 Megs of memory in that machine?
>
> Cause honestly, that's the kinda hardware I was running 7.0.2 on,
> so you
> might as well get retro in your hardware department while you're at
> it.
I think you're being too conservative... I recall that those specs
for me correspond to running Pg 6.5 as the latest release... talk
about performance and corruption issues... :-) He's probably got at
least a Pentium II.
Attachments:
smime.p7s (application/pkcs7-signature, 2.5K) download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgres: 100% CPU utilization
@ 2007-04-23 20:21 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Vivek Khera <vivek@khera.org>
0 siblings, 1 reply; 13+ messages in thread
From: Scott Marlowe @ 2007-04-23 20:21 UTC (permalink / raw)
To: Vivek Khera <vivek@khera.org>; +Cc: pgsql-performance
On Mon, 2007-04-23 at 15:00, Vivek Khera wrote:
> On Apr 23, 2007, at 12:09 PM, Scott Marlowe wrote:
>
> > And do you have 32 or 64 Megs of memory in that machine?
> >
> > Cause honestly, that's the kinda hardware I was running 7.0.2 on,
> > so you
> > might as well get retro in your hardware department while you're at
> > it.
>
> I think you're being too conservative... I recall that those specs
> for me correspond to running Pg 6.5 as the latest release... talk
> about performance and corruption issues... :-) He's probably got at
> least a Pentium II.
Yeah, now that you mention it, I think I was able to come up with a
Pentium 100 with 64 Megs of RAM about the time 6.5 came out, on RedHat
5.1 then 5.2, and with a pair of 1.2 gig IDE drives under the hood.
Those were the days, huh?
I honestly kinda wondered if the original post came out of a time warp,
like some mail relay somewhere held onto it for 4 years or something.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgres: 100% CPU utilization
@ 2007-04-23 23:59 Mark Kirkwood <markir@paradise.net.nz>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
1 sibling, 0 replies; 13+ messages in thread
From: Mark Kirkwood @ 2007-04-23 23:59 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Sergey Tsukinovsky <Sergey.Tsukinovsky@cryptologic.com>; pgsql-performance
Scott Marlowe wrote:
> (snippage) that's the kinda hardware I was running 7.0.2 on, so you
> might as well get retro in your hardware department while you're at it.
>
Notice he's running FreeBSD 4.4(!), so it could well be a very old
machine...
Cheers
Mark
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgres: 100% CPU utilization
@ 2007-04-24 03:41 Greg Smith <gsmith@gregsmith.com>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 0 replies; 13+ messages in thread
From: Greg Smith @ 2007-04-24 03:41 UTC (permalink / raw)
To: pgsql-performance
On Mon, 23 Apr 2007, Scott Marlowe wrote:
> I honestly kinda wondered if the original post came out of a time warp,
> like some mail relay somewhere held onto it for 4 years or something.
That wouldn't be out of the question if this system is also his mail
server.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgres: 100% CPU utilization
@ 2007-04-24 15:30 Sergey Tsukinovsky <Sergey.Tsukinovsky@cryptologic.com>
parent: Ron <rjpeace@earthlink.net>
0 siblings, 2 replies; 13+ messages in thread
From: Sergey Tsukinovsky @ 2007-04-24 15:30 UTC (permalink / raw)
To: Ron <rjpeace@earthlink.net>; Mario Weilguni <mweilguni@sime.com>; +Cc: pgsql-performance
Thanks for this reply, Ron.
This is almost what I was looking for.
While the upgrade to the latest version is out of the question (which
unfortunately for me became the subject of this discussion) still, I was
looking for the ways to improve the performance of the 7.0.2 version.
Extensive use of vacuum was almost obvious, though I was hoping to get
some more tips from postrges gurus (or dinosaurs, if you want).
Anyways, the 8.2.4 was not performing so well without auto-vacuum. It
ramped up to 50% of CPU usage in 2 hours under the load.
With the auto-vacuum ON I've got what I really need and thus I know what
to do next.
Just for the record - the hardware that was used for the test has the
following parameters:
AMD Opteron 2GHZ
2GB RAM
LSI Logic SCSI
Thanks everyone for your assistance!
Sergey
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Ron
Sent: Monday, April 23, 2007 11:07 AM
To: Mario Weilguni
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] postgres: 100% CPU utilization
At 04:53 AM 4/23/2007, Mario Weilguni wrote:
>Am Donnerstag, 19. April 2007 schrieb Sergey Tsukinovsky:
> > 2. What would be the recommended set of parameters to tune up in
order
> > to improve the performance over the time, instead of considering an
> > option to vacuum every 30 minutes or so?
> >
> > 3. Is it safe to run 'vacuum' as frequently as every 15-30 minutes?
>No problem.
>
> >
> > 4. Suggestions?
>Do yourself a favor and upgrade at least to 8.1.x and use autovacuum.
In fact, I'll go one step further and say that pg improves so much
from release to release that everyone should make superhuman efforts
to always be running the latest stable release.
Even the differences between 8.1.x and 8.2.x are worth it.
(and the fewer and more modern the releases "out in the wild", the
easier community support is)
Cheers,
Ron Peacetree
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgres: 100% CPU utilization
@ 2007-04-26 22:50 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Sergey Tsukinovsky <Sergey.Tsukinovsky@cryptologic.com>
1 sibling, 0 replies; 13+ messages in thread
From: Scott Marlowe @ 2007-04-26 22:50 UTC (permalink / raw)
To: Sergey Tsukinovsky <Sergey.Tsukinovsky@cryptologic.com>; +Cc: Ron <rjpeace@earthlink.net>; Mario Weilguni <mweilguni@sime.com>; pgsql-performance
On Tue, 2007-04-24 at 10:30, Sergey Tsukinovsky wrote:
> Thanks for this reply, Ron.
> This is almost what I was looking for.
>
> While the upgrade to the latest version is out of the question (which
> unfortunately for me became the subject of this discussion) still, I was
> looking for the ways to improve the performance of the 7.0.2 version.
>
> Extensive use of vacuum was almost obvious, though I was hoping to get
> some more tips from postrges gurus (or dinosaurs, if you want).
>
> Anyways, the 8.2.4 was not performing so well without auto-vacuum. It
> ramped up to 50% of CPU usage in 2 hours under the load.
> With the auto-vacuum ON I've got what I really need and thus I know what
> to do next.
Could you give us a better picture of how you were testing 8.2.4? My
guess is that you were doing something that seemed right to you, but was
working against yourself, like constant vacuum fulls and getting index
bloat, or something else.
Why were you trying to not use autovacuum, btw? I've found it to be
quite capable, with only a few situations (high speed queueing) where I
needed to manually schedule vacuums. And I've never seen a situation
since about 7.4 where regular full vacuums were required.
> Just for the record - the hardware that was used for the test has the
> following parameters:
> AMD Opteron 2GHZ
> 2GB RAM
> LSI Logic SCSI
Nice hardware. I'd really like to hear the logic behind your statement
that upgrading to 8.1 or 8.2 is out of the question.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgres: 100% CPU utilization
@ 2007-04-27 01:26 Mark Kirkwood <markir@paradise.net.nz>
parent: Sergey Tsukinovsky <Sergey.Tsukinovsky@cryptologic.com>
1 sibling, 0 replies; 13+ messages in thread
From: Mark Kirkwood @ 2007-04-27 01:26 UTC (permalink / raw)
To: Sergey Tsukinovsky <Sergey.Tsukinovsky@cryptologic.com>; +Cc: Ron <rjpeace@earthlink.net>; Mario Weilguni <mweilguni@sime.com>; pgsql-performance
Sergey Tsukinovsky wrote:
>
> Just for the record - the hardware that was used for the test has the
> following parameters:
> AMD Opteron 2GHZ
> 2GB RAM
> LSI Logic SCSI
>
And you ran FreeBSD 4.4 on it right? This may be a source of high cpu
utilization in itself if the box is SMP or dual core, as multi-cpu
support was pretty primitive in that release (4.12 would be better if
you are required to stick to the 4.x branch, if not the 6.2 is recommended)!
Cheers
Mark
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2007-04-27 01:26 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-19 19:29 postgres: 100% CPU utilization Sergey Tsukinovsky <Sergey.Tsukinovsky@cryptologic.com>
2007-04-23 04:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-23 05:39 ` Andreas Tille <tillea@rki.de>
2007-04-23 08:53 ` Mario Weilguni <mweilguni@sime.com>
2007-04-23 15:06 ` Ron <rjpeace@earthlink.net>
2007-04-24 15:30 ` Sergey Tsukinovsky <Sergey.Tsukinovsky@cryptologic.com>
2007-04-26 22:50 ` Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-27 01:26 ` Mark Kirkwood <markir@paradise.net.nz>
2007-04-23 16:09 ` Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-23 20:00 ` Vivek Khera <vivek@khera.org>
2007-04-23 20:21 ` Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-24 03:41 ` Greg Smith <gsmith@gregsmith.com>
2007-04-23 23:59 ` Mark Kirkwood <markir@paradise.net.nz>
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