Jira (PDB-5219) Investigate query performance for "ESTATE - latest corrective change for specific system"

Bogdan Irimie (Jira)

unread,

Jul 29, 2021, 9:41:04 AM7/29/21

to puppe...@googlegroups.com

Bogdan Irimie created an issue

PuppetDB /

PDB-5219

Investigate query performance for "ESTATE - latest corrective change for specific system"

Issue Type:	Task
Assignee:	Unassigned
Created:	2021/07/29 6:40 AM
Priority:	Normal
Reporter:	Bogdan Irimie

From the performance test we ran in https://docs.google.com/spreadsheets/d/1eaOZhSoTyZ3k_0905_3BSDZpViz3WbTTJGQzGdoTsnI/edit?usp=sharing

curl --location -g --request GET 'http://localhost:8080/pdb/query/v4/nodes?query=["extract", ["latest_report_corrective_change","latest_report_status","certname","report_timestamp"], ["and",["~", ["fact", "operatingsystem"], "Cent.*"]]]'

is an outlier and we should investigate if this query can be rewritten in a more efficient way or if we can make changes to the query engine in order to improve it's performance

Add Comment

This message was sent by Atlassian Jira (v8.13.2#813002-sha1:c495a97)

Bogdan Irimie (Jira)

unread,

Jul 29, 2021, 9:49:03 AM7/29/21

to puppe...@googlegroups.com

Bogdan Irimie updated an issue

PuppetDB /

PDB-5219

Investigate query performance for "ESTATE - latest corrective change for specific system"

Change By:	Bogdan Irimie
Priority:	Normal High

Add Comment

Andrei Filipovici (Jira)

unread,

Aug 23, 2021, 10:02:03 AM8/23/21

to puppe...@googlegroups.com

Andrei Filipovici commented on

PDB-5219

Re: Investigate query performance for "ESTATE - latest corrective change for specific system"

This query is not hardcoded, it's based on user input. So the user may do a query based on other fact from the few hundreds available.
So improving just on query, out of potential hundreds, might not be such a good idea, because the indexes might take a long time to create and require additional space.
Plus the user might use custom facts that we are not aware of.

We did some testing on the 100k nodes sandbox with 20 users, but on the n1 server and the query took on average 17983 ms.
Then we added an index on the operatingsystem fact and on average, the request took 7272 ms. That's an improvement of approximately 60%.