Re: Gather performance analysis - Mailing list pgsql-hackers

On 9/23/21 10:31 PM, Robert Haas wrote:
> On Thu, Sep 23, 2021 at 4:00 PM Tomas Vondra
> <tomas.vondra@enterprisedb.com> wrote:
>> I did find some suspicious behavior on the bigger box I have available
>> (with 2x xeon e5-2620v3), see the attached spreadsheet. But it seems
>> pretty weird because the worst affected case is with no parallel workers
>> (so the queue changes should affect it). Not sure how to explain it, but
>> the behavior seems consistent.
> 
> That is pretty odd. I'm inclined to mostly discount the runs with
> 10000 tuples because sending such a tiny number of tuples doesn't
> really take any significant amount of time, and it seems possible that
> variations in the runtime of other code due to code movement effects
> could end up mattering more than the changes to the performance of
> shm_mq. However, the results with a million tuples seem like they're
> probably delivering statistically significant results ... and I guess
> maybe what's happening is that the patch hurts when the tuples are too
> big relative to the queue size.
> 

Agreed on 10k rows being too small, we can ignore that. And yes, binary 
layout might make a difference, of course. My rule of thumb is 5% (in 
both directions) is about the difference that might make, and most 
results are within that range.

> I guess your columns are an md5 value each, which is 32 bytes +
> overhead, so a 20-columns tuple is ~1kB. Since Dilip's patch flushes
> the value to shared memory when more than a quarter of the queue has
> been filled, that probably means we flush every 4-5 tuples. I wonder
> if that means we need a smaller threshold, like 1/8 of the queue size?
> Or maybe the behavior should be adaptive somehow, depending on whether
> the receiver ends up waiting for data? Or ... perhaps only small
> tuples are worth batching, so that the threshold for posting to shared
> memory should be a constant rather than a fraction of the queue size?
> I guess we need to know why we see the time spike up in those cases,
> if we want to improve them.
> 

Not sure about this, because

(a) That should affect both CPUs, I think, but i5-2500k does not have 
any such issue.

(b) One thing I haven't mentioned is I tried with larger queue sizes too 
(that's the 16kB, 64kB, 256kB and 1MB in columns). Although it's true 
larger queue improve the situation a bit.

(c) This can't explain the slowdown for cases without any Gather nodes 
(and it's ~17%, so unlikely due to binary layout).


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company