Improvement of checkpoint IO scheduler for stable transaction responses

On 10 June 2013 11:51, KONDO Mitsumasa <kondo.mitsumasa@lab.ntt.co.jp> wrote:

I create patch which is improvement of checkpoint IO scheduler for stable
transaction responses.

Looks like good results, with good measurements. Should be an
interesting discussion.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Jun 10, 2013 at 3:48 PM, Simon Riggs <simon@2ndquadrant.com> wrote:

On 10 June 2013 11:51, KONDO Mitsumasa <kondo.mitsumasa@lab.ntt.co.jp> wrote:

I create patch which is improvement of checkpoint IO scheduler for stable
transaction responses.

Looks like good results, with good measurements. Should be an
interesting discussion.

+1.

I suspect we want to poke at the algorithms a little here and maybe
see if we can do this without adding new GUCs. Also, I think this is
probably two separate patches, in the end. But the direction seems
good to me.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

(2013/06/12 23:07), Robert Haas wrote:

On Mon, Jun 10, 2013 at 3:48 PM, Simon Riggs <simon@2ndquadrant.com> wrote:

On 10 June 2013 11:51, KONDO Mitsumasa <kondo.mitsumasa@lab.ntt.co.jp> wrote:

I create patch which is improvement of checkpoint IO scheduler for stable
transaction responses.

Looks like good results, with good measurements. Should be an
interesting discussion.

+1.

I suspect we want to poke at the algorithms a little here and maybe
see if we can do this without adding new GUCs. Also, I think this is
probably two separate patches, in the end. But the direction seems
good to me.

Thank you for comment!

I separate my patch in checkpoint-wirte and in checkpoint-fsync. As you
say, my patch has a lot of new GUCs. I don't think it cannot be decided
automatic. However, it is difficult that chekpoint-scheduler is suitable
for all of enviroments which are like virtual server, public cloude server,
and embedded server, etc. So I think that default setting parameter works
same as before. Setting parameter is primitive and difficult, but if we can
set correctly, it is suitable for a lot of enviroments and will not work
unintended action.

I try to take something into consideration about less GUCs version. And if you
have good idea, please discussion about this!

Best Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

On 10.06.2013 13:51, KONDO Mitsumasa wrote:

I create patch which is improvement of checkpoint IO scheduler for
stable transaction responses.

* Problem in checkpoint IO schedule in heavy transaction case
When heavy transaction in database, I think PostgreSQL checkpoint
scheduler has two problems at start and end of checkpoint. One problem
is IO heavy when starting initial checkpoint in rounds of checkpoint.
This problem was caused by full-page-write which cause WAL IO in fast
page writes after checkpoint write page. Therefore, when starting
checkpoint, WAL-based checkpoint scheduler wrong judgment that is late
schedule by full-page-write, nevertheless checkpoint schedule is not
late. This is caused bad transaction response. I think WAL-based
checkpoint scheduler was not property in starting checkpoint.

Yeah, the checkpoint scheduling logic doesn't take into account the
heavy WAL activity caused by full page images. That's an interesting
phenomenon, but did you actually see that causing a problem in your
tests? I couldn't tell from the results you posted what the impact of
that was. Could you repeat the tests separately with the two separate
patches you posted later in this thread?

Rationalizing a bit, I could even argue to myself that it's a *good*
thing. At the beginning of a checkpoint, the OS write cache should be
relatively empty, as the checkpointer hasn't done any writes yet. So it
might make sense to write a burst of pages at the beginning, to
partially fill the write cache first, before starting to throttle. But
this is just handwaving - I have no idea what the effect is in real life.

Another thought is that rather than trying to compensate for that effect
in the checkpoint scheduler, could we avoid the sudden rush of full-page
images in the first place? The current rule for when to write a full
page image is conservative: you don't actually need to write a full page
image when you modify a buffer that's sitting in the buffer cache, if
that buffer hasn't been flushed to disk by the checkpointer yet, because
the checkpointer will write and fsync it later. I'm not sure how much it
would smoothen WAL write I/O, but it would be interesting to try.

Second problem is fsync freeze problem in end of checkpoint.
Normally, checkpoint write is executed in background by OS's IO
scheduler. But when it does not correctly work, end of checkpoint
fsync was caused IO freeze and slower transactions. Unexpected slow
transaction will cause monitor error in HA-cluster and decrease
user-experience in application service. It is especially serious
problem in cloud and virtual server database system which does not
have IO performance. However we don't have solution in
postgresql.conf parameter very much. We prefer checkpoint time to
fast response transactions. In fact checkpoint time is short, and it
becomes little bit long that is not problem. You may think that
checkpoint_segments and checkpoint_timeout are set larger value,
however large checkpoint_segments affects file-cache which is not
read and is wasted, and large checkpoint_timeout was caused
long-time crash-recovery.

A long time ago, Itagaki wrote a patch to sort the checkpoint writes:
www.postgresql.org/message-id/flat/20070614153758.6A62.ITAGAKI.TAKAHIRO@oss.ntt.co.jp.
He posted very promising performance numbers, but it was dropped because
Tom couldn't reproduce the numbers, and because sorting requires
allocating a large array, which has the risk of running out of memory,
which would be bad when you're trying to checkpoint.

Apart from the direct performance impact of that patch, sorting the
writes would allow us to interleave the fsyncs with the writes. You
would write out all buffers for relation A, then fsync it, then all
buffers for relation B, then fsync it, and so forth. That would
naturally spread out the fsyncs.

If we don't mind scanning the buffer cache several times, we don't
necessarily even need to sort the writes for that. Just scan the buffer
cache for all buffers belonging to relation A, then fsync it. Then scan
the buffer cache again, for all buffers belonging to relation B, then
fsync that, and so forth.

Bad point of my patch is longer checkpoint. Checkpoint time was
increased about 10% - 20%. But it can work correctry on schedule-time in
checkpoint_timeout. Please see checkpoint result (http://goo.gl/NsbC6).

For a fair comparison, you should increase the
checkpoint_completion_target of the unpatched test, so that the
checkpoints run for roughly the same amount of time with and without the
patch. Otherwise the benefit you're seeing could be just because of a
more lazy checkpoint.

- Heikki

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2013-06-16 17:27:56 +0300, Heikki Linnakangas wrote:

Another thought is that rather than trying to compensate for that effect in
the checkpoint scheduler, could we avoid the sudden rush of full-page images
in the first place? The current rule for when to write a full page image is
conservative: you don't actually need to write a full page image when you
modify a buffer that's sitting in the buffer cache, if that buffer hasn't
been flushed to disk by the checkpointer yet, because the checkpointer will
write and fsync it later. I'm not sure how much it would smoothen WAL write
I/O, but it would be interesting to try.

Hm. Could you elaborate why that wouldn't open new hazards? I don't see
how that could be safe against crashes in some places. It seems to me
we could end up replaying records like heap_insert or similar pages
while the page is still torn?

A long time ago, Itagaki wrote a patch to sort the checkpoint writes: www.postgresql.org/message-id/flat/20070614153758.6A62.ITAGAKI.TAKAHIRO@oss.ntt.co.jp.
He posted very promising performance numbers, but it was dropped because Tom
couldn't reproduce the numbers, and because sorting requires allocating a
large array, which has the risk of running out of memory, which would be bad
when you're trying to checkpoint.

Hm. We could allocate the array early on since the number of buffers
doesn't change. Sure that would be pessimistic, but that seems fine.

Alternatively I can very well imagine that it would still be beneficial
to sort the dirty buffers in shared buffers. I.e. scan till we found 50k
dirty pages, sort them and only then write them out.

Apart from the direct performance impact of that patch, sorting the writes
would allow us to interleave the fsyncs with the writes. You would write out
all buffers for relation A, then fsync it, then all buffers for relation B,
then fsync it, and so forth. That would naturally spread out the
fsyncs.

I personally think that optionally trying to force the pages to be
written out earlier (say, with sync_file_range) to make the actual
fsync() lateron cheaper is likely to be better overall.

If we don't mind scanning the buffer cache several times, we don't
necessarily even need to sort the writes for that. Just scan the buffer
cache for all buffers belonging to relation A, then fsync it. Then scan the
buffer cache again, for all buffers belonging to relation B, then fsync
that, and so forth.

That would end up with quite a lot of scans in a reasonably sized
machines. Not to talk of those that have a million+ relations. That
doesn't seem to be a good idea for bigger shared_buffers. C.f. the stuff
we did for 9.3 to make it cheaper to drop a bunch of relations at once
by only scanning shared_buffers once.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Thank you for giving comments and my patch reviewer!

(2013/06/16 23:27), Heikki Linnakangas wrote:

On 10.06.2013 13:51, KONDO Mitsumasa wrote:

I create patch which is improvement of checkpoint IO scheduler for
stable transaction responses.

* Problem in checkpoint IO schedule in heavy transaction case
When heavy transaction in database, I think PostgreSQL checkpoint
scheduler has two problems at start and end of checkpoint. One problem
is IO heavy when starting initial checkpoint in rounds of checkpoint.
This problem was caused by full-page-write which cause WAL IO in fast
page writes after checkpoint write page. Therefore, when starting
checkpoint, WAL-based checkpoint scheduler wrong judgment that is late
schedule by full-page-write, nevertheless checkpoint schedule is not
late. This is caused bad transaction response. I think WAL-based
checkpoint scheduler was not property in starting checkpoint.

Yeah, the checkpoint scheduling logic doesn't take into account the heavy WAL
activity caused by full page images. That's an interesting phenomenon, but did
you actually see that causing a problem in your tests? I couldn't tell from the
results you posted what the impact of that was. Could you repeat the tests
separately with the two separate patches you posted later in this thread?

OK, I try to test with the two separate patches. My patches results which I send past
indicate high WAL throughputs(write_size_per_sec) and high transaction during
checkpoint. Please see
under following HTML file which I set tag jump, and put 'checkpoint highlight
switch' button.

* With my patched PG
http://pgstatsinfo.projects.pgfoundry.org/dbt2_result/report/patchedPG-report.html#transaction_statistics
http://pgstatsinfo.projects.pgfoundry.org/dbt2_result/report/patchedPG-report.html#wal_statistics

* Plain PG
http://pgstatsinfo.projects.pgfoundry.org/dbt2_result/report/plainPG-report.html#transaction_statistics
http://pgstatsinfo.projects.pgfoundry.org/dbt2_result/report/plainPG-report.html#wal_statistics

In wal statistics result, I think that high WAL thorouputs in checkpoint starting
indicates that checkpoint IO does not disturb other executing transaction IO.

Rationalizing a bit, I could even argue to myself that it's a *good* thing. At
the beginning of a checkpoint, the OS write cache should be relatively empty, as
the checkpointer hasn't done any writes yet. So it might make sense to write a
burst of pages at the beginning, to partially fill the write cache first, before
starting to throttle. But this is just handwaving - I have no idea what the
effect is in real life.

Yes, I think so. If we want to change IO throttle, we change OS parameter which
are '/proc/sys/vm/dirty_background_ratio' or '/proc/sys/vm/dirty_ratio'. But this
parameter effects whole applications in OS, it is difficult to change this
parameter and cannot set intuitive parameter. And I think that database tuning
should be set in database parameter rather than OS parameter. It is more clear in
tuning a server.

Another thought is that rather than trying to compensate for that effect in the
checkpoint scheduler, could we avoid the sudden rush of full-page images in the
first place? The current rule for when to write a full page image is
conservative: you don't actually need to write a full page image when you modify
a buffer that's sitting in the buffer cache, if that buffer hasn't been flushed
to disk by the checkpointer yet, because the checkpointer will write and fsync it
later. I'm not sure how much it would smoothen WAL write I/O, but it would be
interesting to try.

It is most right method in ideal implementations. But I don't have any idea about
this method. It seems very difficult...

Second problem is fsync freeze problem in end of checkpoint.
Normally, checkpoint write is executed in background by OS's IO
scheduler. But when it does not correctly work, end of checkpoint
fsync was caused IO freeze and slower transactions. Unexpected slow
transaction will cause monitor error in HA-cluster and decrease
user-experience in application service. It is especially serious
problem in cloud and virtual server database system which does not
have IO performance. However we don't have solution in
postgresql.conf parameter very much. We prefer checkpoint time to
fast response transactions. In fact checkpoint time is short, and it
becomes little bit long that is not problem. You may think that
checkpoint_segments and checkpoint_timeout are set larger value,
however large checkpoint_segments affects file-cache which is not
read and is wasted, and large checkpoint_timeout was caused
long-time crash-recovery.

A long time ago, Itagaki wrote a patch to sort the checkpoint writes:
www.postgresql.org/message-id/flat/20070614153758.6A62.ITAGAKI.TAKAHIRO@oss.ntt.co.jp.
He posted very promising performance numbers, but it was dropped because Tom
couldn't reproduce the numbers, and because sorting requires allocating a large
array, which has the risk of running out of memory, which would be bad when
you're trying to checkpoint.

Yes, we tested Itagaki's patche last year. But our test results is not good. I
think that our test server's RAID contoroler with 1GB cache and 8 disks was too
good to indicate good results. Write IO might be eventually optimized in RAID
contoroler which has big chache.

Apart from the direct performance impact of that patch, sorting the writes would
allow us to interleave the fsyncs with the writes. You would write out all
buffers for relation A, then fsync it, then all buffers for relation B, then
fsync it, and so forth. That would naturally spread out the fsyncs.

If we don't mind scanning the buffer cache several times, we don't necessarily
even need to sort the writes for that. Just scan the buffer cache for all buffers
belonging to relation A, then fsync it. Then scan the buffer cache again, for all
buffers belonging to relation B, then fsync that, and so forth.

Yes. But I don't think that it needs *exactly* buffer sort. It needs roughly
buffer sort only for interleving the fsyncs with the writes. Roughly buffer sort
reduce computational complexity which was said by Tom, and it will be optimized
in OS IO scheduler as same as exactly buffer sort. My roughly buffer sort images
are clustering like k-means. If we can know distribution of buffers in advance,
we will be able to realize roughly buffer sort with less computational complexity.

Bad point of my patch is longer checkpoint. Checkpoint time was
increased about 10% - 20%. But it can work correctry on schedule-time in
checkpoint_timeout. Please see checkpoint result (http://goo.gl/NsbC6).

For a fair comparison, you should increase the checkpoint_completion_target of
the unpatched test, so that the checkpoints run for roughly the same amount of
time with and without the patch. Otherwise the benefit you're seeing could be
just because of a more lazy checkpoint.

It is important to understand other contributer, I need more fair comparison and
an objective analysis. Thanks for your advice, I try it!

Best regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Jun 17, 2013 at 2:18 AM, Andres Freund <andres@2ndquadrant.com>wrote:

On 2013-06-16 17:27:56 +0300, Heikki Linnakangas wrote:

A long time ago, Itagaki wrote a patch to sort the checkpoint writes:

www.postgresql.org/message-id/flat/20070614153758.6A62.ITAGAKI.TAKAHIRO@oss.ntt.co.jp
.

He posted very promising performance numbers, but it was dropped because

Tom

couldn't reproduce the numbers, and because sorting requires allocating a
large array, which has the risk of running out of memory, which would be

bad

when you're trying to checkpoint.

Hm. We could allocate the array early on since the number of buffers
doesn't change. Sure that would be pessimistic, but that seems fine.

Alternatively I can very well imagine that it would still be beneficial
to sort the dirty buffers in shared buffers. I.e. scan till we found 50k
dirty pages, sort them and only then write them out.

Without knowing that Itagaki had done something similar in the past, couple
of months back I tried exactly the same thing i.e. sort the shared buffers
in chunks and then write them out at once. But I did not get any
significant performance gain except when the shared buffers are 3/4th (or
some such number) or more than the available RAM. I will see if I can pull
out the patch and the numbers. But if memory serves well, I concluded that
the kernel is already utilising its buffer cache to achieve the same thing
and it does not help beyond a point.

Thanks,
Pavan

--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee

(2013/06/17 5:48), Andres Freund wrote:> On 2013-06-16 17:27:56 +0300, Heikki
Linnakangas wrote:

If we don't mind scanning the buffer cache several times, we don't
necessarily even need to sort the writes for that. Just scan the buffer
cache for all buffers belonging to relation A, then fsync it. Then scan the
buffer cache again, for all buffers belonging to relation B, then fsync
that, and so forth.

That would end up with quite a lot of scans in a reasonably sized
machines. Not to talk of those that have a million+ relations. That
doesn't seem to be a good idea for bigger shared_buffers. C.f. the stuff
we did for 9.3 to make it cheaper to drop a bunch of relations at once
by only scanning shared_buffers once.

As I written to reply to Heikki, I think that it is unnecessary to exactly buffer
sort which has expensive cost. What we need to solve this problem, we need
accuracy of sort which can be optimized in OS IO scheduler. And we normally have
two optimized IO scheduler layer which are OS layer and RAID controller layer. I
think that performance will be improved if it enables sort accuracy to optimize
in these process. I think that computational complexity required to solve this
problem is one sequential buffer descriptor scan for roughly buffer sort. I will
try to study about this implementation, too.

Best regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hi,

I took results of my separate patches and original PG.

* Result of DBT-2
| TPS 90%tile Average Maximum
------------------------------------------------------
original_0.7 | 3474.62 18.348328 5.739 36.977713
original_1.0 | 3469.03 18.637865 5.842 41.754421
fsync | 3525.03 13.872711 5.382 28.062947
write | 3465.96 19.653667 5.804 40.664066
fsync + write | 3564.94 16.31922 5.1 34.530766

- 'original_*' indicates checkpoint_completion_target in PG 9.2.4.
- In other patched postgres, checkpoint_completion_target sets 0.7.
- 'write' is applied write patch, and 'fsync' is applied fsync patch.
- 'fsync + write' is applied both patches.

* Investigation of result
- Large value of checkpoint_completion_target in original and the patch in
write become slow latency in benchmark transactions. Because slow write pages are
caused long time fsync IO in final checkpoint.
- The patch in fsync has an effect latency in each file fsync. Continued
fsyncsin each files are caused slow latency. Therefore, it is good for latency
that fsync stage in checkpoint has sleeping time after slow fsync IO.
- The patches of fsync + write were seemed to improve TPS. I think that write
patch does not disturb transactions which are in full-page-write WAL write than
original(plain) PG.

I will send you more detail investigation and result next week. And I will also
take result in pgbench. If you mind other part of benchmark result or parameter
of postgres, please tell me.

Best Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 21.06.2013 11:29, KONDO Mitsumasa wrote:

I took results of my separate patches and original PG.

* Result of DBT-2
| TPS 90%tile Average Maximum
------------------------------------------------------
original_0.7 | 3474.62 18.348328 5.739 36.977713
original_1.0 | 3469.03 18.637865 5.842 41.754421
fsync | 3525.03 13.872711 5.382 28.062947
write | 3465.96 19.653667 5.804 40.664066
fsync + write | 3564.94 16.31922 5.1 34.530766

- 'original_*' indicates checkpoint_completion_target in PG 9.2.4.
- In other patched postgres, checkpoint_completion_target sets 0.7.
- 'write' is applied write patch, and 'fsync' is applied fsync patch.
- 'fsync + write' is applied both patches.

* Investigation of result
- Large value of checkpoint_completion_target in original and the patch
in write become slow latency in benchmark transactions. Because slow
write pages are caused long time fsync IO in final checkpoint.
- The patch in fsync has an effect latency in each file fsync. Continued
fsyncsin each files are caused slow latency. Therefore, it is good for
latency that fsync stage in checkpoint has sleeping time after slow
fsync IO.
- The patches of fsync + write were seemed to improve TPS. I think that
write patch does not disturb transactions which are in full-page-write
WAL write than original(plain) PG.

Hmm, so the write patch doesn't do much, but the fsync patch makes the
response times somewhat smoother. I'd suggest that we drop the write
patch for now, and focus on the fsyncs.

What checkpointer_fsync_delay_ratio and
checkpointer_fsync_delay_threshold settings did you use with the fsync
patch? It's disabled by default.

This is the interesting part of the patch:

@@ -1171,6 +1174,20 @@ mdsync(void)
FilePathName(seg->mdfd_vfd),
(double) elapsed / 1000);

+                                               /*
+                                                * If this fsync has long time, we sleep 'fsync-time * checkpoint_fsync_delay_ratio'
+                                                * for giving priority to executing transaction.
+                                                */
+                                               if( CheckPointerFsyncDelayThreshold >= 0 &&
+                                                       !shutdown_requested &&
+                                                       !ImmediateCheckpointRequested() &&
+                                                       (elapsed / 1000 > CheckPointerFsyncDelayThreshold))
+                                               {
+                                                       pg_usleep((elapsed / 1000) * CheckPointerFsyncDelayRatio * 1000L);
+                                                       if(log_checkpoints)
+                                                               elog(DEBUG1, "checkpoint sync sleep: time=%.3f msec",
+                                                                       (double) (elapsed / 1000) * CheckPointerFsyncDelayRatio);
+                                               }
break;  /* out of retry loop */
}

I'm not sure it's a good idea to sleep proportionally to the time it
took to complete the previous fsync. If you have a 1GB cache in the RAID
controller, fsyncing the a 1GB segment will fill it up. But since it
fits in cache, it will return immediately. So we proceed fsyncing other
files, until the cache is full and the fsync blocks. But once we fill up
the cache, it's likely that we're hurting concurrent queries. ISTM it
would be better to stay under that threshold, keeping the I/O system
busy, but never fill up the cache completely.

This is just a theory, though. I don't have a good grasp on how the OS
and a typical RAID controller behaves under these conditions.

I'd suggest that we just sleep for a small fixed amount of time between
every fsync, unless we're running behind the checkpoint schedule. And
for a first approximation, let's just assume that the fsync phase is e.g
10% of the whole checkpoint work.

I will send you more detail investigation and result next week. And I
will also take result in pgbench. If you mind other part of benchmark
result or parameter of postgres, please tell me.

Attached is a quick patch to implement a fixed, 100ms delay between
fsyncs, and the assumption that fsync phase is 10% of the total
checkpoint duration. I suspect 100ms is too small to have much effect,
but that happens to be what we have currently in CheckpointWriteDelay().
Could you test this patch along with yours? If you can test with
different delays (e.g 100ms, 500ms and 1000ms) and different ratios
between the write and fsync phase (e.g 0.5, 0.7, 0.9), to get an idea of
how sensitive the test case is to those settings.

- Heikki

On Tue, Jun 25, 2013 at 1:15 PM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:

I'm not sure it's a good idea to sleep proportionally to the time it took to
complete the previous fsync. If you have a 1GB cache in the RAID controller,
fsyncing the a 1GB segment will fill it up. But since it fits in cache, it
will return immediately. So we proceed fsyncing other files, until the cache
is full and the fsync blocks. But once we fill up the cache, it's likely
that we're hurting concurrent queries. ISTM it would be better to stay under
that threshold, keeping the I/O system busy, but never fill up the cache
completely.

Isn't the behavior implemented by the patch a reasonable approximation
of just that? When the fsyncs start to get slow, that's when we start
to sleep. I'll grant that it would be better to sleep when the
fsyncs are *about* to get slow, rather than when they actually have
become slow, but we have no way to know that. The only feedback we
have on how bad things are is how long it took the last fsync to
complete, so I actually think that's a much better way to go than any
fixed sleep - which will often be unnecessarily long on a well-behaved
system, and which will often be far too short on one that's having
trouble. I'm inclined to think think Kondo-san has got it right.

I like your idea of putting a stake in the ground and assuming that
the fsync phase will turn out to be X% of the checkpoint, but I wonder
if we can be a bit more sophisticated, especially for cases where
checkpoint_segments is small. When checkpoint_segments is large, then
we know that some of the data will get written back to disk during the
write phase, because the OS cache is only so big. But when it's
small, the OS will essentially do nothing during the write phase, and
then it's got to write all the data out during the fsync phase. I'm
not sure we can really model that effect thoroughly, but even
something dumb would be smarter than what we have now - e.g. use 10%,
but when checkpoint_segments < 10, use 1/checkpoint_segments. Or just
assume the fsync phase will take 30 seconds. Or ... something. I'm
not really sure what the right model is here.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 25.06.2013 23:03, Robert Haas wrote:

On Tue, Jun 25, 2013 at 1:15 PM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:

I'm not sure it's a good idea to sleep proportionally to the time it took to
complete the previous fsync. If you have a 1GB cache in the RAID controller,
fsyncing the a 1GB segment will fill it up. But since it fits in cache, it
will return immediately. So we proceed fsyncing other files, until the cache
is full and the fsync blocks. But once we fill up the cache, it's likely
that we're hurting concurrent queries. ISTM it would be better to stay under
that threshold, keeping the I/O system busy, but never fill up the cache
completely.

Isn't the behavior implemented by the patch a reasonable approximation
of just that? When the fsyncs start to get slow, that's when we start
to sleep. I'll grant that it would be better to sleep when the
fsyncs are *about* to get slow, rather than when they actually have
become slow, but we have no way to know that.

Well, that's the point I was trying to make: you should sleep *before*
the fsyncs get slow.

The only feedback we have on how bad things are is how long it took
the last fsync to complete, so I actually think that's a much better
way to go than any fixed sleep - which will often be unnecessarily
long on a well-behaved system, and which will often be far too short
on one that's having trouble. I'm inclined to think think Kondo-san
has got it right.

Quite possible, I really don't know. I'm inclined to first try the
simplest thing possible, and only make it more complicated if that's not
good enough. Kondo-san's patch wasn't very complicated, but nevertheless
a fixed sleep between every fsync, unless you're behind the schedule, is
even simpler. In particular, it's easier to tie that into the checkpoint
scheduler - I'm not sure how you'd measure progress or determine how
long to sleep unless you assume that every fsync is the same.

I like your idea of putting a stake in the ground and assuming that
the fsync phase will turn out to be X% of the checkpoint, but I wonder
if we can be a bit more sophisticated, especially for cases where
checkpoint_segments is small. When checkpoint_segments is large, then
we know that some of the data will get written back to disk during the
write phase, because the OS cache is only so big. But when it's
small, the OS will essentially do nothing during the write phase, and
then it's got to write all the data out during the fsync phase. I'm
not sure we can really model that effect thoroughly, but even
something dumb would be smarter than what we have now - e.g. use 10%,
but when checkpoint_segments< 10, use 1/checkpoint_segments. Or just
assume the fsync phase will take 30 seconds.

If checkpoint_segments < 10, there isn't very much dirty data to flush
out. This isn't really problem in that case - no matter how stupidly we
do the writing and fsyncing. the I/O cache can absorb it. It doesn't
really matter what we do in that case.

- Heikki

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Thank you for comments!

On Tue, Jun 25, 2013 at 1:15 PM, Heikki Linnakangas

Hmm, so the write patch doesn't do much, but the fsync patch makes the response
times somewhat smoother. I'd suggest that we drop the write patch for now, and
focus on the fsyncs.

Write patch is effective in TPS! I think that delay of checkpoint write is caused
long time fsync and heavy load in fsync phase. Because it go slow disk right in write
phase. Therefore, combination of write patch and fsync patch are suiter each
other than
only write patch. I think that amount of WAL write in beginning of checkpoint can
indicate effect of write patch.

What checkpointer_fsync_delay_ratio and checkpointer_fsync_delay_threshold
settings did you use with the fsync patch? It's disabled by default.

I used these parameters.
checkpointer_fsync_delay_ratio = 1
checkpointer_fsync_delay_threshold = 1000ms
As a matter of fact, I used long time sleep in slow fsyncs.

And other maintains parameters are here.
checkpoint_completion_target = 0.7
checkpoint_smooth_target = 0.3
checkpoint_smooth_margin = 0.5
checkpointer_write_delay = 200ms

Attached is a quick patch to implement a fixed, 100ms delay between fsyncs, and the
assumption that fsync phase is 10% of the total checkpoint duration. I suspect 100ms
is too small to have much effect, but that happens to be what we have

currently in

CheckpointWriteDelay(). Could you test this patch along with yours? If you can test
with different delays (e.g 100ms, 500ms and 1000ms) and different ratios between
the write and fsync phase (e.g 0.5, 0.7, 0.9), to get an idea of how sensitive the
test case is to those settings.

It seems interesting algorithm! I will test it in same setting and study about
your patch essence.

(2013/06/26 5:28), Heikki Linnakangas wrote:

On 25.06.2013 23:03, Robert Haas wrote:

On Tue, Jun 25, 2013 at 1:15 PM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:

I'm not sure it's a good idea to sleep proportionally to the time it took to
complete the previous fsync. If you have a 1GB cache in the RAID controller,
fsyncing the a 1GB segment will fill it up. But since it fits in cache, it
will return immediately. So we proceed fsyncing other files, until the cache
is full and the fsync blocks. But once we fill up the cache, it's likely
that we're hurting concurrent queries. ISTM it would be better to stay under
that threshold, keeping the I/O system busy, but never fill up the cache
completely.

Isn't the behavior implemented by the patch a reasonable approximation
of just that? When the fsyncs start to get slow, that's when we start
to sleep. I'll grant that it would be better to sleep when the
fsyncs are *about* to get slow, rather than when they actually have
become slow, but we have no way to know that.

Well, that's the point I was trying to make: you should sleep *before* the fsyncs
get slow.

Actuary, fsync time is changed by progress of background disk writes in OS. We
cannot know about progress of background disk write before fsyncs. I think
Robert's argument is right. Please see under following log messages.

* fsync file which had been already wrote in disk
DEBUG: 00000: checkpoint sync: number=23 file=base/16384/16413.5 time=2.546 msec
DEBUG: 00000: checkpoint sync: number=24 file=base/16384/16413.6 time=3.174 msec
DEBUG: 00000: checkpoint sync: number=25 file=base/16384/16413.7 time=2.358 msec
DEBUG: 00000: checkpoint sync: number=26 file=base/16384/16413.8 time=2.013 msec
DEBUG: 00000: checkpoint sync: number=27 file=base/16384/16413.9 time=1232.535
msec
DEBUG: 00000: checkpoint sync: number=28 file=base/16384/16413_fsm time=0.005 msec

* fsync file which had not been wrote in disk very much
DEBUG: 00000: checkpoint sync: number=54 file=base/16384/16419.8 time=3408.759
msec
DEBUG: 00000: checkpoint sync: number=55 file=base/16384/16419.9 time=3857.075
msec
DEBUG: 00000: checkpoint sync: number=56 file=base/16384/16419.10
time=13848.237 msec
DEBUG: 00000: checkpoint sync: number=57 file=base/16384/16419.11 time=898.836
msec
DEBUG: 00000: checkpoint sync: number=58 file=base/16384/16419_fsm time=0.004 msec
DEBUG: 00000: checkpoint sync: number=59 file=base/16384/16419_vm time=0.002 msec

I think it is wasteful of sleep every fsyncs including short time, and fsync time
performance is also changed by hardware which is like RAID card and kind of or
number of disks and OS. So it is difficult to set fixed-sleep-time. My proposed
method will be more adoptive in these cases.

The only feedback we have on how bad things are is how long it took
the last fsync to complete, so I actually think that's a much better
way to go than any fixed sleep - which will often be unnecessarily
long on a well-behaved system, and which will often be far too short
on one that's having trouble. I'm inclined to think think Kondo-san
has got it right.

Quite possible, I really don't know. I'm inclined to first try the simplest thing
possible, and only make it more complicated if that's not good enough.
Kondo-san's patch wasn't very complicated, but nevertheless a fixed sleep between
every fsync, unless you're behind the schedule, is even simpler. In particular,
it's easier to tie that into the checkpoint scheduler - I'm not sure how you'd
measure progress or determine how long to sleep unless you assume that every
fsync is the same.

I think it is important in phase of fsync that short time as possible without IO
freeze, keep schedule of checkpoint, and good for executing transactions. I try
to make progress patch in that's point of view. By the way, executing DBT-2
benchmark has long time(It may be four hours.). For that reason I hope that don't
mind my late reply very much! :-)

Best Regards,
--
Mitsumasa KONDO
NTT Open Sorce Software Center

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 26.06.2013 11:37, KONDO Mitsumasa wrote:

On Tue, Jun 25, 2013 at 1:15 PM, Heikki Linnakangas

Hmm, so the write patch doesn't do much, but the fsync patch makes
the response
times somewhat smoother. I'd suggest that we drop the write patch
for now, and focus on the fsyncs.

Write patch is effective in TPS!

Your test results don't agree with that. You got 3465.96 TPS with the
write patch, and 3474.62 and 3469.03 without it. The fsync+write
combination got slightly more TPS than just the fsync patch, but only by
about 1%, and then the response times were worse.

- Heikki

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

(2013/06/26 20:15), Heikki Linnakangas wrote:

On 26.06.2013 11:37, KONDO Mitsumasa wrote:

On Tue, Jun 25, 2013 at 1:15 PM, Heikki Linnakangas

Hmm, so the write patch doesn't do much, but the fsync patch makes
the response
times somewhat smoother. I'd suggest that we drop the write patch
for now, and focus on the fsyncs.

Write patch is effective in TPS!

Your test results don't agree with that. You got 3465.96 TPS with the write
patch, and 3474.62 and 3469.03 without it. The fsync+write combination got
slightly more TPS than just the fsync patch, but only by about 1%, and then the
response times were worse.

Please see result of DBT-2 more careful. Average latency in fsync+write was
improoved from only fsync patch. 90% tile and Maximum latency are not all of
result but only part of result in DBT-2. And Average and TPS are all of result.
Generally, when TPS become high in benchmark, checkpointer has to write more
pages. Therefore, 90%tile and Maximum are worse in this case, and it is general
in other benchmark tests.

Best regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Jun 25, 2013 at 4:28 PM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:

The only feedback we have on how bad things are is how long it took
the last fsync to complete, so I actually think that's a much better
way to go than any fixed sleep - which will often be unnecessarily
long on a well-behaved system, and which will often be far too short
on one that's having trouble. I'm inclined to think think Kondo-san
has got it right.

Quite possible, I really don't know. I'm inclined to first try the simplest
thing possible, and only make it more complicated if that's not good enough.
Kondo-san's patch wasn't very complicated, but nevertheless a fixed sleep
between every fsync, unless you're behind the schedule, is even simpler.

I'm pretty sure Greg Smith tried it the fixed-sleep thing before and
it didn't work that well. I have also tried it and the resulting
behavior was unimpressive. It makes checkpoints take a long time to
complete even when there's very little data to flush out to the OS,
which is annoying; and when things actually do get ugly, the sleeps
aren't long enough to matter. See the timings Kondo-san posted
downthread: 100ms delays aren't going let the system recover in any
useful way when the fsync can take 13 s for one file. On a system
that's badly weighed down by I/O, the fsync times are often
*extremely* long - 13 s is far from the worst you can see. You have
to give the system a meaningful time to recover from that, allowing
other processes to make meaningful progress before you hit it again,
or system performance just goes down the tubes. Greg's test, IIRC,
used 3 s sleeps rather than your proposal of 100 ms, but it still
wasn't enough.

In
particular, it's easier to tie that into the checkpoint scheduler - I'm not
sure how you'd measure progress or determine how long to sleep unless you
assume that every fsync is the same.

I think the thing to do is assume that the fsync phase will take 10%
or so of the total checkpoint time, but then be prepared to let the
checkpoint run a bit longer if the fsyncs end up being slow. As Greg
has pointed out during prior discussions of this, the normal scenario
when things get bad here is that there is no way in hell you're going
to fit the checkpoint into the originally planned time. Once all of
the write caches between PostgreSQL and the spinning rust are full,
the system is in trouble and things are going to suck. The hope is
that we can stop beating the horse while it is merely in intensive
care rather than continuing until the corpse is fully skeletized.
Fixed delays don't work because - to push an already-overdone metaphor
a bit further - we have no idea how much of a beating the horse can
take; we need something adaptive so that we respond to what actually
happens rather than making predictions that will almost certainly be
wrong a large fraction of the time.

To put this another way, when we start the fsync() phase, it often
consumes 100% of the available I/O on the machine, completing starving
every other process that might need any. This is certainly a
deficiency in the Linux I/O scheduler, but as they seem in no hurry to
fix it we'll have to cope with it as best we can. If you do the
fsyncs in fast succession (and 100ms separation might as well be no
separation at all), then the I/O starvation of the entire system
persists through the entire fsync phase. If, on the other hand, you
sleep for the same amount of time the previous fsync took, then on the
average, 50% of the machine's I/O capacity will be available for all
other system activity throughout the fsync phase, rather than 0%.

Now, unfortunately, this is still not that good, because it's often
the case that all of the fsyncs except one are reasonably fast, and
there's one monster one that is very slow. ext3 has a known bad
behavior that dumps all dirty data for the entire *filesystem* when
you fsync, which tends to create these kinds of effects. But even on
better-behaved filesystem, like ext4, it's fairly common to have one
fsync that is painfully longer than all the others. So even with
this patch, there are still going to be cases where the whole system
becomes unresponsive. I don't see any way to to do better without a
better kernel API, or a better I/O scheduler, but that doesn't mean we
shouldn't do at least this much.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

(2013/06/28 0:08), Robert Haas wrote:

On Tue, Jun 25, 2013 at 4:28 PM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:
I'm pretty sure Greg Smith tried it the fixed-sleep thing before and
it didn't work that well. I have also tried it and the resulting
behavior was unimpressive. It makes checkpoints take a long time to
complete even when there's very little data to flush out to the OS,
which is annoying; and when things actually do get ugly, the sleeps
aren't long enough to matter. See the timings Kondo-san posted
downthread: 100ms delays aren't going let the system recover in any
useful way when the fsync can take 13 s for one file. On a system
that's badly weighed down by I/O, the fsync times are often
*extremely* long - 13 s is far from the worst you can see. You have
to give the system a meaningful time to recover from that, allowing
other processes to make meaningful progress before you hit it again,
or system performance just goes down the tubes. Greg's test, IIRC,
used 3 s sleeps rather than your proposal of 100 ms, but it still
wasn't enough.

Yes. In write phase, checkpointer writes numerous 8KB dirty pages in each
SyncOneBuffer(), therefore it can be well for tiny(100ms) sleep time. But
in fsync phase, checkpointer writes scores of relation files in each fsync(),
therefore it can not be well for tiny sleep. It shoud need longer sleep time
for recovery IO performance. If we know its best sleep time, we had better use
previous fsync time. And if we want to prevent fast long fsync time, we had
better change relation file size which is 1GB in default max size to smaller.

Go back to the subject. Here is our patches test results. Fsync + write patch was
not good result in past result, so I retry benchmark in same condition. It seems
to get good perfomance than past result.

* Performance result in DBT-2 (WH340)
| TPS 90%tile Average Maximum
---------------+---------------------------------------
original_0.7 | 3474.62 18.348328 5.739 36.977713
original_1.0 | 3469.03 18.637865 5.842 41.754421
fsync | 3525.03 13.872711 5.382 28.062947
write | 3465.96 19.653667 5.804 40.664066
fsync + write | 3586.85 14.459486 4.960 27.266958
Heikki's patch | 3504.3 19.731743 5.761 38.33814

* HTML result in DBT-2
http://pgstatsinfo.projects.pgfoundry.org/RESULT/

In attached text, I also describe in each checkpoint time. fsync patch was seemed
to have longer time than not fsync patch. However, checkpoint schedule is on time
in checkpoint_timeout and allowable time. I think that it is most important
things in fsync phase that fast finished checkpoint is not but definitely and
assurance write pages in end of checkpoint. So my fsync patch is not wrong
working any more.

My write patch seems to have lot of riddle, so I try to investigate objective
result and theory of effect.

Best regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

Hi,

I tested and changed segsize=0.25GB which is max partitioned table file size and
default setting is 1GB in configure option (./configure --with-segsize=0.25).
Because I thought that small segsize is good for fsync phase and background disk
write in OS in checkpoint. I got significant improvements in DBT-2 result!

* Performance result in DBT-2 (WH340)
| NOTPM 90%tile Average Maximum
-----------------------------+---------------------------------------
original_0.7 (baseline) | 3474.62 18.348328 5.739 36.977713
fsync + write | 3586.85 14.459486 4.960 27.266958
fsync + write + segsize=0.25 | 3661.17 8.28816 4.117 17.23191

Changing segsize with my checkpoint patches improved original over 50% at 90%tile
and maximum response time.

However, this tests ware not same condition... I also changed SESSION parameter
100 to 300 in DBT-2 driver. In general, I heard good SESSION parameter is 100.
Andt I didn't understand optimized DBT-2 parameters a lot. So I will retry to
test my patches and baseline with optimized parameters in DBT-2. Please wait for
a while.

Best regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

On Wed, Jul 3, 2013 at 4:18 AM, KONDO Mitsumasa
<kondo.mitsumasa@lab.ntt.co.jp> wrote:

I tested and changed segsize=0.25GB which is max partitioned table file size and
default setting is 1GB in configure option (./configure --with-segsize=0.25).
Because I thought that small segsize is good for fsync phase and background disk
write in OS in checkpoint. I got significant improvements in DBT-2 result!

This is interesting. Unfortunately, it has a significant downside:
potentially, there will be a lot more files in the data directory. As
it is, the number of files that exist there today has caused
performance problems for some of our customers. I'm not sure off-hand
to what degree those problems have been related to overall inode
consumption vs. the number of files in the same directory.

If the problem is mainly with number of of files in the same
directory, we could consider revising our directory layout. Instead
of:

base/${DBOID}/${RELFILENODE}_{FORK}

We could have:

base/${DBOID}/${FORK}/${RELFILENODE}

That would move all the vm and fsm forks to separate directories,
which would cut down the number of files in the main-fork directory
significantly. That might be worth doing independently of the issue
you're raising here. For large clusters, you'd even want one more
level to keep the directories from getting too big:

base/${DBOID}/${FORK}/${X}/${RELFILENODE}

...where ${X} is two hex digits, maybe just the low 16 bits of the
relfilenode number. But this would be not as good for small clusters
where you'd end up with oodles of little-tiny directories, and I'm not
sure it'd be practical to smoothly fail over from one system to the
other.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers