PostgreSQL: Re: [NOVICE] cpu utilization question

Thursday, September 18, 2008

Re: [NOVICE] cpu utilization question

On Sep 18, 2008, at 10:59 AM, Wright, George wrote:

We did profile the sql. The stand alone statements generally execute in less than 30 ms. The clients (not browsers) send very short delimited text messages to the server. The server parses them and inserts a record for each message and only returns an HTTP 200, 400 to the client.

I guess my confusion is why such a short message would cause postmaster to use so much CPU. Several messages may arrive simultaneously and that many postmasters get kicked off.

We are currently installing the pgpool tool but I was wondering if there were any other things I could try.

if a single SQL takes 30ms to insert... then I suspect it's a other PG process that takes up the CPU, may be a vacuum process?

From: ries van Twisk [mailto:pg@rvt.dds.nl]
Sent: Thursday, September 18, 2008 11:46 AM
To: Wright, George
Subject: Re: [NOVICE] cpu utilization question

On Sep 18, 2008, at 10:09 AM, Wright, George wrote:

We're running Suse 10.2, PG 8.1.5, PHP 5.2.

We're seeing high CPU percentages for multiple instances of postmaster: 15 – 30% for each instance. I realize a new instance is spawned for each connection that is opened and we have lots of clients sending very small text messages to our app for which we record a row per message.

We also have other periodic jobs that get kicked off but don't use near the cpu percentage that postmaster seems to.

Is there a way to minimize postmaster's cpu usage or is there some way to share that process between requests?

Thx.

You could setup connection pooling for these clients (web clients I assume),
but be-careful with PHP Persistent connections... they are buggy... I never used pgPool,
but that might be something you need.

When it comes to solving your high CPU processes you need to take a look why it's slow.
Use explain analyze as a start...

regards, Ries van Twisk

-------------------------------------------------------------------------------------------------
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing in e-mail?