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

Fact filter from overview page uses nested loops and times out

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: PDB 4.4.1, PDB 5.0.1
    • Fix Version/s: PDB 5.1.0
    • Component/s: None
    • Labels:
      None
    • Template:
    • Team:
      Data Platform
    • Sprint:
      Data Platform 2017-08-09
    • Method Found:
      Customer Feedback
    • Release Notes:
      Bug Fix
    • Release Notes Summary:
      Hide
      PuppetDB 4.4.x and 5.0.x updated fact storage to optimize write throughput. It unfortunately had negative consequences on query performance under heavy load. This rolls back to the older (4.3.x) fact storage style, which has worse write performance but better query performance.
      Show
      PuppetDB 4.4.x and 5.0.x updated fact storage to optimize write throughput. It unfortunately had negative consequences on query performance under heavy load. This rolls back to the older (4.3.x) fact storage style, which has worse write performance but better query performance.
    • QA Risk Assessment:
      Needs Assessment

      Description

      Attempting to filter the console overview page based on two fact filters causes a timeout in the console.

      The attempted query was:

      2017-07-03 13:49:03.676 UTC [db:pe-puppetdb,sess:595a3ced.c711,pid:50961,vtid:22/490887,tid:0] LOG:  duration: 503820.138 ms  execute S_826/C_827: WITH inactive_nodes AS (SELECT certname FROM certnames WHERE (deactivated IS NOT NULL OR expired IS NOT NULL)) SELECT certnames.certname AS certname FROM factsets fs LEFT JOIN environments ON fs.environment_id = environments.id LEFT JOIN producers ON fs.producer_id = producers.id LEFT JOIN certnames ON fs.certname = certnames.certname WHERE (((certnames.certname) in  ( (SELECT fs.certname AS certname FROM factsets fs INNER JOIN facts f ON fs.id = f.factset_id INNER JOIN fact_paths fp ON f.fact_path_id = fp.id INNER JOIN value_types vt ON f.value_type_id = vt.id LEFT JOIN environments env ON fs.environment_id = env.id WHERE (vt.id <> 5 AND ((fp.path ~ $1 AND fp.path IS NOT NULL) AND (f.value_string = $2)))) ) ) AND ((certnames.certname) in  ( (SELECT fs.certname AS certname FROM factsets fs INNER JOIN facts f ON fs.id = f.factset_id INNER JOIN fact_paths fp ON f.fact_path_id = fp.id INNER JOIN value_types vt ON f.value_type_id = vt.id LEFT JOIN environments env ON fs.environment_id = env.id WHERE (vt.id <> 5 AND ((fp.path ~ $3 AND fp.path IS NOT NULL) AND (f.value_string = $4)))) ) ))
      

      Formatted for readability:

      WITH inactive_nodes AS (
        SELECT certname 
        FROM certnames 
        WHERE (deactivated IS NOT NULL 
              OR expired IS NOT NULL)
      ) SELECT certnames.certname AS certname 
      FROM factsets fs 
      LEFT JOIN environments 
        ON fs.environment_id = environments.id 
      LEFT JOIN producers 
        ON fs.producer_id = producers.id 
      LEFT JOIN certnames 
        ON fs.certname = certnames.certname 
      WHERE (((certnames.certname) in ( (SELECT fs.certname AS certname 
                                         FROM factsets fs 
                                         INNER JOIN facts f 
                                           ON fs.id = f.factset_id 
                                         INNER JOIN fact_paths fp 
                                           ON f.fact_path_id = fp.id 
                                         INNER JOIN value_types vt 
                                           ON f.value_type_id = vt.id 
                                         LEFT JOIN environments env 
                                           ON fs.environment_id = env.id 
                                         WHERE (vt.id <> 5 
                                           AND ((fp.path ~ $1 AND fp.path IS NOT NULL) 
                                                AND (f.value_string = $2)))) ) ) 
        AND ((certnames.certname) in ( (SELECT fs.certname AS certname 
                                        FROM factsets fs 
                                        INNER JOIN facts f 
                                          ON fs.id = f.factset_id 
                                        INNER JOIN fact_paths fp 
                                          ON f.fact_path_id = fp.id 
                                        INNER JOIN value_types vt 
                                          ON f.value_type_id = vt.id 
                                        LEFT JOIN environments env 
                                          ON fs.environment_id = env.id 
                                        WHERE (vt.id <> 5 
                                               AND ((fp.path ~ $3 AND fp.path IS NOT NULL) 
                                                     AND (f.value_string = $4)))) ) ))
      

        Attachments

          Issue Links

            Activity

              jsd-sla-details-panel

                People

                • Assignee:
                  russell.mull Russell Mull
                  Reporter:
                  erik.hansen Erik Hansen
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  5 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: