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

help / color / mirror / Atom feed
From: lars <lhofhansl@yahoo.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: Ivan Voras <ivoras@freebsd.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: UPDATEDs slowing SELECTs in a fully cached		 database
Date: Wed, 13 Jul 2011 11:23:09 -0700
Message-ID: <4E1DE28D.6020207@yahoo.com> (raw)
In-Reply-To: <4E1D697C020000250003F29A@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>

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.

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

-- Lars




view thread (57+ messages)  latest in thread

Message-ID: <4E1DE28D.6020207@yahoo.com>
Permalink:  ../4E1DE28D.6020207@yahoo.com/
Also on:    postgresql.org/message-id/4E1DE28D.6020207@yahoo.com

 · 

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: lhofhansl@yahoo.com, Kevin.Grittner@wicourts.gov, ivoras@freebsd.org
  Subject: Re: UPDATEDs slowing SELECTs in a fully cached		 database
  In-Reply-To: <4E1DE28D.6020207@yahoo.com>

* 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