Thread: checkpoints, proper config
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
checkpoint_completion_target = 0.9
checkpoint_warning = 3600s
WAL buffers are 32MB
Very intense work loads during the eve, much lighter during the day. I need the help when I attempt to shove a ton of data in the DB
Any suggestions?
Tory
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 PostgreSQL Centered full stack support, consulting and development. Announcing "I'm offended" is basically telling the world you can't control your own emotions, so everyone else should do it for you.
On Thu, Dec 10, 2015 at 9:20 AM, Joshua D. Drake <jd@commandprompt.com> 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.
JD
Thiis valid regardless of the workload? 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.
My settings on this particular DB
fsync = off
#synchronous_commit = on
Thanks
Tory
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 how it works. http://www.postgresql.org/docs/9.4/static/wal-configuration.html > > My settings on this particular DB > > fsync = off This will cause data corruption in the event of improper shutdown. > > #synchronous_commit = on > I would turn that off and turn fsync back on. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing "I'm offended" is basically telling the world you can't control your own emotions, so everyone else should do it for you.
On Thu, Dec 10, 2015 at 12:00 PM, Joshua D. Drake <jd@commandprompt.com> 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
hits on the hour. I guess I'm not too up to date on the checkpoint
configuration.
No, that isn't how it works.
http://www.postgresql.org/docs/9.4/static/wal-configuration.html
Thanks will give this a read and get my self up to snuff..
My settings on this particular DB
fsync = off
This will cause data corruption in the event of improper shutdown.
#synchronous_commit = on
I would turn that off and turn fsync back on.
synchronous is commented out, is it 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
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. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing "I'm offended" is basically telling the world you can't control your own emotions, so everyone else should do it for you.
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 mentioned that the checkpoints happen 0.027...
minutes apart (assuming the minutes_between_checkpoints is computed in a
sane way). That's way below 5 minutes, so the checkpoints have to be
triggered by something else - probably by running out of segments, but
we don't know the value before Tory increased it to 150.
Also, recommending synchronous_commit=off is a bit silly, because not
only it introduces data loss issues, but it'll likely cause even more
frequent checkpoints.
Tory, please enable logging of checkpoints (log_checkpoints=on). 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.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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 log_checkpoints, but now that we have detailed checkpoint logging I think it's pretty much useless noise. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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 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 triggers when you get below the 30s (or whatever value is set in the config) and explicitly warns you about doing checkpoints too often. log_checkpoints=on logs all checkpoints and you have to do further analysis on the data (and it's just LOG). -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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 off and leaving fsync alone in some cases. In other cases the difference isn't enough to be worth it. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com
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 triggers when you get below the 30s (or > whatever value is set in the config) and explicitly warns you about > doing checkpoints too often. log_checkpoints=on logs all checkpoints and > you have to do further analysis on the data (and it's just LOG). Agree, though I also find it pretty useless to set it significantly less than checkpoint_timeout in almost all cases. If you want ~5 minutes between checkpoints checkpoint_timeout=30 seconds is way too low to be useful. We should really change the default. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com