Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id A84AC2E0045 for ; Fri, 2 May 2008 17:51:52 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 57228-08 for ; Fri, 2 May 2008 17:51:46 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.180]) by developer.postgresql.org (Postfix) with ESMTP id 2E3522E003B for ; Fri, 2 May 2008 17:51:49 -0300 (ADT) Received: by wa-out-1112.google.com with SMTP id n4so393042wag.5 for ; Fri, 02 May 2008 13:51:48 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:cc:message-id:from:to:in-reply-to:content-type:content-transfer-encoding:mime-version:subject:date:references:x-mailer; bh=qAOF9aIKaJoSbp0VGKEXjEyWT9ctUMPGeiKdLejHDt0=; b=eE4ANWzdKHlJWOEr0PCVqJJHpZVJhHvfNUyOOB0cd1Ybnp2kyXaSqhSGCMyS1HeAC6mxe72n9oWLXYkyqI8srIDNWGsxLNvFJQQtyHvdrVl7mZJroQL7sO124TJr7hehotIk3YK7v7AtLEfV1wny2CpRqknrqcgTHMDmpLa7JKo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=cc:message-id:from:to:in-reply-to:content-type:content-transfer-encoding:mime-version:subject:date:references:x-mailer; b=hmhJXWuHPEMXhDvch2talzOeQV+tnjPD1gwdWGIZnYD8CUmI67rvqensLH8BKkfllQleOH692pHW/fFYUcpQfD9lChHYQzW5F0lOel2Ipv8R/InqEPyHAw2WKodkIY0xI4pkm//V3LExNzJNjsF4V+0US7FTLc2rTVsGlFTZZ1c= Received: by 10.114.193.1 with SMTP id q1mr3438818waf.75.1209761507532; Fri, 02 May 2008 13:51:47 -0700 (PDT) Received: from ?192.168.1.100? ( [24.19.22.42]) by mx.google.com with ESMTPS id n20sm6859051pof.0.2008.05.02.13.51.46 (version=TLSv1/SSLv3 cipher=OTHER); Fri, 02 May 2008 13:51:46 -0700 (PDT) Cc: "Tom Lane" , pgsql-performance@postgresql.org Message-Id: From: Alexy Khrabrov To: "Scott Marlowe" In-Reply-To: Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes Content-Transfer-Encoding: 7bit Mime-Version: 1.0 (Apple Message framework v919.2) Subject: Re: two memory-consuming postgres processes Date: Fri, 2 May 2008 13:51:44 -0700 References: <27A4D379-2553-496D-A96C-102F2A5E62C2@gmail.com> <21403.1209759217@sss.pgh.pa.us> <72E02D29-848B-467A-AE6B-401568010254@gmail.com> X-Mailer: Apple Mail (2.919.2) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.175 tagged_above=0 required=5 tests=AWL=-0.140, SARE_MILLIONSOF=0.315 X-Spam-Level: X-Archive-Number: 200805/29 X-Sequence-Number: 29997 On May 2, 2008, at 1:40 PM, Scott Marlowe wrote: > Again, a database protects your data from getting scrambled should the > program updating it quit halfway through etc... Right -- but this is a data mining work, I add a derived column to a row, and it's computed from that very row and a small second table which should fit in RAM. > Have you been vacuuming between these update attempts? Each one has > created millions of dead rows and bloated your data store. vacuum > full / cluster / reindex may be needed. I've read postgresql.conf better and see autovacuum = on is commented out, so it's on. That explains why shutting down was taking so long to shut autovacuum down too. Basically, the derived data is not critical at all, -- can I turn (1) off transactional behavior for an UPDATE, (2) should I care about vacuuming being done on the fly when saving RAM, or need I defer it/ manage it manually? I wonder what MySQL would do here on MyISAM tables without transactional behavior -- perhaps this is the case more suitable for them? Cheers, Alexy