Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 940C49F98F9 for ; Mon, 27 Aug 2007 19:05:40 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 20798-07 for ; Mon, 27 Aug 2007 19:05:35 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from outbound-mail-67.bluehost.com (outbound-mail-67.bluehost.com [69.89.21.27]) by postgresql.org (Postfix) with SMTP id 7C3AC9FA08F for ; Mon, 27 Aug 2007 19:05:35 -0300 (ADT) Received: (qmail 23175 invoked by uid 0); 27 Aug 2007 22:05:33 -0000 Received: from unknown (HELO box54.bluehost.com) (69.89.20.54) by mailproxy4.bluehost.com with SMTP; 27 Aug 2007 22:05:33 -0000 Received: from vdsl-151-118-147-91.dnvr.qwest.net ([151.118.147.91] helo=new-host.home) by box54.bluehost.com with esmtp (Exim 4.66) (envelope-from ) id 1IPmiC-0001yh-BM for pgsql-performance@postgresql.org; Mon, 27 Aug 2007 16:05:32 -0600 From: Kevin Kempter To: pgsql-performance@postgresql.org Subject: Re: significant vacuum issues - looking for suggestions User-Agent: KMail/1.9.7 References: <200708241357.23629.kevin@kevinkempterllc.com> <20070824164144.93ce05bf.wmoran@collaborativefusion.com> <20070827210041.GN54309@nasby.net> In-Reply-To: <20070827210041.GN54309@nasby.net> MIME-Version: 1.0 Content-Disposition: inline Date: Mon, 27 Aug 2007 16:03:52 -0600 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit Message-Id: <200708271603.52740.kevin@kevinkempterllc.com> X-Identified-User: {840:box54.bluehost.com:kevinkem:kevinkempterllc.com} {sentby:bopbeforesmtp 151.118.147.91 authed with ossfusion.com} X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/331 X-Sequence-Number: 26461 On Monday 27 August 2007 15:00:41 you wrote: > On Fri, Aug 24, 2007 at 04:41:44PM -0400, Bill Moran wrote: > > In response to Kevin Kempter : > > > Hi List; > > > > > > I've just started working with a client that has been running Postgres > > > (with no DBA) for a few years. They're running version 8.1.4 on 4-way > > > dell boxes with 4Gig of memory on each box attached to RAID-10 disk > > > arrays. > > > > > > Some of their key config settings are here: > > > shared_buffers = 20480 > > > work_mem = 16384 > > > maintenance_work_mem = 32758 > > > > Before you do any of those other things, bump shared_buffers to about > > 120000 and maintenance_work_mem to 250000 or so -- unless this box > > has other applications on it using significant amounts of those 4G of > > RAM. You may find that these changes alone are enough to get vacuum > > to complete. You'll need to restart the server for the shared_buffers > > setting to take effect. > > For the really bloated table, you might need to go even higher than > 250000 for maint_work_mem. IIRC vacuum needs 6 bytes per dead tuple, so > that means 43M rows... with 5M dead pages, that means less than 10 rows > per page, which is unlikely. Keep in mind that if you do a vacuum > verbose, you'll be able to see if vacuum runs out of > maintenance_work_mem, because you'll see multiple passes through all the > indexes. > > You could also potentially use this to your benefit. Set maint_work_mem > low enough so that vacuum will have to start it's cleaning pass after > only an hour or so... depending on how big/bloated the indexes are on > the table, it might take another 2-3 hours to clean everything. I > believe that as soon as you see it start on the indexes a second time > you can kill it... you'll have wasted some work, but more importantly > you'll have actually vacuumed part of the table. > > But all of that's a moot point if they're running the default free space > map settings, which are way, way, way to conservative in 8.1. If you've > got one table with 5M dead pages, you probably want to set fsm_pages to > at least 50000000 as a rough guess, at least until this is under > control. Keep in mind that does equate to 286M of memory, though. > > As for your pg_dump idea... why not just do a CREATE TABLE AS SELECT * > FROM bloated_table? That would likely be much faster than messing around > with pg_dump. > > What kind of disk hardware is this running on? A good raid 10 array with > write caching should be able to handle a 200G database fairly well; at > least better than it is from what I'm hearing. The memory settings are way low on all their db servers (less than 170Meg for the shared_buffers). I fixed this table via creating a new_** table, select from insert into, and a rename. I'm still working through the memory settings and reviewing their other config settings, the filesystem type/settings and eventually a security audit. It's a new client and theyve been running postgres for a few years on approx 8 db servers with no DBA. The servers are 4-way intel boxes (NOT dual-core) with 4G of memory and running raid-10 arrays.