Русский

Re: UPDATEDs slowing SELECTs in a fully cached database

thread

List

Post date

Sort by

From:	Kevin Grittner
Subject:	Re: UPDATEDs slowing SELECTs in a fully cached database
Date:	12 July 2011 , 20:51:35
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

Tree view

UPDATEDs slowing SELECTs in a fully cached database (lars, 10 July 2011, 07:38:01 )
Re: UPDATEDs slowing SELECTs in a fully cached database (lars, 10 July 2011, 20:34:42 )
Re: UPDATEDs slowing SELECTs in a fully cached database (Craig Ringer, 10 July 2011, 23:11:55 )
Re: UPDATEDs slowing SELECTs in a fully cached database (lars hofhansl, 11 July 2011, 04:47:47 )
Re: UPDATEDs slowing SELECTs in a fully cached database ("Kevin Grittner", 11 July 2011, 17:34:00 )
Re: UPDATEDs slowing SELECTs in a fully cached database (lars, 11 July 2011, 18:33:51 )
Re: UPDATEDs slowing SELECTs in a fully cached database ("Kevin Grittner", 11 July 2011, 19:16:51 )
Re: UPDATEDs slowing SELECTs in a fully cached database (Merlin Moncure, 11 July 2011, 21:43:33 )
Re: UPDATEDs slowing SELECTs in a fully cached database ("Kevin Grittner", 11 July 2011, 21:55:41 )
Re: UPDATEDs slowing SELECTs in a fully cached database (Merlin Moncure, 11 July 2011, 22:11:09 )
Re: UPDATEDs slowing SELECTs in a fully cached database (lars, 11 July 2011, 23:03:40 )
Re: UPDATEDs slowing SELECTs in a fully cached database (lars, 12 July 2011, 00:09:57 )
Re: UPDATEDs slowing SELECTs in a fully cached database (Ivan Voras, 12 July 2011, 15:13:35 )
Re: UPDATEDs slowing SELECTs in a fully cached database (lars, 12 July 2011, 19:02:01 )
Re: UPDATEDs slowing SELECTs in a fully cached database ("Kevin Grittner", 12 July 2011, 19:08:48 )
Re: UPDATEDs slowing SELECTs in a fully cached database (lars, 12 July 2011, 20:05:34 )
Re: UPDATEDs slowing SELECTs in a fully cached database ("Kevin Grittner", 12 July 2011, 20:51:35 )
Re: UPDATEDs slowing SELECTs in a fully cached database (lars, 12 July 2011, 20:58:59 )
Re: UPDATEDs slowing SELECTs in a fully cached database ("Kevin Grittner", 12 July 2011, 21:51:15 )
Re: UPDATEDs slowing SELECTs in a fully cached database (lars, 12 July 2011, 22:36:58 )
Re: UPDATEDs slowing SELECTs in a fully cached database ("Kevin Grittner", 13 July 2011, 14:46:49 )
Re: UPDATEDs slowing SELECTs in a fully cached database (Tom Lane, 13 July 2011, 15:17:49 )
Re: UPDATEDs slowing SELECTs in a fully cached database (lars, 13 July 2011, 18:12:26 )
Re: UPDATEDs slowing SELECTs in a fully cached database (Merlin Moncure, 13 July 2011, 19:01:39 )
Re: UPDATEDs slowing SELECTs in a fully cached database (Jeff Janes, 14 July 2011, 21:45:50 )
Re: UPDATEDs slowing SELECTs in a fully cached database ("Kevin Grittner", 13 July 2011, 21:57:57 )
Re: UPDATEDs slowing SELECTs in a fully cached database (Tom Lane, 13 July 2011, 22:21:24 )
Re: UPDATEDs slowing SELECTs in a fully cached database ("Kevin Grittner", 14 July 2011, 14:31:09 )
Re: UPDATEDs slowing SELECTs in a fully cached database (Robert Klemme, 14 July 2011, 15:06:37 )
Re: UPDATEDs slowing SELECTs in a fully cached database (Tom Lane, 14 July 2011, 16:01:21 )
Re: UPDATEDs slowing SELECTs in a fully cached database (lars, 17 July 2011, 02:13:57 )
Re: UPDATEDs slowing SELECTs in a fully cached database (lars, 13 July 2011, 18:23:46 )
Re: UPDATEDs slowing SELECTs in a fully cached database ("Kevin Grittner", 13 July 2011, 18:42:36 )
Re: UPDATEDs slowing SELECTs in a fully cached database (lars, 13 July 2011, 22:41:54 )
Re: UPDATEDs slowing SELECTs in a fully cached database (Jeff Janes, 14 July 2011, 23:03:50 )
Re: UPDATEDs slowing SELECTs in a fully cached database (lars, 16 July 2011, 00:22:05 )
Re: UPDATEDs slowing SELECTs in a fully cached database (Jeff Janes, 16 July 2011, 22:33:10 )
Re: UPDATEDs slowing SELECTs in a fully cached database (Greg Smith, 18 July 2011, 00:44:12 )
Re: UPDATEDs slowing SELECTs in a fully cached database (Jeff Janes, 13 July 2011, 00:40:18 )
Re: UPDATEDs slowing SELECTs in a fully cached database (Tom Lane, 13 July 2011, 01:16:51 )
Re: UPDATEDs slowing SELECTs in a fully cached database (Merlin Moncure, 12 July 2011, 21:38:13 )
Re: UPDATEDs slowing SELECTs in a fully cached database (lars, 12 July 2011, 23:15:50 )
Re: UPDATEDs slowing SELECTs in a fully cached database (Merlin Moncure, 13 July 2011, 14:53:00 )
Re: UPDATEDs slowing SELECTs in a fully cached database (Pavan Deolasee, 27 July 2011, 14:16:21 )
Re: UPDATEDs slowing SELECTs in a fully cached database (lars hofhansl, 29 July 2011, 17:26:27 )
Re: UPDATEDs slowing SELECTs in a fully cached database (Jeff Janes, 05 August 2011, 09:58:53 )
Re: UPDATEDs slowing SELECTs in a fully cached database ("Kevin Grittner", 12 July 2011, 19:26:43 )
Re: UPDATEDs slowing SELECTs in a fully cached database ("", 11 July 2011, 13:14:49 )
Re: UPDATEDs slowing SELECTs in a fully cached database (Robert Klemme, 11 July 2011, 15:27:04 )
Re: UPDATEDs slowing SELECTs in a fully cached database ("", 11 July 2011, 16:15:20 )
Re: UPDATEDs slowing SELECTs in a fully cached database (lars, 12 July 2011, 04:43:24 )
Re: UPDATEDs slowing SELECTs in a fully cached database ("Kevin Grittner", 12 July 2011, 14:36:29 )
Re: UPDATEDs slowing SELECTs in a fully cached database (Merlin Moncure, 12 July 2011, 16:23:43 )
Re: UPDATEDs slowing SELECTs in a fully cached database (Ivan Voras, 12 July 2011, 13:23:05 )
Re: UPDATEDs slowing SELECTs in a fully cached database (Merlin Moncure, 12 July 2011, 14:18:15 )
Re: UPDATEDs slowing SELECTs in a fully cached database (Ivan Voras, 12 July 2011, 14:43:28 )
Re: UPDATEDs slowing SELECTs in a fully cached database (Lars, 13 July 2011, 04:01:46 )

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: 12 July 2011, 20:04:43
Subject: Re: Planner choosing NestedLoop, although it is slower...

From: Lars
Date: 12 July 2011, 22:01:46
Subject: Re: UPDATEDs slowing SELECTs in a fully cached database