Re: [PERFORM] checkpoints, proper config

2015-12-11 Thread Jim Nasby
On 12/10/15 2:58 PM, Tory M Blue wrote: This is a slony slave node, so I'm not too worried about this particular host losing it's data, thus fsync is off, The Amazon RDS team actually benchmarked fsync=off vs sync commit off and discovered that you get better performance turning sync commit

Re: [PERFORM] checkpoints, proper config

2015-12-11 Thread Jim Nasby
On 12/10/15 7:20 PM, Tomas Vondra wrote: I agree, but actually, what is the sense of checkpoint_warning? I think it was useful back when we didn't have log_checkpoints, but now that we have detailed checkpoint logging I think it's pretty much useless noise. Not entirely. The WARNING only

Re: [PERFORM] checkpoints, proper config

2015-12-10 Thread Joshua D. Drake
On 12/10/2015 10:35 AM, Tory M Blue wrote: Thiis valid regardless of the workload? Yes. Seems that I would be storing a ton of data and writing it once an hour, so would have potential perf hits on the hour. I guess I'm not too up to date on the checkpoint configuration. No, that isn't

Re: [PERFORM] checkpoints, proper config

2015-12-10 Thread Joshua D. Drake
On 12/10/2015 01:12 AM, Tory M Blue wrote: checkpoint_timeout = 5min checkpoint_completion_target = 0.9 The above is your problem. Make checkpoint_timeout = 1h . Also, considering turning synchronous_commit off. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564

[PERFORM] checkpoints, proper config

2015-12-10 Thread Tory M Blue
9.3.4 CentOS 256Gb system total_checkpoints | minutes_between_checkpoints ---+- 109943 | 0.0274886580556895 I've just bumped then to 150. # - Checkpoints - checkpoint_segments = 150 checkpoint_timeout = 5min

Re: [PERFORM] checkpoints, proper config

2015-12-10 Thread Tory M Blue
On Thu, Dec 10, 2015 at 12:00 PM, Joshua D. Drake wrote: > On 12/10/2015 10:35 AM, Tory M Blue wrote: > > >> Thiis valid regardless of the workload? >> > > Yes. > > > Seems that I would be storing a >> ton of data and writing it once an hour, so would have potential perf

Re: [PERFORM] checkpoints, proper config

2015-12-10 Thread Joshua D. Drake
On 12/10/2015 12:58 PM, Tory M Blue wrote: synchronous is commented out, is it on by default? Yes it is on by default. This is a slony slave node, so I'm not too worried about this particular host losing it's data, thus fsync is off, thanks again sir Tory -- Command Prompt, Inc. -

Re: [PERFORM] checkpoints, proper config

2015-12-10 Thread Tomas Vondra
On 12/10/2015 11:45 PM, Alvaro Herrera wrote: Tomas Vondra wrote: Also, I don't think it makes much sense to set (checkpoint_warning > checkpoint_timeout) as it kinda defeats the whole purpose of the warning. I agree, but actually, what is the sense of checkpoint_warning? I think it

Re: [PERFORM] checkpoints, proper config

2015-12-10 Thread Tomas Vondra
On 12/10/2015 06:20 PM, Joshua D. Drake wrote: On 12/10/2015 01:12 AM, Tory M Blue wrote: checkpoint_timeout = 5min checkpoint_completion_target = 0.9 The above is your problem. Make checkpoint_timeout = 1h . Also, considering turning synchronous_commit off. I doubt that. The report

Re: [PERFORM] checkpoints, proper config

2015-12-10 Thread Alvaro Herrera
Tomas Vondra wrote: > Also, I don't think it makes much sense to set > >(checkpoint_warning > checkpoint_timeout) > > as it kinda defeats the whole purpose of the warning. I agree, but actually, what is the sense of checkpoint_warning? I think it was useful back when we didn't have

Re: [PERFORM] checkpoints, proper config

2015-12-10 Thread Tory M Blue
On Thu, Dec 10, 2015 at 9:20 AM, Joshua D. Drake wrote: > On 12/10/2015 01:12 AM, Tory M Blue wrote: > > checkpoint_timeout = 5min >> >> checkpoint_completion_target = 0.9 >> >> > The above is your problem. Make checkpoint_timeout = 1h . Also, > considering turning