launchpad-dev team mailing list archive

[Robert Collins <robertc@xxxxxxxxxxxxxxxxx>]

We are currently dealing with bug 800485 where validation of
sourcepackagenames has gone from 80ms to 1800ms(hot) or minutes
(cold).

This was caused when a patch changed a non-storm query to a storm
query *and* added a single join table in (rather than the substituted
archive ids).

Most of our queries are now tuned; postgresql consistently chooses bad
plans on the 'obvious' way to write things for many of our very large,
or very skewed data sets.

As a result, whenever you change a query on a big table - where big
means > 20K rows - its important to try and exercise it on qastaging.

If the thing you are testing times out, its *vital* that the timeout
be positively identified as a pre-existing condition before assuming
qastaging is slow[1].

In this particular case, the patch was qa'd, but an existing timeout
bug was assumed to be the cause of qa timeouts: we should have grabbed
the oops and positively id'd the timeout as the existing bug - that
would have told us about the regression and let us avoid the crisis.

1) how slow is qastaging? Its not, not really. It has enough memory on
the DB server to page into hot cache the working set for any one page
in the system: you may need to try a lot of times to seed the cache,
but *everything* *can* work on qastaging.


-Rob