Early this morning, after packing through the weekend, our somewhat overweight Oracle RelStorage ZODB pack was completed. I am still waiting for the final size from the customer DBAs, but before the pack this beast was occupying 425GB. The pack removed 80% of the object_state rows, so hopefully this is now reduced to a more manageable 85GB or so.
The nowait locking strategy worked very well. We did have a few transactions that covered a huge amount of object states, and these managed to lock the database up for 10-15 minutes at a time, forcing me to abort the pack at some point on Saturday to prevent the Zope cluster from being unresponsive. These transactions would have locked up the database like this wether we used the nowait strategy or the duty-cycle, I am not sure what we could have done about this problem short of not letting the ZODB and transaction sizes get out of hand in the first place. I've created a spreadsheet with the data from the pack log info, to visualize for ourselves what the database looked like in terms of object states per transaction and such. Note that the timestamps up until row 368 reflect already packed transactions (that's how far we got before aborting on Saturday), after that the times reflect how long it took to remove object states for around 4000 transactions at a time. See: https://spreadsheets.google.com/ccc?key=0Aqf3DYYXSZ6RdEdkUF9lTFBKSEtkNFVqNEo2b3lnTnc&hl=en_GB&authkey=COip9qsL The final pack cleanup took 2.5 hours (4:40: cleaning up, 07:08: finished successfully). I've been looking a bit closer at stage and am wondering if that stage really needs to hold the commit lock. Holding the commit lock for such a long time (at least for the bulk of those 2.5 hours) is a Really Bad Idea, and I was lucky the pack completed within the maintenance window. One hour later and the cluster would have been affected hugely. I think we can remove holding the commit lock during the pack cleanup altogether. For starters, the object_ref and object_refs_added tables are only ever used during packing, so the packing lock is more than enough to protect these. And unless I am missing something, we don't need to worry about the transactions table either. The rest of RelStorage ignores transactions where the packed flag has been set to TRUE, so deleting the packed and empty transactions from the table will never lead to deadlocks, right? Or am I missing something about how RelStorage implements polling and caching? In any case, the history-free version doesn't need to lock at all, because it only ever touches the pack-specific tables in the pack cleanup. I'll move the commit lock down to the section where empty transactions are deleted, and remove it from the history-free version. If the transaction table needs no lock protection either, we can get rid of the lock during cleanup altogether. I'd like to hear confirmation on this though. On a side note: I see that the history-preserving object_ref and object_refs_added deletion SQL statements have been optimized for MySQL, but not for the history-free version. Wouldn't those statements not also benefit from using a JOIN? -- Martijn Pieters _______________________________________________ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev
