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: 4E1C6D7C020000250003F268@gw.wicourts.gov
(view: Whole thread, Raw)
In response to: 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:

> So a read of a row *will* trigger dead tuple pruning, and that
> requires WAL logging, and this is known/expected?

Yes, because pruning dead line pointers will make subsequent reads
faster.  It's intended to be an optimization.

> This is actually the only answer I am looking for. :) I have not
> seen this documented anywhere.

You would currently need to look at the README-HOT file or source
code, I think.  There probably should be some mention in the user
docs, but I haven't noticed any, and it is more technical than most
of the documentation gets.  Perhaps a "note" block somewhere...

> The fact that a select (maybe a big analytical query we'll run)
> touching many rows will update the WAL and wait (apparently) for
> that IO to complete is making a fully cached database far less
> useful.

Well, I've never run into this because I have directly attached
storage through a RAID controller with a battery-backed cache
configured for write-back.  The pruning is pretty light on CPU
usage, and with a BBU controller, the WAL writes just move from one
cache to another.

If that's not an option for you, you could contrive to have the
update code reread the modified rows after COMMIT, or configure your
autovacuum to be very aggressive so that a background process
usually takes care of this before a SELECT gets to it.  And there's
a good chance that tuning your query and/or running with literal
values available to the planner would be a big net win even without
this issue; if this issue is a problem for you, it's just another
reason to do that tuning.

> Just dropped the table to test something so I can't get the plan
> right now. Will send an update as soon as I get
> it setup again.

I'll be surprised if you don't see a seqscan.  The most interesting
bit at this point (at least to me) is working on tuning the cost
factors for the planner.

-Kevin


pgsql-performance by date:

From: Mario Splivalo
Date:
Subject: Re: Planner choosing NestedLoop, although it is slower...
From: Lars
Date:
Subject: Re: UPDATEDs slowing SELECTs in a fully cached database