POFile:+translate timeouts

Bug #734642 reported by Robert Collins on 2011-03-14
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Launchpad itself
Critical
Robert Collins

Bug Description

  8 SELECT TranslationMessage.comment, TranslationMessage.date_created, TranslationMessage.date_revie ... gstr3, -$INT), COALESCE(msgstr4, -$INT), COALESCE(msgstr5, -$INT), date_created DESC ) LIMIT $INT:
   GET: 8 Robots: 0 Local: 8
      5 https://translations.launchpad.net/ubuntu/natty/+source/firefox/+pots/firefox/bs/+translate (POFile:+translate)
       OOPS-1898C2088, OOPS-1898D1863, OOPS-1898G1843, OOPS-1898J1463, OOPS-1898L1817
      2 https://translations.launchpad.net/ubuntu/natty/+source/packagekit/+pots/packagekit/bs/+translate (POFile:+translate)
       OOPS-1898D776, OOPS-1898M759
      1 https://translations.launchpad.net/ubuntu/maverick/+source/vorbis-tools/+pots/vorbis-tools/fr/+translate (POFile:+translate)
       OOPS-1898H228

from the H228 oops:

Branch: launchpad-rev-12568
Revno: 12568
SQL time: 11916 ms
Non-sql time: 1291 ms
Total time: 13207 ms
Statement Count: 264

10 6 10902 1817 9085 SQL-launchpad-main-master
SELECT TranslationMessage.COMMENT, TranslationMessage.date_created,
                                   TranslationMessage.date_reviewed,
                                   TranslationMessage.id,
                                   TranslationMessage.is_current_ubuntu,
                                   TranslationMessage.is_current_upstream,
                                   TranslationMessage.LANGUAGE, TranslationMessage.msgstr0,
                                                                TranslationMessage.msgstr1,
                                                                TranslationMessage.msgstr2,
                                                                TranslationMessage.msgstr3,
                                                                TranslationMessage.msgstr4,
                                                                TranslationMessage.msgstr5,
                                                                TranslationMessage.origin,
                                                                TranslationMessage.potemplate,
                                                                TranslationMessage.potmsgset,
                                                                TranslationMessage.reviewer,
                                                                TranslationMessage.submitter,
                                                                TranslationMessage.validation_status,
                                                                TranslationMessage.was_obsolete_in_last_import
FROM TranslationMessage
WHERE TranslationMessage.id IN
    (SELECT DISTINCT ON (COALESCE(msgstr0, -$INT), COALESCE(msgstr1, -$INT), COALESCE(msgstr2, -$INT), COALESCE(msgstr3, -$INT), COALESCE(msgstr4, -$INT), COALESCE(msgstr5, -$INT)) TranslationMessage.id
     FROM TranslationMessage
     WHERE (TranslationMessage.LANGUAGE IN ($INT))
       AND TranslationMessage.potmsgset <> $INT
       AND potmsgset IN
         (SELECT POTMsgSet.id
          FROM POTMsgSet
          JOIN TranslationTemplateItem ON TranslationTemplateItem.potmsgset = POTMsgSet.id
          JOIN SuggestivePOTemplate ON TranslationTemplateItem.potemplate = SuggestivePOTemplate.potemplate
          WHERE msgid_singular = $INT)
     ORDER BY COALESCE(msgstr0, -$INT), COALESCE(msgstr1, -$INT), COALESCE(msgstr2, -$INT), COALESCE(msgstr3, -$INT), COALESCE(msgstr4, -$INT), COALESCE(msgstr5, -$INT), date_created DESC)LIMIT $INT

Related branches

description: updated
Robert Collins (lifeless) wrote :
Download full text (5.7 KiB)

SELECT TranslationMessage.COMMENT, TranslationMessage.date_created,
                                   TranslationMessage.date_reviewed,
                                   TranslationMessage.id,
                                   TranslationMessage.is_current_ubuntu,
                                   TranslationMessage.is_current_upstream,
                                   TranslationMessage.LANGUAGE, TranslationMessage.msgstr0,
                                                                TranslationMessage.msgstr1,
                                                                TranslationMessage.msgstr2,
                                                                TranslationMessage.msgstr3,
                                                                TranslationMessage.msgstr4,
                                                                TranslationMessage.msgstr5,
                                                                TranslationMessage.origin,
                                                                TranslationMessage.potemplate,
                                                                TranslationMessage.potmsgset,
                                                                TranslationMessage.reviewer,
                                                                TranslationMessage.submitter,
                                                                TranslationMessage.validation_status,
                                                                TranslationMessage.was_obsolete_in_last_import
FROM TranslationMessage
WHERE TranslationMessage.id IN
    (SELECT DISTINCT ON (COALESCE(msgstr0, -1), COALESCE(msgstr1, -1), COALESCE(msgstr2, -1), COALESCE(msgstr3, -1), COALESCE(msgstr4, -1), COALESCE(msgstr5, -1)) TranslationMessage.id
     FROM TranslationMessage
     WHERE (TranslationMessage.LANGUAGE IN (132))
       AND TranslationMessage.potmsgset <> 8171049
       AND potmsgset IN
         (SELECT POTMsgSet.id
          FROM POTMsgSet
          JOIN TranslationTemplateItem ON TranslationTemplateItem.potmsgset = POTMsgSet.id
          JOIN SuggestivePOTemplate ON TranslationTemplateItem.potemplate = SuggestivePOTemplate.potemplate
          WHERE msgid_singular = 347523)
     ORDER BY COALESCE(msgstr0, -1), COALESCE(msgstr1, -1), COALESCE(msgstr2, -1), COALESCE(msgstr3, -1), COALESCE(msgstr4, -1), COALESCE(msgstr5, -1), date_created DESC)LIMIT 2001

 Limit (cost=294364.63..296495.59 rows=200 width=76) (actual time=994.854..1063.958 rows=4 loops=1)
   -> Nested Loop (cost=294364.63..296495.59 rows=200 width=76) (actual time=994.852..1063.954 rows=4 loops=1)
         -> HashAggregate (cost=294364.63..294366.63 rows=200 width=4) (actual time=935.851..935.857 rows=4 loops=1)
               -> Unique (cost=256804.40..291860.61 rows=200322 width=36) (actual time=926.269..926.276 rows=4 loops=1)
                     -> Sort (cost=256804.40..261812.43 rows=2003212 width=36) (actual time=926.268..926.270 rows=6 loops=1)
                           Sort Key: (COALESCE(public.translationmessage.msgstr0, (-1))), (COALESCE(public.translationmessage.msgstr1, (-1))), (COALESCE(public.translation...

Read more...

Robert Collins (lifeless) wrote :
Download full text (5.5 KiB)

this is substantially faster than the double nested query:
with pots as (SELECT POTMsgSet.id
          FROM POTMsgSet
          JOIN TranslationTemplateItem ON TranslationTemplateItem.potmsgset = POTMsgSet.id
          JOIN SuggestivePOTemplate ON TranslationTemplateItem.potemplate = SuggestivePOTemplate.potemplate
          WHERE msgid_singular = 347523)
 SELECT TranslationMessage.COMMENT, TranslationMessage.date_created,
                                   TranslationMessage.date_reviewed,
                                   TranslationMessage.id,
                                   TranslationMessage.is_current_ubuntu,
                                   TranslationMessage.is_current_upstream,
                                   TranslationMessage.LANGUAGE, TranslationMessage.msgstr0,
                                                                TranslationMessage.msgstr1,
                                                                TranslationMessage.msgstr2,
                                                                TranslationMessage.msgstr3,
                                                                TranslationMessage.msgstr4,
                                                                TranslationMessage.msgstr5,
                                                                TranslationMessage.origin,
                                                                TranslationMessage.potemplate,
                                                                TranslationMessage.potmsgset,
                                                                TranslationMessage.reviewer,
                                                                TranslationMessage.submitter,
                                                                TranslationMessage.validation_status,
                                                                TranslationMessage.was_obsolete_in_last_import
FROM TranslationMessage
WHERE TranslationMessage.id IN
    (SELECT DISTINCT ON (COALESCE(msgstr0, -1), COALESCE(msgstr1, -1), COALESCE(msgstr2, -1), COALESCE(msgstr3, -1), COALESCE(msgstr4, -1), COALESCE(msgstr5, -1)) TranslationMessage.id
     FROM TranslationMessage
     WHERE (TranslationMessage.LANGUAGE IN (132))
       AND TranslationMessage.potmsgset <> 8171049
       AND potmsgset IN (select id from pots)
     ORDER BY COALESCE(msgstr0, -1), COALESCE(msgstr1, -1), COALESCE(msgstr2, -1), COALESCE(msgstr3, -1), COALESCE(msgstr4, -1), COALESCE(msgstr5, -1), date_created DESC)LIMIT 2001

 Limit (cost=372.84..697.30 rows=27 width=76) (actual time=0.814..0.872 rows=4 loops=1)
   CTE pots
     -> Nested Loop (cost=0.00..101.45 rows=18 width=4) (actual time=0.057..0.513 rows=24 loops=1)
           -> Nested Loop (cost=0.00..96.38 rows=18 width=8) (actual time=0.044..0.268 rows=25 loops=1)
                 -> Index Scan using potmsgset_primemsgid_idx on potmsgset (cost=0.00..18.54 rows=5 width=4) (actual time=0.021..0.047 rows=9 loops=1)
                       Index Cond: (msgid_singular = 347523)
                 -> Index Scan using translationtemplateitem__potmsgset__idx on translationtemplateitem (cost=0.00..15.52 rows=4 width=8) (actu...

Read more...

Robert Collins (lifeless) wrote :
Download full text (5.6 KiB)

SELECT TranslationMessage.COMMENT, TranslationMessage.date_created,
                                   TranslationMessage.date_reviewed,
                                   TranslationMessage.id,
                                   TranslationMessage.is_current_ubuntu,
                                   TranslationMessage.is_current_upstream,
                                   TranslationMessage.LANGUAGE, TranslationMessage.msgstr0,
                                                                TranslationMessage.msgstr1,
                                                                TranslationMessage.msgstr2,
                                                                TranslationMessage.msgstr3,
                                                                TranslationMessage.msgstr4,
                                                                TranslationMessage.msgstr5,
                                                                TranslationMessage.origin,
                                                                TranslationMessage.potemplate,
                                                                TranslationMessage.potmsgset,
                                                                TranslationMessage.reviewer,
                                                                TranslationMessage.submitter,
                                                                TranslationMessage.validation_status,
                                                                TranslationMessage.was_obsolete_in_last_import
FROM TranslationMessage
WHERE TranslationMessage.id IN
    (SELECT DISTINCT ON (COALESCE(msgstr0, -1), COALESCE(msgstr1, -1), COALESCE(msgstr2, -1), COALESCE(msgstr3, -1), COALESCE(msgstr4, -1), COALESCE(msgstr5, -1)) TranslationMessage.id
     FROM TranslationMessage
     WHERE (TranslationMessage.LANGUAGE IN (132))
       AND TranslationMessage.potmsgset <> 8171049
       AND potmsgset IN
         (SELECT distinct POTMsgSet.id
          FROM POTMsgSet
          JOIN TranslationTemplateItem ON TranslationTemplateItem.potmsgset = POTMsgSet.id
          JOIN SuggestivePOTemplate ON TranslationTemplateItem.potemplate = SuggestivePOTemplate.potemplate
          WHERE msgid_singular = 347523)
     ORDER BY COALESCE(msgstr0, -1), COALESCE(msgstr1, -1), COALESCE(msgstr2, -1), COALESCE(msgstr3, -1), COALESCE(msgstr4, -1), COALESCE(msgstr5, -1), date_created DESC)LIMIT 2001;

(note the distinct in the innermost select)
is also much cheaper:

 Limit (cost=176.77..272.91 rows=8 width=76) (actual time=0.743..0.796 rows=4 loops=1)
   -> Nested Loop (cost=176.77..272.91 rows=8 width=76) (actual time=0.742..0.793 rows=4 loops=1)
         -> HashAggregate (cost=176.77..176.85 rows=8 width=4) (actual time=0.720..0.723 rows=4 loops=1)
               -> Unique (cost=176.53..176.67 rows=8 width=36) (actual time=0.694..0.710 rows=4 loops=1)
                     -> Sort (cost=176.53..176.55 rows=8 width=36) (actual time=0.692..0.700 rows=6 loops=1)
                           Sort Key: (COALESCE(public.translationmessage.msgstr0, (-1))), (COALESCE(public.translationmessage.msgstr1, (-1))), (COALESCE(public.tr...

Read more...

Robert Collins (lifeless) wrote :

using a regular join gets a 225 estimated cost and 300ms runtime:
SELECT DISTINCT ON (COALESCE(msgstr0, -1), COALESCE(msgstr1, -1), COALESCE(msgstr2, -1), COALESCE(msgstr3, -1), COALESCE(msgstr4, -1), COALESCE(msgstr5, -1)) TranslationMessage.id
     FROM TranslationMessage join (potmsgset JOIN TranslationTemplateItem ON TranslationTemplateItem.potmsgset = POTMsgSet.id
          JOIN SuggestivePOTemplate ON TranslationTemplateItem.potemplate = SuggestivePOTemplate.potemplate) on TranslationMessage.potmsgset=POTMsgSet.id
     WHERE (TranslationMessage.LANGUAGE IN (132))
       AND TranslationMessage.potmsgset <> 8171049
       AND msgid_singular = 347523
     ORDER BY COALESCE(msgstr0, -1), COALESCE(msgstr1, -1), COALESCE(msgstr2, -1), COALESCE(msgstr3, -1), COALESCE(msgstr4, -1), COALESCE(msgstr5, -1), date_created DESC LIMIT 2001;

Robert Collins (lifeless) wrote :
Download full text (5.2 KiB)

This has the best plan and performance I've been able to put together: 372 startup and completion estimate, 10ms actual runtime.

 explain analyze with pots as (SELECT POTMsgSet.id
          FROM POTMsgSet
          JOIN TranslationTemplateItem ON TranslationTemplateItem.potmsgset = POTMsgSet.id
          JOIN SuggestivePOTemplate ON TranslationTemplateItem.potemplate = SuggestivePOTemplate.potemplate
          WHERE msgid_singular = 347523 and potmsgset.id<>8171049)
SELECT DISTINCT ON (COALESCE(msgstr0, -1), COALESCE(msgstr1, -1), COALESCE(msgstr2, -1), COALESCE(msgstr3, -1), COALESCE(msgstr4, -1), COALESCE(msgstr5, -1)) TranslationMessage.id, TranslationMessage.COMMENT, TranslationMessage.date_created,
                                   TranslationMessage.date_reviewed,TranslationMessage.is_current_ubuntu,
                                   TranslationMessage.is_current_upstream,
                                   TranslationMessage.LANGUAGE, TranslationMessage.msgstr0,
                                                                TranslationMessage.msgstr1,
                                                                TranslationMessage.msgstr2,
                                                                TranslationMessage.msgstr3,
                                                                TranslationMessage.msgstr4,
                                                                TranslationMessage.msgstr5,
                                                                TranslationMessage.origin,
                                                                TranslationMessage.potemplate,
                                                                TranslationMessage.potmsgset,
                                                                TranslationMessage.reviewer,
                                                                TranslationMessage.submitter,
                                                                TranslationMessage.validation_status,
                                                                TranslationMessage.was_obsolete_in_last_import
     FROM TranslationMessage join pots on pots.id=translationmessage.potmsgset and translationmessage.language in (132)
 ORDER BY COALESCE(msgstr0, -1), COALESCE(msgstr1, -1), COALESCE(msgstr2, -1), COALESCE(msgstr3, -1), COALESCE(msgstr4, -1), COALESCE(msgstr5, -1), date_created DESC LIMIT 2001;
                                                                                                                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (cost=371.72..372.20 rows=27 width=76) (actual time=1.075..1.121 rows=4 loops=1)
   CTE pots
     ...

Read more...

Robert Collins (lifeless) wrote :

On prod, cold: Limit (cost=241.77..242.26 rows=28 width=76) (actual time=14.100..14.162 rows=4 loops=1)
and then
20:01 < stub> 7ms on prod_2
20:02 < stub> 70ms on the master
20:02 < stub> 1.4ms the second run

So, this looks win to me.

Robert Collins (lifeless) wrote :

Its past EOD for me, but if noone picks this up and does it over night I'll implement tomorrow am.

Do note that the original version returned 200 rows, while the final one you have returns 27 (and innermost distinct shouldn't change that, since that was for potmsgset in (...)).

Also, your "pots" query seems to contain unused JOINs:

  JOIN TranslationTemplateItem ON TranslationTemplateItem.potmsgset = POTMsgSet.id
          JOIN SuggestivePOTemplate ON TranslationTemplateItem.potemplate = SuggestivePOTemplate.potemplate

I haven't looked closely otherwise, but unless Gary has added WITH support to Storm in the last week (he did say he wanted to :), then tough luck with that too.

Robert Collins (lifeless) wrote :

Huh, the original one returns 4 rows (on qastaging):
ids 206643309 215539313 192242823 215770253
and the doctored one returns
192242823 215539313 206643309 215770253

- the same ids.

I added with support to storm last week, it made component bug searches about 30 times faster.

The joins will constrain the rows to ones where the potmsg has a suggestion (because they are inner joins). I'm taking it on faith that they are needed because they were present in the original query I refactored.

Robert Collins (lifeless) wrote :

(Oh - rows - I think you're reading the explain *estimated* row count, not the *actual* row count). Here is the original queries outer line of the plan:
 Limit (cost=294364.63..296495.59 rows=200 width=76) (actual time=994.854..1063.958 rows=4 loops=1)

note the actual row count is 4, only the estimate is 200.

Launchpad QA Bot (lpqabot) wrote :
Changed in launchpad:
assignee: nobody → Robert Collins (lifeless)
milestone: none → 11.04
tags: added: qa-needstesting
Changed in launchpad:
status: Triaged → Fix Committed
Robert Collins (lifeless) wrote :

All three pages render on qastaging in about 30% of the time on production; the packagekit one timed out once cold, then worked - OOPS-1900QS38. I think we're good to go on this, it will give us more headroom on partly-cold pages even if it doesn't solve the entire issue.

tags: added: qa-ok
removed: qa-needstesting
Changed in launchpad:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers