Re: 8.4 open item: copy performance regression?

On Sun, Jun 21, 2009 at 11:52 AM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
> Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
>> I wonder if using the small ring showed any benefit when the COPY is not
>> WAL-logged? In that scenario block-on-WAL-flush behavior doesn't happen,
>> so the small ring might have some L2 cache benefits.
>
> I think the notion that we might get a cache win from a smaller ring
> is an illusion.  We're not expecting to go back and re-read from a
> previously filled page in this scenario.  In any case, all of the
> profiling results so far show that the CPU bottlenecks are elsewhere.
> Until we can squeeze an order of magnitude out of COPY's data parsing
> and/or XLogInsert, any possible cache effects will be down in the noise.
>
> 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.

...Robert