Re: [PERFORM] Upgrade to dual processor machine?

I use the Async Query technique of PG to do such tasks as Vacuum-ing.

Henrik Steffen wrote:

>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
>>
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>
>