On 06/12/2011 11:39 PM, Robert Haas wrote: > Here is a patch that applies over the "reducing the overhead of > frequent table locks" (fastlock-v3) patch and allows heavyweight VXID > locks to spring into existence only when someone wants to wait on > them. I believe there is a large benefit to be had from this > optimization, because the combination of these two patches virtually > eliminates lock manager traffic on "pgbench -S" workloads. However, > there are several flies in the ointment. > > 1. It's a bit of a kludge. I leave it to readers of the patch to > determine exactly what about this patch they think is kludgey, but > it's likely not the empty set. I suspect that MyProc->fpLWLock needs > to be renamed to something a bit more generic if we're going to use it > like this, but I don't immediately know what to call it. Also, the > mechanism whereby we take SInvalWriteLock to work out the mapping from > BackendId to PGPROC * is not exactly awesome. I don't think it > matters from a performance point of view, because operations that need > VXID locks are sufficiently rare that the additional lwlock traffic > won't matter a bit. However, we could avoid this altogether if we > rejiggered the mechanism for allocating PGPROCs and backend IDs. > Right now, we allocate PGPROCs off of linked lists, except for > auxiliary procs which allocate them by scanning a three-element array > for an empty slot. Then, when the PGPROC subscribes to sinval, the > sinval mechanism allocates a backend ID by scanning for the lowest > unused backend ID in the ProcState array. If we changed the logic for > allocating PGPROCs to mimic what the sinval queue currently does, then > the backend ID could be defined as the offset into the PGPROC array. > Translating between a backend ID and a PGPROC * now becomes a matter > of pointer arithmetic. Not sure if this is worth doing. > > 2. Bad thing happen with large numbers of connections. This patch > increases peak performance, but as you increase the number of > concurrent connections beyond the number of CPU cores, performance > drops off faster with the patch than without it. For example, on the > 32-core loaner from Nate Boley, using 80 pgbench -S clients, unpatched > HEAD runs at ~36K TPS; with fastlock, it jumps up to about ~99K TPS; > with this patch also applied, it drops down to about ~64K TPS, despite > the fact that nearly all the contention on the lock manager locks has > been eliminated. On Stefan Kaltenbrunner's 40-core box, he was > actually able to see performance drop down below unpatched HEAD with > this applied! This is immensely counterintuitive. What is going on? > > Profiling reveals that the system spends enormous amounts of CPU time > in s_lock.
just to reiterate that with numbers - at 160 threads with both patches applied the profile looks like: samples % image name symbol name 828794 75.8662 postgres s_lock 51672 4.7300 postgres LWLockAcquire 51145 4.6817 postgres LWLockRelease 17636 1.6144 postgres GetSnapshotData 7521 0.6885 postgres hash_search_with_hash_value 6193 0.5669 postgres AllocSetAlloc 4527 0.4144 postgres SearchCatCache 4521 0.4138 postgres PinBuffer 3385 0.3099 postgres SIGetDataEntries 3160 0.2893 postgres PostgresMain 2706 0.2477 postgres _bt_compare 2687 0.2460 postgres fmgr_info_cxt_security 1963 0.1797 postgres UnpinBuffer 1846 0.1690 postgres LockAcquireExtended 1770 0.1620 postgres exec_bind_message 1730 0.1584 postgres hash_any 1644 0.1505 postgres ExecInitExpr even at the peak performance spot of the combined patch-set (-c40) the contention is noticable in the profile: samples % image name symbol name 1497826 22.0231 postgres s_lock 592104 8.7059 postgres LWLockAcquire 512213 7.5313 postgres LWLockRelease 230050 3.3825 postgres GetSnapshotData 176252 2.5915 postgres AllocSetAlloc 155122 2.2808 postgres hash_search_with_hash_value 116235 1.7091 postgres SearchCatCache 110197 1.6203 postgres _bt_compare 94101 1.3836 postgres PinBuffer 80119 1.1780 postgres PostgresMain 65584 0.9643 postgres fmgr_info_cxt_security 55198 0.8116 postgres hash_any 52872 0.7774 postgres exec_bind_message 48438 0.7122 postgres LockReleaseAll 46631 0.6856 postgres MemoryContextAlloc 45909 0.6750 postgres ExecInitExpr 42293 0.6219 postgres AllocSetFree Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
