Reports Extremely Slow

[dbcummings]

Ok. I did the truncation. However, it is still running those reports very slow. I did set the retention to 30 days. The table is still the same size. Do we need to optimize it to reduce the physical table size?

EDIT: I ran the repair database script. I noticed the table resized after that.

AnotherEDIT: It appears that the slowest reports are the Availability and the Executive Summary reports. Event Log and State History are reasonable. SLA report is slowish but ok. Still testing others.

[benjaminsmith]

Hello @dbcummings,

Good to hear. Approximately, how long is the Availablity Report taking and for what time period? How much faster is it when you reduce the period? Please let me know.

[dbcummings]

I set it for Today, a single host, and CPU Usage. It took 3 minutes to run.

I set it for 24 hours with the same config and it took 4 minutes to run.

I set it for this week with the same config and it took 7 minutes.

[benjaminsmith]

Hello @dbcummings,

For the Availability Report, those numbers are typical for a large XI instance like this one. The good news is that we have been working to improve the performance and this will likely be included in the next minor release of Nagios XI. The changes will require updates to both XI and Core ( done by the upgrade script).

[dbcummings]

Ok. This is helpful. Thank you. I sent you a PM as well.

[dbcummings]

Thanks for all the help. During the database work, I tried to optimize the table for logentries and it was corrupted when I cancelled it. I ran database repair and that seemed to work. However, I was receiving alerts from Nagios all night on stale records from dmaint. I deleted the dmaint.lock file as I saw in the forum but I am still getting occasional alerts.

I tailed the log for dbmaint. I notice this step is taking a long time.

OPTIMIZING NDOUTILS TABLE: nagios_logentries
SQL: OPTIMIZE TABLE nagios_logentries

I am wondering if this is holding the lock file open and it is overrunning into the next dbmaint job causing the alert to be triggered and then recover. That is the table I corrupted and repaired yesterday.

It is possible to drop and recreate the logentries table since it still may be corrupted?

[benjaminsmith]

Hello @dbcummings,

You shouldn't need to drop and recreate the table. Try running the following steps:

1. Stop Cron
2. Truncate the table
3. Run the Database Repair Script
4. Restart Cron

Let us know if you're able to get it resolved.

[dbcummings]

Ok. I completed those steps. I will watch the alerts moving forward. Let's keep this post open for a few days until I confirm we are good.

[benjaminsmith]

Hi @dbcummings,

Let's keep this post open for a few days until I confirm we are good.

No problem. We'll keep this open.

[dbcummings]

This is still happening while the dbmain does optimization:

OPTIMIZING NDOUTILS TABLE: nagios_logentries
SQL: OPTIMIZE TABLE nagios_logentries
LOCKFILE '/usr/local/nagiosxi/var/dbmaint.lock' EXISTS - EXITING!

It does complete after that, but I am wondering if this is what is leading to the stale records.