Title: Message Title
| |
|
|
|
|
*Summary*: I would like to propose creating and index on *reports* (_start_time_) to increase query performance. Full details and examples below...
We are currently using the latest version of Puppetserver (5.3.0), Puppet (5.5.0), and PuppetDB (5.2.1) in an environment with approximately 600 nodes, which have agent running in noop mode splayed every 60 min, and we are retaining reports on all nodes for 2 years. Over time, this has obviously increased the size of the puppetdb database, specifically the _reports_ table (and _resource_events_). We use [Puppetboard |[http://example.comhttps://github.com/voxpupuli/puppetboard|https://github.com/voxpupuli/puppetboard]] to access and view the reports and history for all the servers in the environment, and it has recently become apparent that there are some long running queries against the database where there is some room for improvement (_examples below_).
Puppetboard's index page loads a graph of daily reports (by default for the last 7 days), as seen in the following sample, issuing a query similar to this for each day:
{code:java}WITH inactive_nodes AS (SELECT certname FROM certnames WHERE (deactivated IS NOT NULL OR expired IS NOT NULL)) SELECT report_statuses.status AS status, count(*) count FROM reports LEFT JOIN environments ON environments.id = reports.environment_id LEFT JOIN producers ON producers.id = reports.producer_id LEFT JOIN report_statuses ON reports.status_id = report_statuses.id WHERE ((reports.start_time >= '2018-04-16 17:00:00-07') AND (reports.start_time < '2018-04-17 17:00:00-07')) GROUP BY report_statuses.status;
{code}
With the current size of the database and tables, this query was taking approximately 50-60 seconds to complete on average, ultimately causing performance issues with Postgres, leading to canceled queries on the slave affecting both PuppetDB and Puppetboard, including timeouts while viewing the dashboard.
After enabling slow query logging, and analyzing the query, most of the time was spent scanning the reports table only to discard over 2 million rows after the filter was applied:
{code:java}puppetdb=# EXPLAIN ANALYZE WITH inactive_nodes AS (SELECT certname FROM certnames WHERE (deactivated IS NOT NULL OR expired IS NOT NULL)) SELECT report_statuses.status AS status, count(*) count FROM reports LEFT JOIN environments ON environments.id = reports.environment_id LEFT JOIN producers ON producers.id = reports.producer_id LEFT JOIN report_statuses ON reports.status_id = report_statuses.id WHERE ((reports.start_time >= '2018-04-16 17:00:00-07') AND (reports.start_time < '2018-04-17 17:00:00-07')) GROUP BY report_statuses.status;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=451561.06..451561.08 rows=1 width=16) (actual time=54732.151..54735.848 rows=3 loops=1)
Group Key: report_statuses.status
-> Sort (cost=451561.06..451561.06 rows=1 width=8) (actual time=54732.116..54733.627 rows=11413 loops=1)
Sort Key: report_statuses.status
Sort Method: quicksort Memory: 919kB
-> Nested Loop Left Join (cost=0.00..451561.05 rows=1 width=8) (actual time=15114.756..54727.657 rows=11413 loops=1)
Join Filter: (reports.status_id = report_statuses.id)
Rows Removed by Join Filter: 22826
-> Seq Scan on reports (cost=0.00..451559.98 rows=1 width=24) (actual time=15114.736..54691.710 rows=11413 loops=1)
Filter: ((start_time >= '2018-04-16 17:00:00-07'::timestamp with time zone) AND (start_time < '2018-04-17 17:00:00-07'::timestamp with time zone))
Rows Removed by Filter: 2366087
-> Seq Scan on report_statuses (cost=0.00..1.03 rows=3 width=16) (actual time=0.001..0.001 rows=3 loops=11413)
Planning time: 0.404 ms
Execution time: 54746.603 ms
{code}
After creating a new index on the *start_time* column, this same query dropped to ~35ms total which is a _huge_ difference to say the least:
{code:java}puppetdb=# EXPLAIN ANALYZE WITH inactive_nodes AS (SELECT certname FROM certnames WHERE (deactivated IS NOT NULL OR expired IS NOT NULL)) SELECT report_statuses.status AS status, count(*) count FROM reports LEFT JOIN environments ON environments.id = reports.environment_id LEFT JOIN producers ON producers.id = reports.producer_id LEFT JOIN report_statuses ON reports.status_id = report_statuses.id WHERE ((reports.start_time >= '2018-04-16 17:00:00-07') AND (reports.start_time < '2018-04-17 17:00:00-07')) GROUP BY report_statuses.status; QUERY PLAN---------------------------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=1430.80..1430.83 rows=3 width=16) (actual time=34.420..34.421 rows=3 loops=1) Group Key: report_statuses.status -> Hash Left Join (cost=1.50..1423.57 rows=1447 width=8) (actual time=0.076..28.530 rows=11499 loops=1) Hash Cond: (reports.status_id = report_statuses.id) -> Index Scan using reports_start_time_idx on reports (cost=0.43..1402.61 rows=1447 width=24) (actual time=0.028..18.457 rows=11499 loops=1) Index Cond: ((start_time >= '2018-04-16 17:00:00-07'::timestamp with time zone) AND (start_time < '2018-04-17 17:00:00-07'::timestamp with time zone)) -> Hash (cost=1.03..1.03 rows=3 width=16) (actual time=0.016..0.016 rows=3 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on report_statuses (cost=0.00..1.03 rows=3 width=16) (actual time=0.007..0.010 rows=3 loops=1) Planning time: 0.832 ms Execution time: 34.538 ms(11 rows)
{code}
|
|
|
|
| |
|
--
You received this message because you are subscribed to the Google Groups "Puppet Bugs" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/puppet-bugs.
For more options, visit https://groups.google.com/d/optout.
|
