Project

General

Profile

Bug #20309

Large Audit Table very slow upgrade

Added by Jason Lang about 5 years ago. Updated almost 3 years ago.

Status:
Resolved
Priority:
Normal
Assignee:
-
Category:
Database
Target version:
-
Difficulty:
Triaged:
No
Bugzilla link:
Pull request:
Fixed in Releases:
Found in Releases:

Description

Im currently upgrading from foreman 1.14 to 1.15. We admittedly have a larg(er) installation (10,000 managed servers) that has been around for quite some time (3+ years afaik)

The upgrade process seemed to be taking forever (3+ hours so far) so I started digging in a bit

I "think" the issue is generall in db_seed, specifically this section:
Seeding /usr/share/foreman/db/seeds.d/07-provisioning_templates.rb

It seems to be running a ton of queries similar to:
'SELECT `audits`.* FROM `audits` WHERE `audits`.`auditable_name` = ''PXELinux default memdisk'' AND `audits`.`auditable_type` = ''Template'' AND `audits`.`action` = ''destroy'' ORDER BY `audits`.`version` ASC'
'1153265', 'foreman', 'ltmwebcore.paychex.com:42448', 'foreman', 'Query', '17', 'Sorting result', 'SELECT `audits`.* FROM `audits` WHERE `audits`.`auditable_name` = \'epel\' AND `audits`.`auditable_type` = \'Template\' AND `audits`.`action` = \'update\' ORDER BY `audits`.`version` ASC'

However - our audit table is quite large (over 3 million records). So i can see each of these queries taking 30-60 seconds to return and sort on mysql.

Looking around - i know there is a built-in rake task for clearing reports (we use it regularly as we had too many of those affecting performance and upgrades previously) However i don't see any rake task designed at removing old audit records.

I "think" that would help, if i could choose to remove audit records (safely) after X days, months, years from the database, I could greatly shorten the number of audit records, and make the upgrade process go much faster?

It could be user-configurable/callable but i certainly don't "need" audit records from 3+ years ago in my case, and would love to dump older ones if the option were available.


Related issues

Related to Foreman - Feature #22158: Add a rake task for audits cleanupClosed2018-01-04

History

#1 Updated by Tomer Brisker almost 3 years ago

  • Status changed from New to Resolved

Task for cleaning audits table has been introduced in Foreman 1.18

#2 Updated by Tomer Brisker almost 3 years ago

Also available in: Atom PDF