[ZODB-Dev] Big catalog indexes/btrees, reads, and the object cache

[Jeff Shell]

We've recently noticed some fairly significant speed issues cropping up on a customer site (zope 3 / bluebream-ish stack). We use zc.catalog and its Value and Set indexes and it's serves us rather well, up until recently.

In a *cold start* in one environment, profiling the home page reported 27 calls of the 'index.apply' method, which turned into 27 calls of 'BTrees._IFBtree.multiunion' function, and those 27 multiunion calls had a Cumulative Time of 58 seconds. Then I looked at the multiunion callees and got this:

> winterhome-firstload.profile% callees multiunion
>    Random listing order was used
>    List reduced from 1445 to 1 due to restriction <'multiunion'>
> 
> Function                      called...
>                                   ncalls  tottime  cumtime
> {BTrees._IFBTree.multiunion}  ->   65980    0.132   57.891  Connection.py:848(setstate)

I believe this is just doing the raw BTree index loading - not doing any waking of the objects referenced by the int (document) ids. If I profile this page immediately afterwards, those 27 calls to multiunion have a cumulative time of 0.193 seconds with no ZODB activity (this is running from a script).

These numbers vary depending on the machine and storages in use, but this seems to be a constant issue of performance. We seldom see the 57 second times in production, but we're still seeing pages that are significantly slow, or that will seem speedy unless they're not used for an hour or more (ie, looking at some customer-specific reports), and then they're very sluggish again. It feels like at least some the BTree nodes/buckets/whatever are falling out of the object cache, because it seems to be doing the raw query that seems to be taking the most time.

The document counts of these indexes are around 170000 items each, and tend to cover every major-ish content object. I'm wondering how this is impacting the object caches and how we should be sizing them.

Are we hitting a limit on BTree and/or catalog index size?
Do we need to keep upping the zodb-object-cache size? (What is a good metric for setting that cache size?)

Thanks,
Jeff Shell
jeff at bottlerocket.net