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

Re: 8.4 open item: copy performance regression?

From: Simon Riggs <simon(at)2ndQuadrant(dot)com>
To: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)cc>, Greg Smith <gsmith(at)gregsmith(dot)com>, Andrew Dunstan <andrew(at)dunslane(dot)net>, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, Alan Li <ali(at)truviso(dot)com>
Subject: Re: 8.4 open item: copy performance regression?
Date: 2009-06-21 18:29:09
Message-ID: 1245608949.31430.33.camel@ebony.2ndQuadrant (view raw or flat)
Thread:
Lists: pgsql-hackers
On Sun, 2009-06-21 at 20:37 +0300, Heikki Linnakangas wrote:
> Robert Haas wrote:
> > On Sun, Jun 21, 2009 at 11:52 AM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> So to my mind, the only question left to answer (at least for the 8.4
> >> cycle) is "is 16MB enough, or do we want to make the ring even bigger?".
> >> Right at the moment I'd be satisfied with 16, but I wonder whether there
> >> are scenarios where 32MB would show a significant advantage.
> > 
> > Even 32MB is not that much.  It seems to me that in any realistic
> > production scenario you're going to have at least half a gig of shared
> > buffers, so we're really talking about at most one-sixteenth of the
> > shared buffer arena, and possibly quite a bit less.  I think that's
> > pretty conservative.
> 
> I was going to say that since we flush the WAL every 16MB anyway (at 
> every XLOG file switch), you shouldn't see any benefit with larger ring 
> buffers, since to fill 16MB of data you're not going to write more than 
> 16MB WAL. But then I realized that that's not true if you have an 
> unusually low fillfactor. If you only fill each page say 50% full, 
> you're going to use 32MB worth of data pages but only write 16MB of WAL. 
> And maybe you could have a situation like that with very wide rows as 
> well, with wasted space on each page that's not enough to store one more 
>   row.

If walwriter is working correctly then it should be writing and fsyncing
WAL, while the COPY process just inserts WAL. I don't see that as an
argument to limit us to 16MB. But I take your point as being an argument
in favour of that as a consensus value for us to choose.

-- 
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


In response to

pgsql-hackers by date

Next:From: Marc G. FournierDate: 2009-06-21 21:17:06
Subject: Re: rc tarball built with older flex version?
Previous:From: Simon RiggsDate: 2009-06-21 18:26:02
Subject: Re: 8.4 open item: copy performance regression?

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