pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: Ivan Voras <ivoras@freebsd.org>
Cc: pgsql-performance@postgresql.org, "lars" <lhofhansl@yahoo.com>
Subject: Re: UPDATEDs slowing SELECTs in a fully cached database
Date: Wed, 13 Jul 2011 18:21:17 -0400
Message-ID: <11679.1310595677@sss.pgh.pa.us> (raw)
In-Reply-To: <4E1DCE81020000250003F2DC@gw.wicourts.gov>
References: <4E16479D.3050605@yahoo.com>
	<4E1A0CBC.6090009@yahoo.com>
	<4E1A31AB.9010201@postnewspapers.com.au>
	<1310359653.80687.YahooMailRC@web121705.mail.ne1.yahoo.com>
	<4E1AEDAB020000250003F1C3@gw.wicourts.gov>
	<4E1B41EA.90104@yahoo.com>
	<4E1B05CC020000250003F1E6@gw.wicourts.gov>
	<CAHyXU0wPhZsQ_pMzfUWW0O=t_+2uwF-CJQNw-nuaxkjbi2zPEQ@mail.gmail.com>
	<4E1B810D.4070404@yahoo.com>
	<4E1B90AF.1020408@yahoo.com>
	<ivho9v$6dp$1@dough.gmane.org>
	<4E1C9A05.7060005@yahoo.com>
	<4E1C5563020000250003F252@gw.wicourts.gov>
	<4E1CA8E9.6090108@yahoo.com>
	<4E1CB56E.7080801@yahoo.com>
	<4E1D697C020000250003F29A@gw.wicourts.gov>
	<4E1D697C020000250003F29A@gw.wicourts.gov>
	<4752.1310570261@sss.pgh.pa.us>
	<4E1DCE81020000250003F2DC@gw.wicourts.gov>

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> 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.

Oh, hmmm ... that code was written with the idea that things like
sequence XLOG_SEQ_LOG records ought to be flushed to disk before
reporting commit; otherwise you don't have a guarantee that the same
sequence value couldn't be handed out again after crash/restart,
in a transaction that just does something like
	SELECT nextval('myseq');
without any updates of regular tables.

It seems like we ought to distinguish heap cleanup activities from
user-visible semantics (IOW, users shouldn't care if a HOT cleanup has
to be done over after restart, so if the transaction only wrote such
records there's no need to flush).  This'd require more process-global
state than we keep now, I'm afraid.

Another approach we could take (also nontrivial) is to prevent
select-only queries from doing HOT cleanups.  You said upthread that
there were alleged performance benefits from aggressive cleanup, but
IMO that can charitably be described as unproven.  The real reason it
happens is that we didn't see a simple way for page fetches to know soon
enough whether a tuple update would be likely to happen later, so they
just do cleanups unconditionally.

			regards, tom lane



view thread (57+ messages)  latest in thread

Message-ID: <11679.1310595677@sss.pgh.pa.us>
Permalink:  ../11679.1310595677@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/11679.1310595677@sss.pgh.pa.us

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, Kevin.Grittner@wicourts.gov, ivoras@freebsd.org, lhofhansl@yahoo.com
  Subject: Re: UPDATEDs slowing SELECTs in a fully cached database
  In-Reply-To: <11679.1310595677@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox