Re: Trouble with hashagg spill I/O pattern and costing

On Tue, May 19, 2020 at 09:27:34AM -0700, Jeff Davis wrote:
>On Tue, 2020-05-19 at 17:12 +0200, Tomas Vondra wrote:
>> I think there are two related problem - with costing and with
>> excessive
>> I/O due to using logical tapes.
>
>Thank you for the detailed analysis. I am still digesting this
>information.
>
>> This kinda makes me question whether logical tapes are the right tool
>> for hashagg. I've read the explanation in logtape.c why it's about
>> the
>> same amount of I/O as using separate files, but IMO that only really
>> works for I/O patters similar to merge sort - the more I think about
>> this, the more I'm convinced we should just do what hashjoin is
>> doing.
>
>Fundamentally, sort writes sequentially and reads randomly; while
>HashAgg writes randomly and reads sequentially.
>

Not sure. I think the charts and stats of iosnoop data show that an
awful lot of reads during sort is actually pretty sequential. Moreover,
sort manages to read the data in much larger blocks - 128kB instead of
just 8kB (which is what hashagg seems to be doing).

I wonder why is that and if we could achieve that for hashagg too ...

>If the random writes of HashAgg end up fragmented too much on disk,
>then clearly the sequential reads are not so sequential anyway. The
>only way to avoid fragmentation on disk is to preallocate for the
>tape/file.
>

And if there a way to pre-allocate larger chunks? Presumably we could
assign the blocks to tape in larger chunks (e.g. 128kB, i.e. 16 x 8kB)
instead of just single block. I haven't seen anything like that in
tape.c, though ...

>BufFile (relying more on the OS) would probably do a better job of
>preallocating the disk space in a useful way; whereas logtape.c makes
>it easier to manage buffers and the overall number of files created
>(thereby allowing higher fanout of partitions).
>
>We have a number of possibilities here:
>
>1. Improve costing to reflect that HashAgg is creating more random IOs
>than Sort.

I think we'll need to do something about this, but I think we should try
improving the behavior first and then model the costing based on that.

>2. Reduce the partition fanout in the hopes that the OS does a better
>job with readahead.

I doubt this will make a significant difference. I think the problem is
the partitions end up interleaved way too much in the temp file, and I
don't see how a lower fanout would fix that.

BTW what do you mean when you say "fanout"? Do you mean how fast we
increase the number of partitions, or some parameter in particular?

>3. Switch back to BufFile, in which case we probably need to reduce the
>fanout for other reasons.

Maybe, although that seems pretty invasive post beta1.

>4. Change logtape.c to allow preallocation or to write in larger
>blocks.

I think this is what I suggested above (allocating 16 blocks at a time,
or something). I wonder how wasteful this would be, but I think not very
much. Essentially, with 1024 partitions and pre-allocating space in
128kB chunks, that means 128MB may end up unused, which seems ok-ish,
and I guess we could further restrict that by starting with lower value
and gradually increasing the number. Or something like that ...

>5. Change BufFile to allow more control over buffer usage, and switch
>to that.
>

Maybe. I don't recall what exactly is the issue with buffer usage, but I
think it has the same invasiveness issue as (3). OTOH it's what hashjoin
does, and we've lived with it for ages ...

>#1 or #2 are the least invasive, and I think we can get a satisfactory
>solution by combining those.
>

OK. I think tweaking the costing (and essentially reverting to what 12
does for those queries) is perfectly reasonable. But if we can actually
get some speedup thanks to hashagg, even better.

>I saw good results with the high fanout and low work_mem when there is
>still a lot of system memory. That's a nice benefit, but perhaps it's
>safer to use a lower fanout (which will lead to recursion) until we get
>a better handle on the IO patterns.
>

I don't know how much we can rely on that - once we push some of the
data from page cache, it has the issues I described. The trouble is
people may not have enough memory to keep everything in cache, otherwise
they might just as well bump up work_mem and not spill at all.

>Perhaps you can try recompiling with a lower max partitions and rerun
>the query? How much would we have to lower it for either the cost to
>approach reality or the OS readahead to become effective?
>

I can try that, of course. Which parameters should I tweak / how?

I can also try running it with BufFile, in case you prepare a WIP patch.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services