Re: Excessive CPU usage in StandbyReleaseLocks()

On 19 June 2018 at 17:43, Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com> wrote:
> The problem is that StandbyReleaseLocks() does a linear search of all
> known AccessExclusiveLocks when a transaction ends. Luckily, since
> v10 (commit 9b013dc2) that is skipped for transactions that haven't
> taken any AELs and aren't using 2PC, but that doesn't help all users.

Good to see this getting fixed. My original patch [1] to fix this was
more along the lines of yours, only I partitioned the List in an array
indexed by the xid mod size of array. I had done this as I thought it
would be faster than a hash table and would likely see the locks
spread evenly over the table. IIRC Andres complained and said I
should use a hashtable, which I see you've done.

> + 97.88% 96.96% postgres postgres [.] StandbyReleaseLocks
>
> The attached patch splits the AEL list into one list per xid and
> sticks them in a hash table. That makes perf say something like:
>
> + 0.60% 0.00% postgres postgres [.] StandbyReleaseLocks

Yeah, I also saw similar with my patch.

> This seems like something we'd want to back-patch because the problem
> affects all branches (the older releases more severely because they
> lack the above-mentioned optimisation).
>
> Thoughts?

I do know this is causing quite a bit of trouble out in the wilds. I'd
be keen to not have to bear the brunt of any more of those troubles,
but it'll ultimately depend on how risky a patch looks.

[1] https://www.postgresql.org/message-id/CAKJS1f9vJ841HY%3DwonnLVbfkTWGYWdPN72VMxnArcGCjF3SywA%40mail.gmail.com

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services