The proposal to merge lp:~danilo/launchpad/bug-534203 into lp:launchpad/db-devel has been updated.
Description changed to: = Bug 534203 = Doing a query for all translationmessages by a certain person on a particular pofile (template, language) at the moment makes use of tm__language__submitter__idx index on translationmessage table. However, for particularly active submitters who have done many translations across the board, this means that there will be plenty of rows matching a language and a submitter (not surprisingly, one submitter usually takes care only of one language), thus we'll be doing filtering on potmsgset of a large set of rows. Simply dropping the index improves the performance hugely, since we then start using translationmessage__potmsgset__language__idx, thus narrowing down to a smaller set before doing the filtering on submitter. In practice, for contributors with a large number of submissions, this has brought down the query time roughly 10x in testing on staging (and even more when the caches are cold: from 30s to 1-2s; with warm caches, from ~300-700ms to ~35-65ms). In theory, this might limit our best case performance for smaller contributors which have only worked on a single project, but considering the upper bound for that is the potemplate size (or 45k rows which we have in only one template, and other than perhaps 5 other templates, all the rest are sub-10k rows), our worst-case performance should be bound by a more reasonable time limit instead. This can be rolled out in nodowntime as well, or applied directly to running servers. I've also refactored the POFile.getTranslationsFilteredBy to use the Storm syntax, and refactored the unit test to split it into several by the behaviour being tested. = Tests = bin/test -cvvt getTranslationsFilteredBy = Demo & QA = https://translations.(qa)staging.launchpad.net/ubuntu/hardy/+source/evolution-data-server/+pots/evolution-data-server-2.22/it/+filter?person=elle.uca https://translations.(qa)staging.launchpad.net/ubuntu/hardy/+source/evolution-data-server/+pots/evolution-data-server-2.22/sr/+filter?person=danilo = Launchpad lint = Checking for conflicts and issues in changed files. Linting changed files: database/schema/patch-2208-99-1.sql lib/lp/translations/model/pofile.py lib/lp/translations/tests/test_pofile.py For more details, see: https://code.launchpad.net/~danilo/launchpad/bug-534203/+merge/65467 -- https://code.launchpad.net/~danilo/launchpad/bug-534203/+merge/65467 Your team Launchpad code reviewers is requested to review the proposed merge of lp:~danilo/launchpad/bug-534203 into lp:launchpad/db-devel. _______________________________________________ Mailing list: https://launchpad.net/~launchpad-reviewers Post to : [email protected] Unsubscribe : https://launchpad.net/~launchpad-reviewers More help : https://help.launchpad.net/ListHelp
