[ZODB-Dev] Re: Berkley Transactions slow to commit?

[Barry A. Warsaw]

>>>>> "CW" == Chris Withers <chrisw@nipltd.com> writes:

    CW> Hi Barry,

Hi Chris!

    CW> Having had more than my fair share of fun'n'games with
    CW> FileStorage, I'm now experiencing different fun'n'games with
    CW> BerkleyStorage ;-)

Ah, and that's a /good/ thing, right? <wink>

    CW> I need to index about 30,000 documents. I'm doing this using a
    CW> python script (not a (script) python ;-) that imports Zope and
    CW> hence uses custom_zodb.py to open a Full berkley storage.  I
    CW> figured doing all 30,000 documents in one transaction wasn't a
    CW> good idea, so I was trying do them in batches of 500. After
    CW> each batch I'd do a get_transaction().commit().

    CW> First problem, I kept on running out of locks doing this. So,
    CW> I bumped the lock settings up to:

    | set_lk_max_locks 1000000
    | set_lk_max_objects 100000
    | set_lk_max_lockers 100

You don't say which version of Sleepycat's BerkeleyDB you're using (or
did I miss that?).  I would /highly/ suggest upgrading to BDB 3.3.11
which has greatly improved lock usage.  My standard migration test
reduced its lock usage an order of magnitude after the upgrade (from
about 200k locks to 20k locks, which makes sense for the BTree-based
access method that Full uses).

    CW> ...this stopped the error, but the python process chewed
    CW> through 220Mb of RAM.

Memory usage is the fun part. ;)  You choice of Berkeley cache size is
going to make a huge difference in performance, irregardless of the
lock issues.  The default is ridiculously low, so you'll almost
definitely want to set it higher, but be careful not to set the cache
too large or you'll lose performance as you start to contend for
system resources.

Example: on our 256MB PIII, upping the cache to 128MB reduced the
migration test time a factor of 4.  Setting it to 64MB or 512MB both
reduced performance considerably (more with the latter setting).

You shouldn't have too much trouble with transaction sizes or number
of objects per transaction.  Again, in our migration test, we have
5900+ transactions, 248k unique objects, 27M distinct object
revisions.  The largest transaction by # of objects has 12k objects,
while the largest by total pickle size is ~25MB.

    CW> Can you (or anyone else) enlighten me as to what's going on
    CW> here?  Why is the commit taking so long? How can I speed it
    CW> up?

So after upgrading and experimenting with cache, the next issue is to
do some more indepth analysis of the problem.  I'm hoping that the
hotshot profiler work that Fred Drake is doing for Python 2.2 will
help here.  I want to better understand where the time is going to in
_finish(), but since the majority of the work is in builtin functions,
the standard python profiler doesn't help (yes, I could instrument it
for gprof, but that is somewhat painful, but I may have to do that
anyway if hotshot doesn't come along in time).

For now, we've decided that the performance is good enough for a 1.0
release because we're within about a factor of 4 of FileStorage.
speed.py showed us a factor of 2 worse than FileStorage.

    CW> Also, in general, should you try and have a few big
    CW> transactions or many small transactions when using BerkleyDB?
    CW> Does this vary depending on whether you use Minimal or Full?

Size of transactions, either by number of objects or by transaction
size doesn't seem to make much of a difference in individual
transaction commit performance.  The primary factor seems to be
Berkeley cache size.

    CW> Oh, and while I remember, should I use Minimal or Full if I
    CW> want a simple, efficient, non-versioning storage?

At this point, I wouldn't recommend Minimal since it hasn't been a
high priority for us, and I haven't spent much time working on it
recently.  You can certainly try it, and I'd be very interested in
your feedback.  I'll attempt to fix any problems with it that you
might encounter.

-Barry