Still a few +translate timeouts

[Jeroen T. Vermeulen]

We're still getting a few timeouts on translation pages, because finding external suggestions is so expensive. Chances are these will go away when message-sharing migration completes (both because load goes down and because the TranslationMessage table gets smaller).

But if not, or if we simply want more performance, there's another thing we could do: cache the ids of POTemplates that external suggestions can come from. This simplifies the search query from an 8-way join to a more straightforward 3-way one. Thus it also reduces the number of rows touched (and so the number of locks it might be sensitive to, potential disk reads etc.). Updating the cache would be cheap enough that we could afford to update it the easy way: clear it out and rewrite. Even doing it as a temp table inside the request might help, given that we typically dig up the same information up to 300× (or more realistically, "several times") per page otherwise.

[Jeroen T. Vermeulen (jtv)]

Okay, maybe for this page we dig up those POTemplate ids only up to 50×. But then again that limit is in place mainly because this lookup is so expensive in the first place.

OOPS-1522EA217, OOPS-1522EA218, OOPS-1522ED184

[Jeroen T. Vermeulen (jtv)]

Our current database setup makes it profitable to do this on the slave store instead of the default store. That should help specifically for those people who make changes to the database (and that probably includes a lot of people who would want more speed on this page).

[Jeroen T. Vermeulen (jtv)]

We now look for suggestions on the slave store. If that still isn't enough, we can move on to other ideas such as caching the ids of templates that can produce external suggestions in a database table.

(Caching them client-side is now also possible thanks to memcached, but that would mean caching entire POTemplate objects, including their headers, and writing the list of ids again and again in each of the queries.)