Thread: postmaster has high CPU (system) utilization

I'm using postgreSQL and perl DBI and have come across this issue:

postmaster will use most of the CPU for an extended amount of time (which isn't odd) but 75% of that is system level (which seems odd)...

<-- snippit from top -->
  4:45pm  up 17 days, 22:22,  5 users,  load average: 0.71, 0.29, 0.19
84 processes: 69 sleeping, 15 running, 0 zombie, 0 stopped
CPU states: 25.6% user, 74.3% system,  0.0% nice,  0.0% idle
Mem:   257136K av,  241656K used,   15480K free,  147956K shrd,   55740K buff
Swap:  514072K av,     136K used,  513936K free                   42724K cached

  PID   USER     PRI  NI  SIZE  RSS SHARE STAT  LIB %CPU %MEM   TIME COMMAND
19906 postgres  11   0  4552 4552  3792      R       0    87.3      1.7       0:55 postmaster
<-- end of snippit -->

What I am doing is, via a perl script, reading about 5000 records out of the database and printing them to the screen.  This process takes about 5 minutes with postmaster hogging the CPU for the duration.

What is odd is that if I then duplicate the same data on another box and run the same script there the process takes less then 30 seconds to complete.  Postmaster will still hog the CPU, but it is 75% user level load, rather than system level like it is on the problematic box. (Yes, the 2 boxes are of equivalent horsepower and have the same version of linux, postgreSQL, and perl installed)

I should also mention that if I run the same query within psql I will get all the results in about 4 seconds.

Has anybody else run into this problem where postmaster seems to churn in system level calls??  Any ideas on how to fix this issue ?

Thanks in advance,
Trevor.

On Mon, Mar 17, 2003 at 06:10:14PM -0500, Trevor Bylsma wrote:
> What I am doing is, via a perl script, reading about 5000 records out of
> the database and printing them to the screen.  This process takes about
> 5 minutes with postmaster hogging the CPU for the duration.
>
> What is odd is that if I then duplicate the same data on another box and
> run the same script there the process takes less then 30 seconds to
> complete.  Postmaster will still hog the CPU, but it is 75% user level
> load, rather than system level like it is on the problematic box. (Yes,
> the 2 boxes are of equivalent horsepower and have the same version of
> linux, postgreSQL, and perl installed)
>
> I should also mention that if I run the same query within psql I will
> get all the results in about 4 seconds.

Looks to me that it's not the postmaster doing it, but the process doing the
printing. This is one process where a quality of your video card matter.
Especially the scrolling is heavy on non-accelerated cards. The way to check
this is to redirect the output to a file and see if the difference is still
there.

> Has anybody else run into this problem where postmaster seems to churn
> in system level calls??  Any ideas on how to fix this issue ?

Confirm the problem is where you think it is. One useful trick is to use
strace -c. It will run your program and then give you an idea of which
syscalls the time is spent on.

You can use it on running processes to.
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> IT is not something like pizza that you order in at one o'clock in
> the morning. - John Loebenstein, St George CIO

On 17 Mar 2003, Trevor Bylsma wrote:

> I'm using postgreSQL and perl DBI and have come across this issue:
>
> postmaster will use most of the CPU for an extended amount of time
> (which isn't odd) but 75% of that is system level (which seems odd)...

When was the last time you vacuum / analyzed this database?  It sounds
like it may be that the system is having to look through thousands of dead
tuples to return your dataset, hence the high system load.

Could be index bloat too.

How much space does the $PGDATA directory on both those machines take up?

(i.e. 'du -s $PGDATA' as the postgres super user.)

On 17 Mar 2003 18:10:14 -0500, Trevor Bylsma
<tbylsma@atreus-systems.com> wrote:
>postmaster will use most of the CPU for an extended amount of time
>(which isn't odd) but 75% of that is system level (which seems odd)...

You have not told us anything about your hardware and operating
system, but if you are running Linux and if you have IDE drives and if
DMA is not enabled, then

    hdparm -d 1 /dev/hd<x>

might help.

>Mem:   257136K av,  241656K used,   15480K free,  147956K shrd,   55740K buff

BTW, I hope "147956K shrd" is not exclusively due to Postgres shared
buffers.

Servus
 Manfred