Uploaded image for project: 'PuppetDB'
  1. PuppetDB
  2. PDB-2425

Excessive long running select count query

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Normal
    • Resolution: Fixed
    • Affects Version/s: PDB 3.2.3
    • Fix Version/s: PDB 3.2.4
    • Component/s: None
    • Labels:
      None
    • Template:
    • Story Points:
      1
    • Sprint:
      PuppetDB 2016-02-24

      Description

      We're seeing a lot of excessively long running select queries

       00:00:41.200229 | SELECT count(*) AS c FROM catalogs, catalog_resources, certnames WHERE ((catalogs.id = catalog_resources.catalog_id AND cer
      tnames.certname = catalogs.certname) AND (certnames.deactivated IS NULL AND certnames.expired IS NULL))
       00:00:37.913217 | SELECT count(*) AS c FROM catalogs, catalog_resources, certnames WHERE ((catalogs.id = catalog_resources.catalog_id AND cer
      tnames.certname = catalogs.certname) AND (certnames.deactivated IS NULL AND certnames.expired IS NULL))
       00:00:36.647334 | SELECT count(*) AS c FROM catalogs, catalog_resources, certnames WHERE ((catalogs.id = catalog_resources.catalog_id AND cer
      tnames.certname = catalogs.certname) AND (certnames.deactivated IS NULL AND certnames.expired IS NULL))
       00:00:18.539854 | SELECT count(*) AS c FROM (SELECT DISTINCT catalog_resources.resource FROM catalogs, catalog_resources, certnames WHERE ((c
      atalogs.id = catalog_resources.catalog_id AND certnames.certname = catalogs.certname) AND (certnames.deactivated IS NULL AND certnames.expired
       IS NULL)) ) r
       00:00:15.335701 | SELECT count(*) AS c FROM catalogs, catalog_resources, certnames WHERE ((catalogs.id = catalog_resources.catalog_id AND cer
      tnames.certname = catalogs.certname) AND (certnames.deactivated IS NULL AND certnames.expired IS NULL))
       00:00:14.258559 | SELECT count(*) AS c FROM catalogs, catalog_resources, certnames WHERE ((catalogs.id = catalog_resources.catalog_id AND cer
      tnames.certname = catalogs.certname) AND (certnames.deactivated IS NULL AND certnames.expired IS NULL))
       00:00:13.083547 | SELECT count(*) AS c FROM catalogs, catalog_resources, certnames WHERE ((catalogs.id = catalog_resources.catalog_id AND cer
      tnames.certname = catalogs.certname) AND (certnames.deactivated IS NULL AND certnames.expired IS NULL))
       00:00:11.309933 | SELECT DISTINCT encode(resource::bytea, 'hex') AS resource FROM resource_params_cache WHERE resource in ($1,$2,$3,$4,$5,$6,
      $7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$
      43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78
      ,$79,$80,$81,$82,$83,$84,$85,$86,$87,$88,$89,$90,$91,$92,$93,$94,$95,$96,$97,$98,$99,$100,$101,$102,$103,$104,$105,$106,$107,$108,$109,$110,$1
      11,$112,$113,$114,$115,$116,$117,$118,$119,$120,$121,$122,$123,$124,$125,$126,$127,$128,$129,$130,$131,$132,$133,$134,$135,$136,$137,$138,$139
      ,$140,$141,$142,$143,$144,$145,$146,$147,$148,$149,$150,$151,$152,$153,$154,$155,$156,$157,$158,$159,$160,$161,$162,$163,$164,$165,$166,$167,$
      168,$169,$170,$171,$172,$173,$174,$175,$176,$177,$178,$179,$180,$181,$182,$183,$184,$185,$186,$187,$188,$189,$190,$191,$192,$193,$194,$195,$19
      6,$197,$198,$199,$200,$201,$202,$203,$204,$205,$
       00:00:10.527465 | SELECT count(*) AS c FROM catalogs, catalog_resources, certnames WHERE ((catalogs.id = catalog_resources.catalog_id AND cer
      tnames.certname = catalogs.certname) AND (certnames.deactivated IS NULL AND certnames.expired IS NULL))
       00:00:09.428502 | SELECT count(*) AS c FROM catalogs, catalog_resources, certnames WHERE ((catalogs.id = catalog_resources.catalog_id AND cer
      tnames.certname = catalogs.certname) AND (certnames.deactivated IS NULL AND certnames.expired IS NULL))
       00:00:03.802637 | SELECT producer_timestamp FROM catalogs WHERE certname = $1 ORDER BY producer_timestamp DESC LIMIT 1
      

      It seems related to PDB-57 but no one should have access to the dashboard in our case.

        Attachments

          Activity

            jsd-sla-details-panel

              People

              • Assignee:
                Unassigned
                Reporter:
                daenney Daniele Sluijters
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: