标簽
PostgreSQL , Oracle , checksum , IO錯誤
https://github.com/digoal/blog/blob/master/201805/20180528_01.md#%E8%83%8C%E6%99%AF 背景
開啟資料庫block checksum可以發現磁盤、存儲、IO系統的問題引入一些的實體錯誤。
https://github.com/digoal/blog/blob/master/201805/20180528_01.md#postgresql-checksum PostgreSQL checksum
在PostgreSQL中，預設強制對XLOG（WAL）開啟了checksum，是以可以保證從redo buffer寫入redo file裡面的資料是一緻的，讀取的時候也會根據每個REDO PAGE的checksum檢查REDO PAGE的内容是否正确。（換言之沒有參數來關閉WAL的checksum）
而對于DATAFILE，是否開啟CHECKSUM則取決于initdb時配置的checksum參數。
initdb
-k, --data-checksums use data page checksums
通過pg_controldata檢查目前資料庫叢集是否開啟了checksum。
pg_controldata
Data page checksum version: 0
PostgreSQL 11 允許使用者動态的修改checksum的開關，而不是初始化執行個體時固定：
《PostgreSQL 11 preview - Allow on-line enabling and disabling of data checksums (含pg_verify_checksums工具，離線檢查資料檔案有誤塊錯誤)》https://github.com/digoal/blog/blob/master/201805/20180528_01.md#oracle-checksum Oracle checksum
Checksums allow Oracle to detect corruption caused by underlying disks, storage systems, or I/O systems.
If this parameter is set to OFF, DBWn calculates checksums only for the SYSTEM tablespace, but not for user tablespaces. In addition, no log checksum is performed when this parameter is set to OFF.
https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams046.htm#REFRN10030DB_BLOCK_CHECKSUM determines whether DBWn and the direct loader will calculate a checksum (a number calculated from all the bytes stored in the block) and store it in the cache header of every data block when writing it to disk. Checksums are verified when a block is read - only if this parameter is TYPICAL or FULL and the last write of the block stored a checksum. In FULL mode, Oracle also verifies the checksum before a change application from update/delete statements and recomputes it after the change is applied. In addition, Oracle gives every log block a checksum before writing it to the current log.
Starting with Oracle Database 11g, most of the log block checksum is done by the generating foreground processes, while the LGWR performs the rest of the work, for better CPU and cache efficiency. Prior to Oracle Database 11g, the LGWR solely performed the log block checksum. When this parameter is set to FULL, the LGWR verifies the checksum of each log block generated by the foreground processes before writing it to disk.
Checksums allow Oracle to detect corruption caused by underlying disks, storage systems, or I/O systems. If set to FULL, DB_BLOCK_CHECKSUM also catches in-memory corruptions and stops them from making it to the disk. Turning on this feature in TYPICAL mode causes only an additional 1% to 2% overhead. In the FULL mode it causes 4% to 5% overhead. Oracle recommends that you set DB_BLOCK_CHECKSUM to TYPICAL.
For backward compatibility the use of TRUE (implying TYPICAL) and FALSE (implying OFF) values is preserved.
https://github.com/digoal/blog/blob/master/201805/20180528_01.md#postgresql-checksum-%E8%A1%8C%E4%B8%BA PostgreSQL checksum 行為
1、開啟checksum後，PostgreSQL 從shared buffer把資料write出去，需要計算checksum。
2、開啟checksum後，從shared buffer外面（disk, os page cache）讀取BLOCK到shared buffer裡面，需要計算block的checksum，對比存儲在page head裡頭的checksum是否一緻。
3、已經在shared buffer裡面的block，變更、讀取時并不需要計算checksum。
https://github.com/digoal/blog/blob/master/201805/20180528_01.md#checksum%E8%AE%A1%E7%AE%97%E9%80%BB%E8%BE%91 checksum計算邏輯
1、pg_checksum_page
src/include/storage/checksum_impl.h
/*
* Compute the checksum for a Postgres page. The page must be aligned on a
* 4-byte boundary.
*
* The checksum includes the block number (to detect the case where a page is
* somehow moved to a different location), the page header (excluding the
* checksum itself), and the page data.
*/
uint16
pg_checksum_page(char *page, BlockNumber blkno)
{
PageHeader phdr = (PageHeader) page;
uint16 save_checksum;
uint32 checksum;
/* We only calculate the checksum for properly-initialized pages */
Assert(!PageIsNew(page));
/*
* Save pd_checksum and temporarily set it to zero, so that the checksum
* calculation isn't affected by the old checksum stored on the page.
* Restore it after, because actually updating the checksum is NOT part of
* the API of this function.
*/
save_checksum = phdr->pd_checksum;
phdr->pd_checksum = 0;
checksum = pg_checksum_block(page, BLCKSZ);
phdr->pd_checksum = save_checksum;
/* Mix in the block number to detect transposed pages */
checksum ^= blkno;
/*
* Reduce to a uint16 (to fit in the pd_checksum field) with an offset of
* one. That avoids checksums of zero, which seems like a good idea.
*/
return (checksum % 65535) + 1;
}
2、異步寫（bg writer, backend process evict dirty page）時，計算checksum
src/backend/storage/buffer/bufmgr.c
/*
* FlushBuffer
* Physically write out a shared buffer.
*
* NOTE: this actually just passes the buffer contents to the kernel; the
* real write to disk won't happen until the kernel feels like it. This
* is okay from our point of view since we can redo the changes from WAL.
* However, we will need to force the changes to disk via fsync before
* we can checkpoint WAL.
*
* The caller must hold a pin on the buffer and have share-locked the
* buffer contents. (Note: a share-lock does not prevent updates of
* hint bits in the buffer, so the page could change while the write
* is in progress, but we assume that that will not invalidate the data
* written.)
*
* If the caller has an smgr reference for the buffer's relation, pass it
* as the second parameter. If not, pass NULL.
*/
static void
FlushBuffer(BufferDesc *buf, SMgrRelation reln)
/*
* Update page checksum if desired. Since we have only shared lock on the
* buffer, other processes might be updating hint bits in it, so we must
* copy the page to private storage if we do checksumming.
*/
bufToWrite = PageSetChecksumCopy((Page) bufBlock, buf->tag.blockNum);
3、将資料從shared buffer外圍讀入shared buffer時，校驗checksum
src/backend/storage/page/bufpage.c
/*
* PageIsVerified
* Check that the page header and checksum (if any) appear valid.
*
* This is called when a page has just been read in from disk. The idea is
* to cheaply detect trashed pages before we go nuts following bogus item
* pointers, testing invalid transaction identifiers, etc.
*
* It turns out to be necessary to allow zeroed pages here too. Even though
* this routine is *not* called when deliberately adding a page to a relation,
* there are scenarios in which a zeroed page might be found in a table.
* (Example: a backend extends a relation, then crashes before it can write
* any WAL entry about the new page. The kernel will already have the
* zeroed page in the file, and it will stay that way after restart.) So we
* allow zeroed pages here, and are careful that the page access macros
* treat such a page as empty and without free space. Eventually, VACUUM
* will clean up such a page and make it usable.
*/
bool
PageIsVerified(Page page, BlockNumber blkno)
{
PageHeader p = (PageHeader) page;
size_t *pagebytes;
int i;
bool checksum_failure = false;
bool header_sane = false;
bool all_zeroes = false;
uint16 checksum = 0;
/*
* Don't verify page data unless the page passes basic non-zero test
*/
if (!PageIsNew(page))
{
if (DataChecksumsEnabled())
{
checksum = pg_checksum_page((char *) page, blkno);
if (checksum != p->pd_checksum)
checksum_failure = true;
}
...................
/*
* Set checksum for a page in shared buffers.
*
* If checksums are disabled, or if the page is not initialized, just return
* the input. Otherwise, we must make a copy of the page before calculating
* the checksum, to prevent concurrent modifications (e.g. setting hint bits)
* from making the final checksum invalid. It doesn't matter if we include or
* exclude hints during the copy, as long as we write a valid page and
* associated checksum.
*
* Returns a pointer to the block-sized data that needs to be written. Uses
* statically-allocated memory, so the caller must immediately write the
* returned page and not refer to it again.
*/
char *
PageSetChecksumCopy(Page page, BlockNumber blkno)
{
static char *pageCopy = NULL;
/* If we don't need a checksum, just return the passed-in data */
if (PageIsNew(page) || !DataChecksumsEnabled())
return (char *) page;
/*
* We allocate the copy space once and use it over on each subsequent
* call. The point of palloc'ing here, rather than having a static char
* array, is first to ensure adequate alignment for the checksumming code
* and second to avoid wasting space in processes that never call this.
*/
if (pageCopy == NULL)
pageCopy = MemoryContextAlloc(TopMemoryContext, BLCKSZ);
memcpy(pageCopy, (char *) page, BLCKSZ);
((PageHeader) pageCopy)->pd_checksum = pg_checksum_page(pageCopy, blkno);
return pageCopy;
}
/*
* Set checksum for a page in private memory.
*
* This must only be used when we know that no other process can be modifying
* the page buffer.
*/
void
PageSetChecksumInplace(Page page, BlockNumber blkno)
{
/* If we don't need a checksum, just return */
if (PageIsNew(page) || !DataChecksumsEnabled())
return;
((PageHeader) page)->pd_checksum = pg_checksum_page((char *) page, blkno);
}
https://github.com/digoal/blog/blob/master/201805/20180528_01.md#%E6%80%A7%E8%83%BD%E6%B5%8B%E8%AF%95 性能測試
對比開啟checksum, 關閉checksum的性能。
從代碼我們已經了解什麼時候會需要計算checksum，是以設計一個這個的CASE，資料大量寫出，使得bgwrite writeout dirty page頻繁。
用到如下CASE
《HTAP資料庫 PostgreSQL 場景與性能測試之 42 - (OLTP+OLAP) unlogged table 不含索引多表批量寫入》https://github.com/digoal/blog/blob/master/201805/20180528_01.md#%E6%B5%8B%E8%AF%95%E7%BB%93%E6%9E%9C 測試結果
checksum=0
transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 56
number of threads: 56
duration: 120 s
number of transactions actually processed: 885490
latency average = 7.588 ms
latency stddev = 10.896 ms
tps = 7376.390493 (including connections establishing)
tps = 7377.158206 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.002 \set sid random(1,1024)
7.586 select ins_sensor(:sid, 1000);
checksum=1
transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 56
number of threads: 56
duration: 120 s
number of transactions actually processed: 867269
latency average = 7.748 ms
latency stddev = 20.287 ms
tps = 7225.742548 (including connections establishing)
tps = 7226.431737 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.002 \set sid random(1,1024)
7.746 select ins_sensor(:sid, 1000);
開啟checksum時，觀察perf，可以看到pg_checksum_page占用了少量overhead，展現在性能差異上也非常的小。
0.64% postgres [.] pg_checksum_page
https://github.com/digoal/blog/blob/master/201805/20180528_01.md#%E5%B0%8F%E7%BB%93 小結
在bgwriter, backend process write dirty page時，需要計算checksum。(耗費CPU)
在從shared buffer外面讀入page時，需要校驗checksum。(耗費CPU)
當shared buffer較少，同時産生髒頁較快，bg writer 或者backend process 刷髒頁較頻繁時，可能會使得計算checksum引入一定的CPU消耗。實測極端寫出的情況下，pg_checksum_page引入了0.64%左右的開銷。
是以開啟checksum，實際引入的開銷并不大。
https://github.com/digoal/blog/blob/master/201805/20180528_01.md#%E5%8F%82%E8%80%83 參考
1、
https://www.postgresql.org/docs/11/static/runtime-config-preset.htmldata_checksums (boolean)
Reports whether data checksums are enabled for this cluster. See data checksums for more information.
2、
https://www.postgresql.org/docs/11/static/runtime-config-developer.htmlignore_checksum_failure (boolean)
Only has effect if data checksums are enabled.
Detection of a checksum failure during a read normally causes PostgreSQL to report an error, aborting the current transaction. Setting ignore_checksum_failure to on causes the system to ignore the failure (but still report a warning), and continue processing. This behavior may cause crashes, propagate or hide corruption, or other serious problems. However, it may allow you to get past the error and retrieve undamaged tuples that might still be present in the table if the block header is still sane. If the header is corrupt an error will be reported even if this option is enabled. The default setting is off, and it can only be changed by a superuser.
3、
https://www.postgresql.org/docs/11/static/pageinspect.html#id-1.11.7.31.4page_header(page bytea) returns record
page_header shows fields that are common to all PostgreSQL heap and index pages.
A page image obtained with get_raw_page should be passed as argument. For example:
test=# SELECT * FROM page_header(get_raw_page('pg_class', 0));
lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid
-----------+----------+--------+-------+-------+---------+----------+---------+-----------
0/24A1B50 | 0 | 1 | 232 | 368 | 8192 | 8192 | 4 | 0
The returned columns correspond to the fields in the PageHeaderData struct. See src/include/storage/bufpage.h for details.
The checksum field is the checksum stored in the page, which might be incorrect if the page is somehow corrupted. If data checksums are not enabled for this instance, then the value stored is meaningless.
page_checksum(page bytea, blkno int4) returns smallint
page_checksum computes the checksum for the page, as if it was located at the given block.
test=# SELECT page_checksum(get_raw_page('pg_class', 0), 0);
page_checksum
---------------
13443
Note that the checksum depends on the block number, so matching block numbers should be passed (except when doing esoteric debugging).
The checksum computed with this function can be compared with the checksum result field of the function page_header. If data checksums are enabled for this instance, then the two values should be equal.
4、
https://www.postgresql.org/docs/11/static/pgverifychecksums.htmlpg_verify_checksums
pg_verify_checksums — verify data checksums in an offline PostgreSQL database cluster
Synopsis
pg_verify_checksums [option] [[-D] datadir]
Description
pg_verify_checksums verifies data checksums in a PostgreSQL cluster.
5、
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)