Recent work on parallel query has opened my eyes to exactly how frequently we request locks on various catalog tables. (Attached file is a lock analysis on a representative Pg server).
Given these are catalog tables, we aren't doing much to them that requires a strong lock. Specifically, only CLUSTER and VACUUM FULL touch those tables like that. When we do that, pretty much everything else hangs, cos you can't get much done while fundamental tables are locked. So my proposal is that we invent a "big catalog lock". The benefit of this is that we greatly reduce lock request traffic, as well as being able to control exactly when such requests occur. (Fine grained locking isn't always helpful). Currently, SearchCatCache() requests locks on individual catalog tables. Alternatively, we could request an AccessShareLock on a "big catalog lock" that must be accessed first before a strong relation-specific lock is requested. We just need to change the lockid used for each cache. We can still CREATE, ALTER, DROP and VACUUM all catalog tables - but this idea would block VACUUM FULL, but that would have been blocked anyway by general activity. We reduce lock traffic by having SearchCatCache() use a new call heap_catalog_open() which calls a new LockCatalogLock() which specifically caches whether we have already locked the BigCatalogLock or not. That cache can be cleared easily and cheaply at EOX. And it can be set quickly and easily in parallel worker tasks. We then add a special locking clause for VAC FULL on catalog tables. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
stat.out
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers