Product:+index timeouts

Bug #739051 reported by Robert Collins
34
This bug affects 3 people
Affects Status Importance Assigned to Milestone
Launchpad itself
Critical
William Grant

Bug Description

  6 SELECT DISTINCT DistributionSourcePackageCache.archive, DistributionSourcePackageCache.binpkgdesc ... istroseries = $INT ORDER BY rank DESC, DistributionSourcePackageCache.name LIMIT $INT OFFSET $INT:
   GET: 6 Robots: 0 Local: 0
      3 https://launchpad.net/ubuntu-it/+index (Product:+index)
       OOPS-1926E737, OOPS-1926G741, OOPS-1926G742
      1 https://launchpad.net/glib/+index (Product:+index)
       OOPS-1926H1059
      1 https://launchpad.net/indicator-applet/+index (Product:+index)
       OOPS-1926B1590
   [1 other URLs]

  6 SELECT ProductReleaseFile.date_uploaded, ProductReleaseFile.description, ProductReleaseFile.filet ... .productrelease IN ($INT ... $INT) AND ($INT=$INT) ORDER BY ProductReleaseFile.date_uploaded DESC:
   GET: 6 Robots: 0 Local: 6
      6 https://launchpad.net/rhythmbox/+index (Product:+index)
       OOPS-1926C1169, OOPS-1926E1448, OOPS-1926G1357, OOPS-1926H1436, OOPS-1926H1439

slow SQL looking guessing at packaging
11706ms SQL-launchpad-main-slave SELECT DISTINCT DistributionSourcePackageCache.archive, DistributionSourcePackageCache.binpkgdescriptions, DistributionSourcePackageCache.binpkgnames, DistributionSourcePackageCache.binpkgsummaries, DistributionSourcePackageCache.changelog, DistributionSourcePackageCache.distribution, DistributionSourcePackageCache.id, DistributionSourcePackageCache.name, DistributionSourcePackageCache.sourcepackagename, SourcePackageName.id, SourcePackageName.name, rank(fti, ftq('indicator-applet')) AS rank
FROM DistributionSourcePackageCache
JOIN SourcePackageName ON DistributionSourcePackageCache.sourcepackagename = SourcePackageName.id
JOIN SourcePackageRelease ON SourcePackageRelease.sourcepackagename = SourcePackageName.id
JOIN SourcePackagePublishingHistory ON SourcePackagePublishingHistory.sourcepackagerelease = SourcePackageRelease.id
WHERE DistributionSourcePackageCache.distribution = 1
  AND DistributionSourcePackageCache.archive IN (1,
                                                 534)
  AND (DistributionSourcePackageCache.fti @@ ftq('indicator-applet')
       OR DistributionSourcePackageCache.name ILIKE '%' || 'indicator-applet' || '%')
  AND NOT EXISTS
    (SELECT 1
     FROM Packaging
     WHERE Packaging.sourcepackagename = SourcePackageName.id)
  AND SourcePackagePublishingHistory.distroseries = 106
ORDER BY rank DESC, DistributionSourcePackageCache.name LIMIT 8
OFFSET 0

Related branches

description: updated
description: updated
Revision history for this message
Robert Collins (lifeless) wrote :

 select count(*) from DistributionSourcePackageCache;
 count
-------
 96686

Revision history for this message
Robert Collins (lifeless) wrote :
Download full text (7.8 KiB)

cold
 Limit (cost=17832.17..17832.43 rows=8 width=1255) (actual time=4598.463..4599.105 rows=3 loops=1)
   -> Unique (cost=17832.17..17836.39 rows=130 width=1255) (actual time=4598.461..4599.099 rows=3 loops=1)
         -> Sort (cost=17832.17..17832.49 rows=130 width=1255) (actual time=4598.458..4598.462 rows=21 loops=1)
               Sort Key: (rank((distributionsourcepackagecache.fti)::tsvector, '''indic'' & ''applet'' | ''indicatorapplet'''::tsquery)), distributionsourcepackagecache.name, distributionsourcepackagecache.archive, distributionsourcepackagecache.binpkgdescriptions, distributionsourcepackagecache.binpkgnames, distributionsourcepackagecache.binpkgsummaries, distributionsourcepackagecache.changelog, distributionsourcepackagecache.id, distributionsourcepackagecache.sourcepackagename, sourcepackagename.name
               Sort Method: quicksort Memory: 62kB
               -> Nested Loop (cost=345.42..17827.61 rows=130 width=1255) (actual time=978.954..4597.543 rows=21 loops=1)
                     -> Nested Loop (cost=345.42..17448.86 rows=749 width=1259) (actual time=351.189..4244.409 rows=244 loops=1)
                           -> Nested Loop Anti Join (cost=345.42..16827.43 rows=9 width=1255) (actual time=345.667..4207.215 rows=7 loops=1)
                                 -> Nested Loop (cost=345.42..16826.52 rows=10 width=1255) (actual time=30.426..4120.365 rows=28 loops=1)
                                       -> Bitmap Heap Scan on distributionsourcepackagecache (cost=345.42..16763.69 rows=10 width=1237) (actual time=25.031..4036.066 rows=28 loops=1)
                                             Recheck Cond: (archive = ANY ('{1,534}'::integer[]))
                                             Filter: ((distribution = 1) AND (((fti)::tsvector @@ '''indic'' & ''applet'' | ''indicatorapplet'''::tsquery) OR (name ~~* '%indicator-applet%'::text)))
                                             -> Bitmap Index Scan on distributionsourcepackagecache__archive__idx (cost=0.00..345.42 rows=22789 width=0) (actual time=18.823..18.823 rows=22798 loops=1)
                                                   Index Cond: (archive = ANY ('{1,534}'::integer[]))
                                       -> Index Scan using sourcepackagename_pkey on sourcepackagename (cost=0.00..6.27 rows=1 width=18) (actual time=2.999..3.001 rows=1 loops=28)
                                             Index Cond: (sourcepackagename.id = distributionsourcepackagecache.sourcepackagename)
                                 -> Index Scan using packaging_sourcepackagename_idx on packaging (cost=0.00..0.34 rows=4 width=4) (actual time=3.096..3.096 rows=1 loops=28)
                                       Index Cond: (packaging.sourcepackagename = sourcepackagename.id)
                           -> Index Scan using sourcepackagerelease_sourcepackagename_idx on sourcepackagerelease (cost=0.00..68.77 rows=22 width=8) (actual time=4.060..5.273 rows=35 loops=7)
                                 Index Cond: (sourcepackagerelease.sourcepackagename = distributionsourcepackagecache.sourcepackagename)
                     -> Index Scan using securesourcepackagepu...

Read more...

Revision history for this message
Robert Collins (lifeless) wrote :

Ok, under 1 second on all three prod servers, so either a transient plan, or ???

description: updated
Revision history for this message
William Grant (wgrant) wrote :

OOPS-1943CF153 is consistent and terribly slow.

Revision history for this message
Bryce Harrington (bryce) wrote :

OOPS-bf8bf0c832e190ecda83f623c89e18ac

Was trying to fill in a description for a project I'd just created. The change went through okay, it just failed to display the edited page, so I had to reload it.

Revision history for this message
Robert Collins (lifeless) wrote :

Still happening: OOPS-4171dc2f1000578a15350505a835af7f

Revision history for this message
William Grant (wgrant) wrote :

If you can convince the page to render even once, you can now work around the issue semi-permanently for that project by selecting any of the packaging options (including the unpackaged option) at the bottom left of the page.

Revision history for this message
William Grant (wgrant) wrote :

This becomes easy to fix once we upgrade to PostgreSQL 9.1. We can use GIN for DSPC.fti and a GiST trigram index for DSPC.name, and eliminate the join against SPR using SPPH.sourcepackagename. This makes it ~60ms for most things on DF.

William Grant (wgrant)
Changed in launchpad:
assignee: nobody → William Grant (wgrant)
status: Triaged → In Progress
Revision history for this message
Launchpad QA Bot (lpqabot) wrote :
tags: added: qa-needstesting
William Grant (wgrant)
tags: added: qa-ok
removed: qa-needstesting
Revision history for this message
Launchpad QA Bot (lpqabot) wrote :
tags: added: qa-needstesting
removed: qa-ok
Changed in launchpad:
status: In Progress → Fix Committed
William Grant (wgrant)
tags: added: qa-ok
removed: qa-needstesting
William Grant (wgrant)
Changed in launchpad:
status: Fix Committed → Fix Released
Curtis Hovey (sinzui)
tags: added: disclosure infrastructure
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers