From 9b8a73326e99821caf33c36c081cb307e17422d4 Mon Sep 17 00:00:00 2001 From: Heikki Linnakangas Date: Wed, 28 Apr 2010 16:10:43 +0000 Subject: Introduce wal_level GUC to explicitly control if information needed for archival or hot standby should be WAL-logged, instead of deducing that from other options like archive_mode. This replaces recovery_connections GUC in the primary, where it now has no effect, but it's still used in the standby to enable/disable hot standby. Remove the WAL-logging of "unlogged operations", like creating an index without WAL-logging and fsyncing it at the end. Instead, we keep a copy of the wal_mode setting and the settings that affect how much shared memory a hot standby server needs to track master transactions (max_connections, max_prepared_xacts, max_locks_per_xact) in pg_control. Whenever the settings change, at server restart, write a WAL record noting the new settings and update pg_control. This allows us to notice the change in those settings in the standby at the right moment, they used to be included in checkpoint records, but that meant that a changed value was not reflected in the standby until the first checkpoint after the change. Bump PG_CONTROL_VERSION and XLOG_PAGE_MAGIC. Whack XLOG_PAGE_MAGIC back to the sequence it used to follow, before hot standby and subsequent patches changed it to 0x9003. --- doc/src/sgml/backup.sgml | 5 ++- doc/src/sgml/config.sgml | 64 +++++++++++++++++++++++++++++-------- doc/src/sgml/high-availability.sgml | 8 ++--- doc/src/sgml/perform.sgml | 45 +++++++++++++------------- 4 files changed, 79 insertions(+), 43 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml index 3c5e0d5a8e..9055517af5 100644 --- a/doc/src/sgml/backup.sgml +++ b/doc/src/sgml/backup.sgml @@ -1,4 +1,4 @@ - + Backup and Restore @@ -689,8 +689,7 @@ archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/ser - When archive_mode is off and is zero some SQL commands + When wal_level is minimal some SQL commands are optimized to avoid WAL logging, as described in . If archiving or streaming replication were turned on during execution of one of these statements, WAL would not diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 71bab707da..93c9ff183c 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -1,4 +1,4 @@ - + Server Configuration @@ -1353,6 +1353,45 @@ SET ENABLE_SEQSCAN TO OFF; Settings + + wal_level (enum) + + wal_level configuration parameter + + + + wal_level determines how much information is written + to the WAL. The default value is minimal, which writes + only minimal information needed to recover from a crash or immediate + shutdown. archive adds logging required for WAL archiving, + and hot_standby further adds information required to run + read-only queries on a standby server. + This parameter can only be set at server start. + + + In minimal level, WAL-logging of some bulk operations, like + CREATE INDEX, CLUSTER and COPY on + a table that was created or truncated in the same transaction can be + safely skipped, which can make those operations much faster (see + ). But minimal WAL does not contain + enough information to reconstruct the data from a base backup and the + WAL logs, so at least archive level must be used to enable + WAL archiving () and streaming + replication. + + + In hot_standby level, the same information is logged as + with archive, plus information needed to reconstruct + the status of running transactions from the WAL. To enable read-only + queries on a standby server, wal_level must be set to + hot_standby on the primary. It is thought that there is + little measurable difference in performance from using + hot_standby level over archive, so feedback + is welcome if any production impacts are noticeable. + + + + fsync configuration parameter @@ -1726,7 +1765,9 @@ SET ENABLE_SEQSCAN TO OFF; archive_mode and archive_command are separate variables so that archive_command can be changed without leaving archiving mode. - This parameter can only be set at server start. + This parameter can only be set at server start. wal_level + must be set to archive or hot_standby to + enable archive_mode. @@ -1818,7 +1859,9 @@ SET ENABLE_SEQSCAN TO OFF; Specifies the maximum number of concurrent connections from standby servers (i.e., the maximum number of simultaneously running WAL sender processes). The default is zero. This parameter can only be set at - server start. + server start. wal_level must be set to archive + or hot_standby to allow connections from standby + connections. @@ -1884,16 +1927,11 @@ SET ENABLE_SEQSCAN TO OFF; - Parameter has two roles. During recovery, specifies whether or not - you can connect and run queries to enable . - During normal running, specifies whether additional information is written - to WAL to allow recovery connections on a standby server that reads - WAL data generated by this server. The default value is - on. It is thought that there is little - measurable difference in performance from using this feature, so - feedback is welcome if any production impacts are noticeable. - It is likely that this parameter will be removed in later releases. - This parameter can only be set at server start. + Specifies whether or not you can connect and run queries during + recovery, for . The default value is + on. + This parameter can only be set at server start. It only has effect + during archive recovery or in standby mode. diff --git a/doc/src/sgml/high-availability.sgml b/doc/src/sgml/high-availability.sgml index 463bac1f48..da0d4d5de5 100644 --- a/doc/src/sgml/high-availability.sgml +++ b/doc/src/sgml/high-availability.sgml @@ -1,4 +1,4 @@ - + High Availability, Load Balancing, and Replication @@ -1593,9 +1593,9 @@ LOG: database system is ready to accept read only connections Consistency information is recorded once per checkpoint on the primary, as long - as recovery_connections is enabled on the primary. It is not possible + as wal_level is set to hot_standby on the primary. It is not possible to enable recovery connections on the standby when reading WAL written during the - period that recovery_connections was disabled on the primary. + period that wal_level was not set to hot_standby on the primary. Reaching a consistent state can also be delayed in the presence of both of these conditions: @@ -1842,7 +1842,7 @@ LOG: database system is ready to accept read only connections - On the primary, parameters recovery_connections and + On the primary, parameters wal_level and vacuum_defer_cleanup_age can be used. max_standby_delay has no effect if set on the primary. diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml index ccccb3fefb..bd93cb0dd2 100644 --- a/doc/src/sgml/perform.sgml +++ b/doc/src/sgml/perform.sgml @@ -1,4 +1,4 @@ - + Performance Tips @@ -835,10 +835,9 @@ SELECT * FROM x, y, a, b, c WHERE something AND somethingelse; TRUNCATE command. In such cases no WAL needs to be written, because in case of an error, the files containing the newly loaded data will be removed anyway. - However, this consideration does not apply when - is on or streaming replication - is allowed (i.e., is more - than or equal to one), as all commands must write WAL in that case. + However, this consideration only applies when + is minimal as all commands + must write WAL otherwise. @@ -910,29 +909,27 @@ SELECT * FROM x, y, a, b, c WHERE something AND somethingelse; - Turn off <varname>archive_mode</varname> and streaming replication + Disable WAL archival and streaming replication When loading large amounts of data into an installation that uses - WAL archiving or streaming replication, you might want to disable - archiving (turn off the - configuration variable) and replication (zero the - configuration variable) - while loading. It might be - faster to take a new base backup after the load has completed - than to process a large amount of incremental WAL data. - But note that changing either of these variables requires - a server restart. + WAL archiving or streaming replication, it might be faster to take a + new base backup after the load has completed than to process a large + amount of incremental WAL data. You might want to disable archiving + and streaming replication while loading, by setting + to minimal, + off, and + to zero). + But note that changing these settings requires a server restart. Aside from avoiding the time for the archiver or WAL sender to process the WAL data, doing this will actually make certain commands faster, because they - are designed not to write WAL at all if archive_mode - is off and max_wal_senders is zero. (They can - guarantee crash safety more cheaply by doing an - fsync at the end than by writing WAL.) + are designed not to write WAL at all if wal_level + is minimal. (They can guarantee crash safety more cheaply + by doing an fsync at the end than by writing WAL.) This applies to the following commands: @@ -1014,10 +1011,12 @@ SELECT * FROM x, y, a, b, c WHERE something AND somethingelse; - If using WAL archiving, consider disabling it during the restore. - To do that, turn off archive_mode before loading the - dump script, and afterwards turn it back on - and take a fresh base backup. + If using WAL archiving or streaming replication, consider disabling + them during the restore. To do that, set arcive_mode off, + wal_level to minimal, and + max_wal_senders zero before loading the dump script, + and afterwards set them back to the right values and take a fresh + base backup. -- cgit v1.2.1