Best Database Engine for NagiosXI

[safuanmansor]

Hi Support,

Currently our client nagios server is running version 5.6.7 with mariadb v5.5. Number of host monitored is aroung 2000 + with near to 30000 services check.
Recently we are notice that the database often went away and of course repair databases script fix the issue. It happen roughly around 2 or 3 times a month allowing this being questioned by the management and hardly to justify. Appreciate your help on this.

Apart from that currently the database is running MyISAM engine except innodb on the nagios notification table. Should we convert all table to innodb? or aria maybe or MyISAM is more than enough with current environment?

Thanks
Safuan

[dchurch]

If you find that the amount of time you spend recovering from a crashed table is what you're trying to optimize for, then InnoDB is the way to go.

MyISAM does not support any of the four qualities of ACID databases.

InnoDB is designed for crash recovery. It has several features that prevent such corruption in the case of crashes, and also recover from crashes automatically, so you don't have to run REPAIR TABLE.

InnoDB has been the default storage engine since MySQL 5.5.5 in 2010. It's clear that MySQL is moving (slowly) in the direction of phasing out MyISAM. In MySQL 8.0, even the system tables in the mysql schema have been converted to InnoDB.

You can get a list of all the tables in your cluster and their storage engines with this query:

Code: Select all

select TABLE_NAME, ENGINE from information_schema.tables where ENGINE is not null;

[safuanmansor]

Hi dchurch,

I ran the sql statement provided on the newly deployed xi 5.7.4 and i can see that most table (in nagiosql n nagiosxi db) already in innodb instead of MYISAM compared to the older version of nagios installation. Do you have any particular reason why nagios table still maintaining MYISAM?

Do you have other customer that using all innodb for their database and how are they doing.

Thanks
Safuan

[dchurch]

Apparently you're not the first to ask this question. I am unaware of any clients who have made the switch completely to InnoDB. Nagios default install even nowadays creates MyISAM tables.

• **VERY OLD** New install - is Innodb the proper path yet? (2013-10-14)
• **VERY OLD** innodb database conversion (2013-09-13)

I'd take with a grain of salt anything that says InnoDB suffers a performance hit (and look at the date it was written - much of what I'm finding on Google is nearly a decade old).

I did an update to a MariaDB 5.7 database on a VM and saw a slight jump in load average, but the system still worked.

In short, you're welcome to try it. Seems to work alright.