Purging 3 years of statehistory

Icinga 2
#1

Hello Icinga users,

Just a post to let you know of my troubles.
We have a significant Icinga installation (4400 hosts, 62000 services).
It runs since 2018, 3 years ago.
Nothing was made to handle purging, and we can see now essentially one big table : icinga_statehistory (70 GB on drive).

I have tester a select count(*) correponding to the delete that would be done by activating IDO cleanup (should be more advertized) and keeping one year of history.

Select It lasted 40min.

I scheduled an operation for 1-2h for the real delete.

I’m here writing this 3 hours after start, not knowing when it will end…
Icingaweb dashboards are not updated anymore.
At least notifications work.

I though about doing it by shorter interval, but at least 40 only for the select, I thought that it will be faster to do it at once…

So, beware…

Later… My delete lasted 5h15.
Now I try to do an optimize on the table, but innodb_tmpdir is on /tmp which is too small…

#2

Hello @gillesMo,
I have been watching this post. I am working on optimize the Maria database setting also. Recently the ibdata1 file grow super large (30GB) and caused problems. I’m wondering how you resolved your problem? Also what setting you have configured in your Maria database? I am a beginner using Maria database so I do not want to provide wrong advise.

The Icinga2 documentation provides some details on IDO database cleanup and database optimization but I believe more details are needed. I also found this web article about how to search the database for table that need optimized. Mariadb has a web article on optimization and tuning if you did not see it.

I’m not sure if any of this helps your problem but wanted to share. I am still modifying my innodb setting to help with memory usage and stopping the ibdata1 file from growing.

Regards
Alex

#3

Hello Alex @aclark6996,

If you have only ibdata1 that is growing, you probably not have configured MariaDB to use one file by table. I think it’s the default since some release, I have not set it explicitly in my config :
innodb_file_per_table=ON

See https://mariadb.com/kb/en/innodb-file-per-table-tablespaces/.

The problem is not solved in the database, but in Icinga IDO config as you found.
As I found that it’s only the icinga_statehistory table that is growing, I add that cleanup section in /etc/icinga2/features-enabled/ido-mysql.conf

library "db_ido_mysql"
object IdoMysqlConnection "ido-mysql" {
  user = "icinga",
  password = "XXXXXXXXXXXXXXX",
  host = "1.2.3.4",
  database = "icinga"
  cleanup = {
    statehistory_age = 365d
  }
}