Re: Reduce ProcArrayLock contention - Mailing list pgsql-hackers

Numbers look impressive and definitely shows that the idea is worth pursuing. I tried patch on my laptop. Unfortunately, at least for 4 and 8 clients, I did not see any improvement. In fact, averages over 2 runs showed a slight 2-4% decline in the tps. Having said that, there is no reason to disbelieve your numbers and no much powerful machines, we might see the gains.

BTW I ran the tests with, pgbench -s 10 -c 4 -T 300 

 

Well, I am not entirely sure if thats a correct way of looking at it. Sure, you would see less contention on the ProcArrayLock because the fact is that there are far fewer backends trying to acquire it. But those who don't get the lock will sleep and hence the contention is moved somewhere else, at least partially.  

 

As I said, I'm not seeing benefits on my laptop (Macbook Pro, Quad core, SSD). But then I ran with much lower scale factor and much lesser number of clients.

 

Thats seems be perfectly expected.

 

Do you know why that happens? Is it because the contention moves somewhere else with autovacuum on?

 

Regarding the design itself, I've an idea that may be we can create a general purpose infrastructure to use this technique. If its useful here, I'm sure there are other places where this can be applied with similar effect.

For example, how about adding an API such as LWLockDispatchWork(lock, mode, function_ptr, data_ptr)? Here the data_ptr points to somewhere in shared memory that the function_ptr can work on once lock is available. If the lock is available in the requested mode then the function_ptr is executed with the given data_ptr and the function returns. If the lock is not available then the work is dispatched to some Q (tracked on per-lock basis?) and the process goes to sleep. Whenever the lock becomes available in the requested mode, the work is executed by some other backedn and the primary process is woken up. This will most likely happen in the LWLockRelease() path when the last holder is about to give up the lock so that it becomes available in the requested "mode". Now there is lot of handwaving here and I'm not sure if the LWLock infrastructure permits us to add something like this easily. But I thought I will put forward the idea anyways. In fact, I remember trying something of this sort a long time back, but can't recollect why I gave up on the idea. May be I did not see much benefit of the entire approach of clubbing work-pieces and doing them in a single process. But then I probably did not have access to powerful machines then to correctly measure the benefits. Hence I'm not willing to give up on the idea, especially given your test results.

BTW may be the LWLockDispatchWork() makes sense only for EXCLUSIVE locks because we tend to read from shared memory and populate local structures in READ mode and that can only happen in the primary backend itself.