On 2014-07-01 15:20:37 -0400, Tom Lane wrote: > Jeff Frost <jeff@xxxxxxxxxxxxx> writes: > >> On Jun 30, 2014, at 4:04 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > >>> Did you check whether the locks were all on temp tables of the > >>> ON COMMIT DROP persuasion? > > > And indeed it did catch up overnight and the lag increased shortly after a correlating spike in AccessExclusiveLocks that were generated by temp table creation with on commit drop. > > OK, so we have a pretty clear idea of where the problem is now. > > It seems like there are three, not mutually exclusive, ways we might > address this: > > 1. Local revisions inside StandbyReleaseLocks to make it perform better in > the presence of many locks. This would only be likely to improve matters > much if there's a fixable O(N^2) algorithmic issue; but there might well > be one. > > It sounded like Andres had taken a preliminary look at #1 and found a > possible avenue for improvement, which I'd encourage him to pursue. > I don't have the resources to do this right now, but yes, I think we can get relatively easily get rid of the O(num_locks * num_subtransactions) behaviour. > 2. Avoid WAL-logging AccessExclusiveLocks associated with temp tables, on > the grounds that no standby should be touching them. I'm not entirely > sure that that argument is bulletproof though; in particular, even though > a standby couldn't access the table's data, it's possible that it would be > interested in seeing consistent catalog entries. Hm. We definitely perform checks surprisingly late for those. It's possible to do SELECT * FROM pg_temp_<nn>.whatever; without an error f there's no rows of if the rest of the plan doesn't do accesses to that table. The check prohibiting access is only in bufmgr.c... So yea, I don't think we can do this for at least < 9.4. And there it'll still be hard. > 3. Avoid WAL-logging AccessExclusiveLocks associated with > new-in-transaction tables, temp or not, on the grounds that no standby > could even see such tables until they're committed. We could go a bit > further and not take out any locks on a new-in-transaction table in the > first place, on the grounds that other transactions on the master can't > see 'em either. > > For both #2 and the conservative version of #3, the main implementation > problem would be whether the lock WAL-logging code has cheap access to > the necessary information. I suspect it doesn't. Not trivially. It's logged directly in LockAcquireExtended(). We could add the information into locktags as there's unused fields for relation locktags, but brrr. > The radical version of #3 might be pretty easy to do, at least to the > extent of removing locks taken out during CREATE TABLE. I suspect there > are some assertions or other consistency checks that would get unhappy if > we manipulate relations without locks, though, so those would have to be > taught about the exception. > > Also, we sometimes forget new-in-transaction > status during relcache flush events; it's not clear if that would be a > problem for this. I think that hole is actually pluggable in newer releases - at least there's no code around that assumes rd_createSubid now is persistent, even across cache resets. But I think more importantly it's probably quite possible to hit a similar problem without ON COMMIT DROP relations. Say DISCARD TEMP inside a transaction (with several subxacts) or so? So we probaly really should fix the bad scaling. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
