Performance Issue on Nagios XI server

[tthomas]

Hi All,

We have been facing performance issue on our Nagios XI server. It takes time to load pages in GUI and some times the server crashes.

Truncated output for top command.

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3011 mysql 20 0 8893m 1.2g 3412 S 629.5 3.7 28557:15 /usr/libexec/mysqld
27285 apache 20 0 741m 207m 8540 R 75.5 0.6 4:56.56 /usr/sbin/httpd
27220 apache 20 0 600m 70m 8544 R 71.6 0.2 4:07.77 /usr/sbin/httpd
7875 apache 20 0 652m 115m 8232 R 60.5 0.4 0:40.75 /usr/sbin/httpd
15866 apache 20 0 563m 39m 9248 S 50.7 0.1 10:01.57 /usr/sbin/httpd
27854 apache 20 0 605m 79m 9220 R 45.8 0.2 1:29.18 /usr/sbin/httpd
27855 apache 20 0 593m 68m 8512 R 44.1 0.2 3:01.14 /usr/sbin/httpd
17722 apache 20 0 625m 101m 9224 S 41.9 0.3 7:30.30 /usr/sbin/httpd
4490 apache 20 0 572m 47m 8460 S 41.2 0.1 5:35.63 /usr/sbin/httpd
9429 apache 20 0 623m 98m 8236 R 40.6 0.3 1:57.69 /usr/sbin/httpd
9377 apache 20 0 607m 82m 8484 R 36.0 0.3 1:42.89 /usr/sbin/httpd
16592 apache 20 0 558m 33m 8456 S 34.0 0.1 1:23.25 /usr/sbin/httpd
4769 apache 20 0 608m 82m 9408 S 28.1 0.3 14:43.75 /usr/sbin/httpd
22516 apache 20 0 596m 70m 8500 R 27.1 0.2 4:53.89 /usr/sbin/httpd

Looks like mysql and apache are culprits and we would like to do some tweaking.

We have found a solution from Nagios support that mysql database needs to be repaired in this case. We do not have mysql root password handy. Can we run this script as root without knowing mysql root password.

First, login to your Nagios XI server as the root user.
Next, stop the MySQL database server with the following command:
service mysqld stop
Run the Nagios XI database repair script with the following command:
/usr/local/nagiosxi/scripts/repairmysql.sh nagios
The script will check and repair all tables in the nagios MySQL database. This process may take several minutes to complete,
depending on the size of your database.
Once the script has completed, start the MySQL database server with the following command:
service mysqld start

We have a database of 20G and how much time it can take for this repair to get completed?

Any thing else that should be done on server level or GUI level to fix this performance/slowness issue?

It is a VMware virutal machine with 15 virutal CPUs (model name: Intel(R) Xeon(R) CPU L5640 @ 2.27GHz ) in it. We are monitoring around 800 hosts and 10000 services.

~]# free -m
total used free shared buffers cached
Mem: 32105 23288 8816 0 136 16749

Please suggest.

Regards
Tino

[tgriep]

Can you run the following and post the output here in code wraps?

Code: Select all

du -a /var/lib/mysql/
tail -50 /var/log/mysqld.log
tail -100 /var/log/httpd/error_log
df -h
df -i

[tthomas]

df_output.txt

mysql_error_log.txt

http_error_log.txt

[tmcdonald]

Can we run this script as root without knowing mysql root password.?

I believe you will need to do this as root with the root mysql password, or another user who would have appropriate permissions.

We have a database of 20G and how much time it can take for this repair to get completed?

It's hard to say, really. Depends on how fast your disks are, what your load is, how corrupted the DB is. I've seen it take 5 minutes and I've seen it take 30. Either way, I would run it.

Also, how many users are logged in to XI at once and do you have a ramdisk in place by chance?

[tthomas]

Hi,

tmcdonald wrote:
Also, how many users are logged in to XI at once and do you have a ramdisk in place by chance?

Typically in our environment may be around 5 users logged in, at a time. Is there any way to find the exact count?
Since it is a VM machine, we can add additional RAM if requried. Please note this server is already having 32G.


Regards,
Tino

[tgriep]

The root password is the root password for logging in to the system and not the mysql root password.
As long as that hasn't been changed, the mysql database repair will be work for you.
Once it is repaired, we can work on truncating some of the mysql tables.
The logentries and the statehistory could be cleaned out to save some space and gain performance.

To see who is logged in to a system, in a shell, just type a w to see who is logged in to the system.

[tthomas]

Hi tgriep,

Thank you for the update.

Yes, I am aware about server root password and mysql root password. I was referring to mysql root password only.
I do not have it handy.

Code: Select all

root@ ~]## mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[root@ ~]# mysql -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

But I can see that nagios user can login to mysql without password.

Code: Select all

]# su - nagios
[nagios@ ~]$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1003010
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> Bye

1) Can I run the repair command as nagios user?
2) If the repair requires mysql root password , can i reset it?
3) I am aware that you need to stop mysql to reset it, but resetting mysql root password will affect anything in nagios?

Regards
Tino

[jdalrymple]

tthomas,

I'm not sure if we're on the same page with you. We have included a script in your NagiosXI installation:

Code: Select all

/usr/local/nagiosxi/scripts/repair_databases.sh

If you run this script as they root user you will not need the mysql root password.

Direct answers to your questions:

1) no
2) follow the standard mysql root password reset procedure, will require Nagios outage
3) shouldn't - we don't use the mysql root user to connect to the databases

All of the above answers assume that you have not modified the mysql credentials from what they were set by the Nagios installer. If you have, things get complicated.

[tthomas]

Once it is repaired, we can work on truncating some of the mysql tables.
The logentries and the statehistory could be cleaned out to save some space and gain performance.

Could you please let me know how this can be achieved.

Regards
Tino

[tgriep]

The bottom of this document has examples on how to truncate tables.
https://assets.nagios.com/downloads/nag ... tabase.pdf
Truncating the tables will remove all of the data in those tables and that will be reflected in the history if you run reports.