Обсуждение: postmaster(s) have high load average
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?
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
Вложения
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 >> >>
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
> 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.
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
Вложения
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
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.