Thread: about fsync in CLOG buffer write

On 2015-09-10 19:39:59 +0800, 张广舟(明虚) wrote:
> We found there is a fsync call when CLOG buffer
> is written out in SlruPhysicalWritePage(). It is often called when a backend
> needs to check transaction status with SimpleLruReadPage().

That's when there's not enough buffers available some other, and your
case dirty, needs to be written out.

You could try increasing the max (32) in CLOGShmemBuffers() further.

>  ctl->do_fsync is true for CLOG.  Question is, could we just disable fsync
> for CLOG buffer write out here? Is it safe to do so? I understand a
> checkpoint will calls SimpleLruFlush to flush all CLOG buffer at once, and
> the fsync call here (for buffer write out) is not necessary.

No, that'd not be safe. The reason we fsync in SlruPhysicalWritePage()
is that the flush during checkpoint won't necessarily touch those files
at all (as they've been replaced with other buffers in memory).

This could be optimized though - it should be possible to delay the
fsync for slru files that have been evicted from memory till
checkpoint. Using something like ForwardFsyncRequest() except that it
obviously has to be usable for other files than normal relfilenodes.

Greetings,

Andres Freund

Andres,

Thanks for the reply!

I will try the ForwardFsyncRequest-like approach.

在 15-9-2 下午8:32， "Andres Freund" <andres@anarazel.de> 写入:

>On 2015-09-10 19:39:59 +0800, 张广舟(明虚) wrote:
>> We found there is a fsync call when CLOG buffer
>> is written out in SlruPhysicalWritePage(). It is often called when a
>>backend
>> needs to check transaction status with SimpleLruReadPage().
>
>That's when there's not enough buffers available some other, and your
>case dirty, needs to be written out.
>
>You could try increasing the max (32) in CLOGShmemBuffers() further.
>
>>  ctl->do_fsync is true for CLOG.  Question is, could we just disable
>>fsync
>> for CLOG buffer write out here? Is it safe to do so? I understand a
>> checkpoint will calls SimpleLruFlush to flush all CLOG buffer at once,
>>and
>> the fsync call here (for buffer write out) is not necessary.
>
>No, that'd not be safe. The reason we fsync in SlruPhysicalWritePage()
>is that the flush during checkpoint won't necessarily touch those files
>at all (as they've been replaced with other buffers in memory).
>
>This could be optimized though - it should be possible to delay the
>fsync for slru files that have been evicted from memory till
>checkpoint. Using something like ForwardFsyncRequest() except that it
>obviously has to be usable for other files than normal relfilenodes.
>
>Greetings,
>
>Andres Freund

Hi,

On 2015-09-08 15:58:26 +0800, 周正中(德歌) wrote:
> postgres@digoal-> cat 7.sql
> select txid_current();
>
> postgres@digoal-> pgbench -M prepared -n -r -P 1 -f ./7.sql -c 1 -j 1 -T 100000
> About 32K tps.
> progress: 240.0 s, 31164.4 tps, lat 0.031 ms stddev 0.183
> progress: 241.0 s, 33243.3 tps, lat 0.029 ms stddev 0.127

So you're benchmarking how fast you can assign txids. Is that actually
something meaningful? If you have other writes interleaved you'll write
much more WAL, so there'll be checkpoints and such.

FWIW, if you measure something realistic and there's checkpoints,
there'll be fewer fsyncs if you increase the slru buffer size - as
there'll often be clean buffers due to the checkpoint having written
them out.

Greetings,

Andres Freund

On Tue, Sep 8, 2015 at 2:28 PM, Andres Freund <andres@anarazel.de> wrote:
> On 2015-09-08 15:58:26 +0800, 周正中(德歌) wrote:
>> postgres@digoal-> cat 7.sql
>> select txid_current();
>>
>> postgres@digoal-> pgbench -M prepared -n -r -P 1 -f ./7.sql -c 1 -j 1 -T 100000
>> About 32K tps.
>> progress: 240.0 s, 31164.4 tps, lat 0.031 ms stddev 0.183
>> progress: 241.0 s, 33243.3 tps, lat 0.029 ms stddev 0.127
>
> So you're benchmarking how fast you can assign txids. Is that actually
> something meaningful? If you have other writes interleaved you'll write
> much more WAL, so there'll be checkpoints and such.
>
> FWIW, if you measure something realistic and there's checkpoints,
> there'll be fewer fsyncs if you increase the slru buffer size - as
> there'll often be clean buffers due to the checkpoint having written
> them out.

But I think it's not very hard to come up with a workload where 32
clog buffers isn't enough, and you end up waiting for backends to
fsync().  Suppose you have a pgbench workload with N tuples.  We
update tuples at random, so sometimes we hit one that's just recently
been updated, and other times we hit one that hasn't been updated for
many transactions.  At 32k transactions per page, each transaction's
chance of updating a tuple whose existing xmin is on the most recent
clog page is 32k/N.  The chance of hitting a tuple whose existing xmin
is on the next page back is (1 - 32k/N) * 32k/N.  The chance of
hitting a page whose current xmin is at least X pages prior to the
most recent one is (1 - 32k/N)^X.  So, how many tuples do we need in
order for each update to have a 50% chance of hitting a tuple that is
at least 32 pages back?

(1 - 32k/N)^32 = .5
1 - 32k/N = 0.9785720620877
32k/N = 0.0214279379122999
N = 32k/0.0214279379122999
N = 1529218.54329206

...or in other words, scale factor 16.  At scale factors >= 1044, the
chance that the next update hits an xmin more than 32 clog buffers
back is > 99%.  So any test of this sort causes extremely rapid clog
page eviction - basically every transaction is going to request a
buffer that's probably not cached, and as soon as it's done with it,
some other transaction will evict it to bring in a different buffer
that's not cached.

How often such a workload actually has to replace a *dirty* clog
buffer obviously depends on how often you checkpoint, but if you're
getting ~28k TPS you can completely fill 32 clog buffers (1 million
transactions) in less than 40 seconds, and you're probably not
checkpointing nearly that often.

I'm not entirely sure how much fsync absorption for SLRU buffers will
help, but I think it would be worth trying.  Back when I was spending
more time on this area, I saw some evidence that those fsyncs did
cause at least some latency spikes.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:
> ... How often such a workload actually has to replace a *dirty* clog
> buffer obviously depends on how often you checkpoint, but if you're
> getting ~28k TPS you can completely fill 32 clog buffers (1 million
> transactions) in less than 40 seconds, and you're probably not
> checkpointing nearly that often.

But by the same token, at that kind of transaction rate, no clog page is
actively getting dirtied for more than a couple of seconds.  So while it
might get swapped in and out of the SLRU arena pretty often after that,
this scenario seems unconvincing as a source of repeated fsyncs.

Like Andres, I'd want to see a more realistic problem case before
expending a lot of work here.

            regards, tom lane

On Wed, Sep 9, 2015 at 10:35 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> ... How often such a workload actually has to replace a *dirty* clog
>> buffer obviously depends on how often you checkpoint, but if you're
>> getting ~28k TPS you can completely fill 32 clog buffers (1 million
>> transactions) in less than 40 seconds, and you're probably not
>> checkpointing nearly that often.
>
> But by the same token, at that kind of transaction rate, no clog page is
> actively getting dirtied for more than a couple of seconds.  So while it
> might get swapped in and out of the SLRU arena pretty often after that,
> this scenario seems unconvincing as a source of repeated fsyncs.

Well, if you're filling ~1 clog page per second, you're doing ~1 fsync
per second too.  Or if you are not, then you are thrashing the
progressively smaller and smaller number of clean slots ever-harder
until no clean pages remain and you're forced to fsync something -
probably, a bunch of things all at once.

> Like Andres, I'd want to see a more realistic problem case before
> expending a lot of work here.

I think the question here isn't whether the problem case is realistic
- I am quite sure that a pgbench workload is - but rather how much of
a problem it actually causes.  I'm very sure that individual pgbench
backends experience multi-second stallls as a result of this.  What
I'm not sure about is how frequently it happens, and how much of an
effect it has on overall latency.  I think it would be worth someone's
time to try to write some good instrumentation code here and figure
that out.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 2015-09-09 10:46:47 -0400, Robert Haas wrote:
> Well, if you're filling ~1 clog page per second, you're doing ~1 fsync
> per second too.  Or if you are not, then you are thrashing the
> progressively smaller and smaller number of clean slots ever-harder
> until no clean pages remain and you're forced to fsync something -
> probably, a bunch of things all at once.

And you're also triggering a lot of other fsyncs. At the very least
you're pretty constantly flushing the WAL, even with synchronous_commit
= off.

> > Like Andres, I'd want to see a more realistic problem case before
> > expending a lot of work here.
>
> I think the question here isn't whether the problem case is realistic
> - I am quite sure that a pgbench workload is - but rather how much of
> a problem it actually causes.  I'm very sure that individual pgbench
> backends experience multi-second stallls as a result of this.

The fsync causes that? That seems odd. I mean we release the slru
control lock for writing out pages, don't we?

To me it seems the fsyncs are a red herring here, and the problems are,
uh, much bigger. ISTM that cache replacement locking granularity et al
have a much bigger effect than a fsync every now and then.

Greetings,

Andres Freund

On September 12, 2015 5:18:28 PM PDT, Jeff Janes <jeff.janes@gmail.com> wrote:
>On Wed, Sep 2, 2015 at 5:32 AM, Andres Freund <andres@anarazel.de>
>wrote:
>
>> On 2015-09-10 19:39:59 +0800, 张广舟(明虚) wrote:
>> > We found there is a fsync call when CLOG buffer
>> > is written out in SlruPhysicalWritePage(). It is often called when
>a
>> backend
>> > needs to check transaction status with SimpleLruReadPage().
>>
>> That's when there's not enough buffers available some other, and your
>> case dirty, needs to be written out.
>>
>
>Why bother to find a place to store the page in shared memory at all? 
>If
>we just want to read it, and it isn't already in shared memory, then
>why
>not just ask the kernel for the specific byte we need?  The byte we
>want to
>read can't differ between shared memory and kernel, because it doesn't
>exist in shared memory.

I doubt that'd help - the next access would be more expensive, and we'd need to have a more complex locking regime.
Thesepages aren't necessarily read only at that point.
 

Andres

--- 
Please excuse brevity and formatting - I am writing this on my mobile phone.

On 2015-10-04 12:14:05 -0700, Jeff Janes wrote:
> My (naive) expectation is that no additional locking is needed.
> 
> Once we decide to consult the clog, we already know the transaction is no
> longer in progress, so it can't be in-flight to change that clog entry we
> care about because it was required to have done that already.

Other xids on the same page can still be in progress and those
concurrently might need to be written to.

> Once we have verified (under existing locking) that the relevant page is
> already not in memory, we know it can't be dirty in memory.  If someone
> pulls it into memory after we observe it to be not there, it doesn't matter
> to us as whatever transaction they are about to change can't be the one we
> care about.

The read of the page from disk from a concurrent process might have been
before our write, i.e. containing an unmodified page, but now future
writes will overwrite the entry we wrote directly. I think there's a
bunch of related issues.

Such things will currently prevented by the IO locks in slru.c.

> Is there a chance that, if we read a byte from the kernel when someone is
> in the process of writing adjacent bytes (or writing the same byte, with
> changes only to bits in it which we don't care about), the kernel will
> deliver us something which is neither the old value nor the new value, but
> some monstrosity?

Depends on the granularity of the write/read and the OS IIRC.


I don't think it's worth investing time and complexity to bypass SLRU in
certain cases. We should rather rewrite the thing completely.


Greetings,

Andres Freund

On Sun, Oct 4, 2015 at 3:25 PM, Andres Freund <andres@anarazel.de> wrote:
> I don't think it's worth investing time and complexity to bypass SLRU in
> certain cases. We should rather rewrite the thing completely.

+1.  That code is considerably past its sell-by date.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company