Home » Categories » Products » Nagios XI » Documentation » Advanced Topics

Database Storage Engine and High CPU usage in Nagios XI

Storage Engine Overview 

A storage engine is a software module that a database management system uses to create, read, update data from a database.

For MySQL 5.5 and later, the default storage engine is InnoDB. The default storage engine for MySQL prior to version 5.5 was MyISAM.

InnoDB is the most widely used storage engine with transaction support. It supports row-level locking, crash recovery and multi-version concurrency control. It is the only engine which provides foreign key referential integrity constraint. 

MyISAM is the original storage engine. It is a fast storage engine. It is used mostly in Web and data warehousing.

If your database tables have a proclivity for crashing, it is better to use InnoDB as the storage engine. For more information, read the following:

Benefits of using InnoDB 

Checking Storage Engine Type

Run the following within mysql to determine the storage engine type for your database:

USE <database>;
SHOW TABLE STATUS\G

Nagios XI Databases

Database Name Default Storage Engine
nagios MyISAM
nagiosxi InnoDB
nagiosql InnoDB

 

Potential Nagios XI Symptoms

Your server is very slow and operates at high CPU. You also experience crashed tables frequently and must run the Nagios XI repair script to solve the error on a regular basis.

Possible Solution

Please read this article below to ensure that the steps presented in the article do not fix your issue before attempting the commands below:

Crashed Database Tables

Note: Ensure that you create a backup before attempting the below database commands.

The following commands will convert the storage engine to InnoDB for all Nagios XI tables. This should decrease the probability of crashed tables and other table errors that may cause high cpu usage.

mysql -h 127.0.0.1 -uroot -pnagiosxi nagios -e "SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') as '' FROM information_schema.tables AS tb WHERE table_schema = 'nagios' AND ENGINE = 'MyISAM' AND TABLE_TYPE = 'BASE TABLE' ORDER BY table_name DESC;" > /tmp/nagios_convert_myisam_to_innodb.sql
mysql -h 127.0.0.1 -uroot -pnagiosxi nagiosql -e "SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') as '' FROM information_schema.tables AS tb WHERE table_schema = 'nagiosql' AND ENGINE = 'MyISAM' AND TABLE_TYPE = 'BASE TABLE' ORDER BY table_name DESC;" > /tmp/nagiosql_convert_myisam_to_innodb.sql
mysql -h 127.0.0.1 -uroot -pnagiosxi -e "SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') as '' FROM information_schema.tables AS tb WHERE table_schema = 'nagiosxi' AND ENGINE = 'MyISAM' AND TABLE_TYPE = 'BASE TABLE' ORDER BY table_name DESC;" > /tmp/nagiosxi_convert_myisam_to_innodb.sql
mysql -h 127.0.0.1 -uroot -pnagiosxi nagios < /tmp/nagios_convert_myisam_to_innodb.sql
mysql -h 127.0.0.1 -uroot -pnagiosxi nagiosql < /tmp/nagiosql_convert_myisam_to_innodb.sql
mysql -h 127.0.0.1 -uroot -pnagiosxi nagiosxi < /tmp/nagiosxi_convert_myisam_to_innodb.sql
0 (0)
Article Rating (No Votes)
Rate this article
  • Icon PDFExport to PDF
  • Icon MS-WordExport to MS Word
Attachments Attachments
There are no attachments for this article.
Related Articles RSS Feed
Nagios XI - Restarting A Linux Service With NRPE
Viewed 2875 times since Thu, Jan 28, 2016
Nagios XI - Special Characters
Viewed 9763 times since Tue, Apr 25, 2017
Nagios Core - Passive Monitoring with Nagios - NWC14
Viewed 5370 times since Mon, Feb 8, 2016
Nagios XI - Importing Config Files From Nagios Core into Nagios XI
Viewed 8904 times since Wed, Jan 27, 2016
Nagios XI - Hardware Requirements - Baseline Testing
Viewed 2471 times since Sun, Jul 17, 2016
Nagios XI - How to Restart A Windows Service With NRPE
Viewed 4593 times since Thu, Jan 28, 2016
Nagios XI - Advanced Features of Nagios XI - NWC14
Viewed 2028 times since Thu, Feb 4, 2016
Nagios XI - SNMP Trap Hardening
Viewed 5784 times since Tue, Nov 6, 2018
Converting PostgreSQL to MySQL for Nagios XI
Viewed 21652 times since Tue, Dec 6, 2016
Nagios XI - Receiving SNMP Traps From Nagios Network Analyzer
Viewed 2726 times since Mon, Oct 17, 2016