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

idx_certnames_latest_report_id causes timeouts for filter in console overview

    Details

    • Template:
    • Team:
      Data Platform
    • Story Points:
      3
    • Sprint:
      Hopper/Triage, PuppetDB 2017-05-31
    • Release Notes:
      Bug Fix
    • Release Notes Summary:
      Improved performance for some queries involving each node's latest report.
    • QA Risk Assessment:
      Needs Assessment

      Description

      Potential regression ...

      User with the following amount of data:

      pe-puppetdb=# \d+ 
      List of relations 
      Schema | Name | Type | Owner | Size | Description 
      --------+----------------------------+----------+-------------+------------+------------- 
      public | catalog_resources | table | pe-puppetdb | 629 MB | 
      public | catalogs | table | pe-puppetdb | 7728 kB | 
      public | catalogs_id_seq | sequence | pe-puppetdb | 8192 bytes | 
      public | catalogs_transform_id_seq1 | sequence | pe-puppetdb | 8192 bytes | 
      public | certname_facts | view | pe-postgres | 0 bytes | 
      public | certname_id_seq | sequence | pe-puppetdb | 8192 bytes | 
      public | certnames | table | pe-puppetdb | 1880 kB | 
      public | edges | table | pe-puppetdb | 279 MB | 
      public | environments | table | pe-puppetdb | 16 kB | 
      public | environments_id_seq | sequence | pe-puppetdb | 8192 bytes | 
      public | fact_paths | table | pe-puppetdb | 5264 kB | 
      public | fact_paths_id_seq | sequence | pe-puppetdb | 8192 bytes | 
      public | fact_values | table | pe-puppetdb | 97 MB | 
      public | fact_values_id_seq | sequence | pe-puppetdb | 8192 bytes | 
      public | facts | table | pe-puppetdb | 182 MB | 
      public | factsets | table | pe-puppetdb | 2712 kB | 
      public | factsets_id_seq | sequence | pe-puppetdb | 8192 bytes | 
      public | producers | table | pe-puppetdb | 16 kB | 
      public | producers_id_seq | sequence | pe-puppetdb | 8192 bytes | 
      public | report_statuses | table | pe-puppetdb | 16 kB | 
      public | report_statuses_id_seq | sequence | pe-puppetdb | 8192 bytes | 
      public | reports | table | pe-puppetdb | 42 GB | 
      public | reports_id_seq | sequence | pe-puppetdb | 8192 bytes | 
      public | resource_events | table | pe-puppetdb | 3563 MB | 
      public | resource_params | table | pe-puppetdb | 38 MB | 
      public | resource_params_cache | table | pe-puppetdb | 23 MB | 
      public | schema_migrations | table | pe-puppetdb | 8192 bytes | 
      public | value_types | table | pe-puppetdb | 8192 bytes | 
      (28 rows)
       
      pe-puppetdb=# select count(*) from certnames; 
      count 
      ------- 
      5236 
      (1 row)
       
      pe-puppetdb=# select count(*) from resource_events; 
      count 
      --------- 
      1217849 
      (1 row)
       
      pe-puppetdb=# select count(*) from reports; 
      count 
      --------- 
      1079718 
      (1 row)

      Adding the index from PDB-3053:

      CREATE UNIQUE INDEX CONCURRENTLY idx_certnames_latest_report_id ON certnames(latest_report_id)

      Resolved timeout issues in the Console Overview, but created timeout issues when using the "Unresponsive for N+ hours" Filter in the Console Overview.

      2017-05-15 10:11:03.131 EDT [db:pe-puppetdb,sess:5919ab15.2fd7,pid:12247,vtid:59/1655914,tid:0] LOG:  duration: 129949.316 ms  execute S_1012/C_1013: 

      SELECT certnames.certname AS certname, reports_environment.environment AS report_environment, reports.end_time AS report_timestamp, encode(reports.hash::bytea, 'hex') AS latest_report_hash, report_statuses.status AS latest_report_status, reports.noop AS latest_report_noop, reports.noop_pending AS latest_report_noop_pending, reports.cached_catalog_status AS cached_catalog_status, reports.corrective_change AS latest_report_corrective_change FROM certnames LEFT JOIN catalogs ON catalogs.certname = certnames.certname LEFT JOIN factsets fs ON certnames.certname = fs.certname LEFT JOIN reports ON certnames.latest_report_id = reports.id LEFT JOIN environments catalog_environment ON catalog_environment.id = catalogs.environment_id LEFT JOIN report_statuses ON reports.status_id = report_statuses.id LEFT JOIN environments facts_environment ON facts_environment.id = fs.environment_id LEFT JOIN environments reports_environment ON reports_environment.id = reports.environment_id WHERE ((reports.end_time < $1) AND ((certnames.certname) in  ( (SELECT certnames.certname AS certname FROM certnames LEFT JOIN catalogs ON catalogs.certname = certnames.certname LEFT JOIN factsets fs ON certnames.certname = fs.certname LEFT JOIN reports ON certnames.latest_report_id = reports.id LEFT JOIN environments catalog_environment ON catalog_environment.id = catalogs.environment_id LEFT JOIN report_statuses ON reports.status_id = report_statuses.id LEFT JOIN environments facts_environment ON facts_environment.id = fs.environment_id LEFT JOIN environments reports_environment ON reports_environment.id = reports.environment_id WHERE (certnames.deactivated IS NULL AND certnames.expired IS NULL)) ) )) ORDER BY reports.end_time DESC, reports.end_time DESC LIMIT 50 OFFSET 0;

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                russell.mull Russell Mull
                Reporter:
                tom.kishel Thomas Kishel
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Zendesk Support