NagiosXI Database Error Occurs Frequently

[mejokj]

Hi Team,

We are frequently getting database error for Nagiosxi, attached error screenshot for your reference.

So every time we should repair the database and restart mysql service to fix the issue. Also increased the number of database connection.

While checking the logs we could see the below error. Please note that we are using remote database for the Nagiosxi server.

Kindly suggest a permanent solution for this issue.

Error Log
+++++++

mysqlcheck: Got error: 1129: Host 'nagiosxi.ae.iffconet.com' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts' when trying to connectIssued remote command 'mysqlcheck -f -r -u nagiosxi -pnagiosxi -h db --port=3306 --databases nagiosxi'SOMETHING WENT WRONG. ATTEMPT MANUAL REPAIR

Thanks

[benjaminsmith]

Hi @mejokj,

Let's take a deeper dive into the systems logs and see if we can figure out the root cause. Please PM your system profile (see instructions below). The error code 1129 is indicating that MySQL is shutting down connections due to abnormal activity.

When you increased the max connections, did you follow our guide below?
Nagios XI - MySQL/MariaDB - Max Connections

Let's check the current peak connections again to see where that's at..

Code: Select all

mysql -uroot -pnagiosxi -e "show global status like 'Max_used_connections';"

Additionally, let's check the table sizes in the database and make sure everything is looking normal there. Post the output to the following query(s) to the thread. Thanks. Benjamin

Run the following command:

Code: Select all

grep mysql /usr/local/nagiosxi/html/config.inc.php | wc -l

If it outputs the number 3, run the following to check the table sizes:

Code: Select all

echo "SELECT table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema IN ('nagios', 'nagiosql', 'nagiosxi');" | mysql -h 127.0.0.1 -uroot -pnagiosxi --table

If it outputs the number 2, run the following queries.
Mysql/Mariadb

Code: Select all

echo "SELECT table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema IN ('nagios', 'nagiosql');" | mysql -h 127.0.0.1 -uroot -pnagiosxi --table

Postgres

Code: Select all

echo "SELECT relname as Table, pg_size_pretty(pg_total_relation_size(relid)) As Size, pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as ExternalSize FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;" | psql nagiosxi nagiosxi

To send us your system profile.
Login to the Nagios XI GUI using a web browser
Click the "Admin" > "System Profile" Menu
Click the "Download Profile" button
Save the profile.zip file and share this in a private message and then reply to this post to bring it up in the queue.

[mejokj]

Hi Benjaminsmith,

I have sent you the system profile file privately. Please have a look.

When you increased the max connections, did you follow our guide below?
Nagios XI - MySQL/MariaDB - Max Connections

>> We have followed the same procedure.

Thanks,

[mejokj]

Hi Benjaminsmith,

I am adding few more inputs. When we click on the nagios xi dashboard, the webpage become not responding and the database going to crash

As per mysql logs, the nagios_logentries crashed during this time. Please find the logs below.

Mysql Logs
+++++++++
16:19:59 [ERROR] mysqld: Table './nagios/nagios_logentries' is marked as crashed and last (automatic?) repair failed
200609 16:19:59 [ERROR] mysqld: Table './nagios/nagios_logentries' is marked as crashed and last (automatic?) repair failed
200609 16:20:01 [ERROR] mysqld: Table './nagios/nagios_logentries' is marked as crashed and last (automatic?) repair failed
200609 16:20:17 [ERROR] mysqld: Table './nagios/nagios_logentries' is marked as crashed and last (automatic?) repair failed
200609 16:20:17 [ERROR] mysqld: Table './nagios/nagios_logentries' is marked as crashed and last (automatic?) repair failed

Disk Utilization
++++++++++++

[root@nagiosxi-db nagios]# du -sch nagios_logentries.*
12K nagios_logentries.frm
11G nagios_logentries.MYD
3.2G nagios_logentries.MYI
14G total
[root@nagiosxi-db nagios]#

Thanks

[benjaminsmith]

Hi,

Thanks for your profile. It' would be good to truncate the nagios_logentries table as it has grown quite large. However, I'm not seeing the database log in the profile.

Did you offload the database on this server? By default, the database is set to localhost, but in this case, it's, the database host is set to db, and the profile script is unable to include it.

Try running the following to repair the database:

Code: Select all

### REPAIR DATABASE
mysqlcheck -r -f -uroot -pnagiosxi --all-databases --use_frm

[akwangari]

Hi,

Yes i am using remote db with name db. I have repaired the db table nagios_logentries. But it again crashing.

If we truncate the nagios_logentries table is it loss any data?

[benjaminsmith]

Hello,

Ok, thanks for the clarification. Truncating the log entries table will impact Event Log report length. For most customers, this is acceptable. A few example commands, you'll need to modify these for the offloaded db/password settings

The following command will clear anything older than 6 months.

Code: Select all

mysql -uroot -pnagiosxi -h 127.0.0.1 -B nagios -e 'DELETE FROM nagios_logentries WHERE logentry_time <= (NOW() - INTERVAL 6 MONTH);'

To completely truncate the table:

Code: Select all

mysql -u ndoutils -pn@gweb nagios -e 'TRUNCATE TABLE nagios_logentries'

You can also set the max time to keep this data in the GUI. Go to Admin > Performance Settings > Databases. Take a look a the following guide for detailed explanation of the various database settings.

Nagios XI Database Optimization

[mejokj]

Hello,

If we delete the data older than 6 month nagios_logentries , can we generate report older than 6 month or is it lost the historial data of performance graph older than 6 months. what type of data's are stored inside the nagios_logentries table ?

[benjaminsmith]

Hi,

Yeah, this won't impact performance data coming from the host and services checks. Additionally, it will not affect the Availablity Report as that data is parsed directly from the nagios.log files.

It will impact the Event Log Report, go to Home > Monitoring Process > Event Log to run this report. This data in this report is fetched from this table in the database.

Let me know if you have other questions.

Benjamin