Settings

wal_level

Parameter description: Specifies the level of the information that is written to the write ahead log (WAL).

This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1 GUC parameters.

NOTICE:

• To enable WAL archiving and data streaming replication between primary and standby servers, set this parameter to archive or hot_standby.
• This parameter can be set to minimal only in a single-server system. When the parameter value is minimal, archive_mode must be set to off, hot_standby must be set to off, and max_wal_senders must be set to 0. Otherwise, the database startup fails.
• If this parameter is set to archive, hot_standby must be set to off. Otherwise, the database startup fails. However, hot_standby cannot be set to off in a primary-standby deployment. For details, see the description of the hot_standby parameter.

Value range: enumerated values

• minimal

  Advantages: Certain bulk operations (including creating tables and indexes, executing cluster operations, and copying tables) are safely skipped in logging, which can make those operations much faster.

  Disadvantages: WALs contain only basic information required for recovery from a database server crash or an emergency shutdown. Data cannot be restored from archived WALs.

• archive

  Adds logging required for WAL archiving, supporting the database restoration from archives.

• hot_standby

  • Further adds information required to run SQL queries on a standby server and takes effect after a server restart.
  • To enable read-only queries on a standby server, the wal_level parameter must be set to hot_standby on the primary server and the hot_standby parameter must be set to on on the standby server. There are few measurable differences in performance between using hot_standby and archive levels. However, feedback is welcome if any differences in their impacts on product performance are noticeable.

• logical

  This parameter indicates that WALs support logical replication.

Default value: hot_standby

fsync

Parameter description: Specifies whether the MogDB server uses the fsync() function (see wal_sync_method) to ensure that updates can be written to disks in a timely manner.

This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1 GUC parameters.

NOTICE:

• Using the fsync() function ensures that the data can be recovered to a known state when an OS or a hardware crashes.
• Setting this parameter to off may result in unrecoverable data corruption in a system crash.

Value range: Boolean

• on indicates that the fsync() function is used.
• off indicates that the fsync() function is not used.

Default value: on

synchronous_commit

Parameter description: Specifies the synchronization mode of the current transaction.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1 GUC parameters.

Generally, logs generated by a transaction are synchronized in the following sequence:

1. The primary node writes the log content to the local memory.
2. The primary node writes the logs in the local memory to the local file system.
3. The primary node flushes logs in the local file system to disks.
4. The primary node sends the log content to the standby node.
5. The standby node receives the log content and saves it to the local memory.
6. The standby node writes logs in the local memory to the local file system.
7. The standby node flushes logs in the local file system to disks.
8. The standby node replays logs to complete the incremental update of data files.

Value range: enumerated values

• on: The primary node waits for the standby node to flush logs to disks before committing a transaction.
• off: The primary node commits transactions without waiting for the primary node to flush logs to disks. This mode is also called asynchronous commit.
• local: The primary node waits for the primary node to flush logs to disks before committing a transaction. This mode is also called local commit.
• remote_write: The primary node waits for the standby node to write logs to the file system before committing a transaction. (The logs do not need to be flushed to disks.)
• remote_receive: The primary node waits for the standby node to receive logs before committing a transaction. (The logs do not need to be written to the file system.)
• remote_apply: The primary node waits for the standby node to complete log replay before committing a transaction.
• true: same as on.
• false: same as off.
• yes: same as on.
• no: same as off.
• 1: same as on.
• 0: same as off.
• 2: same as remote_apply.

Default value: on

wal_sync_method

Parameter description: Specifies the method used for forcing WAL updates out to disk.

This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1 GUC parameters.

NOTICE: If fsync is set to off, the setting of this parameter does not take effect because WAL file updates will not be forced out to disk.

Value range: enumerated values

• open_datasync indicates that WAL files are opened with the O_DSYNC option.

• fdatasync indicates that fdatasync() is called at each commit.

• fsync_writethrough indicates that fsync() is called at each commit to force data in the buffer to be written to the disk.

  NOTE: wal_sync_method can be set to fsync_writethrough on a Windows platform, but this setting has the same effect as setting the parameter to fsync.

• fsync indicates that fsync() is called at each commit. (SUSE Linux 10

• open_sync indicates that WAL files are written with open() option O_SYNC.

  NOTE: Not all platforms support the preceding parameters.

Default value: fdatasync

full_page_writes

Parameter description: Specifies whether the MogDB server writes the entire content of each disk page to WALs during the first modification of that page after a checkpoint. When the incremental checkpoint function and enable_double_write are enabled at the same time, full_page_writes is not used.

This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1 GUC parameters.

NOTICE:

• This parameter is needed because a page write that is in process during an OS crash might be only partially completed, leading to an on-disk page that contains a mix of old and new data. The row-level change data normally stored in WALs will not be enough to completely restore such a page during post-crash recovery. Storing the full page image guarantees that the page can be correctly restored, but at the price of increasing the amount of data that must be written to WALs.
• You must enable either enable_double_write or full_page_writes, or both of them. If both of them are disabled, it will lead to unrecoverable data corruption after a system failure. It might be safe to set this parameter to off if you have hardware (such as a battery-backed disk controller) or file-system software (such as ReiserFS 4) that reduces the risk of partial page writes to an acceptably low level.

Value range: Boolean

• on indicates that this feature is enabled.
• off indicates that this feature is disabled.

Default value: on

wal_log_hints

Parameter description: Specifies whether to write an entire page to WALs during the first modification of that page after a checkpoint, even for non-critical modifications of so-called hint bits. You are advised not to modify the setting.

This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1 GUC parameters.

Value range: Boolean

• on indicates that the entire page is written to WALs.
• off indicates that the entire page is not written to WALs.

Default value: on

wal_buffers

Parameter description: Specifies the number of XLOG_BLCKSZs used for storing WAL data. The size of each XLOG_BLCKSZ is 8 KB.

This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1 GUC parameters.

Value range: -1 to 2^18.

• If this parameter is set to -1, the value of wal_buffers is automatically changed to 1⁄32 of shared_buffers. The minimum value is 8 x XLOG_BLCKSZ, and the maximum value is 2048 x XLOG_BLCKSZ. That means if the 1⁄32 of shared_buffers is smaller than 8 x XLOG_BLCKSZ or larger than 2048 x XLOG_BLCKSZ, the minimum value or the maximum value is taken.
• If this parameter is set to a value other than -1 and smaller than 4, the value 4 is used.

Default value: 16MB

Setting suggestions: The content of the WAL buffers is written to disk at every transaction commit, so extremely large values are unlikely to provide a significant increase in system performance. However, setting this parameter to hundreds of megabytes can improve the disk write performance on a server to which a large number of transactions are committed at the same time. The auto-tuning selected by the value -1 should give reasonable results in most cases.

wal_writer_delay

Parameter description: Specifies the delay between activity rounds for the WAL writer.

This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1 GUC parameters.

NOTICE: A longer delay might lead to insufficient WAL buffer and a shorter delay leads to continuously writing of the WALs, thereby increasing the load of disk I/O.

Value range: an integer ranging from 1 to 10000. The unit is millisecond.

Default value: 200ms

commit_delay

Parameter description: Specifies the duration for committed data to be stored in the WAL buffer.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1 GUC parameters.

NOTICE:

• When this parameter is set to a non-zero value, the committed transaction is stored in the WAL buffer instead of being written to the WAL immediately. Then the WAL writer process flushes the buffer out to disk periodically.
• If system load is high, other transactions are probably ready to be committed within the delay. If no other transactions are ready to be committed, the delay is a waste of time.

Value range: an integer ranging from 0 to 100000. The unit is μs. 0 indicates no delay.

Default value: 0

commit_siblings

Parameter description: Specifies a threshold on the number of concurrent open transactions. If the number of concurrent open transactions is greater than the value of this parameter, a transaction that initiates a commit request will wait for a period of time specified by commit_delay. Otherwise, this transaction is written into WALs immediately.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1 GUC parameters.

Value range: an integer ranging from 0 to 1000

Default value: 5

wal_block_size

Parameter description: Specifies the size of a WAL disk block.

This parameter is a fixed INTERNAL parameter and cannot be modified.

Value range: an integer. The unit is byte.

Default value: 8192

wal_segment_size

Parameter description: Specifies the size of a WAL segment file.

This parameter is a fixed INTERNAL parameter and cannot be modified.

Value range: an integer. The unit is 8 KB.

Default value: 16 MB (2048 x 8 KB)

walwriter_cpu_bind

Parameter description: Specifies the CPU core bound to the WAL write thread. This parameter is used together with the thread pool parameter.

This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1 GUC parameters.

Value range: an integer ranging from -1 to the number of cores minus 1

Default value: -1

walwriter_sleep_threshold

Parameter description: Specifies the number of times that idle Xlogs are refreshed before the xlogflusher enters the sleep state. If the number of times reaches the threshold, the xlogflusher enters the sleep state.

This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1 GUC parameters.

Value range: an integer ranging from 1 to 50000

Default value: 500

wal_file_init_num

Parameter description: Specifies the number of Xlog segment files to be created by the WAL writer.

This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1 GUC parameters.

Value range: an integer ranging from 0 to 1000000

Default value: 10

force_promote

Parameter description: Specifies whether to enable the forcible switchover function on the standby node.

When a cluster is faulty, the forcible switchover enables the cluster to recover services as soon as possible at the cost of losing some data. This is an escape method used when the cluster is unavailable. You are not advised to trigger this method frequently. You are not advised not to use this function if you are not clear about the impact of data loss on services.

Value range: 0 or 1

The value 0 indicates that the function is disabled and the value 1 indicates that the function is enabled.

Default value: 0

wal_flush_timeout

Parameter description: Specifies the timeout interval for traversing WalInsertStatusEntryTbl. It is the maximum wait time for the adaptive Xlog disk flushing I/O to traverse WalInsertStatusEntryTbl.

This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1 GUC parameters.

NOTICE: If the timeout interval is too long, the Xlog flushing frequency may decrease, reducing the Xlog processing performance.

Value range: an integer ranging from 0 to 90000000 (μs)

Default value: 2

wal_flush_delay

Parameter description: Specifies the wait interval when an entry in the WAL_NOT_COPIED state is encountered during WalInsertStatusEntryTbl traversal.

This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1 GUC parameters.

Value range: an integer ranging from 0 to 90000000 (μs)

Default value: 1