Launchpad itself

POFile:+translate timeouts

Bug #730391 reported by Robert Collins on 2011-03-07

This bug affects 1 person

Affects		Status	Importance	Assigned to	Milestone
	Launchpad itself	Fix Released	Critical	Robert Collins	Launchpad itself 11.03

Bug Description

19 SELECT TranslationMessage.comment, TranslationMessage.date_created, TranslationMessage.date_revie ... gstr3, -$INT), COALESCE(msgstr4, -$INT), COALESCE(msgstr5, -$INT), date_created DESC ) LIMIT $INT:
   GET: 19 Robots: 0 Local: 19
      4 https://translations.launchpad.net/openobject-addons/6.0/+pots/share/ca/+translate (POFile:+translate)
       OOPS-1891B1975, OOPS-1891C2210, OOPS-1891E2220, OOPS-1891H2179
      2 https://translations.edge.launchpad.net/calibre/trunk/+pots/calibre/ca/+translate (POFile:+translate)
       OOPS-1891EC998, OOPS-1891ED951
      2 https://translations.launchpad.net/habari/trunk/+pots/habari/de/+translate (POFile:+translate)
       OOPS-1891F2188, OOPS-1891K2198

Two moderate-cost high-repeat queries:
5 10 7094 709 6385 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 = $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
6 10 4662 466 4196 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 (is_current_ubuntu IS TRUE
            OR is_current_upstream IS TRUE)
       AND TranslationMessage.LANGUAGE = $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

See original description

Tags:

Related branches

lp:~lifeless/launchpad/bug-730391

Merged into lp:launchpad at revision 12549

Stuart Bishop (community): Approve on 2011-03-07

Revision history for this message

Robert Collins (lifeless) wrote on 2011-03-07:

Download full text (9.8 KiB)

Once particular query was 2 seconds long:

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 (is_current_ubuntu IS TRUE
            OR is_current_upstream IS TRUE)
       AND TranslationMessage.LANGUAGE = 387
       AND TranslationMessage.potmsgset <> 9885700
       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 = 5033)
     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

cold explain analyse:

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=343660.46..345771.54 rows=200 width=76) (actual t...

Once particular query was 2 seconds long:

cold explain analyse:
                                                                                                                                                           
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=343660.46..345771.54 rows=200 width=76) (actual time=15636.356..15742.182 rows=8 loops=1)
   ->  Nested Loop  (cost=343660.46..345771.54 rows=200 width=76) (actual time=15636.354..15742.171 rows=8 loops=1)
         ->  HashAggregate  (cost=343660.46..343662.46 rows=200 width=4) (actual time=15571.462..15571.472 rows=8 loops=1)
               ->  Unique  (cost=302143.55..340892.66 rows=221424 width=36) (actual time=15571.158..15571.434 rows=8 loops=1)
                     ->  Sort  (cost=302143.55..307679.14 rows=2214235 width=36) (actual time=15571.156..15571.230 rows=274 loops=1)
                           Sort Key: (COALESCE(public.translationmessage.msgstr0, (-1))), (COALESCE(public.translationmessage.msgstr1, (-1))), (COALESCE(public.translationmessage.msgstr2, (-1))), (COALESCE(public.translationmessage.msgstr3, (-1))), (COALESCE(public.translationmessage.msgstr4, (-1))), (COALESCE(public.translationmessage.msgstr5, (-1))), public.translationmessage.date_created
                           Sort Method:  quicksort  Memory: 46kB
                           ->  Nested Loop  (cost=9484.42..16884.15 rows=2214235 width=36) (actual time=9815.541..15569.326 rows=274 loops=1)
                                 ->  HashAggregate  (cost=9484.42..9489.37 rows=495 width=8) (actual time=9671.184..9672.093 rows=401 loops=1)
                                       ->  Nested Loop  (cost=0.00..9479.99 rows=1772 width=8) (actual time=0.058..9669.608 rows=684 loops=1)
                                             ->  Nested Loop  (cost=0.00..8980.88 rows=1772 width=12) (actual time=0.045..9435.732 rows=921 loops=1)
                                                   ->  Index Scan using potmsgset_primemsgid_idx on potmsgset  (cost=0.00..1480.55 rows=495 width=4) (actual time=0.024..2747.430 rows=609 loops=1)
                                                         Index Cond: (msgid_singular = 5033)
                                                   ->  Index Scan using translationtemplateitem__potmsgset__idx on translationtemplateitem  (cost=0.00..15.10 rows=4 width=8) (actual time=8.109..10.978 rows=2 loops=609)
                                                         Index Cond: (translationtemplateitem.potmsgset = potmsgset.id)
                                             ->  Index Scan using suggestivepotemplate_pkey on suggestivepotemplate  (cost=0.00..0.27 rows=1 width=4) (actual time=0.251..0.252 rows=1 loops=921)
                                                   Index Cond: (suggestivepotemplate.potemplate = translationtemplateitem.potemplate)
                                 ->  Index Scan using translationmessage__potmsgset__language__idx on translationmessage  (cost=0.00..14.91 rows=2 width=40) (actual time=14.398..14.703 rows=1 loops=401)
                                       Index Cond: ((public.translationmessage.potmsgset = translationtemplateitem.potmsgset) AND (public.translationmessage.language = 387))
                                       Filter: (((public.translationmessage.is_current_ubuntu IS TRUE) OR (public.translationmessage.is_current_upstream IS TRUE)) AND (public.translationmessage.potmsgset <> 9885700))
         ->  Index Scan using translationmessage_pkey on translationmessage  (cost=0.00..10.53 rows=1 width=76) (actual time=21.331..21.332 rows=1 loops=8)
               Index Cond: (public.translationmessage.id = public.translationmessage.id)
 Total runtime: 15742.620 ms
(23 rows)

hot:
 Limit  (cost=343660.46..345771.54 rows=200 width=76) (actual time=25.492..25.618 rows=8 loops=1)
   ->  Nested Loop  (cost=343660.46..345771.54 rows=200 width=76) (actual time=25.490..25.611 rows=8 loops=1)
         ->  HashAggregate  (cost=343660.46..343662.46 rows=200 width=4) (actual time=25.465..25.469 rows=8 loops=1)
               ->  Unique  (cost=302143.55..340892.66 rows=221424 width=36) (actual time=25.170..25.441 rows=8 loops=1)
                     ->  Sort  (cost=302143.55..307679.14 rows=2214235 width=36) (actual time=25.167..25.233 rows=274 loops=1)
                           Sort Key: (COALESCE(public.translationmessage.msgstr0, (-1))), (COALESCE(public.translationmessage.msgstr1, (-1))), (COALESCE(public.translationmessage.msgstr2, (-1))), (COALESCE(public.translationmessage.msgstr3, (-1))), (COALESCE(public.translationmessage.msgstr4, (-1))), (COALESCE(public.translationmessage.msgstr5, (-1))), public.translationmessage.date_created
                           Sort Method:  quicksort  Memory: 46kB
                           ->  Nested Loop  (cost=9484.42..16884.15 rows=2214235 width=36) (actual time=16.926..23.901 rows=274 loops=1)
                                 ->  HashAggregate  (cost=9484.42..9489.37 rows=495 width=8) (actual time=16.832..17.073 rows=401 loops=1)
                                       ->  Nested Loop  (cost=0.00..9479.99 rows=1772 width=8) (actual time=0.066..16.102 rows=684 loops=1)
                                             ->  Nested Loop  (cost=0.00..8980.88 rows=1772 width=12) (actual time=0.052..10.094 rows=921 loops=1)
                                                   ->  Index Scan using potmsgset_primemsgid_idx on potmsgset  (cost=0.00..1480.55 rows=495 width=4) (actual time=0.026..1.798 rows=609 loops=1)
                                                         Index Cond: (msgid_singular = 5033)
                                                   ->  Index Scan using translationtemplateitem__potmsgset__idx on translationtemplateitem  (cost=0.00..15.10 rows=4 width=8) (actual time=0.009..0.012 rows=2 loops=609)
                                                         Index Cond: (translationtemplateitem.potmsgset = potmsgset.id)
                                             ->  Index Scan using suggestivepotemplate_pkey on suggestivepotemplate  (cost=0.00..0.27 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=921)
                                                   Index Cond: (suggestivepotemplate.potemplate = translationtemplateitem.potemplate)
                                 ->  Index Scan using translationmessage__potmsgset__language__idx on translationmessage  (cost=0.00..14.91 rows=2 width=40) (actual time=0.015..0.015 rows=1 loops=401)
                                       Index Cond: ((public.translationmessage.potmsgset = translationtemplateitem.potmsgset) AND (public.translationmessage.language = 387))
                                       Filter: (((public.translationmessage.is_current_ubuntu IS TRUE) OR (public.translationmessage.is_current_upstream IS TRUE)) AND (public.translationmessage.potmsgset <> 9885700))
         ->  Index Scan using translationmessage_pkey on translationmessage  (cost=0.00..10.53 rows=1 width=76) (actual time=0.014..0.014 rows=1 loops=8)
               Index Cond: (public.translationmessage.id = public.translationmessage.id)
 Total runtime: 25.888 ms

description:

updated

Revision history for this message

Launchpad QA Bot (lpqabot) wrote on 2011-03-08:

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

Changed in launchpad:
assignee:	nobody → Robert Collins (lifeless)
milestone:	none → 11.03
tags:	added: qa-needstesting
Changed in launchpad:
status:	Triaged → Fix Committed

Revision history for this message

Robert Collins (lifeless) wrote on 2011-03-08:

6.9 seconds cold on qastaging, 2.73 seconds hot.

tags:

added: qa-ok
removed: qa-needstesting

Curtis Hovey (sinzui) on 2011-03-10

Changed in launchpad:
status:	Fix Committed → Fix Released

Report a bug

This report contains Public information

Everyone can see this information.

You are

Subscribing...

Edit bug mail

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.