pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feeddata-checksums
19+ messages / 13 participants
[nested] [flat]
* data-checksums
@ 2018-01-09 17:42 Rakesh Kumar <rakeshkumar464@mail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Rakesh Kumar @ 2018-01-09 17:42 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
--data-checksums
Use checksums on data pages to help detect corruption by the I/O system that would otherwise be silent. Enabling checksums may incur a noticeable performance penalty. This option can only be set during initialization, and cannot be changed later. If set, checksums are calculated for all objects, in all databases.
====
If I understand it correctly, the performance penalty is when the blocks are written to the disk by the background writer and/or during checkpoint. Given that the process is async and application does not wait on it, is the performance penalty really a big concern.
thanks
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: data-checksums
@ 2018-01-09 17:54 Stephen Frost <sfrost@snowman.net>
parent: Rakesh Kumar <rakeshkumar464@mail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Stephen Frost @ 2018-01-09 17:54 UTC (permalink / raw)
To: Rakesh Kumar <rakeshkumar464@mail.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Greetings,
* Rakesh Kumar (rakeshkumar464@mail.com) wrote:
> --data-checksums
> Use checksums on data pages to help detect corruption by the I/O system that would otherwise be silent. Enabling checksums may incur a noticeable performance penalty. This option can only be set during initialization, and cannot be changed later. If set, checksums are calculated for all objects, in all databases.
> ====
>
> If I understand it correctly, the performance penalty is when the blocks are written to the disk by the background writer and/or during checkpoint. Given that the process is async and application does not wait on it, is the performance penalty really a big concern.
There's also a hit when pages are read back in, since we need to
calculate the checksum and verify it hasn't changed.
That said, imv anyway, the performance hit is small and having checksums
is well worth it.
Thanks!
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: data-checksums
@ 2018-01-09 17:58 Rakesh Kumar <rakeshkumar464@mail.com>
parent: Stephen Frost <sfrost@snowman.net>
0 siblings, 3 replies; 19+ messages in thread
From: Rakesh Kumar @ 2018-01-09 17:58 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
> That said, imv anyway, the performance hit is small and having checksums
> is well worth it.
I also would like to believe that the hit is small, but when PG official document writes "noticeable performance penalty", it becomes difficult to convince management that the hit is small :-)
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: data-checksums
@ 2018-01-09 18:39 Andres Freund <andres@anarazel.de>
parent: Rakesh Kumar <rakeshkumar464@mail.com>
2 siblings, 1 reply; 19+ messages in thread
From: Andres Freund @ 2018-01-09 18:39 UTC (permalink / raw)
To: Rakesh Kumar <rakeshkumar464@mail.com>; +Cc: Stephen Frost <sfrost@snowman.net>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On 2018-01-09 18:58:41 +0100, Rakesh Kumar wrote:
>
> > That said, imv anyway, the performance hit is small and having checksums
> > is well worth it.
>
> I also would like to believe that the hit is small, but when PG
> official document writes "noticeable performance penalty", it becomes
> difficult to convince management that the hit is small :-)
noticeable != huge.
- Andres
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: data-checksums
@ 2018-01-09 18:41 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: Rakesh Kumar <rakeshkumar464@mail.com>
2 siblings, 1 reply; 19+ messages in thread
From: Alvaro Herrera @ 2018-01-09 18:41 UTC (permalink / raw)
To: Rakesh Kumar <rakeshkumar464@mail.com>; +Cc: Stephen Frost <sfrost@snowman.net>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Rakesh Kumar wrote:
>
> > That said, imv anyway, the performance hit is small and having
> > checksums is well worth it.
>
> I also would like to believe that the hit is small, but when PG
> official document writes "noticeable performance penalty", it becomes
> difficult to convince management that the hit is small :-)
Why believe, when you can measure?
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: data-checksums
@ 2018-01-09 19:02 Rakesh Kumar <rakeshkumar464@mail.com>
parent: Andres Freund <andres@anarazel.de>
0 siblings, 1 reply; 19+ messages in thread
From: Rakesh Kumar @ 2018-01-09 19:02 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; +Cc: Stephen Frost <sfrost@snowman.net>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Hello Mr. Pedantic,
> noticeable != huge.
and noticeable != small/negligible either, at least from English point of view.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: data-checksums
@ 2018-01-09 19:04 Rakesh Kumar <rakeshkumar464@mail.com>
parent: Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 19+ messages in thread
From: Rakesh Kumar @ 2018-01-09 19:04 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Stephen Frost <sfrost@snowman.net>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
> > I also would like to believe that the hit is small, but when PG
> > official document writes "noticeable performance penalty", it becomes
> > difficult to convince management that the hit is small :-)
>
> Why believe, when you can measure?
yup doing that. But I still feel that PG documentation should stay away from such scare mongering. Or did the lawyers write that :)
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: data-checksums
@ 2018-01-09 19:20 Jeff Janes <jeff.janes@gmail.com>
parent: Rakesh Kumar <rakeshkumar464@mail.com>
2 siblings, 0 replies; 19+ messages in thread
From: Jeff Janes @ 2018-01-09 19:20 UTC (permalink / raw)
To: Rakesh Kumar <rakeshkumar464@mail.com>; +Cc: Stephen Frost <sfrost@snowman.net>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Tue, Jan 9, 2018 at 12:58 PM, Rakesh Kumar <rakeshkumar464@mail.com>
wrote:
>
> > That said, imv anyway, the performance hit is small and having checksums
> > is well worth it.
>
> I also would like to believe that the hit is small, but when PG official
> document writes "noticeable performance penalty", it becomes difficult to
> convince management that the hit is small :-)
>
>
Why ask us questions if you won't believe our answers?
Noticeable means it probably isn't important for most real-world cases, but
if you work at it you can probably detect it.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: data-checksums
@ 2018-01-09 20:02 George Neuner <gneuner2@comcast.net>
parent: Rakesh Kumar <rakeshkumar464@mail.com>
0 siblings, 0 replies; 19+ messages in thread
From: George Neuner @ 2018-01-09 20:02 UTC (permalink / raw)
To: pgsql-general
"On Tue, 9 Jan 2018 20:02:37 +0100, "Rakesh Kumar"
<rakeshkumar464@mail.com> wrote:
>Hello Mr. Pedantic,
>
>> noticeable != huge.
>
>and noticeable != small/negligible either, at least from English
>point of view.
small != negligible. <grin>
The word "noticable" does not imply any particular magnitude of event.
It means only that <something> can be observed.
There is no canon technical usage. In layman's use, "noticable" often
*does* imply that an effect is small, and that one might not see it if
not looking specifically for it.
Unfortunately, English is a slippery language. Perhaps technical
documents should be written in Sumerian.
YMMV,
George
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: data-checksums
@ 2018-01-09 20:22 Andres Freund <andres@anarazel.de>
parent: Rakesh Kumar <rakeshkumar464@mail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Andres Freund @ 2018-01-09 20:22 UTC (permalink / raw)
To: Rakesh Kumar <rakeshkumar464@mail.com>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; Stephen Frost <sfrost@snowman.net>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On 2018-01-09 20:04:04 +0100, Rakesh Kumar wrote:
>
> > > I also would like to believe that the hit is small, but when PG
> > > official document writes "noticeable performance penalty", it becomes
> > > difficult to convince management that the hit is small :-)
> >
> > Why believe, when you can measure?
>
> yup doing that. But I still feel that PG documentation should stay
> away from such scare mongering. Or did the lawyers write that :)
So we should rather lie about it having a potential for performance
impact? Who'd be helped by that?
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: data-checksums
@ 2018-01-09 20:37 Joshua D. Drake <jd@commandprompt.com>
parent: Andres Freund <andres@anarazel.de>
0 siblings, 0 replies; 19+ messages in thread
From: Joshua D. Drake @ 2018-01-09 20:37 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; Rakesh Kumar <rakeshkumar464@mail.com>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; Stephen Frost <sfrost@snowman.net>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On 01/09/2018 12:22 PM, Andres Freund wrote:
> On 2018-01-09 20:04:04 +0100, Rakesh Kumar wrote:
>>>> I also would like to believe that the hit is small, but when PG
>>>> official document writes "noticeable performance penalty", it becomes
>>>> difficult to convince management that the hit is small :-)
>>> Why believe, when you can measure?
>> yup doing that. But I still feel that PG documentation should stay
>> away from such scare mongering. Or did the lawyers write that :)
> So we should rather lie about it having a potential for performance
> impact? Who'd be helped by that?
It isn't a lie, it depends on the workload and hardware. Adjusting the
documentation to say something like the following probably isn't a bad idea:
The use of the data checksum feature may incur a performance penalty.
However, this does depend on your particular workload and provisioned
hardware. It is wise to test the feature based on your specific
requirements.
JD
--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
***** Unless otherwise stated, opinions are my own. *****
^ permalink raw reply [nested|flat] 19+ messages in thread
* data checksums
@ 2024-08-06 15:11 bruno vieira da silva <brunogiovs@gmail.com>
0 siblings, 3 replies; 19+ messages in thread
From: bruno vieira da silva @ 2024-08-06 15:11 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Hello.
I've been dealing with some database corruption events, so i've been
wondering to enable data checksums on my deployments.
so my question is why data checksums aren't enabled by default on pg? the
pg doc
mentions a considerable performance penality, how considerable it is?
Thanks
--
Bruno Vieira da Silva
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: data checksums
@ 2024-08-06 16:25 Ron Johnson <ronljohnsonjr@gmail.com>
parent: bruno vieira da silva <brunogiovs@gmail.com>
2 siblings, 0 replies; 19+ messages in thread
From: Ron Johnson @ 2024-08-06 16:25 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
On Tue, Aug 6, 2024 at 11:12 AM bruno vieira da silva <brunogiovs@gmail.com>
wrote:
> Hello.
> I've been dealing with some database corruption events, so i've been
> wondering to enable data checksums on my deployments.
>
> so my question is why data checksums aren't enabled by default on pg? the
> pg doc
> mentions a considerable performance penality, how considerable it is?
>
I'm going to hazard a guess that the relevant line from the docs were
written many many years ago, back when servers were much slower.
We certainly didn't notice any speed degradation when going from PG 9.6.24
to PG 14.12. Or... we just use enough hardware and flash-only SAN disks to
make the overhead minuscule.
--
Death to America, and butter sauce.
Iraq lobster!
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: data checksums
@ 2024-08-06 16:29 Christophe Pettus <xof@thebuild.com>
parent: bruno vieira da silva <brunogiovs@gmail.com>
2 siblings, 2 replies; 19+ messages in thread
From: Christophe Pettus @ 2024-08-06 16:29 UTC (permalink / raw)
To: bruno vieira da silva <brunogiovs@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
> On Aug 6, 2024, at 08:11, bruno vieira da silva <brunogiovs@gmail.com> wrote:
>
> so my question is why data checksums aren't enabled by default on pg?
At this point, mostly historical reasons. They're also superfluous if your underlying file system or storage hardware does storage-level corruption checks (which most don't).
> the pg doc
> mentions a considerable performance penality, how considerable it is?
That line is probably somewhat out of date at this point. We haven't seen a significant slowdown in enabling them on any modern hardware. I always turn them on, except on the type of filesystems/hardware mentioned above.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: data checksums
@ 2024-08-06 16:29 Greg Sabino Mullane <htamfids@gmail.com>
parent: bruno vieira da silva <brunogiovs@gmail.com>
2 siblings, 0 replies; 19+ messages in thread
From: Greg Sabino Mullane @ 2024-08-06 16:29 UTC (permalink / raw)
To: bruno vieira da silva <brunogiovs@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
The penalty is not "considerable", but it is not completely trivial either.
But it's more on the trivial side. Part of the problem is that it is hard
to measure, as it is very workload dependent. As to why it is not the
default, Postgres is very careful and conservative by default, and not
everybody was convinced that enabling checksums is worth the tradeoff,
especially (IIRC) with the lack of real-world examples of people
discovering issues thanks to these checksums. So yes, please enable and
share with us if the checksums catch anything.
I think the argument is worth re-opening again, because (as far as I know),
all of the companies providing Postgres support, whether completely
cloud-managed, setting up a complex HA cluster, or just providing tuning
advice, have enabled checksums by default for many, many years. So a big +1
from me to finally making it the default. It's a de-facto default anyway at
this point.
Cheers,
Greg
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: data checksums
@ 2024-08-07 02:45 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Christophe Pettus <xof@thebuild.com>
1 sibling, 1 reply; 19+ messages in thread
From: Laurenz Albe @ 2024-08-07 02:45 UTC (permalink / raw)
To: Christophe Pettus <xof@thebuild.com>; bruno vieira da silva <brunogiovs@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
On Tue, 2024-08-06 at 09:29 -0700, Christophe Pettus wrote:
>
> > On Aug 6, 2024, at 08:11, bruno vieira da silva <brunogiovs@gmail.com> wrote:
> >
> > so my question is why data checksums aren't enabled by default on pg?
>
> At this point, mostly historical reasons. They're also superfluous if your underlying
> file system or storage hardware does storage-level corruption checks (which most don't).
I am surprised by that. Would you say that most storage systems will happily give you a
garbage block if there was a hardware problem somewhere?
> > the pg doc
> > mentions a considerable performance penality, how considerable it is?
>
> That line is probably somewhat out of date at this point. We haven't seen a significant
> slowdown in enabling them on any modern hardware. I always turn them on, except on the
> type of filesystems/hardware mentioned above.
Turning data checksums on will write WAL for hint bits, which can significantly increase
the amount of WAL written.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: data checksums
@ 2024-08-07 03:14 Christophe Pettus <xof@thebuild.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 19+ messages in thread
From: Christophe Pettus @ 2024-08-07 03:14 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: bruno vieira da silva <brunogiovs@gmail.com>; pgsql-general@lists.postgresql.org
> On Aug 6, 2024, at 19:45, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> I am surprised by that. Would you say that most storage systems will happily give you a
> garbage block if there was a hardware problem somewhere?
"Most" is hard for me to judge. HDDs can have uncorrected and undetected errors, definitely. ZFS, for example, can correct those (within limits); XFS doesn't try. I have been told that SSDs can have uncorrected/undetected errors as well, but I don't know details on that.
> Turning data checksums on will write WAL for hint bits, which can significantly increase
> the amount of WAL written.
I was curious about that, so I just did a quick experiment using pgbench, with identical databases except for checksums. They both generated the same amount of WAL within 10% or so, so I don't think the impact is huge. (And you need the hint bits for pg_rewind, which is a nice thing to have in your back pocket if required.)
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: data checksums
@ 2024-08-07 07:41 Daniel Gustafsson <daniel@yesql.se>
parent: Christophe Pettus <xof@thebuild.com>
1 sibling, 1 reply; 19+ messages in thread
From: Daniel Gustafsson @ 2024-08-07 07:41 UTC (permalink / raw)
To: Christophe Pettus <xof@thebuild.com>; +Cc: bruno vieira da silva <brunogiovs@gmail.com>; pgsql-general@lists.postgresql.org
> On 6 Aug 2024, at 18:29, Christophe Pettus <xof@thebuild.com> wrote:
>> On Aug 6, 2024, at 08:11, bruno vieira da silva <brunogiovs@gmail.com> wrote:
>> the pg doc
>> mentions a considerable performance penality, how considerable it is?
>
> That line is probably somewhat out of date at this point. We haven't seen a significant slowdown in enabling them on any modern hardware. I always turn them on, except on the type of filesystems/hardware mentioned above.
The last in-depth analysis of data checksums (and hint bits) overhead that I
can remember is from 2019:
https://www.postgresql.org/message-id/20190330192543.GH4719%40development
Hardware advances in the last five years may very well have made these findings
irrelevant however.
--
Daniel Gustafsson
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: data checksums
@ 2024-08-07 13:52 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Daniel Gustafsson <daniel@yesql.se>
0 siblings, 0 replies; 19+ messages in thread
From: Ron Johnson @ 2024-08-07 13:52 UTC (permalink / raw)
To: Daniel Gustafsson <daniel@yesql.se>; +Cc: Christophe Pettus <xof@thebuild.com>; bruno vieira da silva <brunogiovs@gmail.com>; pgsql-general@lists.postgresql.org
On Wed, Aug 7, 2024 at 3:41 AM Daniel Gustafsson <daniel@yesql.se> wrote:
> > On 6 Aug 2024, at 18:29, Christophe Pettus <xof@thebuild.com> wrote:
> >> On Aug 6, 2024, at 08:11, bruno vieira da silva <brunogiovs@gmail.com>
> wrote:
>
> >> the pg doc
> >> mentions a considerable performance penality, how considerable it is?
> >
> > That line is probably somewhat out of date at this point. We haven't
> seen a significant slowdown in enabling them on any modern hardware. I
> always turn them on, except on the type of filesystems/hardware mentioned
> above.
>
> The last in-depth analysis of data checksums (and hint bits) overhead that
> I
> can remember is from 2019:
>
>
> https://www.postgresql.org/message-id/20190330192543.GH4719%40development
A quote from that post:
"I have not investigated the exact reasons, but my hypothesis it's about
the amount of WAL generated during the initial CREATE INDEX (because it
probably ends up setting the hint bits), which puts additional pressure
on the storage."
Presuming that hypothesis is true: how often do "you" run CREATE INDEX (or
VACUUM FULL or CLUSTER)? I certainly don't run them very often.
--
Death to America, and butter sauce!
Iraq lobster...
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2024-08-07 13:52 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-01-09 17:42 data-checksums Rakesh Kumar <rakeshkumar464@mail.com>
2018-01-09 17:54 ` Stephen Frost <sfrost@snowman.net>
2018-01-09 17:58 ` Rakesh Kumar <rakeshkumar464@mail.com>
2018-01-09 18:39 ` Andres Freund <andres@anarazel.de>
2018-01-09 19:02 ` Rakesh Kumar <rakeshkumar464@mail.com>
2018-01-09 20:02 ` George Neuner <gneuner2@comcast.net>
2018-01-09 18:41 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2018-01-09 19:04 ` Rakesh Kumar <rakeshkumar464@mail.com>
2018-01-09 20:22 ` Andres Freund <andres@anarazel.de>
2018-01-09 20:37 ` Joshua D. Drake <jd@commandprompt.com>
2018-01-09 19:20 ` Jeff Janes <jeff.janes@gmail.com>
2024-08-06 15:11 data checksums bruno vieira da silva <brunogiovs@gmail.com>
2024-08-06 16:25 ` Re: data checksums Ron Johnson <ronljohnsonjr@gmail.com>
2024-08-06 16:29 ` Re: data checksums Christophe Pettus <xof@thebuild.com>
2024-08-07 02:45 ` Re: data checksums Laurenz Albe <laurenz.albe@cybertec.at>
2024-08-07 03:14 ` Re: data checksums Christophe Pettus <xof@thebuild.com>
2024-08-07 07:41 ` Re: data checksums Daniel Gustafsson <daniel@yesql.se>
2024-08-07 13:52 ` Re: data checksums Ron Johnson <ronljohnsonjr@gmail.com>
2024-08-06 16:29 ` Re: data checksums Greg Sabino Mullane <htamfids@gmail.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