I have one process which writes a single float into 300 columns once per 
second.  I then run 4 process, from remote computers, to query a small 
subset of the latest row.

I have even commented out everything in the query programs, all they do 
is sleep, and the associated postmaster still sucks up 15% - 20% CPU.

Computer is a P4 /w 1Gig memory, all disk access is local.  RH9 /w stock 
postgresql-7.3 installed.

I have searched the documentation and tech site high and low for ideas....


  17:36:27  up 31 days,  6:07, 13 users,  load average: 4.11, 2.48, 1.62
107 processes: 99 sleeping, 8 running, 0 zombie, 0 stopped
CPU states:  22.3% user  76.0% system   0.0% nice   0.0% iowait   1.5% idle
Mem:  1030408k av,  976792k used,   53616k free,       0k shrd,  178704k 
buff
                     715252k actv,   33360k in_d,   22348k in_c
Swap: 2048248k av,   91308k used, 1956940k free                  589572k 
cached
 

   PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME CPU COMMAND
23389 cjw       16   0  2896 2752  2132 R    18.2  0.2   0:44   0 postmaster
23388 cjw       16   0  2896 2752  2132 S    18.0  0.2   0:45   0 postmaster
23391 cjw       16   0  2896 2752  2132 S    18.0  0.2   0:43   0 postmaster
23366 cjw       16   0  3788 3644  2560 S    17.8  0.3   2:32   0 postmaster
23392 cjw       16   0  2896 2752  2132 R    16.2  0.2   0:05   0 postmaster


-- 
--Chris

     How is it one careless match can start a forest fire, but it takes a
     whole box to start a campfire?


Thu, 07 Aug 2003 17:44:05 -0600
Chris Webster <cjw@ucar.edu>
postmaster(s) have high load average
Have you run VACUUM and/or VACUUM FULL and/or ANALYZE recently?

On Thu, Aug 07, 2003 at 05:44:05PM -0600, Chris Webster wrote:
> I have one process which writes a single float into 300 columns once per 
> second.  I then run 4 process, from remote computers, to query a small 
> subset of the latest row.
> 
> I have even commented out everything in the query programs, all they do 
> is sleep, and the associated postmaster still sucks up 15% - 20% CPU.
> 
> Computer is a P4 /w 1Gig memory, all disk access is local.  RH9 /w stock 
> postgresql-7.3 installed.
> 
> I have searched the documentation and tech site high and low for ideas....
> 
> 
>  17:36:27  up 31 days,  6:07, 13 users,  load average: 4.11, 2.48, 1.62
> 107 processes: 99 sleeping, 8 running, 0 zombie, 0 stopped
> CPU states:  22.3% user  76.0% system   0.0% nice   0.0% iowait   1.5% idle
> Mem:  1030408k av,  976792k used,   53616k free,       0k shrd,  178704k 
> buff
>                     715252k actv,   33360k in_d,   22348k in_c
> Swap: 2048248k av,   91308k used, 1956940k free                  589572k 
> cached
> 
> 
>   PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME CPU COMMAND
> 23389 cjw       16   0  2896 2752  2132 R    18.2  0.2   0:44   0 postmaster
> 23388 cjw       16   0  2896 2752  2132 S    18.0  0.2   0:45   0 postmaster
> 23391 cjw       16   0  2896 2752  2132 S    18.0  0.2   0:43   0 postmaster
> 23366 cjw       16   0  3788 3644  2560 S    17.8  0.3   2:32   0 postmaster
> 23392 cjw       16   0  2896 2752  2132 R    16.2  0.2   0:05   0 postmaster
> 
> 
> -- 
> --Chris
> 
>     How is it one careless match can start a forest fire, but it takes a
>     whole box to start a campfire?
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato
Fri, 8 Aug 2003 15:03:58 +1000
Martijn van Oosterhout <kleptog@svana.org>
Re: postmaster(s) have high load average
After a long battle with technology, 
 "Merlin Moncure" <merlin.moncure@rcsonline.com>, an earthling, wrote:
>INAL, but I would read carefully over the Oracle license agreement
>and redistribution allowances before doing this, especially if the
>database is installed in a commercial environment.  With all the
>legal activity around open source lately, it seems dangerous
>(probably harmless in a test/development environment though).

This begs the question of whether the PL/SQL code in question was
provided by Oracle Corporation or not; that's not clear.

If the person wanting to redeploy it is actually the author of the
code, then Oracle shouldn't have anything to say about it, any more
than Microsoft would have anything to say about code someone wrote in
Visual BASIC, or that SAP AG would about code I wrote in ABAP/4, or
such.
-- 
wm(X,Y):-write(X),write('@'),write(Y). wm('aa454','freenet.carleton.ca').
http://cbbrowne.com/info/wp.html
"I think that helps the users too much."   -- CSTACY

Fri, 08 Aug 2003 08:25:20 -0400
Christopher Browne <cbbrowne@acm.org>
Re: postmaster(s) have high load average
Martijn van Oosterhout wrote:

>Have you run VACUUM and/or VACUUM FULL and/or ANALYZE recently?
>  
>
a) yes.  I have it run analyze every 30 minutes or 1600 record 
additions.  Records are never updated or deleted so I assume I don't 
need vacuum.

b) It does it even at start up when there are fewer than 100 records in 
the database.

c)  Would this even matter for clients that only connect but NEVER make 
any requests from the database?

--Chris

>On Thu, Aug 07, 2003 at 05:44:05PM -0600, Chris Webster wrote:
>  
>
>>I have one process which writes a single float into 300 columns once per 
>>second.  I then run 4 process, from remote computers, to query a small 
>>subset of the latest row.
>>
>>I have even commented out everything in the query programs, all they do 
>>is sleep, and the associated postmaster still sucks up 15% - 20% CPU.
>>
>>Computer is a P4 /w 1Gig memory, all disk access is local.  RH9 /w stock 
>>postgresql-7.3 installed.
>>
>>I have searched the documentation and tech site high and low for ideas....
>>
>>
>> 17:36:27  up 31 days,  6:07, 13 users,  load average: 4.11, 2.48, 1.62
>>107 processes: 99 sleeping, 8 running, 0 zombie, 0 stopped
>>CPU states:  22.3% user  76.0% system   0.0% nice   0.0% iowait   1.5% idle
>>Mem:  1030408k av,  976792k used,   53616k free,       0k shrd,  178704k 
>>buff
>>                    715252k actv,   33360k in_d,   22348k in_c
>>Swap: 2048248k av,   91308k used, 1956940k free                  589572k 
>>cached
>>
>>
>>  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME CPU COMMAND
>>23389 cjw       16   0  2896 2752  2132 R    18.2  0.2   0:44   0 postmaster
>>23388 cjw       16   0  2896 2752  2132 S    18.0  0.2   0:45   0 postmaster
>>23391 cjw       16   0  2896 2752  2132 S    18.0  0.2   0:43   0 postmaster
>>23366 cjw       16   0  3788 3644  2560 S    17.8  0.3   2:32   0 postmaster
>>23392 cjw       16   0  2896 2752  2132 R    16.2  0.2   0:05   0 postmaster
>>    
>>



Fri, 08 Aug 2003 06:58:47 -0600
Chris Webster <cjw@ucar.edu>
Re: postmaster(s) have high load average
Oops! cjw@ucar.edu (Chris Webster) was seen spray-painting on a wall:
> Martijn van Oosterhout wrote:
>
>>Have you run VACUUM and/or VACUUM FULL and/or ANALYZE recently?
>>
> a) yes.  I have it run analyze every 30 minutes or 1600 record
> additions.  Records are never updated or deleted so I assume I don't
> need vacuum.

You only really need to run analyze when the statistical
characteristics of the data changes; as the database grows, that is
fairly likely to stabilize somewhat so that you can ANALYZE less
frequently over time...

Have you verified that nothing has gotten touched?  Run a VACUUM
VERBOSE and see what it does...  Note that if you ever get cases where
records are added but rolled back due to some later part of a
transaction failing, that too will lead to dead tuples...

> b) It does it even at start up when there are fewer than 100 records
> in the database.
>
> c)  Would this even matter for clients that only connect but NEVER
> make any requests from the database?

Run VACUUM VERBOSE on it; you'll no doubt see that some internal
tables such as pg_activity, pg_statistic, and such have a lot of dead
tuples.  Establishing a connection leads to _some_ DB activity, and
probably a dead tuple or two; every time you ANALYZE, you create a
bunch of dead tuples since old statistics are "killed off."
-- 
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne> rate me
http://www3.sympatico.ca/cbbrowne/sap.html
"Rules of Optimization:
     Rule 1: Don't do it.
     Rule 2 (for experts only): Don't do it yet."
-- M.A. Jackson

Sat, 09 Aug 2003 11:34:56 -0400
Christopher Browne <cbbrowne@acm.org>
Re: postmaster(s) have high load average
> Run VACUUM VERBOSE on it; you'll no doubt see that some internal
> tables such as pg_activity, pg_statistic, and such have a lot of dead
> tuples.  Establishing a connection leads to _some_ DB activity, and
> probably a dead tuple or two; every time you ANALYZE, you create a
> bunch of dead tuples since old statistics are "killed off."

What? Does this mean that it is needed to routinely vacuum system tables
too? If so, which is the recommended procedure?

thx
cl.

Sat, 9 Aug 2003 17:45:59 -0300
"Claudio Lapidus" <clapidus@hotmail.com>
Re: postmaster(s) have high load average
On Sat, Aug 09, 2003 at 05:45:59PM -0300, Claudio Lapidus wrote:
> > Run VACUUM VERBOSE on it; you'll no doubt see that some internal
> > tables such as pg_activity, pg_statistic, and such have a lot of dead
> > tuples.  Establishing a connection leads to _some_ DB activity, and
> > probably a dead tuple or two; every time you ANALYZE, you create a
> > bunch of dead tuples since old statistics are "killed off."
> 
> What? Does this mean that it is needed to routinely vacuum system tables
> too? If so, which is the recommended procedure?

On our system we do a vacuum analyze every night to cleanup what happened
during the day. Vacuum without a table name does all tables so you don't
explicitly need to list them.

-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato
Sun, 10 Aug 2003 11:02:50 +1000
Martijn van Oosterhout <kleptog@svana.org>
Re: postmaster(s) have high load average
In an attempt to throw the authorities off his trail, clapidus@hotmail.com ("Claudio Lapidus") transmitted:
>> Run VACUUM VERBOSE on it; you'll no doubt see that some internal
>> tables such as pg_activity, pg_statistic, and such have a lot of dead
>> tuples.  Establishing a connection leads to _some_ DB activity, and
>> probably a dead tuple or two; every time you ANALYZE, you create a
>> bunch of dead tuples since old statistics are "killed off."
>
> What? Does this mean that it is needed to routinely vacuum system tables
> too? If so, which is the recommended procedure?

On some 7.2 systems I work with, a smattering of system tables are
vacuumed hourly along with applications that are known to be good
"fodder" for the purpose.

In 7.3 and 7.4, the "contrib" application, pg_autovacuum can do the
trick, vacuuming anything that reaches thresholds of
inserts/deletes/updates, and do so more or less as often as necessary.

If you haven't got a cron job looking something like:

   0 0 * * * * vacuumdb -a -z > /dev/null 2> /dev/null

then you should probably add that, at least.
-- 
wm(X,Y):-write(X),write('@'),write(Y). wm('aa454','freenet.carleton.ca').
http://cbbrowne.com/info/multiplexor.html
"A   hack  is a   terrible   thing  to  waste,    please  give to  the
implementation of your choice..." -- GJC

Sat, 09 Aug 2003 21:25:23 -0400
Christopher Browne <cbbrowne@acm.org>
Re: postmaster(s) have high load average

On Sat, 2003-08-09 at 21:25, Christopher Browne wrote:
> In 7.3 and 7.4, the "contrib" application, pg_autovacuum can do the
> trick, vacuuming anything that reaches thresholds of
> inserts/deletes/updates, and do so more or less as often as necessary.

Actually pg_autovacuum is not included with 7.3, but works just fine
once you get it compiled.

> If you haven't got a cron job looking something like:
> 
>    0 0 * * * * vacuumdb -a -z > /dev/null 2> /dev/null
> 
> then you should probably add that, at least.

might be better to have it only vacuum a few specific tables that cause
most of your problems.  


19 Aug 2003 19:56:08 -0400
"Matthew T. O'Connor" <matthew@zeut.net>
Re: postmaster(s) have high load average