In this thread, I outlined an idea for reducing cost of WAL CRC checking
http://archives.postgresql.org/pgsql-hackers/2006-10/msg01299.php
wal_checksum = on (default) | off
Recovery can occur with/without same setting of wal_checksum, to avoid
complications from crashes immediately after turning GUC on.
Patch enclosed here against CVS HEAD, passes make check.
Useful reduction in CPU for both normal operation and recovery.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
? GNUmakefile
? RUIP-Oct12.patch
? UIP-datetime.patch
? WAL_notes.txt
? autovac.patch
? backup_minor_changes.patch
? backup_single_transaction.patch
? backup_typo.patch
? cache_option.sql
? check
? checl
? commitsiblings.v1.patch
? config.log
? config.status
? copy_noWAL.patch
? copy_noWAL2.patch
? crc
? d.out
? datatype_frequency.sql
? dbtValidate.sql
? ddl_caveats.patch
? edbDataValidation.sql
? error.sql
? executestricttest
? explain_analyze_timer.v1.patch
? f.sql
? faq.patch
? fastcopytest.sql
? iub_doc.patch
? iubackup.patch
? keepcache.patch
? logfile
? notintransblock.patch
? num_commits++
? on.sql
? pg_standby.v2.tar
? pg_standby.v3.tar
? pgbenchValidate.sql
? pgre.tar
? pitr_cleanup.patch
? portals_per_user.v1.patch
? ps.txt
? relationkeepcache.patch
? relopt
? restartablerecovery_docs.patch
? ri_initial_check.sql
? sel.patch
? singletransdocpatch.patch
? sss
? table_options.patch
? test_warm_standby.tar
? testcrc
? toast_options.sql
? toast_relopts.patch
? toastcache.patch
? tracedocs.patch
? uip_both.patch
? unlink_pg_internal_at_startup.patch
? wal_checksum.v1.patch
? wrap_limit.patch
? xlog_clog_truncate.patch
? xlog_relcache.patch
? xlogswitchtuning.patch
? xlogswitchtuning2.patch
? contrib/pg_relation_extend
? contrib/pg_standby
? contrib/pgbench/.runtest.sh.swp
? contrib/pgbench/pgbench
? contrib/pgbench/tpc_b.sql
? contrib/pgbench/truckin.pgb
? doc/src/sgml/check
? doc/src/sgml/trace.sgml
? src/Makefile.global
? src/backend/postgres
? src/backend/access/nbtree/nbttuple.c
? src/backend/catalog/postgres.bki
? src/backend/catalog/postgres.description
? src/backend/catalog/postgres.shdescription
? src/backend/utils/mb/conversion_procs/conversion_create.sql
? src/backend/utils/mb/conversion_procs/ascii_and_mic/libascii_and_mic.so.0.0
? src/backend/utils/mb/conversion_procs/cyrillic_and_mic/libcyrillic_and_mic.so.0.0
? src/backend/utils/mb/conversion_procs/euc_cn_and_mic/libeuc_cn_and_mic.so.0.0
? src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/libeuc_jp_and_sjis.so.0.0
? src/backend/utils/mb/conversion_procs/euc_kr_and_mic/libeuc_kr_and_mic.so.0.0
? src/backend/utils/mb/conversion_procs/euc_tw_and_big5/libeuc_tw_and_big5.so.0.0
? src/backend/utils/mb/conversion_procs/latin2_and_win1250/liblatin2_and_win1250.so.0.0
? src/backend/utils/mb/conversion_procs/latin_and_mic/liblatin_and_mic.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_ascii/libutf8_and_ascii.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_big5/libutf8_and_big5.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_cyrillic/libutf8_and_cyrillic.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_euc_cn/libutf8_and_euc_cn.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_euc_jp/libutf8_and_euc_jp.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_euc_kr/libutf8_and_euc_kr.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_euc_tw/libutf8_and_euc_tw.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_gb18030/libutf8_and_gb18030.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_gbk/libutf8_and_gbk.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_iso8859/libutf8_and_iso8859.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_iso8859_1/libutf8_and_iso8859_1.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_johab/libutf8_and_johab.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_sjis/libutf8_and_sjis.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_uhc/libutf8_and_uhc.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_win/libutf8_and_win.so.0.0
? src/bin/initdb/initdb
? src/bin/ipcclean/ipcclean
? src/bin/pg_config/pg_config
? src/bin/pg_controldata/pg_controldata
? src/bin/pg_ctl/pg_ctl
? src/bin/pg_dump/pg_dump
? src/bin/pg_dump/pg_dumpall
? src/bin/pg_dump/pg_restore
? src/bin/pg_resetxlog/pg_resetxlog
? src/bin/psql/psql
? src/bin/scripts/clusterdb
? src/bin/scripts/createdb
? src/bin/scripts/createlang
? src/bin/scripts/createuser
? src/bin/scripts/dropdb
? src/bin/scripts/droplang
? src/bin/scripts/dropuser
? src/bin/scripts/reindexdb
? src/bin/scripts/vacuumdb
? src/include/pg_config.h
? src/include/stamp-h
? src/interfaces/ecpg/compatlib/libecpg_compat.so.2.2
? src/interfaces/ecpg/ecpglib/libecpg.so.5.2
? src/interfaces/ecpg/include/ecpg_config.h
? src/interfaces/ecpg/pgtypeslib/libpgtypes.so.2.2
? src/interfaces/ecpg/preproc/ecpg
? src/interfaces/ecpg/test/pg_regress
? src/interfaces/ecpg/test/compat_informix/charfuncs
? src/interfaces/ecpg/test/compat_informix/charfuncs.c
? src/interfaces/ecpg/test/compat_informix/dec_test
? src/interfaces/ecpg/test/compat_informix/dec_test.c
? src/interfaces/ecpg/test/compat_informix/rfmtdate
? src/interfaces/ecpg/test/compat_informix/rfmtdate.c
? src/interfaces/ecpg/test/compat_informix/rfmtlong
? src/interfaces/ecpg/test/compat_informix/rfmtlong.c
? src/interfaces/ecpg/test/compat_informix/rnull
? src/interfaces/ecpg/test/compat_informix/rnull.c
? src/interfaces/ecpg/test/compat_informix/test_informix
? src/interfaces/ecpg/test/compat_informix/test_informix.c
? src/interfaces/ecpg/test/compat_informix/test_informix2
? src/interfaces/ecpg/test/compat_informix/test_informix2.c
? src/interfaces/ecpg/test/complex/test1
? src/interfaces/ecpg/test/complex/test1.c
? src/interfaces/ecpg/test/complex/test2
? src/interfaces/ecpg/test/complex/test2.c
? src/interfaces/ecpg/test/complex/test3
? src/interfaces/ecpg/test/complex/test3.c
? src/interfaces/ecpg/test/connect/test1
? src/interfaces/ecpg/test/connect/test1.c
? src/interfaces/ecpg/test/connect/test1.pgc
? src/interfaces/ecpg/test/connect/test2
? src/interfaces/ecpg/test/connect/test2.c
? src/interfaces/ecpg/test/connect/test3
? src/interfaces/ecpg/test/connect/test3.c
? src/interfaces/ecpg/test/connect/test4
? src/interfaces/ecpg/test/connect/test4.c
? src/interfaces/ecpg/test/connect/test5
? src/interfaces/ecpg/test/connect/test5.c
? src/interfaces/ecpg/test/pgtypeslib/dt_test
? src/interfaces/ecpg/test/pgtypeslib/dt_test.c
? src/interfaces/ecpg/test/pgtypeslib/dt_test2
? src/interfaces/ecpg/test/pgtypeslib/dt_test2.c
? src/interfaces/ecpg/test/pgtypeslib/num_test
? src/interfaces/ecpg/test/pgtypeslib/num_test.c
? src/interfaces/ecpg/test/pgtypeslib/num_test2
? src/interfaces/ecpg/test/pgtypeslib/num_test2.c
? src/interfaces/ecpg/test/preproc/comment
? src/interfaces/ecpg/test/preproc/comment.c
? src/interfaces/ecpg/test/preproc/define
? src/interfaces/ecpg/test/preproc/define.c
? src/interfaces/ecpg/test/preproc/init
? src/interfaces/ecpg/test/preproc/init.c
? src/interfaces/ecpg/test/preproc/type
? src/interfaces/ecpg/test/preproc/type.c
? src/interfaces/ecpg/test/preproc/variable
? src/interfaces/ecpg/test/preproc/variable.c
? src/interfaces/ecpg/test/preproc/whenever
? src/interfaces/ecpg/test/preproc/whenever.c
? src/interfaces/ecpg/test/sql/array
? src/interfaces/ecpg/test/sql/array.c
? src/interfaces/ecpg/test/sql/binary
? src/interfaces/ecpg/test/sql/binary.c
? src/interfaces/ecpg/test/sql/code100
? src/interfaces/ecpg/test/sql/code100.c
? src/interfaces/ecpg/test/sql/copystdout
? src/interfaces/ecpg/test/sql/copystdout.c
? src/interfaces/ecpg/test/sql/define
? src/interfaces/ecpg/test/sql/define.c
? src/interfaces/ecpg/test/sql/desc
? src/interfaces/ecpg/test/sql/desc.c
? src/interfaces/ecpg/test/sql/dynalloc
? src/interfaces/ecpg/test/sql/dynalloc.c
? src/interfaces/ecpg/test/sql/dynalloc2
? src/interfaces/ecpg/test/sql/dynalloc2.c
? src/interfaces/ecpg/test/sql/dyntest
? src/interfaces/ecpg/test/sql/dyntest.c
? src/interfaces/ecpg/test/sql/execute
? src/interfaces/ecpg/test/sql/execute.c
? src/interfaces/ecpg/test/sql/fetch
? src/interfaces/ecpg/test/sql/fetch.c
? src/interfaces/ecpg/test/sql/func
? src/interfaces/ecpg/test/sql/func.c
? src/interfaces/ecpg/test/sql/indicators
? src/interfaces/ecpg/test/sql/indicators.c
? src/interfaces/ecpg/test/sql/quote
? src/interfaces/ecpg/test/sql/quote.c
? src/interfaces/ecpg/test/sql/show
? src/interfaces/ecpg/test/sql/show.c
? src/interfaces/ecpg/test/sql/update
? src/interfaces/ecpg/test/sql/update.c
? src/interfaces/ecpg/test/thread/thread
? src/interfaces/ecpg/test/thread/thread.c
? src/interfaces/ecpg/test/thread/thread_implicit
? src/interfaces/ecpg/test/thread/thread_implicit.c
? src/interfaces/libpq/exports.list
? src/interfaces/libpq/libpq.so.5.0
? src/pl/plperl/SPI.c
? src/pl/plperl/libplperl.so.0.0
? src/pl/plpgsql/src/libplpgsql.so.1.0
? src/port/pg_config_paths.h
? src/test/regress/libregress.so.0.0
? src/test/regress/log
? src/test/regress/pg_regress
? src/test/regress/results
? src/test/regress/testtablespace
? src/test/regress/tmp_check
? src/test/regress/expected/btree_insert.out
? src/test/regress/expected/btree_misc.out
? src/test/regress/expected/btree_search.out
? src/test/regress/expected/btree_unique.out
? src/test/regress/expected/btree_vacuum.out
? src/test/regress/expected/constraints.out
? src/test/regress/expected/copy.out
? src/test/regress/expected/create_function_1.out
? src/test/regress/expected/create_function_2.out
? src/test/regress/expected/misc.out
? src/test/regress/expected/tablespace.out
? src/test/regress/sql/btree_insert.sql
? src/test/regress/sql/btree_misc.sql
? src/test/regress/sql/btree_search.sql
? src/test/regress/sql/btree_unique.sql
? src/test/regress/sql/btree_vacuum.sql
? src/test/regress/sql/constraints.sql
? src/test/regress/sql/copy.sql
? src/test/regress/sql/create_function_1.sql
? src/test/regress/sql/create_function_2.sql
? src/test/regress/sql/misc.sql
? src/test/regress/sql/tablespace.sql
? src/timezone/zic
Index: src/backend/access/transam/xlog.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/xlog.c,v
retrieving revision 1.259
diff -c -r1.259 xlog.c
*** src/backend/access/transam/xlog.c 8 Dec 2006 19:50:53 -0000 1.259
--- src/backend/access/transam/xlog.c 4 Jan 2007 13:06:47 -0000
***************
*** 137,142 ****
--- 137,143 ----
char *XLOG_sync_method = NULL;
const char XLOG_sync_method_default[] = DEFAULT_SYNC_METHOD_STR;
bool fullPageWrites = true;
+ bool compute_wal_crc = true;
#ifdef WAL_DEBUG
bool XLOG_DEBUG = false;
***************
*** 607,613 ****
*/
doPageWrites = fullPageWrites || Insert->forcePageWrites;
! INIT_CRC32(rdata_crc);
len = 0;
for (rdt = rdata;;)
{
--- 608,614 ----
*/
doPageWrites = fullPageWrites || Insert->forcePageWrites;
! INIT_CRC32(rdata_crc);
len = 0;
for (rdt = rdata;;)
{
***************
*** 615,621 ****
{
/* Simple data, just include it */
len += rdt->len;
! COMP_CRC32(rdata_crc, rdt->data, rdt->len);
}
else
{
--- 616,623 ----
{
/* Simple data, just include it */
len += rdt->len;
! if (compute_wal_crc)
! COMP_CRC32(rdata_crc, rdt->data, rdt->len);
}
else
{
***************
*** 630,636 ****
else if (rdt->data)
{
len += rdt->len;
! COMP_CRC32(rdata_crc, rdt->data, rdt->len);
}
break;
}
--- 632,639 ----
else if (rdt->data)
{
len += rdt->len;
! if (compute_wal_crc)
! COMP_CRC32(rdata_crc, rdt->data, rdt->len);
}
break;
}
***************
*** 647,653 ****
else if (rdt->data)
{
len += rdt->len;
! COMP_CRC32(rdata_crc, rdt->data, rdt->len);
}
break;
}
--- 650,657 ----
else if (rdt->data)
{
len += rdt->len;
! if (compute_wal_crc)
! COMP_CRC32(rdata_crc, rdt->data, rdt->len);
}
break;
}
***************
*** 662,701 ****
rdt = rdt->next;
}
! /*
! * Now add the backup block headers and data into the CRC
! */
! for (i = 0; i < XLR_MAX_BKP_BLOCKS; i++)
! {
! if (dtbuf_bkp[i])
! {
! BkpBlock *bkpb = &(dtbuf_xlg[i]);
! char *page;
! COMP_CRC32(rdata_crc,
! (char *) bkpb,
! sizeof(BkpBlock));
! page = (char *) BufferGetBlock(dtbuf[i]);
! if (bkpb->hole_length == 0)
! {
! COMP_CRC32(rdata_crc,
! page,
! BLCKSZ);
! }
! else
! {
! /* must skip the hole */
! COMP_CRC32(rdata_crc,
! page,
! bkpb->hole_offset);
! COMP_CRC32(rdata_crc,
! page + (bkpb->hole_offset + bkpb->hole_length),
! BLCKSZ - (bkpb->hole_offset + bkpb->hole_length));
! }
! }
! }
!
! /*
* NOTE: We disallow len == 0 because it provides a useful bit of extra
* error checking in ReadRecord. This means that all callers of
* XLogInsert must supply at least some not-in-a-buffer data. However, we
--- 666,708 ----
rdt = rdt->next;
}
! if (compute_wal_crc)
! {
! /*
! * Now add the backup block headers and data into the CRC
! */
! for (i = 0; i < XLR_MAX_BKP_BLOCKS; i++)
! {
! if (dtbuf_bkp[i])
! {
! BkpBlock *bkpb = &(dtbuf_xlg[i]);
! char *page;
!
! COMP_CRC32(rdata_crc,
! (char *) bkpb,
! sizeof(BkpBlock));
! page = (char *) BufferGetBlock(dtbuf[i]);
! if (bkpb->hole_length == 0)
! {
! COMP_CRC32(rdata_crc,
! page,
! BLCKSZ);
! }
! else
! {
! /* must skip the hole */
! COMP_CRC32(rdata_crc,
! page,
! bkpb->hole_offset);
! COMP_CRC32(rdata_crc,
! page + (bkpb->hole_offset + bkpb->hole_length),
! BLCKSZ - (bkpb->hole_offset + bkpb->hole_length));
! }
! }
! }
! }
! /*
* NOTE: We disallow len == 0 because it provides a useful bit of extra
* error checking in ReadRecord. This means that all callers of
* XLogInsert must supply at least some not-in-a-buffer data. However, we
***************
*** 919,928 ****
record->xl_rmid = rmid;
/* Now we can finish computing the record's CRC */
! COMP_CRC32(rdata_crc, (char *) record + sizeof(pg_crc32),
! SizeOfXLogRecord - sizeof(pg_crc32));
! FIN_CRC32(rdata_crc);
! record->xl_crc = rdata_crc;
#ifdef WAL_DEBUG
if (XLOG_DEBUG)
--- 926,940 ----
record->xl_rmid = rmid;
/* Now we can finish computing the record's CRC */
! if (compute_wal_crc)
! {
! COMP_CRC32(rdata_crc, (char *) record + sizeof(pg_crc32),
! SizeOfXLogRecord - sizeof(pg_crc32));
! FIN_CRC32(rdata_crc);
! record->xl_crc = rdata_crc;
! }
! else
! record->xl_crc = 0;
#ifdef WAL_DEBUG
if (XLOG_DEBUG)
***************
*** 2783,2791 ****
BkpBlock bkpb;
char *blk;
/* First the rmgr data */
! INIT_CRC32(crc);
! COMP_CRC32(crc, XLogRecGetData(record), len);
/* Add in the backup blocks, if any */
blk = (char *) XLogRecGetData(record) + len;
--- 2795,2806 ----
BkpBlock bkpb;
char *blk;
+ if (!compute_wal_crc || record->xl_crc == 0)
+ return true;
+
/* First the rmgr data */
! INIT_CRC32(crc);
! COMP_CRC32(crc, XLogRecGetData(record), len);
/* Add in the backup blocks, if any */
blk = (char *) XLogRecGetData(record) + len;
***************
*** 2805,2811 ****
return false;
}
blen = sizeof(BkpBlock) + BLCKSZ - bkpb.hole_length;
! COMP_CRC32(crc, blk, blen);
blk += blen;
}
--- 2820,2826 ----
return false;
}
blen = sizeof(BkpBlock) + BLCKSZ - bkpb.hole_length;
! COMP_CRC32(crc, blk, blen);
blk += blen;
}
***************
*** 4146,4157 ****
record->xl_rmid = RM_XLOG_ID;
memcpy(XLogRecGetData(record), &checkPoint, sizeof(checkPoint));
! INIT_CRC32(crc);
! COMP_CRC32(crc, &checkPoint, sizeof(checkPoint));
! COMP_CRC32(crc, (char *) record + sizeof(pg_crc32),
! SizeOfXLogRecord - sizeof(pg_crc32));
! FIN_CRC32(crc);
! record->xl_crc = crc;
/* Create first XLOG segment file */
use_existent = false;
--- 4161,4177 ----
record->xl_rmid = RM_XLOG_ID;
memcpy(XLogRecGetData(record), &checkPoint, sizeof(checkPoint));
! if (compute_wal_crc)
! {
! INIT_CRC32(crc);
! COMP_CRC32(crc, &checkPoint, sizeof(checkPoint));
! COMP_CRC32(crc, (char *) record + sizeof(pg_crc32),
! SizeOfXLogRecord - sizeof(pg_crc32));
! FIN_CRC32(crc);
! record->xl_crc = crc;
! }
! else
! record->xl_crc = 0;
/* Create first XLOG segment file */
use_existent = false;
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.364
diff -c -r1.364 guc.c
*** src/backend/utils/misc/guc.c 30 Dec 2006 21:21:54 -0000 1.364
--- src/backend/utils/misc/guc.c 4 Jan 2007 13:06:54 -0000
***************
*** 99,104 ****
--- 99,105 ----
extern int CommitSiblings;
extern char *default_tablespace;
extern bool fullPageWrites;
+ extern bool compute_wal_crc;
#ifdef TRACE_SORT
extern bool trace_sort;
***************
*** 548,553 ****
--- 549,564 ----
true, NULL, NULL
},
{
+ {"wal_checksum", PGC_SIGHUP, WAL_SETTINGS,
+ gettext_noop("Calculates CRC checksum for all WAL records."),
+ gettext_noop("This option calculates checksums for all WAL records, so that the "
+ "checksum can be rechecked during recovery following a system crash."),
+ GUC_NOT_IN_SAMPLE
+ },
+ &compute_wal_crc,
+ true, NULL, NULL
+ },
+ {
{"silent_mode", PGC_POSTMASTER, LOGGING_WHEN,
gettext_noop("Runs the server silently."),
gettext_noop("If this parameter is set, the server will automatically run in the "
? contrib/xlogviewer/libxlogviewer.so.0.0
? contrib/xlogviewer/xlogviewer.sql
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
