Reports Extremely Slow

[benjaminsmith]

Hello @dbcummings,

Let's see how long the DB maintenance job is taking. Can you post the output of the following command to the thread? Thanks.

Code: Select all

time /usr/bin/php -q /usr/local/nagiosxi/cron/dbmaint.php

[dbcummings]

CREATING: /usr/local/nagiosxi/var/dbmaint.lock
CLEANING ndoutils TABLE 'commenthistory'...
SQL: DELETE FROM nagios_commenthistory WHERE entry_time < FROM_UNIXTIME(1510769519)
CLEANING ndoutils TABLE 'processevents'...
SQL: DELETE FROM nagios_processevents WHERE event_time < FROM_UNIXTIME(1542305519)
CLEANING ndoutils TABLE 'externalcommands'...
SQL: DELETE FROM nagios_externalcommands WHERE entry_time < FROM_UNIXTIME(1573236719)
CLEANING ndoutils TABLE 'logentries'...
SQL: DELETE FROM nagios_logentries WHERE logentry_time < FROM_UNIXTIME(1571249519)
CLEANING ndoutils TABLE 'notifications'...
SQL: DELETE FROM nagios_notifications WHERE start_time < FROM_UNIXTIME(1566065519)
CLEANING ndoutils TABLE 'contactnotifications'...
SQL: DELETE FROM nagios_contactnotifications WHERE start_time < FROM_UNIXTIME(1566065519)
CLEANING ndoutils TABLE 'contactnotificationmethods'...
SQL: DELETE FROM nagios_contactnotificationmethods WHERE start_time < FROM_UNIXTIME(1566065519)
CLEANING ndoutils TABLE 'statehistory'...
SQL: DELETE FROM nagios_statehistory WHERE state_time < FROM_UNIXTIME(1510769519)
CLEANING ndoutils TABLE 'timedevents'...
SQL: DELETE FROM nagios_timedevents WHERE event_time < FROM_UNIXTIME(1573841219)
CLEANING ndoutils TABLE 'systemcommands'...
SQL: DELETE FROM nagios_systemcommands WHERE start_time < FROM_UNIXTIME(1573841219)
CLEANING ndoutils TABLE 'servicechecks'...
SQL: DELETE FROM nagios_servicechecks WHERE start_time < FROM_UNIXTIME(1573841219)
CLEANING ndoutils TABLE 'hostchecks'...
SQL: DELETE FROM nagios_hostchecks WHERE start_time < FROM_UNIXTIME(1573841219)
CLEANING ndoutils TABLE 'eventhandlers'...
SQL: DELETE FROM nagios_eventhandlers WHERE start_time < FROM_UNIXTIME(1573841219)
LASTOPT: 1573841101
INTERVAL: 60
NOW: 1573841519
OPTTIME: 1573844701
CLEANING nagiosxi TABLE 'commands'...
SQL: DELETE FROM xi_commands WHERE processing_time < FROM_UNIXTIME(1573812719) AND status_code = 2
CLEANING nagiosxi TABLE 'events'...
SQL: DELETE FROM xi_events WHERE processing_time < FROM_UNIXTIME(1573812719) AND status_code = 2
CLEANING nagiosxi TABLE 'auth_tokens'...
SQL: DELETE FROM xi_auth_tokens WHERE auth_valid_until < FROM_UNIXTIME(1573755119)
CLEANING nagiosxi TABLE 'cmp_trapdata_log'...
SQL: DELETE FROM xi_cmp_trapdata_log WHERE trapdata_log_datetime < FROM_UNIXTIME(1566065519)
SQL1: SELECT xi_meta.meta_id FROM xi_meta LEFT JOIN xi_events ON xi_meta.metaobj_id=xi_events.event_id WHERE metatype_id='1' AND event_id IS NULL
SQL2: Deleted 7 (DELETE FROM xi_meta WHERE meta_id IN (SELECT xi_meta.meta_id FROM xi_meta LEFT JOIN xi_events ON xi_meta.metaobj_id=xi_events.event_id WHERE metatype_id='1' AND event_id IS NULL))
CLEANING nagiosxi TABLE 'auditlog'...
SQL: DELETE FROM xi_auditlog WHERE log_time < FROM_UNIXTIME(1558289519)
CLEANING nagiosql TABLE 'logbook'...
SQL: DELETE FROM tbl_logbook WHERE time < FROM_UNIXTIME(1573812719)




Repair Complete: Removing Lock File

real 0m4.373s
user 0m0.160s
sys 0m0.037s

[dbcummings]

Just received this alert:

Database Maintenance (dbmaint) stale (660 seconds old)

[dbcummings]

Also, my load spiked at this same time. And, I think optimization was running. None of this seemed to happen until I truncated and tried to run the optimization on the logentries tables yesterday.

[benjaminsmith]

Hello @dbcummings,

So this script runs every 5 minutes or 300 seconds as a cron job but is taking longer at times on your system due to load spikes. When we timed it, it ran in under 5 minutes, but this isn't always the case.

Let's increase the thresholds in the check_nagisoxiserver.php script to allow for more time. Go to line 216 in this file ( it's in the /user/local/nagios/libexec directory).

Code: Select all

 // db maintenance has longer interval
$warn = 360;
$crit = 900;

And change to ( you can adjust this to your requirements):

Code: Select all

 // db maintenance has longer interval
 $warn = 900;
 $crit = 1500;

[dbcummings]

I have made the change.

[benjaminsmith]

Hello @dbcummings,

Great. Just let us know when we're ok to close this post.

[dbcummings]

The instance continued to throws error all weekend. I decided to go ahead and restore it to before we attempted to truncate the table and this all happened. However, I am getting this error during the restore:

ERROR 1050 (42S01) at line 35: Table '`nagiosql`.`tbl_command`' already exists
Error restoring MySQL database 'nagiosql' - check the password in this script!

We are need to getting this server into production within the next two days. So, it is pretty urgent.

[benjaminsmith]

Hello @dbcummings,

We are need to getting this server into production within the next two days. So, it is pretty urgent.

Are you still getting this error after running the repair script?

Anyways, let's move this to a support ticket (reference this post ) for faster resolution. Please attached a fresh copy of the system profile to the support ticket. Thanks.

To open a support ticket:
https://support.nagios.com/tickets/login.php

[dbcummings]

I did not use that script. However, I did do a restore. When it failed at the MySQL portion, I did a manual restore of the Nagios databases using this article. It has been running for 2.5 hours and things appears to be healthy. Anything you want me to send you for verification?