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

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