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

Deleting from reports spends most of the time enforcing a constraint on certnames

    XMLWordPrintable

Details

    • PuppetDB
    • Needs Assessment

    Description

      The Problem

      https://explain.depesz.com/s/Ve5S

                                                                              QUERY PLAN
      ----------------------------------------------------------------------------------------------------------------------------------------------------------
       Delete on reports  (cost=2112.19..255114.61 rows=77903 width=6) (actual time=9879.603..9879.603 rows=0 loops=1)
         ->  Bitmap Heap Scan on reports  (cost=2112.19..255114.61 rows=77903 width=6) (actual time=3081.974..3542.301 rows=3134 loops=1)
               Recheck Cond: (producer_timestamp < (now() - '7 days'::interval))
               Heap Blocks: exact=34281
               ->  Bitmap Index Scan on idx_reports_producer_timestamp  (cost=0.00..2092.71 rows=77903 width=0) (actual time=38.582..38.582 rows=95352 loops=1)
                     Index Cond: (producer_timestamp < (now() - '7 days'::interval))
       Planning time: 0.895 ms
       Trigger for constraint certnames_reports_id_fkey: time=74064.742 calls=3134
       Trigger for constraint resource_events_report_id_fkey: time=215.914 calls=3134
       Execution time: 84169.223 ms
      (10 rows)
      

      We can see 74 of the 84 seconds are spent on certnames_reports_id_fkey constraint.

      This is related to past tickets about reducing the use of cascading deletes and updates in puppetdb.

      PDB-2415

      This ticket has a PR that I can be used if we also recreate the constraints at the end of the GC to be NOT VALID. Which means they would not be validated at creation time.

      PDB-2477

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              nick.walker Nick Walker
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Zendesk Support