Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[dev.icinga.com #13203] IDO MySQL - delete statement creates high load and locks tabe after enabling cleanup #4788

Closed
icinga-migration opened this issue Nov 15, 2016 · 2 comments
Labels
area/db-ido Database output bug Something isn't working

Comments

@icinga-migration
Copy link

This issue has been migrated from Redmine: https://dev.icinga.com/issues/13203

Created by winem_ on 2016-11-15 13:24:10 +00:00

Assignee: (none)
Status: New
Target Version: (none)
Last Update: 2016-11-15 13:24:10 +00:00 (in Redmine)

Icinga Version: 2.5.4
Backport?: Not yet backported
Include in Changelog: 1

We run this icinga instance for some months now and initially enabled the cleanup in the IdoMySqlConnection configuration today:

cleanup = {
downtimehistory_age = 31d
contactnotifications_age = 31d
statehistory_age = 31d
externalcommands_age = 14d
}

This was fine for contactnotifications, statehistory and downtimehistory but caused a high load when running the delete statement on the externalcommands table.

Query: DELETE FROM icinga_externalcommands WHERE instance_id = 1 AND entry_time < FROM_UNIXTIME(1477991035)

This is fine, but, in this case, more than 50 million rows are affected. So the delete statement was running for a almost an hour, when I killed it, increased the load and prevented table locks by other queries.

The solution that came into my mind is to run the delete query with a limit in a loop and fetch the number of affected rows from the response. Exit the loop when 0 rows have been deleted.

@icinga-migration icinga-migration added bug Something isn't working area/db-ido Database output labels Jan 17, 2017
@dnsmichi
Copy link
Contributor

I'd say it would be a good idea to not log externalcommands anymore beforehand, but for the deleting algorithm - yep, nice idea. Patches welcome.

dnsmichi pushed a commit that referenced this issue Apr 28, 2017
That's not used in Icinga Web 2 and might lock the feature
on cleanup.

refs #4788
@dnsmichi
Copy link
Contributor

I'm removing the default categories setting in a separate PR. If you come up with a solution for batched deletes, please send in a PR and we can reopen this issue.

dnsmichi pushed a commit that referenced this issue Apr 28, 2017
…d-history

DB IDO: Disable external command history by default

fixes #5207 
refs #4788
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
area/db-ido Database output bug Something isn't working
Projects
None yet
Development

No branches or pull requests

2 participants