[ZODB-Dev] Large ZODB packing and progress

[Shane Hathaway]

On 02/25/2011 03:44 AM, Martijn Pieters wrote:
> 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.

Out of curiosity, how long did the pre-pack take?

> 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).

I agree.  It's unfortunate that packing has to hold the commit lock, but 
without it, deadlocks tend to occur.

> 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.

Good to know!

> 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.

Please do!  I'll watch my buildbot.

Shane