Re: strange perf regression with data checksums

On 5/9/25 15:19, Aleksander Alekseev wrote:
> Hi,
>
>>> I'm not claiming that hint bits are necessarily the reason for the
>>> observed behavior but when something is off with presumably read-only
>>> queries this is the first reason that comes to mind. At least we
>>> should make sure hint bits are excluded from the equation. If memory
>>> serves, VACUUM FULL and CHECKPOINT after filling the table and
>>> creating the index should do the trick.
>>
>> Good question. I haven't checked that explicitly, but it's a tiny data
>> set (15MB) and I observed this even on long benchmarks with tens of
>> millions of queries. So the hint bits should have been set.
>>
>> Also, I should have mentioned the query does an index-only scan, and the
>> pin/unpin calls are on index pages, not on the heap.
>
> There is one more thing I would check. As I recall perf shows only
> on-CPU time while actually the backends may be sleeping on the locks
> most of the time. If this is the case perf will not show you the
> accurate picture.
>

Right, perf only shows on-cpu time (at least by default). But the
backends really are consuming 100% CPU (or close to that, the pgbench
needs some CPU too), there are no lock waits that I can see.

I forgot to share flamegraphs I collected on the EPYC machine, for cases
with 96 clients. So here they are.

> In order to check this personally I create gdb.script with a single GDB command:
>
> ```
> bt
> ```
>
> And execute:
>
> ```
> gdb --batch --command=gdb.script -p (backend_pid_here)
> ```
>
> ... 10+ times or so. If what you are observing is actually a lock
> contention and the backend sleeps on a lock most of the time, 8/10 or
> so stacktraces will show you this.
>
> I assume of course that the benchmark is done on release builds with
> disabled Asserts, etc.

Yes, there are regular builds with just --enable-debug --enable-depend,
nothing else (certainly not asserts).

>
> BTW do you believe this is a problem related exclusively to NUMA CPUs
> with 90+ cores or I can reproduce it on SMT as well?
>

No idea. I couldn't reproduce this on my ryzen machine at all, but that
only has 12 cores. The xeon (with 2x22 cores) seems to reproduce it, but
the difference is much smaller (1.2M vs. 1.5M tps), the pin/unpin have
only ~5% CPU, not 50%. Assuming it's the same issue, of course.

regards

--
Tomas Vondra