launchpad-dev team mailing list archive

[Robert Collins <robert.collins@xxxxxxxxxxxxx>]

One of the very common patterns I've seen as I've been examining our
timeouts goes something like this:
95.	1306	0ms	launchpad-main-slave	SELECT
Distribution.bug_reported_acknowledgement,
...Distribution.translationpermission FROM Distribution WHERE id IN
(NULL) ORDER BY Distribution.name
96.	1415	17ms	launchpad-main-slave	SELECT Milestone.active,
Milestone.codename, Milestone.dateexpected, ... WHERE
Milestone.product = %s AND Milestone.active = %s
97.	1436	7ms	launchpad-main-slave	SELECT Product.owner, Product.,...l
FROM Product WHERE id IN (13594)
98.	1444	2ms	launchpad-main-slave	SELECT Distribution.bug_re...on FROM
Distribution WHERE id IN (NULL) ORDER BY Distribution.name
99.	1478	52ms	launchpad-main-slave	SELECT Milestone.active,
Milestone.code... FROM Milestone WHERE Milestone.product = %s AND
Milestone.active = %s
100.	1534	3ms	launchpad-main-slave	SELECT Product.owner,
...Product.wikiurl FROM Product WHERE id IN (1834)

(And so on)

All the times add up substantially, get 100 products in a productset
and we're doing 300 queries just-like-that. (Launchpad-project, or
zope?)

Thats roughly, in pseudo code - because I haven't looked at this
particular cases python yet:
for product in (some query):
   for distro in product.getDistroSeries():
      for milestone in distro.getMileStones()

This is pretty obvious that its likely doing DB access.

A more insidious version of it looks like this:
for person in team:
    print person.karma, person.is_ubuntu_coc_signer

Its entirely unobvious that this is going to do database access unless
you know, a-priori that both of those attributes are in fact derived:
the former from the karmatotalcache (which is itself a derived table
which we update lazily) and a coc signing table where we record each
individual signature.

This makes it very hard for folk looking at a template, or a python
function to reason about its performance without knowing the
underlying data model very well : this raises the bar for
contributors, makes performance tuning more tricky, and is generally a
negative thing.

I have some long term ideas about this: broadly, to separate out
'looks like python' from 'looks like it does DB access' so that its
obvious when db access is going to happen, and structure things so
that:
 - if it looks cheap, it is cheap - always
 - no database access is needed during template rendering (as a safety
measure for the first point : templates look cheap always)
 - set based, fast behaviour is easy and the default

And I have some ideas about implementation; however this isn't
something I see us having cycles or  bandwidth for for many months.
Such a change is going to be a significant investment: we need to find
ways to do it, trial them out, migrate code, and so on.

In the interim, I'd like us to start using caches in the model code.
We already have ~40 such caches using cachedproperty (one particular
implementation strategy) - so this isn't a new thing.

There are some risks, primarily that code which changes things will
need to start invalidating caches - e.g. above if you add a coc
signature, and the is_ubuntu_coc_signer attribute has already cached
'No' as a result, then you need to toggle the cached value.

There are some very concrete benefits: less DB time, faster page
rendering, more efficient use of resources.

We have pages which should be rendering in 10-20 queries rendering in
hundreds or thousands of queries.

Now, there are some questions which turn up.



Can Storm help us?

Free has an eager loading branch -
http://bazaar.launchpad.net/~free.ekanayaka/storm/eager-loading. What
this does is a storm-connected version of our prejoins. I believe that
like our prejoins its opt-in when you need it (good), but its also
very unsophisticated. Specifically it isn't constrained (so it cannot
model is_ubuntu_coc_signer), and it only (appears) to handle a single
link. That said, its not finished yet, and perhaps we can make
prejoins a bit nicer with it.

A related problem with prejoins is that they only help with
*reference* lookups, not ReferenceSet :  a ReferenceSet will query the
DB again, a Reference that has been prejoined won't (because the
primary key is in the storm object cache). This means that when you
have many rows with many different base objects, prejoins will help,
but they won't ever be the full story.

I think Storm will be of great assistance in the long term stuff I
mention above, but in the short term we want to achieve something
which a complete solution to will put considerable stress on storm -
*all* our derived data lookups that trigger in web pages or API's need
to be expressable for a depend-on-storm solution to be feasible. Using
storm features where we can obviously makes sense.



How can we cache things today? What are the options?

I hope to have a live document for this on
https://dev.launchpad.net/Database/Performance - its got some basic
notes now, but I'll polish as things land in the codebase and we can
reference them.

The simplest cache is to just have a method, which looks at an
attribute and returns a stored result from that : prepopulate that
attribute in a single query, and you are done:

If on Person we write
def check_for_coc_signature():
    if safe_hasattr(self, '_coc_signer_cache'):
        return self._coc_signer_cache
    return self.is_ubuntu_coc_signer # magic lookup property

Then we can when we query for some people do:

def fill_in_fields(row):
   row[0]._coc_signer_cache = row[1] is not None
   return row[0]
resultset = Store.find((Person, CocSignings),
And(Person.id==CocSignings.person, CocSigning.active==True)):
resultset = DecoratedResultSet(resultset, fill_in_fields)

And looking at each Person in the resultset will answer
check_for_coc_signature without DB access.

This has some problems:
 - it doesn't cache if you don't prepopulate, so you can't tell if its
fast or not
 - we'd have to change call sites to move over to this, and nearly all
call sites will be happy with cached answers anyway.
 - invalidation isn't really defined

An easier approach is to use @cachedproperty
@cachedproperty('_coc_signer_cache')
def is_ubuntu_coc_signer(self):
   # normal lookup here

And the above prepopulation query will 'just work'.

I'm working on an automatic invalidation facility for our model
classes, so that they will invalidate when storm invalidates an
object.

For existing query-methods though, the former approach above is
probably most sensible, if tweaked to also cache its results: define a
known attribute and look in that.

For bonus points when you think something is only ever going to be
needed in the webapp, consider raising an exception if the cache is
missing: this way you won't ever accidentally start doing DB access in
inner loops.

It won't be as systemic and pervasive as what I think we will want to
do long term, but it would be just as effective ;)

_Rob