8000 Skip WAL for new relfilenodes, under wal_level=minimal. · postgrespro/postgres@cb2fd7e · GitHub
[go: up one dir, main page]
Skip to content

    • Commit cb2fd7e

    Browse files
    nmischnmisch
    committed
    Skip WAL for new relfilenodes, under wal_level=minimal.
    Until now, only selected bulk operations (e.g. COPY) did this. If a given relfilenode received both a WAL-skipping COPY and a WAL-logged operation (e.g. INSERT), recovery could lose tuples from the COPY. See src/backend/access/transam/README section "Skipping WAL for New RelFileNode" for the new coding rules. Maintainers of table access methods should examine that section. To maintain data durability, just before commit, we choose between an fsync of the relfilenode and copying its contents to WAL. A new GUC, wal_skip_threshold, guides that choice. If this change slows a workload that creates small, permanent relfilenodes under wal_level=minimal, try adjusting wal_skip_threshold. Users setting a timeout on COMMIT may need to adjust that timeout, and log_min_duration_statement analysis will reflect time consumption moving to COMMIT from commands like COPY. Internally, this requires a reliable determination of whether RollbackAndReleaseCurrentSubTransaction() would unlink a relation's current relfilenode. Introduce rd_firstRelfilenodeSubid. Amend the specification of rd_createSubid such that the field is zero when a new rel has an old rd_node. Make relcache.c retain entries for certain dropped relations until end of transaction. Back-patch to 9.5 (all supported versions). This introduces a new WAL record type, XLOG_GIST_ASSIGN_LSN, without bumping XLOG_PAGE_MAGIC. As always, update standby systems before master systems. This changes sizeof(RelationData) and sizeof(IndexStmt), breaking binary compatibility for affected extensions. (The most recent commit to affect the same class of extensions was 089e4d4.) Kyotaro Horiguchi, reviewed (in earlier, similar versions) by Robert Haas. Heikki Linnakangas and Michael Paquier implemented earlier designs that materially clarified the problem. Reviewed, in earlier designs, by Andrew Dunstan, Andres Freund, Alvaro Herrera, Tom Lane, Fujii Masao, and Simon Riggs. Reported by Martijn van Oosterhout. Discussion: https://postgr.es/m/20150702220524.GA9392@svana.org
    1 parent d3e5728 commit cb2fd7e

    Some content is hidden

    Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

    51 files changed

    +1439
    -363
    lines changed

    ‎contrib/pg_visibility/expected/pg_visibility.out

    Lines changed: 35 additions & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -1,5 +1,40 @@
    11
    CREATE EXTENSION pg_visibility;
    22
    --
    3+
    -- recently-dropped table
    4+
    --
    5+
    \set VERBOSITY sqlstate
    6+
    BEGIN;
    7+
    CREATE TABLE droppedtest (c int);
    8+
    SELECT 'droppedtest'::regclass::oid AS oid \gset
    9+
    SAVEPOINT q; DROP TABLE droppedtest; RELEASE q;
    10+
    SAVEPOINT q; SELECT * FROM pg_visibility_map(:oid); ROLLBACK TO q;
    11+
    ERROR: XX000
    12+
    -- ERROR: could not open relation with OID 16xxx
    13+
    SAVEPOINT q; SELECT 1; ROLLBACK TO q;
    14+
    ?column?
    15+
    ----------
    16+
    1
    17+
    (1 row)
    18+
    19+
    SAVEPOINT q; SELECT 1; ROLLBACK TO q;
    20+
    ?column?
    21+
    ----------
    22+
    1
    23+
    (1 row)
    24+
    25+
    SELECT pg_relation_size(:oid), pg_relation_filepath(:oid),
    26+
    has_table_privilege(:oid, 'SELECT');
    27+
    pg_relation_size | pg_relation_filepath | has_table_privilege
    28+
    ------------------+----------------------+---------------------
    29+
    | |
    30+
    (1 row)
    31+
    32+
    SELECT * FROM pg_visibility_map(:oid);
    33+
    ERROR: XX000
    34+
    -- ERROR: could not open relation with OID 16xxx
    35+
    ROLLBACK;
    36+
    \set VERBOSITY default
    37+
    --
    338
    -- check that using the module's functions with unsupported relations will fail
    439
    --
    540
    -- partitioned tables (the parent ones) don't have visibility maps

    ‎contrib/pg_visibility/sql/pg_visibility.sql

    Lines changed: 19 additions & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -1,5 +1,24 @@
    11
    CREATE EXTENSION pg_visibility;
    22

    3+
    --
    4+
    -- recently-dropped table
    5+
    --
    6+
    \set VERBOSITY sqlstate
    7+
    BEGIN;
    8+
    CREATE TABLE droppedtest (c int);
    9+
    SELECT 'droppedtest'::regclass::oid AS oid \gset
    10+
    SAVEPOINT q; DROP TABLE droppedtest; RELEASE q;
    11+
    SAVEPOINT q; SELECT * FROM pg_visibility_map(:oid); ROLLBACK TO q;
    12+
    -- ERROR: could not open relation with OID 16xxx
    13+
    SAVEPOINT q; SELECT 1; ROLLBACK TO q;
    14+
    SAVEPOINT q; SELECT 1; ROLLBACK TO q;
    15+
    SELECT pg_relation_size(:oid), pg_relation_filepath(:oid),
    16+
    has_table_privilege(:oid, 'SELECT');
    17+
    SELECT * FROM pg_visibility_map(:oid);
    18+
    -- ERROR: could not open relation with OID 16xxx
    19+
    ROLLBACK;
    20+
    \set VERBOSITY default
    21+
    322
    --
    423
    -- check that using the module's functions with unsupported relations will fail
    524
    --

    ‎doc/src/sgml/config.sgml

    Lines changed: 31 additions & 8 deletions
    Original file line numberDiff line numberDiff line change
    @@ -2501,16 +2501,19 @@ include_dir 'conf.d'
    25012501
    levels. This parameter can only be set at server start.
    25022502
    </para>
    25032503
    <para>
    2504-
    In <literal>minimal</literal> level, WAL-logging of some bulk
    2505-
    operations can be safely skipped, which can make those
    2506-
    operations much faster (see <xref linkend="populate-pitr"/>).
    2507-
    Operations in which this optimization can be applied include:
    2504+
    In <literal>minimal</literal> level, no information is logged for
    2505+
    permanent relations for the remainder of a transaction that creates or
    2506+
    rewrites them. This can make operations much faster (see
    2507+
    <xref linkend="populate-pitr"/>). Operations that initiate this
    2508+
    optimization include:
    25082509
    <simplelist>
    2509-
    <member><command>CREATE TABLE AS</command></member>
    2510-
    <member><command>CREATE INDEX</command></member>
    2510+
    <member><command>ALTER ... SET TABLESPACE</command></member>
    25112511
    <member><command>CLUSTER</command></member>
    2512-
    <member><command>COPY</command> into tables that were created or truncated in the same
    2513-
    transaction</member>
    2512+
    <member><command>CREATE TABLE</command></member>
    2513+
    <member><command>REFRESH MATERIALIZED VIEW</command>
    2514+
    (without <option>CONCURRENTLY</option>)</member>
    2515+
    <member><command>REINDEX</command></member>
    2516+
    <member><command>TRUNCATE</command></member>
    25142517
    </simplelist>
    25152518
    But minimal WAL does not contain enough information to reconstruct the
    25162519
    data from a base backup and the WAL logs, so <literal>replica</literal> or
    @@ -2907,6 +2910,26 @@ include_dir 'conf.d'
    29072910
    </listitem>
    29082911
    </varlistentry>
    29092912

    2913+
    <varlistentry id="guc-wal-skip-threshold" xreflabel="wal_skip_threshold">
    2914+
    <term><varname>wal_skip_threshold</varname> (<type>integer</type>)
    2915+
    <indexterm>
    2916+
    <primary><varname>wal_skip_threshold</varname> configuration parameter</primary>
    2917+
    </indexterm>
    2918+
    </term>
    2919+
    <listitem>
    2920+
    <para>
    2921+
    When <varname>wal_level</varname> is <literal>minimal</literal> and a
    2922+
    transaction commits after creating or rewriting a permanent relation,
    2923+
    this setting determines how to persist the new data. If the data is
    2924+
    smaller than this setting, write it to the WAL log; otherwise, use an
    2925+
    fsync of affected files. Depending on the properties of your storage,
    2926+
    raising or lowering this value might help if such commits are slowing
    2927+
    concurrent transactions. The default is two megabytes
    2928+
    (<literal>2MB</literal>).
    2929+
    </para>
    2930+
    </listitem>
    2931+
    </varlistentry>
    2932+
    29102933
    <varlistentry id="guc-commit-delay" xreflabel="commit_delay">
    29112934
    <term><varname>commit_delay</varname> (<type>integer</type>)
    29122935
    <indexterm>

    ‎doc/src/sgml/perform.sgml

    Lines changed: 9 additions & 38 deletions
    Original file line numberDiff line numberDiff line change
    @@ -1607,8 +1607,8 @@ SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
    16071607
    needs to be written, because in case of an error, the files
    16081608
    containing the newly loaded data will be removed anyway.
    16091609
    However, this consideration only applies when
    1610-
    <xref linkend="guc-wal-level"/> is <literal>minimal</literal> for
    1611-
    non-partitioned tables as all commands must write WAL otherwise.
    1610+
    <xref linkend="guc-wal-level"/> is <literal>minimal</literal>
    1611+
    as all commands must write WAL otherwise.
    16121612
    </para>
    16131613

    16141614
    </sect2>
    @@ -1708,42 +1708,13 @@ SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
    17081708
    </para>
    17091709

    17101710
    <para>
    1711-
    Aside from avoiding the time for the archiver or WAL sender to
    1712-
    process the WAL data,
    1713-
    doing this will actually make certain commands faster, because they
    1714-
    are designed not to write WAL at all if <varname>wal_level</varname>
    1715-
    is <literal>minimal</literal>. (They can guarantee crash safety more cheaply
    1716-
    by doing an <function>fsync</function> at the end than by writing WAL.)
    1717-
    This applies to the following commands:
    1718-
    <itemizedlist>
    1719-
    <listitem>
    1720-
    <para>
    1721-
    <command>CREATE TABLE AS SELECT</command>
    1722-
    </para>
    1723-
    </listitem>
    1724-
    <listitem>
    1725-
    <para>
    1726-
    <command>CREATE INDEX</command> (and variants such as
    1727-
    <command>ALTER TABLE ADD PRIMARY KEY</command>)
    1728-
    </para>
    1729-
    </listitem>
    1730-
    <listitem>
    1731-
    <para>
    1732-
    <command>ALTER TABLE SET TABLESPACE</command>
    1733-
    </para>
    1734-
    </listitem>
    1735-
    <listitem>
    1736-
    <para>
    1737-
    <command>CLUSTER</command>
    1738-
    </para>
    1739-
    </listitem>
    1740-
    <listitem>
    1741-
    <para>
    1742-
    <command>COPY FROM</command>, when the target table has been
    1743-
    created or truncated earlier in the same transaction
    1744-
    </para>
    1745-
    </listitem>
    1746-
    </itemizedlist>
    1711+
    Aside from avoiding the time for the archiver or WAL sender to process the
    1712+
    WAL data, doing this will actually make certain commands faster, because
    1713+
    they do not to write WAL at all if <varname>wal_level</varname>
    1714+
    is <literal>minimal</literal> and the current subtransaction (or top-level
    1715+
    transaction) created or truncated the table or index they change. (They
    1716+
    can guarantee crash safety more cheaply by doing
    1717+
    an <function>fsync</function> at the end than by writing WAL.)
    17471718
    </para>
    17481719
    </sect2>
    17491720

    ‎src/backend/access/gist/gistutil.c

    Lines changed: 26 additions & 5 deletions
    Original file line numberDiff line numberDiff line change
    @@ -1004,23 +1004,44 @@ gistproperty(Oid index_oid, int attno,
    10041004
    }
    10051005

    10061006
    /*
    1007-
    * Temporary and unlogged GiST indexes are not WAL-logged, but we need LSNs
    1008-
    * to detect concurrent page splits anyway. This function provides a fake
    1009-
    * sequence of LSNs for that purpose.
    1007+
    * Some indexes are not WAL-logged, but we need LSNs to detect concurrent page
    1008+
    * splits anyway. This function provides a fake sequence of LSNs for that
    1009+
    * purpose.
    10101010
    */
    10111011
    XLogRecPtr
    10121012
    gistGetFakeLSN(Relation rel)
    10131013
    {
    1014-
    static XLogRecPtr counter = FirstNormalUnloggedLSN;
    1015-
    10161014
    if (rel->rd_rel->relpersistence == RELPERSISTENCE_TEMP)
    10171015
    {
    10181016
    /*
    10191017
    * Temporary relations are only accessible in our session, so a simple
    10201018
    * backend-local counter will do.
    10211019
    */
    1020+
    static XLogRecPtr counter = FirstNormalUnloggedLSN;
    1021+
    10221022
    return counter++;
    10231023
    }
    1024+
    else if (rel->rd_rel->relpersistence == RELPERSISTENCE_PERMANENT)
    1025+
    {
    1026+
    /*
    1027+
    * WAL-logging on this relation will start after commit, so its LSNs
    1028+
    * must be distinct numbers smaller than the LSN at the next commit.
    1029+
    * Emit a dummy WAL record if insert-LSN hasn't advanced after the
    1030+
    * last call.
    1031+
    */
    1032+
    static XLogRecPtr lastlsn = InvalidXLogRecPtr;
    1033+
    XLogRecPtr currlsn = GetXLogInsertRecPtr();
    1034+
    1035+
    /* Shouldn't be called for WAL-logging relations */
    1036+
    Assert(!RelationNeedsWAL(rel));
    1037+
    1038+
    /* No need for an actual record if we already have a distinct LSN */
    1039+
    if (!XLogRecPtrIsInvalid(lastlsn) && lastlsn == currlsn)
    1040+
    currlsn = gistXLogAssignLSN();
    1041+
    1042+
    lastlsn = currlsn;
    1043+
    return currlsn;
    1044+
    }
    10241045
    else
    10251046
    {
    10261047
    /*

    ‎src/backend/access/gist/gistxlog.c

    Lines changed: 21 additions & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -449,6 +449,9 @@ gist_redo(XLogReaderState *record)
    449449
    case XLOG_GIST_PAGE_DELETE:
    450450
    gistRedoPageDelete(record);
    451451
    break;
    452+
    case XLOG_GIST_ASSIGN_LSN:
    453+
    /* nop. See gistGetFakeLSN(). */
    454+
    break;
    452455
    default:
    453456
    elog(PANIC, "gist_redo: unknown op code %u", info);
    454457
    }
    @@ -592,6 +595,24 @@ gistXLogPageDelete(Buffer buffer, FullTransactionId xid,
    592595
    return recptr;
    593596
    }
    594597

    598+
    /*
    599+
    * Write an empty XLOG record to assign a distinct LSN.
    600+
    */
    601+
    XLogRecPtr
    602+
    gistXLogAssignLSN(void)
    603+
    {
    604+
    int dummy = 0;
    605+
    606+
    /*
    607+
    * Records other than SWITCH_WAL must have content. We use an integer 0 to
    608+
    * follow the restriction.
    609+
    */
    610+
    XLogBeginInsert();
    611+
    XLogSetRecordFlags(XLOG_MARK_UNIMPORTANT);
    612+
    XLogRegisterData((char *) &dummy, sizeof(dummy));
    613+
    return XLogInsert(RM_GIST_ID, XLOG_GIST_ASSIGN_LSN);
    614+
    }
    615+
    595616
    /*
    596617
    * Write XLOG record about reuse of a deleted page.
    597618
    */

    ‎src/backend/access/heap/heapam.c

    Lines changed: 2 additions & 43 deletions
    Original file line numberDiff line numberDiff line change
    @@ -21,7 +21,6 @@
    2121
    * heap_multi_insert - insert multiple tuples into a relation
    2222
    * heap_delete - delete a tuple from a relation
    2323
    * heap_update - replace a tuple in a relation with another tuple
    24-
    * heap_sync - sync heap, for when no WAL has been written
    2524
    *
    2625
    * NOTES
    2726
    * This file contains the heap_ routines which implement
    @@ -1939,7 +1938,7 @@ heap_insert(Relation relation, HeapTuple tup, CommandId cid,
    19391938
    MarkBufferDirty(buffer);
    19401939

    19411940
    /* XLOG stuff */
    1942-
    if (!(options & HEAP_INSERT_SKIP_WAL) && RelationNeedsWAL(relation))
    1941+
    if (RelationNeedsWAL(relation))
    19431942
    {
    19441943
    xl_heap_insert xlrec;
    19451944
    xl_heap_header xlhdr;
    @@ -2122,7 +2121,7 @@ heap_multi_insert(Relation relation, TupleTableSlot **slots, int ntuples,
    21222121
    /* currently not needed (thus unsupported) for heap_multi_insert() */
    21232122
    AssertArg(!(options & HEAP_INSERT_NO_LOGICAL));
    21242123

    2125-
    needwal = !(options & HEAP_INSERT_SKIP_WAL) && RelationNeedsWAL(relation);
    2124+
    needwal = RelationNeedsWAL(relation);
    21262125
    saveFreeSpace = RelationGetTargetPageFreeSpace(relation,
    21272126
    HEAP_DEFAULT_FILLFACTOR);
    21282127

    @@ -8920,46 +8919,6 @@ heap2_redo(XLogReaderState *record)
    89208919
    }
    89218920
    }
    89228921

    8923-
    /*
    8924-
    * heap_sync - sync a heap, for use when no WAL has been written
    8925-
    *
    8926-
    * This forces the heap contents (including TOAST heap if any) down to disk.
    8927-
    * If we skipped using WAL, and WAL is otherwise needed, we must force the
    8928-
    * relation down to disk before it's safe to commit the transaction. This
    8929-
    * requires writing out any dirty buffers and then doing a forced fsync.
    8930-
    *
    8931-
    * Indexes are not touched. (Currently, index operations associated with
    8932-
    * the commands that use this are WAL-logged and so do not need fsync.
    8933-
    * That behavior might change someday, but in any case it's likely that
    8934-
    * any fsync decisions required would be per-index and hence not appropriate
    8935-
    * to be done here.)
    8936-
    */
    8937-
    void
    8938-
    heap_sync(Relation rel)
    8939-
    {
    8940-
    /* non-WAL-logged tables never need fsync */
    8941-
    if (!RelationNeedsWAL(rel))
    8942-
    return;
    8943-
    8944-
    /* main heap */
    8945-
    FlushRelationBuffers(rel);
    8946-
    /* FlushRelationBuffers will have opened rd_smgr */
    8947-
    smgrimmedsync(rel->rd_smgr, MAIN_FORKNUM);
    8948-
    8949-
    /* FSM is not critical, don't bother syncing it */
    8950-
    8951-
    /* toast heap, if any */
    8952-
    if (OidIsValid(rel->rd_rel->reltoastrelid))
    8953-
    {
    8954-
    Relation toastrel;
    8955-
    8956-
    toastrel = table_open(rel->rd_rel->reltoastrelid, AccessShareLock);
    8957-
    FlushRelationBuffers(toastrel);
    8958-
    smgrimmedsync(toastrel->rd_smgr, MAIN_FORKNUM);
    8959-
    table_close(toastrel, AccessShareLock);
    8960-
    }
    8961-
    }
    8962-
    89638922
    /*
    89648923
    * Mask a heap page before performing consistency checks on it.
    89658924
    */

    ‎src/backend/access/heap/heapam_handler.c

    Lines changed: 3 additions & 19 deletions
    Original file line numberDiff line numberDiff line change
    @@ -555,17 +555,6 @@ heapam_tuple_lock(Relation relation, ItemPointer tid, Snapshot snapshot,
    555555
    return result;
    556556
    }
    557557

    558-
    static void
    559-
    heapam_finish_bulk_insert(Relation relation, int options)
    560-
    {
    561-
    /*
    562-
    * If we skipped writing WAL, then we need to sync the heap (but not
    563-
    * indexes since those use WAL anyway / don't go through tableam)
    564-
    */
    565-
    if (options & HEAP_INSERT_SKIP_WAL)
    566-
    heap_sync(relation);
    567-
    }
    568-
    569558

    570559
    /* ------------------------------------------------------------------------
    571560
    * DDL related callbacks for heap AM.
    @@ -698,7 +687,6 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Relation NewHeap,
    698687
    IndexScanDesc indexScan;
    699688
    TableScanDesc tableScan;
    700689
    HeapScanDesc heapScan;
    701-
    bool use_wal;
    702690
    bool is_system_catalog;
    703691
    Tuplesortstate *tuplesort;
    704692
    TupleDesc oldTupDesc = RelationGetDescr(OldHeap);
    @@ -713,12 +701,9 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Relation NewHeap,
    713701
    is_system_catalog = IsSystemRelation(OldHeap);
    714702

    715703
    /*
    716-
    * We need to log the copied data in WAL iff WAL archiving/streaming is
    717-
    * enabled AND it's a WAL-logged rel.
    704+
    * Valid smgr_targblock implies something already wrote to the relation.
    705+
    * This may be harmless, but this function hasn't planned for it.
    718706
    */
    719-
    use_wal = XLogIsNeeded() && RelationNeedsWAL(NewHeap);
    720-
    721-
    /* use_wal off requires smgr_targblock be initially invalid */
    722707
    Assert(RelationGetTargetBlock(NewHeap) == InvalidBlockNumber);
    723708

    724709
    /* Preallocate values/isnull arrays */
    @@ -728,7 +713,7 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Relation NewHeap,
    728713

    729714
    /* Initialize the rewrite operation */
    730715
    rwstate = begin_heap_rewrite(OldHeap, NewHeap, OldestXmin, *xid_cutoff,
    731-
    *multi_cutoff, use_wal);
    716+
    *multi_cutoff);
    732717

    733718

    734719
    /* Set up sorting if wanted */
    @@ -2525,7 +2510,6 @@ static const TableAmRoutine heapam_methods = {
    25252510
    .tuple_delete = heapam_tuple_delete,
    25262511
    .tuple_update = heapam_tuple_update,
    25272512
    .tuple_lock = heapam_tuple_lock,
    2528-
    .finish_bulk_insert = heapam_finish_bulk_insert,
    25292513

    25302514
    .tuple_fetch_row_version = heapam_fetch_row_version,
    25312515
    .tuple_get_latest_tid = heap_get_latest_tid,

    0 commit comments

    Comments
     (0)
    0