Re: [PERFORM] Upgrade to dual processor machine? - Mailing list pgsql-general

Hi Shridhar,

do you seriously think that I should vacuum frequently updated/inserted
tables every 120 seconds ?

This is what it says in the manual and what I have been doing until today:

"You should run VACUUM periodically to clean out expired rows. For tables that are heavily modified, it is useful to
runVACUUM 
every night in an automated manner. For tables with few modifications, VACUUM should be run less frequently. The
commandexclusively 
locks the table while processing. "

And:

"You should run VACUUM ANALYZE when a table is initially loaded and when a table's data changes dramatically. "


I have many UPDATEs and INSERTs on my log-statistics. For each http-request
there will be an INSERT into the logfile. And if certain customer pages
are downloaded there will even be an UPDATE in a customer-statistics table
causing a hits column to be set to hits+1... I didn't think this was a
dramatical change so far.

Still sure to run VACUUM ANALYZE on these tables so often?

VACUUM ANALYZE takes about 30 seconds on one of these tables and will be done once
every night automatically.


> Besides almost transactions are insert/update.. And if you have 11K blocks per
> second to write.. I suggest you vacuum analyse most used table one in a minute
> or so. Decide the best frequency by trial and error. A good start is double the
> time it takes for vacuum. i.e. if vacuum analyse takes 60 sec to finish, leave
> a gap of 120 sec. between two runs of vacuum.

--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
To: <pgsql-performance@postgresql.org>
Sent: Thursday, November 14, 2002 11:28 AM
Subject: Re: [PERFORM] [GENERAL] Upgrade to dual processor machine?


> On 14 Nov 2002 at 11:03, Henrik Steffen wrote:
> > vmstat 1 5:
> >    procs                      memory    swap          io     system         cpu
> >  r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us  sy  id
> >  1  8  1     60   4964   5888 309684   0   0   176    74   16    32  25   9  66
> >  0  6  3     60   4964   5932 308772   0   0  6264   256  347   347  13   9  78
> >  0  5  1     60   4964   5900 309364   0   0  9312   224  380   309  11   6  83
> >  1  4  1     60   5272   5940 309152   0   0 10320   116  397   429  17   6  77
> >  1  4  1     60   4964   5896 309512   0   0 11020   152  451   456  14  10  76
> > w:
> > 12:04pm  up 2 days, 17:44,  1 user,  load average: 10.28, 7.22, 3.88
> > USER     TTY      FROM              LOGIN@   IDLE   JCPU   PCPU  WHAT
> > root     pts/0    condor.city-map. 11:46am  0.00s  0.09s  0.01s  w
> > this is when things begin to go more slowly....
>
> Two things immediately noticable.. Load average and block ins..
>
> Either your disk write BW is saturated or CPU is too full, which I believe is
> the case. HAve you ever got faster write performance than 12K blocks say? Disk
> BW may be a bottleneck here.. Are they IDE disks?
>
> Besides almost transactions are insert/update.. And if you have 11K blocks per
> second to write.. I suggest you vacuum analyse most used table one in a minute
> or so. Decide the best frequency by trial and error. A good start is double the
>
> time it takes for vacuum. i.e. if vacuum analyse takes 60 sec to finish, leave
> a gap of 120 sec. between two runs of vacuum.
>
> You need to vacuum only those tables which are heavily updated. This will make
> vacuum faster.
>
> HTH
> Bye
>  Shridhar
>
> --
> Nouvelle cuisine, n.: French for "not enough food".Continental breakfast, n.:
> English for "not enough food".Tapas, n.: Spanish for "not enough food".Dim Sum,
>
> n.: Chinese for more food than you've ever seen in your entire life.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org