[ZODB-Dev] Pack complete: 80% of a 425GB database packed

[Martijn Pieters]

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