pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedcheckpoints, proper config
11+ messages / 5 participants
[nested] [flat]
* checkpoints, proper config
@ 2015-12-10 09:12 Tory M Blue <tmblue@gmail.com>
2015-12-10 17:20 ` Re: checkpoints, proper config Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tory M Blue @ 2015-12-10 09:12 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: checkpoints, proper config
2015-12-10 09:12 checkpoints, proper config Tory M Blue <tmblue@gmail.com>
@ 2015-12-10 17:20 ` Joshua D. Drake <jd@commandprompt.com>
2015-12-10 18:35 ` Re: checkpoints, proper config Tory M Blue <tmblue@gmail.com>
2015-12-10 22:40 ` Re: checkpoints, proper config Tomas Vondra <tomas.vondra@2ndquadrant.com>
0 siblings, 2 replies; 11+ messages in thread
From: Joshua D. Drake @ 2015-12-10 17:20 UTC (permalink / raw)
To: Tory M Blue <tmblue@gmail.com>; pgsql-performance
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.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: checkpoints, proper config
2015-12-10 09:12 checkpoints, proper config Tory M Blue <tmblue@gmail.com>
2015-12-10 17:20 ` Re: checkpoints, proper config Joshua D. Drake <jd@commandprompt.com>
@ 2015-12-10 18:35 ` Tory M Blue <tmblue@gmail.com>
2015-12-10 20:00 ` Re: checkpoints, proper config Joshua D. Drake <jd@commandprompt.com>
1 sibling, 1 reply; 11+ messages in thread
From: Tory M Blue @ 2015-12-10 18:35 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: checkpoints, proper config
2015-12-10 09:12 checkpoints, proper config Tory M Blue <tmblue@gmail.com>
2015-12-10 17:20 ` Re: checkpoints, proper config Joshua D. Drake <jd@commandprompt.com>
2015-12-10 18:35 ` Re: checkpoints, proper config Tory M Blue <tmblue@gmail.com>
@ 2015-12-10 20:00 ` Joshua D. Drake <jd@commandprompt.com>
2015-12-10 20:58 ` Re: checkpoints, proper config Tory M Blue <tmblue@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Joshua D. Drake @ 2015-12-10 20:00 UTC (permalink / raw)
To: Tory M Blue <tmblue@gmail.com>; +Cc: pgsql-performance
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.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: checkpoints, proper config
2015-12-10 09:12 checkpoints, proper config Tory M Blue <tmblue@gmail.com>
2015-12-10 17:20 ` Re: checkpoints, proper config Joshua D. Drake <jd@commandprompt.com>
2015-12-10 18:35 ` Re: checkpoints, proper config Tory M Blue <tmblue@gmail.com>
2015-12-10 20:00 ` Re: checkpoints, proper config Joshua D. Drake <jd@commandprompt.com>
@ 2015-12-10 20:58 ` Tory M Blue <tmblue@gmail.com>
2015-12-10 21:14 ` Re: checkpoints, proper config Joshua D. Drake <jd@commandprompt.com>
2015-12-11 23:45 ` Re: checkpoints, proper config Jim Nasby <Jim.Nasby@BlueTreble.com>
0 siblings, 2 replies; 11+ messages in thread
From: Tory M Blue @ 2015-12-10 20:58 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: checkpoints, proper config
2015-12-10 09:12 checkpoints, proper config Tory M Blue <tmblue@gmail.com>
2015-12-10 17:20 ` Re: checkpoints, proper config Joshua D. Drake <jd@commandprompt.com>
2015-12-10 18:35 ` Re: checkpoints, proper config Tory M Blue <tmblue@gmail.com>
2015-12-10 20:00 ` Re: checkpoints, proper config Joshua D. Drake <jd@commandprompt.com>
2015-12-10 20:58 ` Re: checkpoints, proper config Tory M Blue <tmblue@gmail.com>
@ 2015-12-10 21:14 ` Joshua D. Drake <jd@commandprompt.com>
1 sibling, 0 replies; 11+ messages in thread
From: Joshua D. Drake @ 2015-12-10 21:14 UTC (permalink / raw)
To: Tory M Blue <tmblue@gmail.com>; +Cc: pgsql-performance
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.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: checkpoints, proper config
2015-12-10 09:12 checkpoints, proper config Tory M Blue <tmblue@gmail.com>
2015-12-10 17:20 ` Re: checkpoints, proper config Joshua D. Drake <jd@commandprompt.com>
2015-12-10 18:35 ` Re: checkpoints, proper config Tory M Blue <tmblue@gmail.com>
2015-12-10 20:00 ` Re: checkpoints, proper config Joshua D. Drake <jd@commandprompt.com>
2015-12-10 20:58 ` Re: checkpoints, proper config Tory M Blue <tmblue@gmail.com>
@ 2015-12-11 23:45 ` Jim Nasby <Jim.Nasby@BlueTreble.com>
1 sibling, 0 replies; 11+ messages in thread
From: Jim Nasby @ 2015-12-11 23:45 UTC (permalink / raw)
To: Tory M Blue <tmblue@gmail.com>; Joshua D. Drake <jd@commandprompt.com>; +Cc: pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: checkpoints, proper config
2015-12-10 09:12 checkpoints, proper config Tory M Blue <tmblue@gmail.com>
2015-12-10 17:20 ` Re: checkpoints, proper config Joshua D. Drake <jd@commandprompt.com>
@ 2015-12-10 22:40 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2015-12-10 22:45 ` Re: checkpoints, proper config Alvaro Herrera <alvherre@2ndquadrant.com>
1 sibling, 1 reply; 11+ messages in thread
From: Tomas Vondra @ 2015-12-10 22:40 UTC (permalink / raw)
To: pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: checkpoints, proper config
2015-12-10 09:12 checkpoints, proper config Tory M Blue <tmblue@gmail.com>
2015-12-10 17:20 ` Re: checkpoints, proper config Joshua D. Drake <jd@commandprompt.com>
2015-12-10 22:40 ` Re: checkpoints, proper config Tomas Vondra <tomas.vondra@2ndquadrant.com>
@ 2015-12-10 22:45 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2015-12-11 01:20 ` Re: checkpoints, proper config Tomas Vondra <tomas.vondra@2ndquadrant.com>
0 siblings, 1 reply; 11+ messages in thread
From: Alvaro Herrera @ 2015-12-10 22:45 UTC (permalink / raw)
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>; +Cc: pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: checkpoints, proper config
2015-12-10 09:12 checkpoints, proper config Tory M Blue <tmblue@gmail.com>
2015-12-10 17:20 ` Re: checkpoints, proper config Joshua D. Drake <jd@commandprompt.com>
2015-12-10 22:40 ` Re: checkpoints, proper config Tomas Vondra <tomas.vondra@2ndquadrant.com>
2015-12-10 22:45 ` Re: checkpoints, proper config Alvaro Herrera <alvherre@2ndquadrant.com>
@ 2015-12-11 01:20 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2015-12-11 23:47 ` Re: checkpoints, proper config Jim Nasby <Jim.Nasby@BlueTreble.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tomas Vondra @ 2015-12-11 01:20 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: checkpoints, proper config
2015-12-10 09:12 checkpoints, proper config Tory M Blue <tmblue@gmail.com>
2015-12-10 17:20 ` Re: checkpoints, proper config Joshua D. Drake <jd@commandprompt.com>
2015-12-10 22:40 ` Re: checkpoints, proper config Tomas Vondra <tomas.vondra@2ndquadrant.com>
2015-12-10 22:45 ` Re: checkpoints, proper config Alvaro Herrera <alvherre@2ndquadrant.com>
2015-12-11 01:20 ` Re: checkpoints, proper config Tomas Vondra <tomas.vondra@2ndquadrant.com>
@ 2015-12-11 23:47 ` Jim Nasby <Jim.Nasby@BlueTreble.com>
0 siblings, 0 replies; 11+ messages in thread
From: Jim Nasby @ 2015-12-11 23:47 UTC (permalink / raw)
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>; Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2015-12-11 23:47 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-12-10 09:12 checkpoints, proper config Tory M Blue <tmblue@gmail.com>
2015-12-10 17:20 ` Joshua D. Drake <jd@commandprompt.com>
2015-12-10 18:35 ` Tory M Blue <tmblue@gmail.com>
2015-12-10 20:00 ` Joshua D. Drake <jd@commandprompt.com>
2015-12-10 20:58 ` Tory M Blue <tmblue@gmail.com>
2015-12-10 21:14 ` Joshua D. Drake <jd@commandprompt.com>
2015-12-11 23:45 ` Jim Nasby <Jim.Nasby@BlueTreble.com>
2015-12-10 22:40 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2015-12-10 22:45 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2015-12-11 01:20 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2015-12-11 23:47 ` Jim Nasby <Jim.Nasby@BlueTreble.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox