MultiXact\SLRU buffers configuration

x4mmm@yandex-team.ru

From: "Andrey M. Borodin" <x4mmm@yandex-team.ru>
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2020-05-08T16:36:40Z
Lists: pgsql-hackers
Hi, hackers!

*** The problem ***
I'm investigating some cases of reduced database performance due to MultiXactOffsetLock contention (80% MultiXactOffsetLock, 20% IO DataFileRead).
The problem manifested itself during index repack and constraint validation. Both being effectively full table scans.
The database workload contains a lot of select for share\select for update queries. I've tried to construct synthetic world generator and could not achieve similar lock configuration: I see a lot of different locks in wait events, particularly a lot more MultiXactMemberLocks. But from my experiments with synthetic workload, contention of MultiXactOffsetLock can be reduced by increasing NUM_MXACTOFFSET_BUFFERS=8 to bigger numbers.

*** Question 1 ***
Is it safe to increase number of buffers of MultiXact\All SLRUs, recompile and run database as usual?
I cannot experiment much with production. But I'm mostly sure that bigger buffers will solve the problem.

*** Question 2 ***
Probably, we could do GUCs for SLRU sizes? Are there any reasons not to do them configurable? I think multis, clog, subtransactions and others will benefit from bigger buffer. But, probably, too much of knobs can be confusing.

*** Question 3 ***
MultiXact offset lock is always taken as exclusive lock. It turns MultiXact Offset subsystem to single threaded. If someone have good idea how to make it more concurrency-friendly, I'm willing to put some efforts into this.
Probably, I could just add LWlocks for each offset buffer page. Is it something worth doing? Or are there any hidden cavers and difficulties?

Thanks!

Best regards, Andrey Borodin.


Commits

  1. Rework new SLRU test with injection points

  2. injection_point: Add injection_points.stats

  3. injection_points: Add initialization of shmem state when loading module

  4. Add injection-point test for new multixact CV usage

  5. Use conditional variable to wait for next MultiXact offset

  6. Improve performance of subsystems on top of SLRU

  7. Adjust VACUUM hastup LP_REDIRECT comments.

  8. Test replay of regression tests, attempt II.