Re: UPDATEDs slowing SELECTs in a fully cached database

От: Kevin Grittner
Тема: Re: UPDATEDs slowing SELECTs in a fully cached database
Дата: ,
Msg-id: 4E1DCE81020000250003F2DC@gw.wicourts.gov
(см: обсуждение, исходный текст)
Ответ на: Re: UPDATEDs slowing SELECTs in a fully cached database  (Tom Lane)
Ответы: Re: UPDATEDs slowing SELECTs in a fully cached database  (Tom Lane)
Список: pgsql-performance

Скрыть дерево обсуждения

UPDATEDs slowing SELECTs in a fully cached database  (lars, )
 Re: UPDATEDs slowing SELECTs in a fully cached database  (lars, )
  Re: UPDATEDs slowing SELECTs in a fully cached database  (Craig Ringer, )
   Re: UPDATEDs slowing SELECTs in a fully cached database  (lars hofhansl, )
    Re: UPDATEDs slowing SELECTs in a fully cached database  ("Kevin Grittner", )
     Re: UPDATEDs slowing SELECTs in a fully cached database  (lars, )
      Re: UPDATEDs slowing SELECTs in a fully cached database  ("Kevin Grittner", )
       Re: UPDATEDs slowing SELECTs in a fully cached database  (Merlin Moncure, )
        Re: UPDATEDs slowing SELECTs in a fully cached database  ("Kevin Grittner", )
         Re: UPDATEDs slowing SELECTs in a fully cached database  (Merlin Moncure, )
        Re: UPDATEDs slowing SELECTs in a fully cached database  (lars, )
         Re: UPDATEDs slowing SELECTs in a fully cached database  (lars, )
          Re: UPDATEDs slowing SELECTs in a fully cached database  (Ivan Voras, )
           Re: UPDATEDs slowing SELECTs in a fully cached database  (lars, )
            Re: UPDATEDs slowing SELECTs in a fully cached database  ("Kevin Grittner", )
             Re: UPDATEDs slowing SELECTs in a fully cached database  (lars, )
              Re: UPDATEDs slowing SELECTs in a fully cached database  ("Kevin Grittner", )
              Re: UPDATEDs slowing SELECTs in a fully cached database  (lars, )
               Re: UPDATEDs slowing SELECTs in a fully cached database  ("Kevin Grittner", )
                Re: UPDATEDs slowing SELECTs in a fully cached database  (lars, )
               Re: UPDATEDs slowing SELECTs in a fully cached database  ("Kevin Grittner", )
                Re: UPDATEDs slowing SELECTs in a fully cached database  (Tom Lane, )
                 Re: UPDATEDs slowing SELECTs in a fully cached database  (lars, )
                  Re: UPDATEDs slowing SELECTs in a fully cached database  (Merlin Moncure, )
                  Re: UPDATEDs slowing SELECTs in a fully cached database  (Jeff Janes, )
                 Re: UPDATEDs slowing SELECTs in a fully cached database  ("Kevin Grittner", )
                  Re: UPDATEDs slowing SELECTs in a fully cached database  (Tom Lane, )
                   Re: UPDATEDs slowing SELECTs in a fully cached database  ("Kevin Grittner", )
                    Re: UPDATEDs slowing SELECTs in a fully cached database  (Robert Klemme, )
                    Re: UPDATEDs slowing SELECTs in a fully cached database  (Tom Lane, )
                     Re: UPDATEDs slowing SELECTs in a fully cached database  (lars, )
                Re: UPDATEDs slowing SELECTs in a fully cached database  (lars, )
                 Re: UPDATEDs slowing SELECTs in a fully cached database  ("Kevin Grittner", )
                  Re: UPDATEDs slowing SELECTs in a fully cached database  (lars, )
                   Re: UPDATEDs slowing SELECTs in a fully cached database  (Jeff Janes, )
                    Re: UPDATEDs slowing SELECTs in a fully cached database  (lars, )
                     Re: UPDATEDs slowing SELECTs in a fully cached database  (Jeff Janes, )
                      Re: UPDATEDs slowing SELECTs in a fully cached database  (Greg Smith, )
              Re: UPDATEDs slowing SELECTs in a fully cached database  (Jeff Janes, )
               Re: UPDATEDs slowing SELECTs in a fully cached database  (Tom Lane, )
             Re: UPDATEDs slowing SELECTs in a fully cached database  (Merlin Moncure, )
              Re: UPDATEDs slowing SELECTs in a fully cached database  (lars, )
               Re: UPDATEDs slowing SELECTs in a fully cached database  (Merlin Moncure, )
                Re: UPDATEDs slowing SELECTs in a fully cached database  (Pavan Deolasee, )
                 Re: UPDATEDs slowing SELECTs in a fully cached database  (lars hofhansl, )
                  Re: UPDATEDs slowing SELECTs in a fully cached database  (Jeff Janes, )
            Re: UPDATEDs slowing SELECTs in a fully cached database  ("Kevin Grittner", )
 Re: UPDATEDs slowing SELECTs in a fully cached database  ("", )
  Re: UPDATEDs slowing SELECTs in a fully cached database  (Robert Klemme, )
   Re: UPDATEDs slowing SELECTs in a fully cached database  ("", )
   Re: UPDATEDs slowing SELECTs in a fully cached database  (lars, )
    Re: UPDATEDs slowing SELECTs in a fully cached database  ("Kevin Grittner", )
     Re: UPDATEDs slowing SELECTs in a fully cached database  (Merlin Moncure, )
 Re: UPDATEDs slowing SELECTs in a fully cached database  (Ivan Voras, )
  Re: UPDATEDs slowing SELECTs in a fully cached database  (Merlin Moncure, )
   Re: UPDATEDs slowing SELECTs in a fully cached database  (Ivan Voras, )
 Re: UPDATEDs slowing SELECTs in a fully cached database  (Lars, )

Tom Lane <> 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 по дате сообщения:

От: lars
Дата:
Сообщение: Re: UPDATEDs slowing SELECTs in a fully cached database
От: alan
Дата:
Сообщение: Trigger or Function