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

Coming in late here, but I always thought the fact that the FPW happen mostly at the start of the checkpoint, and the checkpoint writes/fsyncs happen mostly in the first half of the checkpoint period was always suboptimal, i.e. it would be nice of one of these was more active in the second half

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

On Wed, Dec 23, 2015 at 04:37:00PM +0100, Fabien COELHO wrote: > Hmmm. Let us try with both hands: > > AFAICR with xlog-triggered checkpoints, the checkpointer progress is > measured with respect to the size of the WAL file, which does not > grow linearly in time for the reason you pointed above

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

On Wed, Dec 23, 2015 at 9:22 AM, Fabien COELHO wrote: >> Wait, what? On what workload does the FPW spike last only a few >> seconds? [...] > > Ok. AFAICR, a relatively small part at the beginning of the checkpoint, but > possibly more that a few seconds. On a pgbench test,

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

Hello Robert, I think that the 1.5 value somewhere in the patch is much too high for the purpose because it shifts the checkpoint load quite a lot (50% more load at the end of the checkpoint) just for the purpose of avoiding a spike which lasts a few seconds (I think) at the beginning. A much

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

Hello Robert, On a pgbench test, and probably many other workloads, the impact of FPWs declines exponentially (or maybe geometrically, but I think exponentially) as we get further into the checkpoint. Indeed. If the probability of hitting a page is uniform, I think that the FPW probability

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

On Wed, Dec 23, 2015 at 2:16 PM, Tomas Vondra wrote: >> Another point (which Jan Wieck made me think of) is that the optimal >> behavior here likely depends on whether xlog and data are on the same >> disk controller. If they aren't, the FPW spike and background

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

Hi, On 12/23/2015 03:38 PM, Robert Haas wrote: I think one thing that this conversation exposes is that the size of the working set matters a lot. For example, if the workload is pgbench, you're going to see a relatively short FPW-related spike at scale factor 100, but at scale factor 3000

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

On 12/21/2015 01:11 PM, Heikki Linnakangas wrote: On 21/12/15 13:53, Tomas Vondra wrote: On 12/21/2015 12:03 PM, Heikki Linnakangas wrote: On 17/12/15 19:07, Robert Haas wrote: If it works well empirically, does it really matter that it's arbitrary? I mean, the entire planner is full of

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

On Wed, Dec 23, 2015 at 10:37 AM, Fabien COELHO wrote: > Hmmm. Let us try with both hands: > > AFAICR with xlog-triggered checkpoints, the checkpointer progress is > measured with respect to the size of the WAL file, which does not grow > linearly in time for the reason you

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

Hi, On 12/23/2015 08:22 PM, Robert Haas wrote: On Wed, Dec 23, 2015 at 2:16 PM, Tomas Vondra wrote: Another point (which Jan Wieck made me think of) is that the optimal behavior here likely depends on whether xlog and data are on the same disk controller. If they

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

AFAICR with xlog-triggered checkpoints, the checkpointer progress is measured with respect to the size of the WAL file, which does not grow linearly in time for the reason you pointed above (a lot of FPW at the beginning, less in the end). As the WAL file is growing quickly, the checkpointer

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

On Mon, Dec 21, 2015 at 7:51 AM, Fabien COELHO wrote: > I think that the 1.5 value somewhere in the patch is much too high for the > purpose because it shifts the checkpoint load quite a lot (50% more load at > the end of the checkpoint) just for the purpose of avoiding a

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

On 17/12/15 19:07, Robert Haas wrote: On Mon, Dec 14, 2015 at 6:08 PM, Tomas Vondra wrote: So we know that we should expect about (prev_wal_bytes - wal_bytes) + (prev_wal_fpw_bytes - wal_fpw_bytes) ( regular WAL) + ( FPW WAL

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

Hello Heikki, The reason I didn't commit this back then was lack of performance testing. I'm fairly confident that this would be a significant improvement for some workloads, and shouldn't hurt much even in the worst case. But I did only a little testing on my laptop. I think Simon was in

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

Hi, On 12/21/2015 12:03 PM, Heikki Linnakangas wrote: On 17/12/15 19:07, Robert Haas wrote: On Mon, Dec 14, 2015 at 6:08 PM, Tomas Vondra wrote: So we know that we should expect about (prev_wal_bytes - wal_bytes) + (prev_wal_fpw_bytes - wal_fpw_bytes) (

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

On 21/12/15 13:53, Tomas Vondra wrote: On 12/21/2015 12:03 PM, Heikki Linnakangas wrote: On 17/12/15 19:07, Robert Haas wrote: If it works well empirically, does it really matter that it's arbitrary? I mean, the entire planner is full of fairly arbitrary assumptions about which things to

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

On Mon, Dec 14, 2015 at 6:08 PM, Tomas Vondra wrote: > So we know that we should expect about > > (prev_wal_bytes - wal_bytes) + (prev_wal_fpw_bytes - wal_fpw_bytes) > > ( regular WAL) + ( FPW WAL ) > > to be produced until

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

Hi, I was planning to do some review/testing on this patch, but then I noticed it was rejected with feedback in 2015-07 and never resubmitted into another CF. So I won't waste time in testing this unless someone shouts that I should do that anyway. Instead I'll just post some ideas about how

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

On Mon, Jul 6, 2015 at 12:30 PM, Amit Kapila wrote: Yes, we definitely want to see the effect on TPS at the beginning of checkpoint, but even measuring the IO during checkpoint with the way Digoal was capturing the data can show the effect of this patch. I am marking this patch as returned

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

On 07/04/2015 07:34 PM, Fabien COELHO wrote: 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've looked at the maths. I think that the load is distributed as the derivative of this function, that is (1.5

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

On 07/05/2015 08:19 AM, Fabien COELHO wrote: I am a bit skeptical about this. We need test scenarios that clearly show the benefit of having and of not having this behavior. It might be that doing this always is fine for everyone. Do you mean I have to proove that there is an actual problem

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

You don't have to do anything if you don't want to. Sure:-) What I mean is that I think that this patch is not ripe, and I understood that some people were suggesting that it could be applied as is right away. I'm really disagreeing with that. I said myself that this needs performance

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

Hello Heikki, I think that the load is distributed as the derivative of this function, that is (1.5 * x ** 0.5): It starts at 0 but very quicky reaches 0.5, it pass the 1.0 (average load) around 40% progress, and ends up at 1.5, that is the finishing load is 1.5 the average load, just before

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

On Sun, Jul 5, 2015 at 1:19 AM, Fabien COELHO coe...@cri.ensmp.fr wrote: Do you mean I have to proove that there is an actual problem induced from this patch? No, I'm not saying anyone *has* to do anything. What I'm saying is that I'm not convinced by your analysis. I don't think we have

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

No, I'm not saying anyone *has* to do anything. What I'm saying is that I'm not convinced by your analysis. Well, the gist of my analysis is really to say that there are potential performance issues with the proposed change, and that it must be tested thoroughly. The details may varry:-)

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

On 2015-07-05 11:05:28 -0400, Robert Haas wrote: More broadly, I don't really know how to test this patch and show when it helps and when it hurts. And I think we need that, rather than just a theoretical analysis, to tune the behavior. Heikki, can you describe what you think a good test

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

On Sun, Jul 5, 2015 at 1:18 PM, Heikki Linnakangas hlinn...@iki.fi wrote: On 07/04/2015 07:34 PM, Fabien COELHO wrote: I have ran some tests with this patch and the detailed results of the runs are attached with this mail. I do not understand really the aggregated figures in the files

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

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've looked at the maths. I think that the load is distributed as the derivative of this function, that is (1.5 * x ** 0.5): It starts at 0 but very quicky

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

On Jul 4, 2015, at 11:34 AM, Fabien COELHO coe...@cri.ensmp.fr wrote: 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've looked at the maths. I think that the load is distributed as the derivative of

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

Hello Robert, I've looked at the maths. I think that the load is distributed as the derivative of this function, that is (1.5 * x ** 0.5): It starts at 0 but very quicky reaches 0.5, it pass the 1.0 (average load) around 40% progress, and ends up at 1.5, that is the finishing load is 1.5

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

power 1,5 is almost certainly not right for all cases, but it is simple and better. It is better in some cases, as I've been told on my patch. If you have a separate disk for WAL writes the power formula may just degrade performance, or maybe not, or not too much, or it really should be a

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

Hello Andres, In conclusion, and very egoistically, I would prefer if this patch could wait for the checkpoint scheduling patch to be considered, as it would basically invalidate the X00 hours of performance tests I ran:-) These two patches target pretty independent mechanics. If you patch

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

On 3 July 2015 at 06:38, Fabien COELHO coe...@cri.ensmp.fr wrote: Hello Simon, We could do better, but that is not a reason not to commit this, as is. Commit, please. My 0,02€: Please do not commit without further testing... I've submitted a patch to improve checkpoint write

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

On 2015-07-03 07:38:15 +0200, Fabien COELHO wrote: I've submitted a patch to improve checkpoint write scheduling, including X00 hours of performance test on various cases. This patch changes significantly the load distribution over the whole checkpoint, and AFAICS has been tested on rather

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

On Thu, Jul 2, 2015 at 4:16 PM, Simon Riggs si...@2ndquadrant.com wrote: On 13 May 2015 at 09:35, Heikki Linnakangas hlinn...@iki.fi wrote: 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. Agreed Do we

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

On 13 May 2015 at 09:35, Heikki Linnakangas hlinn...@iki.fi wrote: 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. Agreed I don't want to over-compensate for the full-page-write effect either, because

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

Hello Simon, We could do better, but that is not a reason not to commit this, as is. Commit, please. My 0,02€: Please do not commit without further testing... I've submitted a patch to improve checkpoint write scheduling, including X00 hours of performance test on various cases. This patch

Re: [HACKERS] 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

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

On 05/12/2015 03:27 AM, digoal zhou wrote: PostgreSQL (=9.4) trend to smooth buffer write smooth in a checkpoint_completion_target (checkpoint_timeout or checkpoint_segments), but when we use synchronous_commit=off, there is a little problem for the checkpoint_segments target, because xlog

[HACKERS] Let PostgreSQL's On Schedule checkpoint write buffer smooth spread cycle by tuning IsCheckpointOnSchedule?

PostgreSQL (=9.4) trend to smooth buffer write smooth in a checkpoint_completion_target (checkpoint_timeout or checkpoint_segments), but when we use synchronous_commit=off, there is a little problem for the checkpoint_segments target, because xlog write fast(for full page write which the first