(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
checkpoint-progress-charts.ods
Description: application/vnd.oasis.opendocument.spreadsheet
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
