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
-
Rework new SLRU test with injection points
- 94a3373ac5c3 18.0 landed
-
injection_point: Add injection_points.stats
- 2e35c67f9568 18.0 landed
-
injection_points: Add initialization of shmem state when loading module
- b2b023aa3706 18.0 landed
-
Add injection-point test for new multixact CV usage
- 768a9fd5535f 18.0 landed
-
Use conditional variable to wait for next MultiXact offset
- a0e0fb1ba56f 17.0 landed
-
Improve performance of subsystems on top of SLRU
- 53c2a97a9266 17.0 landed
-
Adjust VACUUM hastup LP_REDIRECT comments.
- 325bc54eed4e 16.0 cited
-
Test replay of regression tests, attempt II.
- f47ed79cc8a0 15.0 cited