Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 73EC22E3847 for ; Fri, 4 Jan 2008 00:55:19 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 40738-01-4 for ; Fri, 4 Jan 2008 00:55:14 -0400 (AST) Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.178]) by postgresql.org (Postfix) with ESMTP id A7D632E3849 for ; Fri, 4 Jan 2008 00:55:14 -0400 (AST) Received: by py-out-1112.google.com with SMTP id d37so3314399pye.29 for ; Thu, 03 Jan 2008 20:55:14 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=googlemail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=iLOoP3CkNreB9gAg5af8UJ2CcFlnSqtE7HnYIHDkr6o=; b=pcxr1YzNL8EjqqtxnoZgaQ5b389cEMQyJl15JYKXNgaBt9Y0vXS/ghjtToiL9DoiTaCl88ravMGCZAhJtmle9VnYzZR0Fh4tp2yMLeN7axZUEdRjoob449v6uMw1TUmMUaBWe8kBGny6Hwk7fnr9hxfxLAyASKWIGnll5H2j4/U= DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=IYHjyhrX+TJ3rcDCt+IG+sUbOuD0TzS/sx9GopEHo1OZR4TfKD29VbbMJzOZGx31w+OAnbqQf8Kl0CRUzToTRcJs+YElmV41rbJ3XYVU9wci+iq4a7UbokLCEvVilh5lovFnWKSnz6P7A6lkcCJVbDyamaX/qR5JRPRegK6tIHs= Received: by 10.35.86.19 with SMTP id o19mr5024634pyl.9.1199422513959; Thu, 03 Jan 2008 20:55:13 -0800 (PST) Received: by 10.35.72.8 with HTTP; Thu, 3 Jan 2008 20:55:13 -0800 (PST) Message-ID: <5a9699850801032055t4d10a7e3k4e37763b4cd77d2e@mail.gmail.com> Date: Fri, 4 Jan 2008 06:55:13 +0200 From: "Brian Modra" To: "Scott Marlowe" Subject: Re: Vacuum taking an age Cc: pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <5a9699850801030448k126a5028m85ddb2153e95184f@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/49 X-Sequence-Number: 27823 Hi, thanks for your reply. The number of rows per second has been increasing rapidly, but its averaging about 1 row per second, and a far smaller number of updates. So maybe there are not such a huge number of dead rows. I hope that a normal vacuum will clean it up. Total number of rows is about 3 million. Last night before I got your reply, I noticed that the number of shared memory buffers was only 1000, so I increased shmmax and when I restart the server next, its number of buffers will be 10000. The server has 8GB of memory, so that will only be a small proportion of its total memory. I have not restarted postgres yet because a vacuum is still running. Maybe I should kill that and restart postgres? The reason I increased this is because I noticed if I did a partial count of rows (e.g. those inserted with a timestamp after midnight last night), then the first time takes about 17 seconds, and the second time 1/4 second. I started a vacuum on the table yesterday, and its still running. I guess thats because the table is live. I am pretty sure that if I take it offline, then the vacuum will complete relatively quickly. Am I right? (I don't want to take it offline unless I really need to.) On 04/01/2008, Scott Marlowe wrote: > On Jan 3, 2008 6:48 AM, Brian Modra wrote: > > Hi, > > > > > > I have a pretty "live" table: rows being inserted and updated more > > than once 1 per second, though far, far more inserts than updates. > > > > There are currently over 3 million rows. > > > > It has not been vacuumed for months. > > How many rows per second? 1? all of them? Kinda makes a difference. > > If it was 1 a second updated for 3 months that's about 7million dead > rows. If it was all 3million, then that's 7million * 3million dead > rows, also know as a whole bunch of rows. > > Either way, you probably have a table so terribly bloated that a > regular vacuum will not help you in terms of speeding it up. Regular > vacuums are like brushing your teeth three times a day. If you've > forgotten for three months, brushing them once isn't likely to fix all > the cavities you've got. Same thing here. You'll either need a > vacuum full or a cluster. Cluster is often faster. Or you can try > selecting everything into a temp table, truncating the real table, and > inserting the data back in. Truncation will remove all rows, dead or > otherwise. The advantage is that it's often faster to truncate / > reload than it is to vacuum full. If you have indexes, you might want > to drop them while re-inserting and then recreated them. > -- Brian Modra Land line: +27 23 5411 462 Mobile: +27 79 183 8059 6 Jan Louw Str, Prince Albert, 6930 Postal: P.O. Box 2, Prince Albert 6930 South Africa