POFile:+translate Timeout error trying to access the translation interface

[Alessandro Gasparini]

Hi to all,
I am writing to report a problem we encountered on the project that we are administering: logicaldoc.
There are approximately 2 (two) days that are no longer able to run the translations interface on logicaldocTrunk.
I am attaching a screenshot of the problem detected.
In practice, we are no longer able to access the maintenance interface of the single translation of branch Trunk.

To reproduce the problem simply try to access the page for translate german language of logicaldoc (Trunk)
http://translations.launchpad.net/logicaldoc/trunk/+pots/frontend/de/+translate

On the other branches, and packages the translation system works.

Note: on Saturday and Sunday we uploaded some single translation files in .po format (on Trunk)

Could you kindly check this ?
Thanks in advance for your effort :-)

OOPS-1752H707 OOPS-1755ED1033

[Данило Шеган (danilo)]

It seems fetching global suggestions is taking even longer than usual (probably related to PG 8.4 upgrade). Though, fetching POFiles takes a long time as well. As a stop-gap measure, we are disabling global suggestions so we've got more room for investigation (and so we don't block users while we do the investigation).

[Данило Шеган (danilo)]

Also, note that you can manually reduce the batch size for now to work-around the problem. I.e. append "?batch=3" to the URL to see only 3 messages for translation per page.

[Launchpad QA Bot (lpqabot)]

Fixed in stable r11800 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/11800>.

[Jeroen T. Vermeulen (jtv)]

stable r11800 is in db-stable r9929.

[Launchpad QA Bot (lpqabot)]

Fixed in stable r11807 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/11807>.

[Jeroen T. Vermeulen (jtv)]

Successfully Q/A'ed Danilo's branch as well as the first two of mine:

lp:~danilo/lp-production-configs/disable-global-suggestions (lp-production-configs 144)
lp:~jtv/launchpad/bug-662552-suggestive-potemplates (devel 11800)
lp:~jtv/launchpad/bug-662552-defer-potmsgset-filter (devel 11807)

[Launchpad QA Bot (lpqabot)]

Fixed in stable r11811 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/11811>.

[Данило Шеган (danilo)]

Marked as qa-ok so as not to block deployments.

[Jeroen T. Vermeulen (jtv)]

This branch was also good in Q/A:
lp:~jtv/launchpad/bug-662552-get-tm-or-dummy (devel 11811)

[Launchpad QA Bot (lpqabot)]

Fixed in stable r11818 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/11818>.

[Jeroen T. Vermeulen (jtv)]

Having trouble, probably with this branch:
lp:~jtv/launchpad/bug-662552-view-permissions (devel 11818)

Logged in as an unprivileged user, I can't enter suggestions where I should be able to.

[Jeroen T. Vermeulen (jtv)]

Phew. It's okay. I was logged in as an unprivileged user with the licensing agreement declined. The branch is OK.

[Launchpad QA Bot (lpqabot)]

Fixed in stable r11818 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/11818>.

[Данило Шеган (danilo)]

Note that these fixes are all incremental, and don't really address the problem. Problem is only worked around for now by disabling global suggestions.

The problem seems to come from the pofile derivation code which gets executed too many times (TranslationMessage.getOnePOFile, likely through ensurePOFile call): see eg. OOPS-1768S265 (batch size of 2, 31 queries for a total of 5 external suggestions, each taking 80-160ms). There're also repeated POTMsgSet fetching calls, though they are two orders of magnitude faster so I'd leave fixing that for later.

[Данило Шеган (danilo)]

Ok, I think I figured where the problem is: we call ensureBrowserPOFile for every plural form for each of imported and shared translations. For those messages, we don't even have to figure it out, because we can consider them "local" to this PO file, so just using self.pofile in the TM view is fine. I'll be testing the hypotheses on staging first.

[Данило Шеган (danilo)]

This's apparently not enough. More investigation needed.

[Данило Шеган (danilo)]

Ok, I've managed to speed up the relevant query roughly a 100x by knowing more about the data model than postgres can :) I'll just land a simplistic fix which doesn't reduce the query count.

[Launchpad QA Bot (lpqabot)]

Fixed in stable r11911 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/11911>.

[Данило Шеган (danilo)]

We need to re-enable global suggestions before we can be certain this works. Here's the MP for that: https://code.launchpad.net/~danilo/lp-production-configs/reenable-global-suggestions/+merge/40943