From: | Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | Dilip Kumar <dilipbalaut(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Gather performance analysis |
Date: | 2021-09-23 21:36:34 |
Message-ID: | 7cf6e323-459c-b0c8-0702-75a2a306da3a@enterprisedb.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 9/23/21 10:31 PM, Robert Haas wrote:
> On Thu, Sep 23, 2021 at 4:00 PM Tomas Vondra
> <tomas(dot)vondra(at)enterprisedb(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel Gustafsson | 2021-09-23 21:41:26 | Re: OpenSSL 3.0.0 compatibility |
Previous Message | Peter Eisentraut | 2021-09-23 21:26:46 | Re: OpenSSL 3.0.0 compatibility |