From: Tom Lane <tgl@sss.pgh.pa.us>
To: Gurjeet Singh <gurjeet@singh.im>
Cc: PostgreSQL Docs <pgsql-docs@postgresql.org>
Cc: PGSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: synchronize_seqscans' description is a bit misleading
Date: Wed, 10 Apr 2013 23:10:05 -0400
Message-ID: <18280.1365649805@sss.pgh.pa.us> (raw)
In-Reply-To: <CABwTF4VwxS+jjT2RZSzHny5LArW+jFjFn5uiGH8cTRCXETGNag@mail.gmail.com>
References: <CABwTF4VwxS+jjT2RZSzHny5LArW+jFjFn5uiGH8cTRCXETGNag@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-docs>
Gurjeet Singh <gurjeet@singh.im> writes:
> If I'm reading the code right [1], this GUC does not actually *synchronize*
> the scans, but instead just makes sure that a new scan starts from a block
> that was reported by some other backend performing a scan on the same
> relation.
Well, that's the only *direct* effect, but ...
> Since the backends scanning the relation may be processing the relation at
> different speeds, even though each one took the hint when starting the
> scan, they may end up being out of sync with each other.
The point you're missing is that the synchronization is self-enforcing:
whichever backend gets ahead of the others will be the one forced to
request (and wait for) the next physical I/O. This will naturally slow
down the lower-CPU-cost-per-page scans. The other ones tend to catch up
during the I/O operation.
The feature is not terribly useful unless I/O costs are high compared to
the CPU cost-per-page. But when that is true, it's actually rather
robust. Backends don't have to have exactly the same per-page
processing cost, because pages stay in shared buffers for a while after
the current scan leader reads them.
> Imagining that all scans on a table are always synchronized, may make some
> wrongly believe that adding more backends scanning the same table will not
> incur any extra I/O; that is, only one stream of blocks will be read from
> disk no matter how many backends you add to the mix. I noticed this when I
> was creating partition tables, and each of those was a CREATE TABLE AS
> SELECT FROM original_table (to avoid WAL generation), and running more than
> 3 such transactions caused the disk read throughput to behave unpredictably,
> sometimes even dipping below 1 MB/s for a few seconds at a stretch.
It's not really the scans that's causing that to be unpredictable, it's
the write I/O from the output side, which is forcing highly
nonsequential behavior (or at least I suspect so ... how many disk units
were involved in this test?)
regards, tom lane
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: tgl@sss.pgh.pa.us, gurjeet@singh.im, pgsql-hackers@postgresql.org
Subject: Re: synchronize_seqscans' description is a bit misleading
In-Reply-To: <18280.1365649805@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox