Re: [PERFORM] Upgrade to dual processor machine?

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 run VACUUM
every night in an automated manner. For tables with few modifications, VACUUM should be run less frequently. The command exclusively
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(at)topconcepts(dot)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(at)persistent(dot)co(dot)in>
To: <pgsql-performance(at)postgresql(dot)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