Thread: data-checksums

--data-checksums
Use checksums on data pages to help detect corruption by the I/O system that would otherwise be silent. Enabling
checksumsmay incur a noticeable performance penalty. This option can only be set during initialization, and cannot be
changedlater. 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
writerand/or during checkpoint.  Given that the process is async and application does not wait on it, is the
performancepenalty really a big concern.
 

thanks

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
checksumsmay incur a noticeable performance penalty. This option can only be set during initialization, and cannot be
changedlater. 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
writerand/or during checkpoint.  Given that the process is async and application does not wait on it, is the
performancepenalty 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

> 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 :-)
 

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

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

Hello Mr. Pedantic,

> noticeable != huge.

and noticeable != small/negligible either, at least from English point of view.

> > 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
writethat :)
 

"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

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

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.   *****

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.

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

Hi,

On 2018-01-09 21:47:17 +0100, Andreas Joseph Krogh wrote:
> Does PG use HW-accellerated crc if CPU supports it[1]?

Yes we do, for WAL checksums. The page checksums are a different
algorithm though, one which has the advantage of being SIMD compatible.

The checksum computations have some impact, but if there's bigger impact
it's much more likely to be related to the fact that some hint bit
writes to a page now needs to be WAL logged.

Andres Freund

Hi,

On 2018-01-09 21:47:17 +0100, Andreas Joseph Krogh wrote:
> Does PG use HW-accellerated crc if CPU supports it[1]?

Yes we do, for WAL checksums. The page checksums are a different
algorithm though, one which has the advantage of being SIMD compatible.

The checksum computations have some impact, but if there's bigger impact
it's much more likely to be related to the fact that some hint bit
writes to a page now needs to be WAL logged.

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

På tirsdag 09. januar 2018 kl. 23:06:06, skrev Andres Freund <andres@anarazel.de>:

Hi,

On 2018-01-09 21:47:17 +0100, Andreas Joseph Krogh wrote:
> Does PG use HW-accellerated crc if CPU supports it[1]?

Yes we do, for WAL checksums. The page checksums are a different
algorithm though, one which has the advantage of being SIMD compatible.

The checksum computations have some impact, but if there's bigger impact
it's much more likely to be related to the fact that some hint bit
writes to a page now needs to be WAL logged.

 

But SIMD-instructions are also HW-accellerated by modern CPUs IIUC?

 

So, if these CRCs all are HW-accelerated the penalty chould be next to neglishable?

 

   

On 01/09/2018 03:30 PM, Andreas Joseph Krogh wrote:

På tirsdag 09. januar 2018 kl. 23:06:06, skrev Andres Freund <andres@anarazel.de>:

Hi,

On 2018-01-09 21:47:17 +0100, Andreas Joseph Krogh wrote:
> Does PG use HW-accellerated crc if CPU supports it[1]?

Yes we do, for WAL checksums. The page checksums are a different
algorithm though, one which has the advantage of being SIMD compatible.

The checksum computations have some impact, but if there's bigger impact
it's much more likely to be related to the fact that some hint bit
writes to a page now needs to be WAL logged.

 

But SIMD-instructions are also HW-accellerated by modern CPUs IIUC?

 

So, if these CRCs all are HW-accelerated the penalty chould be next to neglishable?

 

Leading directly back to JD's proposed documentation update.

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

On 2018-01-10 00:25:08 +0100, Andreas Joseph Krogh wrote:
> På tirsdag 09. januar 2018 kl. 23:42:45, skrev Rob Sargent <
> robjsargent@gmail.com <mailto:robjsargent@gmail.com>>:
>  
> 
>    On 01/09/2018 03:30 PM, Andreas Joseph Krogh wrote:
> På tirsdag 09. januar 2018 kl. 23:06:06, skrev Andres Freund <
> andres@anarazel.de <mailto:andres@anarazel.de>>:
> Hi,
> 
>  On 2018-01-09 21:47:17 +0100, Andreas Joseph Krogh wrote:
>  > Does PG use HW-accellerated crc if CPU supports it[1]?
> 
>  Yes we do, for WAL checksums. The page checksums are a different
>  algorithm though, one which has the advantage of being SIMD compatible.
> 
>  The checksum computations have some impact, but if there's bigger impact
>  it's much more likely to be related to the fact that some hint bit
>  writes to a page now needs to be WAL logged.
>  
> But SIMD-instructions are also HW-accellerated by modern CPUs IIUC?

Sure. Still measurable, but even if weren't, it's irrelevant given my
primary point:

>  The checksum computations have some impact, but if there's bigger impact
>  it's much more likely to be related to the fact that some hint bit
>  writes to a page now needs to be WAL logged.

which isn't mitigated by SIMD / hardware CRC / whatnot.

Greetings,

Andres Freund

On 2018-01-10 00:25:08 +0100, Andreas Joseph Krogh wrote:
> På tirsdag 09. januar 2018 kl. 23:42:45, skrev Rob Sargent <
> robjsargent@gmail.com <mailto:robjsargent@gmail.com>>:
>  
>
>    On 01/09/2018 03:30 PM, Andreas Joseph Krogh wrote:
> På tirsdag 09. januar 2018 kl. 23:06:06, skrev Andres Freund <
> andres@anarazel.de <mailto:andres@anarazel.de>>:
> Hi,
>
>  On 2018-01-09 21:47:17 +0100, Andreas Joseph Krogh wrote:
>  > Does PG use HW-accellerated crc if CPU supports it[1]?
>
>  Yes we do, for WAL checksums. The page checksums are a different
>  algorithm though, one which has the advantage of being SIMD compatible.
>
>  The checksum computations have some impact, but if there's bigger impact
>  it's much more likely to be related to the fact that some hint bit
>  writes to a page now needs to be WAL logged.
>  
> But SIMD-instructions are also HW-accellerated by modern CPUs IIUC?

Sure. Still measurable, but even if weren't, it's irrelevant given my
primary point:

>  The checksum computations have some impact, but if there's bigger impact
>  it's much more likely to be related to the fact that some hint bit
>  writes to a page now needs to be WAL logged.

which isn't mitigated by SIMD / hardware CRC / whatnot.

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

Greetings,

* Andreas Joseph Krogh (andreas@visena.com) wrote:
> Aha, so enabling CRC causes hint-bits to be written causing extra WAL-logging,
> which woudn't be the case without CRC enabled?
> Thanks for pointing that out.

Yes, having checksums enabled forces logging of hint bits.  You can
enable wal_log_hints independently too, without having checksums, to see
what kind of an impact it'll have on your environment.

A useful documentation update might be:

---
With checksums enabled, wal_log_hints <link to the GUC's documentation>
will be enabled and each page read or write will involve calculating the
checksum for the page.
---

I'd probably just replace the "Enabling checksums may incur a noticeable
performance penalty" with the above, as it should be clear that doing
more work implies an impact on performance and that avoids the whole
question of trying to characterize in a general way something that can't
be generalized (as it's workload dependent).

Thanks!

Stephen

On 2018-01-10 01:31:58 +0100, Andreas Joseph Krogh wrote:
> På onsdag 10. januar 2018 kl. 01:01:26, skrev Andres Freund <andres@anarazel.de 
> <mailto:andres@anarazel.de>>:
> On 2018-01-10 00:25:08 +0100, Andreas Joseph Krogh wrote:
>  > But SIMD-instructions are also HW-accellerated by modern CPUs IIUC?
> 
>  Sure. Still measurable, but even if weren't, it's irrelevant given my
>  primary point:
> 
>  >  The checksum computations have some impact, but if there's bigger impact
>  >  it's much more likely to be related to the fact that some hint bit
>  >  writes to a page now needs to be WAL logged.
> 
>  which isn't mitigated by SIMD / hardware CRC / whatnot.
>  
> Aha, so enabling CRC causes hint-bits to be written causing extra WAL-logging, 
> which woudn't be the case without CRC enabled?
> Thanks for pointing that out.

Well, enabling checksums enables that. CRCs don't play a role for data
checksums. CRCs are a specific class of checksums, a specific one of
those is used in our WAL logging, but the data checksum algorithm isn't
in that class.

Greetings,

Andres Freund

On 2018-01-09 20:51:17 -0500, Stephen Frost wrote:
> Greetings,
> 
> * Andreas Joseph Krogh (andreas@visena.com) wrote:
> > Aha, so enabling CRC causes hint-bits to be written causing extra WAL-logging, 
> > which woudn't be the case without CRC enabled?
> > Thanks for pointing that out.
> 
> Yes, having checksums enabled forces logging of hint bits.  You can
> enable wal_log_hints independently too, without having checksums, to see
> what kind of an impact it'll have on your environment.
> 
> A useful documentation update might be:
> 
> ---
> With checksums enabled, wal_log_hints <link to the GUC's documentation>
> will be enabled and each page read or write will involve calculating the
> checksum for the page.
> ---
> 
> I'd probably just replace the "Enabling checksums may incur a noticeable
> performance penalty" with the above, as it should be clear that doing
> more work implies an impact on performance and that avoids the whole
> question of trying to characterize in a general way something that can't
> be generalized (as it's workload dependent).

-1. I think this is underplaying the cost.

Greetings,

Andres Freund

On Wed, Jan 10, 2018 at 12:23 PM, Thomas Poty <thomas.poty@gmail.com> wrote:

Hello,

A question seems to be, according to me, important :

How a corruption, detected thanks to data-checksums, is fixed?

 

Take two full cold backups of the current mess you have, including the executables, and lock one of them away where you can't accidentally do something to make it worse.

 

Replace the hardware (or fix the software bug) which lead to this, so it doesn't eat more of your data than it already has.

 

If you have a wal archive, then restore from the most recent backup and recover it forward with the WAL, hoping your hardware problem hasn't polluted that as well.  Make sure it rolls forward as far as you think it should.  If you think it rolled forward all the way, then you are probably done.  I'd take a full cold backup as well as as full pg_dump(all) at this point and lock it away for future forensics, just in case, and also to see if any more errors are found by the pg_dump.

 

I'd also extract the damaged block and inspect it.  If the value says "MQs. Johnson" but the obvious (due to context) correction to "Mrs. Johnson" matches what the post-recovery block also says, then I'd say you are pretty good.  If the damage was to a floating point number or a header in which you don't have any useful context to guide you, you might want to engage a professional at this kind of thing if the data is very important to you.

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

On 2018-01-10 11:39:21 -0800, Andres Freund wrote:
> On 2018-01-09 20:51:17 -0500, Stephen Frost wrote:
> > * Andreas Joseph Krogh (andreas@visena.com) wrote:
> > > Aha, so enabling CRC causes hint-bits to be written causing extra WAL-logging,
> > > which woudn't be the case without CRC enabled?
> > > Thanks for pointing that out.
> >
> > Yes, having checksums enabled forces logging of hint bits.  You can
> > enable wal_log_hints independently too, without having checksums, to see
> > what kind of an impact it'll have on your environment.
> >
> > A useful documentation update might be:
> >
> > ---
> > With checksums enabled, wal_log_hints <link to the GUC's documentation>
> > will be enabled and each page read or write will involve calculating the
> > checksum for the page.
> > ---
> >
> > I'd probably just replace the "Enabling checksums may incur a noticeable
> > performance penalty" with the above, as it should be clear that doing
> > more work implies an impact on performance and that avoids the whole
> > question of trying to characterize in a general way something that can't
> > be generalized (as it's workload dependent).
>
> -1. I think this is underplaying the cost.

I disagree. At least two people in this thread interpreted "noticable"
as "measurable but negligible" (which I personally find a bit
surprising). Computing the CRC basically means reading the whole page
from RAM which should be fast compared to a transfer to or from disk. So
it is easy to disregard this sentence as "was probably written when a
Pentium II was new and never updated". Stephen's version draws attention
to the fact that enabling CRCs may cause extra disk writes, which rings
a much louder alarm bell for me. (When are those hint-bits set? Does this
happen often when otherwise no write would have been necessary? I have
no idea so I guess I'd better measure it!)

        hp

--
   _  | Peter J. Holzer    | we build much bigger, better disasters now
|_|_) |                    | because we have much more sophisticated
| |   | hjp@hjp.at         | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>