Re: Gradual increase in CPU utilization by postmaster

> "Yasuo Ohgaki" <yasuo_ohgaki(at)hotmail(dot)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