We are occasionally seeing heavy CPU contention with hundreds of processes active but waiting on a lightweight lock - usually lock manager or buffer mapping it seems. This is happening with VMs configured with about 64 CPUs, 350GBs ram, and while we would typically only have 30-100 concurrent processes, there will suddenly be ~300 and many show active with LwLock and they take much longer than usual. Any suggested options to monitor for such issues or logging to setup so the next issue can be debugged properly?
It has seemed to me that this occurs when there are more than the usual number of a particular process type and also something that is a bit heavy in usage of memory/disk. It has happened on various tenant instances and different application processes as well.
Would/how might the use of huge pages (or transparent huge pages, or OFF) play into this scenario?