Project

General

Profile

Bug #10329

report::expire still running very slowly (at least on PostgreSQL)

Added by Dirk Heinrichs over 5 years ago. Updated over 5 years ago.

Status:
New
Priority:
High
Assignee:
-
Category:
Puppet Reports
Target version:
-
Difficulty:
Triaged:
No
Bugzilla link:
Pull request:
Fixed in Releases:
Found in Releases:

Description

This is a followup to #8565. With many long reports in the database (large messages and logs tables), expiring reports takes quite a long time on our machine. Sometimes more than a day so that multiple jobs sum up and start hogging the CPU. Here's how the situation looks like:

foreman=> select count(*) from reports;
 count
-------
  1951
(1 row)

foreman=> select count(*) from messages;
 count
--------
 704462
(1 row)

foreman=> select count(*) from logs;
 count
--------
 802815
(1 row)

foreman=> explain DELETE FROM "messages" WHERE (id not IN (SELECT DISTINCT message_id FROM "logs" ));
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Delete on messages  (cost=0.42..9806323789.43 rows=352231 width=6)
   ->  Seq Scan on messages  (cost=0.42..9806323789.43 rows=352231 width=6)
         Filter: (NOT (SubPlan 1))
         SubPlan 1
           ->  Materialize  (cost=0.42..27142.52 rows=279366 width=4)
                 ->  Unique  (cost=0.42..24653.69 rows=279366 width=4)
                       ->  Index Only Scan using index_logs_on_message_id on logs  (cost=0.42..22646.65 rows=802815 width=4)
(7 rows)

History

#1 Updated by Dirk Heinrichs over 5 years ago

Just wanted to add the link to the blog post giving a possible solution to the problem.

Also available in: Atom PDF