MySQL Locks Each Night

[mikew]

Once the socket goes stale it will not write to the database and will not allow any checks to occur.

This event happened before the backup process which worked but the database never recovered.

MySQL process remains very high 100% + until restarted.

The night before this even happened at about 7:40.

Mod-Gearman is running on the server with a mod-gearman worker. When the database stops all the gearman workers are orphaned.

Nagios XI 3.2
RHEL 5.x 64-bit
8 Cores, 16 GB RAM
new install with about 100 hosts/600 services

Code: Select all

2012-08-09T09:54:37.264767-04:00 id-nag01 ndo2db: Error: mysql_query() failed for 'DELETE FROM nagios_timedevents WHERE instance_id='1' AND scheduled_
time<FROM_UNIXTIME(1344433766)'
2012-08-09T09:54:37.264785-04:00 id-nag01 ndo2db: mysql_error: 'Server shutdown in progress'

2012-08-09T09:54:42.457087-04:00 id-nag01 ndo2db: Error: Could not connect to MySQL database: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

2012-08-09T09:54:37.267184-04:00 id-nag01 ndo2db: mysql_error: 'Server shutdown in progress'
2012-08-09T09:54:37.267334-04:00 id-nag01 ndo2db: Error: mysql_query() failed for 'DELETE FROM nagios_systemcommands WHERE instance_id='1' AND start_t
ime<FROM_UNIXTIME(1343915366)'
2012-08-09T09:54:37.267341-04:00 id-nag01 ndo2db: mysql_error: 'Lost connection to MySQL server during query'
2012-08-09T09:54:37.267346-04:00 id-nag01 ndo2db: Error: Connection to MySQL database has been lost!

[scottwilkerson]

Mike,

Can you look to see what mysql package this server is running.

Code: Select all

yum list installed |grep mysql

We have a non-nagios server that also seems to have this exact behavior that we have been trying to find the root cause for months... CPU would go to 100+% and stay there until the service is restarted, I am starting to think it may be a mysql bug.

[mikew]

Here is the MySQL version.

mysql.x86_64 5.0.77-4.el5_5.4 installed
mysql-devel.x86_64 5.0.77-4.el5_5.4 installed
mysql-server.x86_64 5.0.77-4.el5_5.4 installed
php-mysql.x86_64 5.1.6-27.el5_5.3 installed

[yancy]

Mike,

Could there be a file permissions issue with mysql.sock?

Code: Select all

ll /var/lib/mysql/mysql.sock

Regards,

-Yancy

[mikew]

I do not think it is the socket. We have cleaned up Warnings for "duplicates" and Warnings for minor issues which seems to have resolved the issue.

I did see others discussing issues with a MySQL error on other sites but their only solution was modification of tables which I assume is a problem in itself.

At this point we are watching the database closely to see if it is really resolved. I will report back if this is a solution.

[mguthrie]

I did see something like this on a much larger install where mysql was getting flooded each night at midnight because of Core's log rotation, but this was on a system well over a thousand hosts. Could there be anything that's bombing mysql with a large amount of queries when it's crashing?

[mikew]

We considered log rotation but the times that the MySQL chocked was 7 PM and 10 PM, not the times that the logs would be rotating.

Nothing else was happening on the box.

The last 3 nights, since the cleanup all has been fine. Currently running 4000 checks,some of them every 10 seconds.

[mguthrie]

Whoa, that's a different story. That's a high frequency of checks, and a lot of information to pass through the kernel message queue in additional to SQL. Do you get anything in the system log around that time related to ndo and "queue send error" or "queue init error"? If so it may require you tune the kernel:
http://support.nagios.com/wiki/index.ph ... 3.x_Issues

Any fork errors or revealing information showing up in the nagios.log around those times?

Here's a couple of ideas you can snoop for:
- Check the Admin->Performance Settings->database(tab), and see if there could be a relationships between one of the database maintenance runs and the SQL crash. With checks happening that often, I'm sure our queries aren't tuned for 10 second check intervals.

- You could try turning off sending of ndoutils log data, which will render the Nagios XI Event Log page useless, but it could reveal if that's the data causing the issue.
ndomod.cfg

Code: Select all

##send everything to DB except log data
data_processing_options=67108859