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

Refactor GC processes to avoid cascades



    • Improvement
    • Status: Closed
    • Normal
    • Resolution: Fixed
    • None
    • PDB 4.1.0
    • PuppetDB


      Current GC processes are somewhat simple and rely on cascading from a single delete in certname/report etc. Although the cascaded calls are pretty simple, there can be enough of them to be taxing on the database. nick.walker has a new approach that is faster on larger datasets. Below is an example of the approach applied to reports

      SELECT id INTO TEMP TABLE report_ids_past_report_ttl from reports r where r.producer_timestamp < NOW() - INTERVAL '14 days';
      ANALYZE report_ids_past_report_ttl;
      DELETE from resource_events where report_id IN ( select id from report_ids_past_report_ttl );
      UPDATE certnames SET latest_report_id = NULL where latest_report_id IN ( select id from report_ids_past_report_ttl );
      DELETE FROM reports where id IN ( select id from report_ids_past_report_ttl );
      DROP TABLE report_ids_past_report_ttl;

      We should be able to recast node_purge_ttl GC in a similar way. It's worth re-evaluating facts as well, though it's probably different.

      From Nick:

      I potentially faster way to do the same as above. Depending on how it treats the statistics on the CTEs vs the temp table. I can imagine that on large datasets this may have more of a variable performance depending on statistics.

      WITH deleted_reports AS (
        DELETE FROM reports r
        where r.producer_timestamp < NOW() - INTERVAL '2 days'
        RETURNING r.id
      deleted_resource_events AS (
        DELETE FROM resource_events re
        WHERE report_id IN ( SELECT id FROM deleted_reports )
        RETURNING re.report_id
      UPDATE certnames
      SET latest_report_id = NULL
      WHERE latest_report_id IN ( select report_id from deleted_resource_events );
                                                                      QUERY PLAN
       Update on certnames  (cost=15.76..37.62 rows=430 width=94) (actual time=0.059..0.059 rows=0 loops=1)
         CTE deleted_reports
           ->  Delete on reports r  (cost=0.00..11.05 rows=20 width=6) (actual time=0.012..0.013 rows=0 loops=1)
                 ->  Seq Scan on reports r  (cost=0.00..11.05 rows=20 width=6) (actual time=0.009..0.009 rows=0 loops=1)
                       Filter: (producer_timestamp < (now() - '2 days'::interval))
                       Rows Removed by Filter: 3
         CTE deleted_resource_events
           ->  Delete on resource_events re  (cost=0.00..4.66 rows=1 width=38) (actual time=0.030..0.031 rows=0 loops=1)
                 ->  Nested Loop Semi Join  (cost=0.00..4.66 rows=1 width=38) (actual time=0.028..0.028 rows=0 loops=1)
                       Join Filter: (re.report_id = deleted_reports.id)
                       ->  Seq Scan on resource_events re  (cost=0.00..4.01 rows=1 width=14) (actual time=0.005..0.006 rows=1 loops=1)
                       ->  CTE Scan on deleted_reports  (cost=0.00..0.40 rows=20 width=40) (actual time=0.015..0.015 rows=0 loops=1)
         ->  Hash Join  (cost=0.04..21.91 rows=430 width=94) (actual time=0.056..0.056 rows=0 loops=1)
               Hash Cond: (certnames.latest_report_id = deleted_resource_events.report_id)
               ->  Seq Scan on certnames  (cost=0.00..18.60 rows=860 width=70) (actual time=0.007..0.007 rows=1 loops=1)
               ->  Hash  (cost=0.03..0.03 rows=1 width=40) (actual time=0.040..0.040 rows=0 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 0kB
                     ->  HashAggregate  (cost=0.02..0.03 rows=1 width=40) (actual time=0.037..0.037 rows=0 loops=1)
                           Group Key: deleted_resource_events.report_id
                           ->  CTE Scan on deleted_resource_events  (cost=0.00..0.02 rows=1 width=40) (actual time=0.033..0.033 rows=0 loops=1)
       Planning time: 0.587 ms
       Execution time: 0.154 ms
      (22 rows)


        Issue Links



              Unassigned Unassigned
              ryan.senior Ryan Senior
              0 Vote for this issue
              2 Start watching this issue



                Zendesk Support