crash tables

[gregwhite]

Every few months our logentry table will crash. We run the following commands to fix it

service mysqld stop
cd /var/lib/mysql/nagios
myisamchk -r -f nagios_logentries.MYI
service mysqld start
rm -f /usr/local/nagiosxi/var/dbmaint.lock

Should we be running this on a regular basis (Preventative Maintenance) to avoid that table crashing in the first place?

Thanks,
Greg

[dchurch]

Part of the reason that the nagios_logentries table crashes is because its storage engine is MyISAM. MyISAM does not support data integrity and transactional integrity. Indeed, it does not support any of the features needed for ACID. But it's fast.

If your time is really important to you, and you find yourself spending an inordinate amount of time recovering crashed tables, you could try converting them to InnoDB, which is robust and venerable, and is far less susceptible to "crashed" tables. But... there's a performance hit. If you're fine with that, then it should be okay for you to try it out.

Convert to InnoDB using this command:

Code: Select all

alter table nagios_logentries engine = InnoDB;

More on ACID and MySQL

[gregwhite]

Thank your for you reply.
In lieu of converting to InnoDB, If we did a weekly or monthly repair, commands listed in the post, would that help avoid nagios_logentries table from crashing?

Thanks

[scottwilkerson]

There wouldn't be a benefit to preemptively running the repair. The most common reason a table becomes crashed (about 99.9% in my experience) is the table is locked open for writing and there is a power interruption (server loses power, or in a VM scenario, an unclean shutdown) OR there for some reason is an inability to write to disk.

In either of these scenarios, preemptively running a repair would not prevent the table from crashing if this occurs

[gregwhite]

Thank you. Bear with me for one last question.
The nagios_logentries table seems to be the only table that crashes. Our instance of Nagios is polling 2250 devices and over 13,000 services and our database is hosted remotely on our Fusion server.
A lot of data is being written to the event log. If the number of events becomes excessive, could the event log become so busy that
the table crashes? Is this something to be concerned about? Would the performance hit for converting to lnnodb be worse than a crashed table?

I know, I said one question.
Thanks for your help.
Greg

[scottwilkerson]

Thank you. Bear with me for one last question.
The nagios_logentries table seems to be the only table that crashes. Our instance of Nagios is polling 2250 devices and over 13,000 services and our database is hosted remotely on our Fusion server.
A lot of data is being written to the event log. If the number of events becomes excessive, could the event log become so busy that
the table crashes? Is this something to be concerned about? Would the performance hit for converting to lnnodb be worse than a crashed table?

I know, I said one question.
Thanks for your help.
Greg

This is the table that crashes because this is the table that is being constantly written to.

With that many hosts and services I would actually not recommend changing the table type.

[SteveBeauchemin]

Do you have disk space problems?
The /tmp location may fill and cause problems in an innocent manner you may not notice.

I relocate my DB away from /var/lib which on my systems is tiny. I put it on a large dedicated volume.
I also relocate the temp data.

In my /etc/my.cnf file I use these settings. I have other settings too, but these pertain to possible space issues. I have a very large install so this matters to me and makes a big difference.

datadir=/usr/local/mysql
socket=/usr/local/mysql/mysql.sock
tmpdir=/usr/local/mysqltmp

You need to set the permission and owners properly.
Since doing this, in the last 4 years I may have had one DB issue. maybe...

I also use MySQLTuner-perl to get other suggestions to help make my DB operations run better.
My system has an offloaded database, 3374 host definitions, and 60,000 service tests running.

I hope this helps. You should have NO database problems.

Steve B
Hi Scott!

[scottwilkerson]

Do you have disk space problems?
The /tmp location may fill and cause problems in an innocent manner you may not notice.

I relocate my DB away from /var/lib which on my systems is tiny. I put it on a large dedicated volume.
I also relocate the temp data.

In my /etc/my.cnf file I use these settings. I have other settings too, but these pertain to possible space issues. I have a very large install so this matters to me and makes a big difference.

datadir=/usr/local/mysql
socket=/usr/local/mysql/mysql.sock
tmpdir=/usr/local/mysqltmp

You need to set the permission and owners properly.
Since doing this, in the last 4 years I may have had one DB issue. maybe...

I also use MySQLTuner-perl to get other suggestions to help make my DB operations run better.
My system has an offloaded database, 3374 host definitions, and 60,000 service tests running.

I hope this helps. You should have NO database problems.

Steve B
Hi Scott!

Thanks for the input @SteveBeauchemin!
Hope all is well

[gregwhite]

Thanks to DChurch, Scott and Steve. Picked up some real good information and learned at lot more about tables and the cause of crashing. We will definitely look at Steve's recommendations. I would be interested in how Steve runs 60,000+ services. As I mentioned we are at 13,000 and wonder if we can push it more.

Thanks again,
Greg

[scottwilkerson]

Thanks to DChurch, Scott and Steve. Picked up some real good information and learned at lot more about tables and the cause of crashing. We will definitely look at Steve's recommendations. I would be interested in how Steve runs 60,000+ services. As I mentioned we are at 13,000 and wonder if we can push it more.

Thanks again,
Greg

Glad we could help!