Message-ID: <4F4A0D84.2070108@lerner.co.il>
Date: Sun, 26 Feb 2012 12:46:28 +0200
From: "Reuven M. Lerner" <reuven@lerner.co.il>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.6;
	rv:10.0.2) Gecko/20120216 Thunderbird/10.0.2
MIME-Version: 1.0
To: sthomas@peak6.com
CC: Glyn Astill <glynastill@yahoo.co.uk>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Very long deletion time on a 200 GB database
References: <4F45FB55.1020600@lerner.co.il>
	<1329990620.15378.YahooMailNeo@web171403.mail.ir2.yahoo.com>
	<4F4730B4.2050602@lerner.co.il> <4F479FDE.1070509@peak6.com>
	<4F47A48C.6000302@lerner.co.il> <4F47A9E2.3030404@peak6.com>
In-Reply-To: <4F47A9E2.3030404@peak6.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Hi again, everyone.

Wow, I can't get over how helpful everyone has been.

Shaun wrote:

> The main problem you're going to run into is that your table is larger than the memory in that server. 4GB is really pretty small for a server hosting a 200+GB database. That they didn't mean it to get that big doesn't really help you clean it up.

Yep!  And as you pointed out later in you note, PostgreSQL isn't the 
only thing running on this computer.  There's also a full-fledged 
Windows application normally running on it.  And the nature of the 
manufacturing, black-box context means that maintenance is supposed to 
be rare, and that anything which gets us off of a 24/7 work schedule is 
enormously expensive.

This has been a fun problem to fix, for sure...  We're not there yet, 
but I feel like we're really close.

I'm currently trying a hybrid approach, based on several suggestions 
that were posted to this list:

Given that during this maintenance operation, nothing else should 
running, I'm going to bump up the shared_buffers.  Even after we run our 
maintenance, the fact that shared_buffers was so ridiculously low 
couldn't be helping anything, and I'll push it up.

I finally remembered why I had such a tortured set of subselects in my 
original query: If you're going to do a query with LIMIT in it, you had 
better be sure that you know what you're doing, because without an ORDER 
BY clause, you might be in for surprises.  And sure enough, in our 
testing, I realized that when we asked the database for up to 5 rows, we 
were getting the same rows again and again, thus stopping after it 
deleted a few bunches of rows.

So I changed tactics somewhat, and it appears to be working much, much 
faster: I first created a table (not a temp table, simply because my 
functions are getting invoked by the .NET application in a new 
connection each time, and I obviously don't want my table to go away) 
with the IDs of the R table that are older than n days old.    This 
table has about 200,000 rows in it, but each column is an int, so it's 
pretty small.

I then have a separate function that takes a parameter, the chunk size. 
  I loop through the table created in the first function 
(old_report_ids), deleting all of the records in the B table that 
references the R table.  I then remove the row from the old_report_ids 
table, and then loop again, until I've reached the chunk size.  There 
are undoubtedly more elegant ways to do this, but we just gotta get it 
working at this point. :-)

We're about to test this, but from my small tests on my computer, it ran 
much, much faster than other options.  We'll see what happens when we 
try it now on the 200 GB monster...

Reuven