Обсуждение: question about checksum in 9.3
Hello, I am in the process of planning a 9.3 migration of postgres and I am curious about the checksum features available. In my test 9.3 instance it seemed like this feature provides a log entry of the exact database/oid of the corrupt object when it is accessed, but not much else. I can't find much documentation on anything else this feature provides.
Is there a built-in method of scanning the server to check for corruption or will I have to wait for a corrupt object to be accessed to see the log entry? Is there a relation that stores last checksum status or anyway of reporting on what objects are identified by postgres as corrupt or not corrupt?
Are there any other features of the checksum I am missing besides the log entry?
Thanks
Mike
On 01/13/2014 08:50 AM, Mike Broers wrote: > Hello, I am in the process of planning a 9.3 migration of postgres and I > am curious about the checksum features available. In my test 9.3 > instance it seemed like this feature provides a log entry of the exact > database/oid of the corrupt object when it is accessed, but not much > else. I can't find much documentation on anything else this feature > provides. > > Is there a built-in method of scanning the server to check for > corruption or will I have to wait for a corrupt object to be accessed to > see the log entry? Is there a relation that stores last checksum status > or anyway of reporting on what objects are identified by postgres as > corrupt or not corrupt? > > Are there any other features of the checksum I am missing besides the > log entry? I could not find anything and the reasons for that would seem to a result of this sentence: "The checksum is not valid at all times on a data page!!" from the below where the gory details are laid out: src/backend/storage/page/README > > Thanks > Mike -- Adrian Klaver adrian.klaver@gmail.com
On Tue, Jan 14, 2014 at 1:50 AM, Mike Broers <mbroers@gmail.com> wrote: > Hello, I am in the process of planning a 9.3 migration of postgres and I am > curious about the checksum features available. In my test 9.3 instance it > seemed like this feature provides a log entry of the exact database/oid of > the corrupt object when it is accessed, but not much else. I can't find > much documentation on anything else this feature provides. Few things: - The only way to know if a server is using data checksums is to use pg_controldata. - Be aware as well of the potential performance impact on your CPU, checksums are checked each time a page is read, and recalculated each time a page is updated. - ignore_checksum_failure can be used to ignore failures. Don't use that on a production system. > Is there a built-in method of scanning the server to check for corruption or > will I have to wait for a corrupt object to be accessed to see the log > entry? You can as well access manually tables with some for example sequential scan to check if blocks are broken or not. > Is there a relation that stores last checksum status or anyway of > reporting on what objects are identified by postgres as corrupt or not > corrupt? No, you could build one though with a background worker that scans relation pages and registers that failing blocks. > Are there any other features of the checksum I am missing besides the log > entry? 9.4 has a new GUC parameter called data_checksums that allow you to check with a psql client if checksums are used on a server. Regards, -- Michael
Thanks for the responses, that clarifies the checksum feature for me.
FWIW, my pgbench tests between a 9.2 server and a 9.3 server with checksum showed very similar performance characteristics and system resource utilization. Im going to set up another load test with our actual application to see if that reveals any noticeable performance difference.
Thanks again
Mike
On Mon, Jan 13, 2014 at 7:11 PM, Michael Paquier <michael.paquier@gmail.com> wrote:
On Tue, Jan 14, 2014 at 1:50 AM, Mike Broers <mbroers@gmail.com> wrote:Few things:
> Hello, I am in the process of planning a 9.3 migration of postgres and I am
> curious about the checksum features available. In my test 9.3 instance it
> seemed like this feature provides a log entry of the exact database/oid of
> the corrupt object when it is accessed, but not much else. I can't find
> much documentation on anything else this feature provides.
- The only way to know if a server is using data checksums is to use
pg_controldata.
- Be aware as well of the potential performance impact on your CPU,
checksums are checked each time a page is read, and recalculated each
time a page is updated.
- ignore_checksum_failure can be used to ignore failures. Don't use
that on a production system.You can as well access manually tables with some for example
> Is there a built-in method of scanning the server to check for corruption or
> will I have to wait for a corrupt object to be accessed to see the log
> entry?
sequential scan to check if blocks are broken or not.No, you could build one though with a background worker that scans
> Is there a relation that stores last checksum status or anyway of
> reporting on what objects are identified by postgres as corrupt or not
> corrupt?
relation pages and registers that failing blocks.9.4 has a new GUC parameter called data_checksums that allow you to
> Are there any other features of the checksum I am missing besides the log
> entry?
check with a psql client if checksums are used on a server.
Regards,
--
Michael
On 13/01/14 17:50, Mike Broers wrote: > Is there a built-in method of scanning the server to check for > corruption or will I have to wait for a corrupt object to be accessed to > see the log entry? This presentation: http://www.youtube.com/watch?v=TovZ0lb16-Q suggests pg_filedump. Torsten