[ZODB-Dev] Large ZODB packing and progress

[Martijn Pieters]

Last night we used our two-phase pack to start packing the largest
Oracle RelStorage ZODB we run. Some statistics first:

* Never packed before in it's 2 year existence.
* Has more than 4.5 million transactions, 52 million object states.
* Packing to 60 days ago means we'll loose 4 million transactions, and
41.5 million states.

Packing ran through the night, but I had to abort the pack this
morning as I am still skittish about holding commit locks for packing
during normal operations. I'm confident we can resume the pack though,
as we can skip the analysis phase, and just have the pack run re-do
the already packed transactions quickly (all DELETE sql statements
will operate on already deleted rows, should be fast).

In the 8 hours that we actually packed, almost 300k transactions were
processed. That's only 7 percent of the whole, so I may want to tune
the duty cycle to a more aggressive setting. Some 8 million object
states were deleted though, because the earliest transactions in this
database comprised of massive imports of data from the system we
replaced.

We encountered only 2 problems: The Oracle cluster is set up to create
redo logs for transactions to be backed up from a staging area. We
generated 35 GB (estimated) of such logs, so you need to make sure
there is enough space allocated for these files. My initial analysis
runs from a day earlier managed to overrun this space and Oracle
ground to a screeching halt. The limits were lifted and we kept a
close eye on the Oracle v$recovery_file_dest view after that.

The other problem is that the packing process offers no progress
information between the "will pack" and "cleaning up" lines. You could
turn up the log level to 'debug' at which point you'll get lines for
*every* transaction to be packed, and at 4 million such transactions
that's a bit too much for me. I've added INFO level logging that
provides progress feedback instead, at most every .1% of the total
number of transactions. See:

  https://bitbucket.org/mjpieters/relstorage-mq/src/tip/packingprogress.patch

I think I'll commit that progress patch to RelStorage SVN after we
tried it on for size, in the next few days.

-- 
Martijn Pieters