Re: UPDATEDs slowing SELECTs in a fully cached database - Mailing list pgsql-performance

From Kevin Grittner
Subject Re: UPDATEDs slowing SELECTs in a fully cached database
Date
Msg-id 4E1DCE81020000250003F2DC@gw.wicourts.gov
Whole thread Raw
In response to Re: UPDATEDs slowing SELECTs in a fully cached database  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: UPDATEDs slowing SELECTs in a fully cached database  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Huh?  If there was never an XID, there's no commit WAL record,
> hence nothing to make asynchronous.

If you look at the RecordTransactionCommit() function in xact.c
you'll see that's not correct.  Currently the commit record has
nothing to do with whether it synchronizes on WAL writes.  In
particular, this section around line 1096 is where the choice is
made:

    if ((wrote_xlog && synchronous_commit > SYNCHRONOUS_COMMIT_OFF)
        || forceSyncCommit || nrels > 0)
    {
        /*
         * Synchronous commit case:

In the OP's test case, wrote_xlog is true, while forceSyncCommit is
false and nrels == 0.

It doesn't seem like commit of a read-only transaction should be a
magical time for pruning WAL entries to hit the disk, so it probably
would work to modify that "if" to not drop into the synchronous
commit code if the transaction is explicitly declared READ ONLY or
if it never acquired an XID; although there's likely some better way
to deal with it.

-Kevin

pgsql-performance by date:

Previous
From: Merlin Moncure
Date:
Subject: Re: UPDATEDs slowing SELECTs in a fully cached database
Next
From: Tom Lane
Date:
Subject: Re: UPDATEDs slowing SELECTs in a fully cached database