Project

General

Profile

Actions

Bug #23623

closed

Break report expiration into batches

Added by Lukas Zapletal almost 6 years ago. Updated over 5 years ago.

Status:
Closed
Priority:
Normal
Category:
Reporting
Target version:
Difficulty:
Triaged:
No
Fixed in Releases:
Found in Releases:

Description

The problem we are facing with reports not being deleted and slowed down report importing is concurrency. If you want to delete many records in one transaction, the RDBMS must lock whole table and this blocks further inserts or updates. The same problem is on PostgreSQL.

You can indeed use any SQL query which will do the job of getting rid of the records. Inner join is something that is exceptionally problematic on MySQL, but it’s super slow on PostgreSQL as well.

The recommended solution to this long-standing issue is to break the deletion into chunks. Each chunk needs to be just hundreds of thousands of records, then do sleep (dozens of seconds or minutes) so the locked transactions have a chance to complete. You need to find the right balance of deletion of records so you actually keep up with incoming records.

To do that, we really need to use joins because whole chain of joined records must be deleted in a single transaction (we don’t have ON DELETE CASCADE set for those). Or if MySQL supports cascade deletion, it is worth setting that and then it is as easy as deleting batch of records from reports table but this was problematic with Rails AFAIK.

Currently the SQL statements are:

DELETE FROM “logs” WHERE “logs”.“report_id” IN (SELECT “reports”.“id” FROM “reports” WHERE “reports”.“type” IN (‘ConfigReport’) AND (reports.created_at < ‘2018-05-10 13:36:21’))

DELETE FROM “messages” WHERE (id not IN (SELECT DISTINCT message_id FROM “logs”))

DELETE FROM “sources” WHERE (id not IN (SELECT DISTINCT source_id FROM “logs”))

DELETE FROM “reports” WHERE “reports”.“type” IN (‘ConfigReport’) AND (reports.created_at < ‘2018-05-10 13:36:21’)

Related issues 3 (0 open3 closed)

Related to Foreman - Bug #24900: foreman-rake reports:expire fails to runClosedLukas ZapletalActions
Related to Foreman - Bug #24257: command "foreman-rake reports:expire" does not delete reportsClosedMarek HulánActions
Related to Foreman - Bug #25961: Report expiration task occasionally deletes messages and sourcesClosedLukas ZapletalActions
Actions #1

Updated by The Foreman Bot almost 6 years ago

  • Status changed from New to Ready For Testing
  • Assignee set to Lukas Zapletal
  • Pull request https://github.com/theforeman/foreman/pull/5587 added
Actions #2

Updated by Lukas Zapletal over 5 years ago

  • Status changed from Ready For Testing to Closed
  • % Done changed from 0 to 100
Actions #3

Updated by Marek Hulán over 5 years ago

  • Target version set to 1.19.0
  • Triaged set to No
  • Fixed in Releases 1.19.0 added
Actions #4

Updated by Tomer Brisker over 5 years ago

  • Related to Bug #24900: foreman-rake reports:expire fails to run added
Actions #5

Updated by Lukas Zapletal over 5 years ago

  • Related to Bug #24257: command "foreman-rake reports:expire" does not delete reports added
Actions #6

Updated by Lukas Zapletal about 5 years ago

  • Related to Bug #25961: Report expiration task occasionally deletes messages and sources added
Actions

Also available in: Atom PDF