Re: Let PostgreSQL's On Schedule checkpoint write buffer smooth spread cycle by tuning IsCheckpointOnSchedule?

(please keep the mailing list CC'd, and please don't top-post)

On 05/13/2015 05:00 AM, digoal zhou wrote:
> I test it, but use exponent not very perfect in any environment.
> why cann't use time only?

As you mentioned yourself earlier, if you only use time but you reach
checkpoint_segments before checkpoint_timeout, you will not complete the
checkpoint until you'd already need to begin the next checkpoint. You
can't completely ignore checkpoint_segments.

Comparing the numbers you give below with
compensate-fpw-effect-on-checkpoint-scheduling-1.patch, with the ones
from your first post, it looks like the patch already made the situation
much better. You still have a significant burst in the beginning of the
checkpoint cycle, but it's a lot smaller than without the patch. Before
the patch, the "count" topped at 9078, and below it topped at 2964.
There is a strange "lull" after the burst, I'm not sure what's going on
there, but overall it seems like a big improvement.

Did the patch alleviate the bump in latency that pgbench reports?

I put the "count" numbers from your original post and below into a
spreadsheet, and created some fancy charts. See attached. It shows the
same thing but with pretty pictures. Assuming we want the checkpoint to
be spread as evenly as possible across the cycle, the ideal would be a
straight line from 0 to about 150000 in 270 seconds in the cumulative
chart. You didn't give the full data, but you can extrapolate the lines
to get a rough picture of how close the different versions are from that
ideal.

In summary, the X^1.5 correction seems to work pretty well. It doesn't
completely eliminate the problem, but it makes it a lot better.

I don't want to over-compensate for the full-page-write effect either,
because there are also applications where that effect isn't so big. For
example, an application that performs a lot of updates, but all the
updates are on a small number of pages, so the full-page-write storm
immediately after checkpoint doesn't last long. A worst case for this
patch would be such an application - lots of updates on only a few pages
- with a long checkpoint_timeoout but relatively small
checkpoint_segments, so that checkpoints are always driven by
checkpoint_segments. I'd like to see some benchmarking of that worst
case before committing anything like this.

> ----------------------------------end-----------------------------
> checkpoint start
> buffer__sync__start num_buffers: 524288, dirty_buffers: 156931
> r1_or_w2 2, pid: 29132, min: 44, max: 151, avg: 52, sum: 49387, count: 932
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 44, max: 95, avg: 49, sum: 41532, count: 837
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 44, max: 747, avg: 54, sum: 100419, count: 1849
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 44, max: 372, avg: 52, sum: 110701, count: 2090
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 44, max: 115, avg: 57, sum: 147510, count: 2575
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 44, max: 470, avg: 58, sum: 145217, count: 2476
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 44, max: 120, avg: 54, sum: 161401, count: 2964
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 44, max: 208, avg: 59, sum: 170280, count: 2847
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 44, max: 10089, avg: 62, sum: 136106, count:
> 2181
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 41, max: 487, avg: 56, sum: 88990, count: 1570
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 39, max: 102, avg: 55, sum: 59807, count: 1083
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 40, max: 557, avg: 56, sum: 117274, count: 2083
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 44, max: 537, avg: 58, sum: 169867, count: 2882
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 44, max: 147, avg: 60, sum: 92835, count: 1538
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 30, max: 93, avg: 55, sum: 14641, count: 264
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 48, max: 92, avg: 56, sum: 11834, count: 210
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 45, max: 91, avg: 56, sum: 9151, count: 162
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 46, max: 92, avg: 57, sum: 8621, count: 151
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 36, max: 90, avg: 57, sum: 7962, count: 139
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 48, max: 93, avg: 58, sum: 7194, count: 123
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 33, max: 95, avg: 58, sum: 7143, count: 123
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 46, max: 89, avg: 57, sum: 6801, count: 118
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 49, max: 100, avg: 58, sum: 6818, count: 117
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 45, max: 90, avg: 57, sum: 6982, count: 121
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 46, max: 88, avg: 55, sum: 6459, count: 117
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 48, max: 88, avg: 58, sum: 7022, count: 121
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 47, max: 94, avg: 57, sum: 5952, count: 104
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 49, max: 95, avg: 57, sum: 6871, count: 119
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 49, max: 85, avg: 58, sum: 6829, count: 117
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 45, max: 89, avg: 57, sum: 6851, count: 119
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 49, max: 100, avg: 57, sum: 6779, count: 117
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 45, max: 93, avg: 55, sum: 6502, count: 117
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 47, max: 98, avg: 58, sum: 6805, count: 117
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 38, max: 90, avg: 57, sum: 6771, count: 118
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 46, max: 96, avg: 56, sum: 6593, count: 116
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 35, max: 101, avg: 57, sum: 6809, count: 119
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 45, max: 100, avg: 57, sum: 6171, count: 107
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 48, max: 105, avg: 57, sum: 6801, count: 119
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 45, max: 95, avg: 57, sum: 6792, count: 119
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 46, max: 93, avg: 56, sum: 6693, count: 118
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 46, max: 93, avg: 57, sum: 6878, count: 120
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 46, max: 93, avg: 56, sum: 6664, count: 117
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 46, max: 94, avg: 57, sum: 7051, count: 123
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 46, max: 92, avg: 57, sum: 6957, count: 120
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 48, max: 94, avg: 57, sum: 6842, count: 119
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 46, max: 100, avg: 57, sum: 6865, count: 119
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 49, max: 102, avg: 58, sum: 6915, count: 119
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 48, max: 94, avg: 57, sum: 6187, count: 107
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 50, max: 86, avg: 58, sum: 6957, count: 119
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 46, max: 97, avg: 55, sum: 33636, count: 609
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 36, max: 90, avg: 55, sum: 34180, count: 620
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 29, max: 92, avg: 53, sum: 36569, count: 680
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 40, max: 91, avg: 54, sum: 37374, count: 681
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 32, max: 86, avg: 54, sum: 33347, count: 613
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 46, max: 94, avg: 54, sum: 37603, count: 684
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 32, max: 93, avg: 55, sum: 33777, count: 613
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 46, max: 104, avg: 55, sum: 37566, count: 682
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 35, max: 92, avg: 54, sum: 37037, count: 681
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 42, max: 106, avg: 57, sum: 35181, count: 614
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 45, max: 99, avg: 54, sum: 36981, count: 681
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 44, max: 88, avg: 53, sum: 33202, count: 622
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 35, max: 89, avg: 54, sum: 36825, count: 681
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 30, max: 88, avg: 53, sum: 33917, count: 635
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 46, max: 89, avg: 55, sum: 36234, count: 658
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 30, max: 99, avg: 55, sum: 37719, count: 682
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 46, max: 93, avg: 54, sum: 33491, count: 613
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 46, max: 96, avg: 54, sum: 37365, count: 681
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 45, max: 101, avg: 54, sum: 33481, count: 613
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 37, max: 93, avg: 54, sum: 37102, count: 685
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 40, max: 87, avg: 54, sum: 36968, count: 681
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 44, max: 84, avg: 54, sum: 33565, count: 613
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 46, max: 92, avg: 54, sum: 37271, count: 681
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 48, max: 96, avg: 55, sum: 34272, count: 613
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 44, max: 85, avg: 54, sum: 37378, count: 682
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 45, max: 107, avg: 53, sum: 36715, count: 680
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 44, max: 88, avg: 54, sum: 33620, count: 616
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 29, max: 94, avg: 54, sum: 37093, count: 681
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 39, max: 110, avg: 53, sum: 33013, count: 612
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 40, max: 97, avg: 54, sum: 37215, count: 682
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 37, max: 90, avg: 54, sum: 37240, count: 681
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 41, max: 95, avg: 54, sum: 33555, count: 613
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 38, max: 89, avg: 54, sum: 37503, count: 683
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 38, max: 95, avg: 55, sum: 33803, count: 614
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 44, max: 89, avg: 56, sum: 38403, count: 682
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 33, max: 92, avg: 54, sum: 37354, count: 681
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 46, max: 93, avg: 55, sum: 33881, count: 613
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 34, max: 91, avg: 54, sum: 37047, count: 681
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 32, max: 85, avg: 53, sum: 33003, count: 613
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 30, max: 92, avg: 53, sum: 36854, count: 683
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 40, max: 92, avg: 54, sum: 36597, count: 673
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 46, max: 96, avg: 54, sum: 33689, count: 620
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 32, max: 92, avg: 54, sum: 37194, count: 684
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 45, max: 90, avg: 53, sum: 32813, count: 612
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 32, max: 100, avg: 54, sum: 37485, count: 682
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 31, max: 97, avg: 54, sum: 33294, count: 613
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 33, max: 94, avg: 54, sum: 37320, count: 681
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 28, max: 92, avg: 54, sum: 37067, count: 681
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 45, max: 87, avg: 54, sum: 33766, count: 614
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 33, max: 110, avg: 53, sum: 36220, count: 680
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 35, max: 98, avg: 54, sum: 33442, count: 613
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 33, max: 97, avg: 55, sum: 37692, count: 682
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 33, max: 95, avg: 54, sum: 37073, count: 681
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 35, max: 88, avg: 54, sum: 33676, count: 613
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 30, max: 103, avg: 53, sum: 36770, count: 681
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 46, max: 96, avg: 54, sum: 33447, count: 613
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 44, max: 91, avg: 55, sum: 37643, count: 682
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 38, max: 90, avg: 54, sum: 37377, count: 681
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 46, max: 103, avg: 56, sum: 34531, count: 614
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 30, max: 121, avg: 54, sum: 37412, count: 683
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 40, max: 89, avg: 54, sum: 33173, count: 613
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 31, max: 94, avg: 54, sum: 37385, count: 681
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 28, max: 106, avg: 55, sum: 38132, count: 682
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 34, max: 96, avg: 55, sum: 33800, count: 613
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 37, max: 98, avg: 56, sum: 38305, count: 682
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 28, max: 104, avg: 55, sum: 33744, count: 613
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 45, max: 103, avg: 54, sum: 36923, count: 681
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 36, max: 89, avg: 55, sum: 37797, count: 681
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 44, max: 103, avg: 56, sum: 34902, count: 620
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 46, max: 88, avg: 55, sum: 38025, count: 682
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 43, max: 102, avg: 56, sum: 34545, count: 614
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 34, max: 94, avg: 55, sum: 37756, count: 681
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 28, max: 93, avg: 54, sum: 33530, count: 613
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 31, max: 97, avg: 55, sum: 37992, count: 682
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 35, max: 99, avg: 55, sum: 37923, count: 681
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 39, max: 101, avg: 55, sum: 34027, count: 614
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 28, max: 93, avg: 53, sum: 36078, count: 680
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 41, max: 89, avg: 51, sum: 31563, count: 612
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 31, max: 92, avg: 52, sum: 35596, count: 680
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 35, max: 102, avg: 55, sum: 37816, count: 685
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 45, max: 102, avg: 55, sum: 33828, count: 613
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 30, max: 93, avg: 54, sum: 37285, count: 681
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 30, max: 90, avg: 55, sum: 34037, count: 614
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 30, max: 86, avg: 54, sum: 37584, count: 684
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 32, max: 103, avg: 55, sum: 37946, count: 681
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 44, max: 97, avg: 56, sum: 34556, count: 617
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 33, max: 99, avg: 56, sum: 38213, count: 681
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 44, max: 97, avg: 56, sum: 34613, count: 614
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 35, max: 101, avg: 55, sum: 37925, count: 682
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 35, max: 93, avg: 55, sum: 35504, count: 639
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 46, max: 90, avg: 55, sum: 36459, count: 655
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 30, max: 97, avg: 54, sum: 37369, count: 682
> ----------------------------------end-----------------------------
> r1_or_w2 2, pid: 29132, min: 31, max: 93, avg: 54, sum: 33161, count: 612
> ----------------------------------end----------------------------

- Heikki