[PDB-3611] Fact filter from overview page uses nested loops and times out Created: 2017/07/05  Updated: 2018/08/24  Resolved: 2017/08/06

Status: Closed
Project: PuppetDB
Component/s: None
Affects Version/s: PDB 4.4.1, PDB 5.0.1
Fix Version/s: PDB 5.1.0

Type: Bug Priority: Major
Reporter: Erik Hansen Assignee: Russell Mull
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Relates
relates to PDB-3684 PDB performance issues with large str... Closed
relates to PDB-3249 Reduplicate fact values Closed
Template:
Team: Data Platform
Sprint: Data Platform 2017-08-09
Method Found: Customer Feedback
Release Notes: Bug Fix
Release Notes Summary: 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)))) ) ))



 Comments   
Comment by Russell Mull [ 2017/07/05 ]

Example query:

  puppet query 'inventory[certname] { facts.wm_puppet_env = "development" and facts.buildtype = "store" }'

Generated at Fri Dec 13 08:18:42 PST 2019 using JIRA 7.7.1#77002-sha1:e75ca93d5574d9409c0630b81c894d9065296414.