Skip site navigation (1) Skip section navigation (2)

Re: Seq scans status update

From: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Patches <pgsql-patches(at)postgresql(dot)org>
Subject: Re: Seq scans status update
Date: 2007-05-18 09:07:04
Message-ID: 464D6CB8.9030200@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-patches
Tom Lane wrote:
> Heikki Linnakangas <heikki(at)enterprisedb(dot)com> writes:
>> In any case, I do want this for VACUUMs to fix the "WAL flush for every 
>> dirty page" problem. Maybe we should indeed drop the other aspects of 
>> the patch and move on, I'm getting tired of this as well.
> 
> Can we devise a small patch that fixes that issue without creating
> uncertain impacts on other behavior?

Yeah certainly, that's my backup plan.

> The thing that has made me uncomfortable with this set of patches right
> along is the presumption that it's a good idea to tweak cache behavior
> to improve a small set of cases.  We are using cache behavior (now
> clock-sweep, formerly LRU) that is well tested and proven across a large
> body of experience; my gut feeling is that deviating from that behavior
> is likely to be a net loss when the big picture is considered.  I
> certainly have not seen any test results that try to prove that there's
> no such generalized disadvantage to these patches.

That was my initial reaction as well. However, people claimed that using 
a small number of buffers you can achieve higher raw throughput. 
Avoiding the cache spoiling sounds plausible as well, if you're going to 
do a seq scan of a table larger than shared_buffers, you know those 
pages are not going to be needed in the near future; you're going to 
replace them yourself with pages from the same scan.

> One could argue that the real bug here is that VACUUM deviates from the
> standard behavior by forcing immediate recycling of pages it releases,
> and that getting rid of that wart is the correct answer rather than
> piling more warts atop it --- especially warts that will change the
> behavior for anything besides VACUUM.

Maybe a better approach would be switching to an algorithm that's more 
resistant to sequential scans by nature. That's definitely not going to 
happen for 8.3, though.

It's also possible that whatever algorithm we choose doesn't make much 
difference in practice because the in typical configurations the OS 
cache is bigger and more significant anyway. It's also possible that 
there's some counter-productive interactions between our and OS cache 
management.

In any case, I'd like to see more test results before we make a 
decision. I'm running tests with DBT-2 and a seq scan running in the 
background to see if the cache-spoiling effect shows up. I'm also trying 
to get hold of some bigger hardware to run on. Running these tests takes 
some calendar time, but the hard work has already been done. I'm going 
to start reviewing Jeff's synchronized scans patch now.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

In response to

Responses

pgsql-patches by date

Next:From: Heikki LinnakangasDate: 2007-05-18 09:47:19
Subject: Re: Updated bitmap index patch
Previous:From: FAST PostgreSQLDate: 2007-05-18 08:55:50
Subject: Re: Updateable cursors patch

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group