Re: Synchronized scans versus relcache reinitialization

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: Synchronized scans versus relcache reinitialization
Date: 2012-05-31 02:19:38
Message-ID: 1338430778.14941.5.camel@jdavis
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Sat, 2012-05-26 at 15:14 -0400, Tom Lane wrote:
> 3. Having now spent a good deal of time poking at this, I think that the
> syncscan logic is in need of more tuning, and I am wondering whether we
> should even have it turned on by default. It appears to be totally
> useless for fully-cached-in-RAM scenarios, even if most of the relation
> is out in kernel buffers rather than in shared buffers. The best case
> I saw was less than 2X speedup compared to N-times-the-single-client
> case, and that wasn't very reproducible, and it didn't happen at all
> unless I hacked BAS_BULKREAD mode to use a ring buffer size many times
> larger than the current 256K setting (otherwise the timing requirements
> are too tight for multiple backends to stay in sync --- a seqscan can
> blow through that much data in a fraction of a millisecond these days,
> if it's reading from kernel buffers). The current tuning may be all
> right for cases where you're actually reading from spinning rust, but
> that seems to be a decreasing fraction of real-world use cases.

Do you mean that the best case you saw ever was 2X, or the best case
when the table is mostly in kernel buffers was 2X?

I clearly saw better than 2X when the table was on disk, so if you
aren't, we should investigate.

One thing we could do is drive the threshold from effective_cache_size
rather than shared_buffers, which was discussed during 8.3 development.

Regards,
Jeff Davis

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2012-05-31 02:49:56 Re: Synchronized scans versus relcache reinitialization
Previous Message Jeff Janes 2012-05-31 02:19:02 Re: 9.2beta1, parallel queries, ReleasePredicateLocks, CheckForSerializableConflictIn in the oprofile