Обсуждение: Gradual increase in CPU utilization by postmaster

We are running Postgres 7.0.3 on a Linux machine with a 2.4.0 kernel.

We have noticed that as we run our application which does
a lot of updates that CPU utilization (by postmaster) gradually
increases as time passes.

We have been able to reproduce it with the following
shell script which we use to feed a continuous stream
of updates to the DB:

-----------------
#!/bin/sh

while ( true ) ; do

  echo "update ruser set cname='xyz' where userid='abcdef'; "

done
-----------------

We connect it to psql to send the updates to the DB as follows:

./psql_perf2.sh  | psql -U AAA DBNAME -q

When we start the script, the "top" command shows about 75%
CPU utilization but this gradually increases to 97%
in a few minutes.

Could anyone suggest a reason for this behavior?

Thanks
R. Anand

"Rangachari Anand" <anand@reefedge.com> writes:
> We are running Postgres 7.0.3 on a Linux machine with a 2.4.0 kernel.
> We have noticed that as we run our application which does
> a lot of updates that CPU utilization (by postmaster) gradually
> increases as time passes.

Try 7.1.2 --- if it still does it in current sources, it'd be worth
investigating further.  I recall a couple of bugs fixed that might
explain this.

            regards, tom lane

> "Rangachari Anand" <anand@reefedge.com> writes:
> > We are running Postgres 7.0.3 on a Linux machine with a 2.4.0 kernel.
> > We have noticed that as we run our application which does
> > a lot of updates that CPU utilization (by postmaster) gradually
> > increases as time passes.
>
> Try 7.1.2 --- if it still does it in current sources, it'd be worth
> investigating further.  I recall a couple of bugs fixed that might
> explain this.
>
> regards, tom lane

FYI:

It happened on my test system also
 - RedHat Linux 7.0.1 (kernel-2.2.19, glibc-2.2, gcc-2.96,
    Intel Celeron466Mhz)
 - PostgreSQL 7.1.2 (build from src)

Postmaster size and CPU time increases gradually as time passes.
After 25 minutes elapsed, postmaster size became about 7000.
CPU time increased to about 90% from 70%.

Terminating script and executing again will result in the same higher
CPU and memory usage.
Vacuuming table seems to reset this behavior.

I've used the same script as Anand, but I think this situation is
common in HTTP session management. (Updating the same record
again and again.) Session record would not be updated as this test
case, though.

It seems I'm better off vacuuming my session table more often.

Hope this helps,
--
Yasuo Ohgaki

>Terminating script and executing again will result in the same higher
>CPU and memory usage.
>Vacuuming table seems to reset this behavior.
>
>I've used the same script as Anand, but I think this situation is
>common in HTTP session management. (Updating the same record
>again and again.) Session record would not be updated as this test
>case, though.
>
>It seems I'm better off vacuuming my session table more often.

I was also able to use vacuum to reset CPU utilization.
We are unable to upgrade to 7.1.2 immediately
so as a temporary fix, we have vacuumdb being invoked periodically by
crond.

Thanks for your help.

R. Anand

"Yasuo Ohgaki" <yasuo_ohgaki@hotmail.com> writes:
> Postmaster size and CPU time increases gradually as time passes.
> After 25 minutes elapsed, postmaster size became about 7000.
> CPU time increased to about 90% from 70%.

> Terminating script and executing again will result in the same higher
> CPU and memory usage.
> Vacuuming table seems to reset this behavior.

Wait a minute, are we talking about *postmaster* size or *backend* size?

            regards, tom lane

> "Yasuo Ohgaki" <yasuo_ohgaki@hotmail.com> writes:
> > Postmaster size and CPU time increases gradually as time passes.
> > After 25 minutes elapsed, postmaster size became about 7000.
> > CPU time increased to about 90% from 70%.
>
> > Terminating script and executing again will result in the same higher
> > CPU and memory usage.
> > Vacuuming table seems to reset this behavior.
>
> Wait a minute, are we talking about *postmaster* size or *backend* size?
>
> regards, tom lane

Sorry for the ambiguous description. It's *backend* size.
CPU usage and Memory size of the backend process continuously
grows.  When I start the script, most of CPU time is used by
system, and user time increases gradually.
Once CPU and Memory usage is increased, new backend, that uses the
table, end up with using the increased amount of resources (CPU and
memory). To reset this behavior, the table is needed to be vacuumed
and all backends, which are using the table, are needed to be terminated.

Table is very simple and has only 1 record.
test_table.sql
--------
CREATE TABLE test_table (
key  text,
val  text);

INSERT INTO test_table
(key,val) VALUES
('abc', 'aaa');
---------

Script
pgtest.sh
--------
#!/bin/sh
while ( true ) ; do
  echo "UPDATE test_table SET val='xyz' where key='abc'; "
done
---------
% psql -f test_table.sql
% pgtest.sh | psql -q

Hope this helps.

Regards,
--
Yasuo Ohgaki