Thread: postgres: 100% CPU utilization

"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

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

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

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

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.

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.

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.

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

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

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
______________________________________________________________________

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.

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