Re: tuplesort memory usage: grow_memtuples

On Thu, Aug 16, 2012 at 4:26 PM, Peter Geoghegan <peter(at)2ndquadrant(dot)com> wrote:
> On 27 July 2012 16:39, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>>> Can you suggest a benchmark that will usefully exercise this patch?
>>
>> I think the given sizes below work on most 64 bit machines.
>
> My apologies for not getting around to taking a look at this sooner.
>
...
>
> I have attached a revision for your consideration, with a few
> editorialisations, mostly style-related.

Sorry, this fell through the cracks. Your proposed patch looks good.

...

> I think this patch (or at least your observation about I/O waits
> within vmstat) may point to a more fundamental issue with our sort
> code: Why are we not using asynchronous I/O in our implementation?

I only see a lot of io waits when using a small value of work_mem.
And I don't know how useful async would be there, as where would we
stash the read-ahead data with work_mem being so small? At larger
sizes, the kernel or raid controller automatic read ahead seems to be
enough to saturate a CPU.

Maybe just giving more aggressive advice about the default value of
work_mem being quite low for modern hardware, or making it scale with
shared_buffers by default similar to the way wal_buffers now does,
would be sufficient.

Cheers,

Jeff