Re: UPDATEDs slowing SELECTs in a fully cached database

From: Kevin Grittner
Subject: Re: UPDATEDs slowing SELECTs in a fully cached database
Date: ,
Msg-id: 4E1DA0C2020000250003F2B1@gw.wicourts.gov
(view: Whole thread, Raw)
In response to: Re: UPDATEDs slowing SELECTs in a fully cached database  (lars)
Responses: Re: UPDATEDs slowing SELECTs in a fully cached database  (lars)
List: pgsql-performance

Tree view

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, )

lars <> wrote:
> On 07/13/2011 07:46 AM, Kevin Grittner wrote:
>>
>> I've mentioned this in a hand-wavy general sense, but I should
>> have mentioned specifics ages ago: for a database where the
>> active portion of the database is fully cached, it is best to set
>> seq_page_cost and random_page_cost to the same value, somewhere
>> in the 0.1 to 0.05 range.  (In your case I would use 0.05.)  In
>> highly cached databases I have sometimes also found it necessary
>> to increase cpu_tuple_cost.  (In your case I might try 0.02.)
>>
> I've been doing that for other tests already (I didn't want to add
> too many variations here).
> The Bitmap Heap scans through the table are only useful for
> spinning media and not the cache (just to state the obvious).
>
> As an aside: I found that queries in a cold database take almost
> twice as long when I make that change,
> so for spinning media this is very important.

No doubt.  We normally run months to years between reboots, with
most of our cache at the OS level.  We don't have much reason to
ever restart PostgreSQL except to install new versions.  So we don't
worry overly much about the cold cache scenario.

>> Which raises an interesting question -- what happens to the
>> timings if your SELECTs are done with synchronous_commit = off?
>
> Just tried that...
> In that case the WAL is still written (as seen via iostat), but
> not synchronously by the transaction (as seen by strace).

So transactions without an XID *are* sensitive to
synchronous_commit.  That's likely a useful clue.

How much did it help the run time of the SELECT which followed the
UPDATE?

-Kevin


pgsql-performance by date:

From: lars
Date:
Subject: Re: UPDATEDs slowing SELECTs in a fully cached database
From: alan
Date:
Subject: Trigger or Function