Re: Bug: Buffer cache is not scan resistant

From: "Luke Lonergan" <llonergan(at)greenplum(dot)com>
To: "Luke Lonergan" <llonergan(at)greenplum(dot)com>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "Mark Kirkwood" <markir(at)paradise(dot)net(dot)nz>
Cc: "Gavin Sherry" <swm(at)alcove(dot)com(dot)au>, "PGSQL Hackers" <pgsql-hackers(at)postgresql(dot)org>, "Doug Rady" <drady(at)greenplum(dot)com>, "Sherry Moore" <sherry(dot)moore(at)sun(dot)com>
Subject: Re: Bug: Buffer cache is not scan resistant
Date: 2007-03-05 18:44:51
Message-ID: C211A523.28788%llonergan@greenplum.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Here's four more points on the curve - I'd use a "dirac delta function" for
your curve fit ;-)

Shared_buffers Select Count Vacuum
(KB) (s) (s)
=======================================
248 5.52 2.46
368 4.77 2.40
552 5.82 2.40
824 6.20 2.43
1232 5.60 3.59
1848 6.02 3.14
2768 5.53 4.56
5536 6.05 3.95
8304 5.80 4.37
12456 5.86 4.12
18680 5.83 4.10
28016 6.11 4.46

WRT what you found on the selection algorithm, it might also explain the L2
effects I think.

I'm also still of the opinion that polluting the shared buffer cache for a
seq scan does not make sense.

- Luke

On 3/5/07 10:21 AM, "Luke Lonergan" <llonergan(at)greenplum(dot)com> wrote:

> Tom,
>
> On 3/5/07 8:53 AM, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Hm, that seems to blow the "it's an L2 cache effect" theory out of the
>> water. If it were a cache effect then there should be a performance
>> cliff at the point where the cache size is exceeded. I see no such
>> cliff, in fact the middle part of the curve is darn near a straight
>> line on a log scale ...
>
> Here's that cliff you were looking for:
>
> Size of Orders table: 7178MB
> Blocksize: 8KB
>
> Shared_buffers Select Count Vacuum
> (KB) (s) (s)
> =======================================
> 248 5.52 2.46
> 368 4.77 2.40
> 552 5.82 2.40
> 824 6.20 2.43
> 1232 5.60 3.59
> 1848 6.02 3.14
> 2768 5.53 4.56
>
> All of these were run three times and the *lowest* time reported. Also, the
> behavior of "fast VACUUM after SELECT" begins abruptly at 1232KB of
> shared_buffers.
>
> These are Opterons with 2MB of L2 cache shared between two cores.
>
> - Luke
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Pavan Deolasee 2007-03-05 18:46:15 Re: Bug: Buffer cache is not scan resistant
Previous Message Jeff Davis 2007-03-05 18:39:34 Re: Synchronized Scan update