Skip site navigation (1) Skip section navigation (2)

Re: Sorting writes during checkpoint

From: Greg Smith <gsmith(at)gregsmith(dot)com>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, ITAGAKI Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>, pgsql-patches(at)postgresql(dot)org
Subject: Re: Sorting writes during checkpoint
Date: 2008-07-10 01:39:29
Message-ID: Pine.GSO.4.64.0807092023510.8953@westnet.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
On Fri, 4 Jul 2008, Simon Riggs wrote:

> No action on this seen since last commitfest, but I think we should do
> something with it, rather than just ignore it.

Just no action worth reporting yet.  Over the weekend I finally reached 
the point where I've got a system that should be capable of independently 
replicating the results improvement setup here, and I've started 
performance testing of the patch.  Getting useful checkpoint test results 
from pgbench is really a pain.

> Sorting by file might have inadvertently shown benefit at the tablespace
> level on a larger server with spread out data whereas on Tom's test
> system I would guess just a single tablespace was used.

I doubt this has anything to do with it, only because the pgbench schema 
doesn't split into tablespaces usefully.  Almost all of the real action is 
on a single table, accounts.

My suspicion is that sorting only benefits in situations where you have a 
disk controller with a significant amount of RAM on it, but the server RAM 
is much larger.  In that case the sorting horizon of the controller itself 
is smaller than what the server can do, and the sorting makes it less 
likely you'll end up with the controller filled with unsorted stuff that 
takes a long time to clear.

In Tom's test, there's probably only 8 or 16MB worth of cache on the disk 
itself, so you can't get a large backlog of unsorted writes clogging the 
write pipeline.  But most server systems have 256MB or more of RAM there, 
and if you get that filled with seek-heavy writes (which might only clear 
at a couple of MB a second) the delay for that cache to empty can be 
considerable.

That said, I've got a 256MB controller here and have a very similar disk 
setup to the one postiive results were reported on, but so far I don't see 
any significant difference after applying the sorted writes patch.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

In response to

Responses

pgsql-hackers by date

Next:From: Abhijit Menon-SenDate: 2008-07-10 03:11:40
Subject: Re: Extending grant insert on tables to sequences
Previous:From: Robert HodgesDate: 2008-07-10 00:55:25
Subject: Follow-up on replication hooks for PostgreSQL

pgsql-patches by date

Next:From: Abhijit Menon-SenDate: 2008-07-10 03:11:40
Subject: Re: Extending grant insert on tables to sequences
Previous:From: David FetterDate: 2008-07-10 00:06:19
Subject: Re: [PATCHES] WITH RECURSIVE updated to CVS TIP

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group