OPTIMIZE TABLE causing DB problems

[chicjo01]

Our DBA Team have identified dead locks happened with the nagios_logentries table and it appears that the OPTIMIZE Table is the cause. Can you provide more detail how often the Nagios software is calling optimize tables? Is this part of a script or a certain mechanic? If so is there any way to turn it off?

8181264 system user <Nagios Server 1> Connect 1861 altering table OPTIMIZE TABLE nagios_logentries 0 0

8210730 ndoutils 10.176.30.7:53778 <Nagios Server 2> Query 58 Waiting for table metadata lock INSERT INTO nagios_logentries SET instance_id
='1', logentry_time=FROM_UNIXTIME(1463661910), entry_ti 0 0

8211099 ndoutils 10.176.30.7:35863 <Nagios Server 2> Query 64 altering table OPTIMIZE TABLE nagios_logentries 0 0

8211132 ndoutils 10.176.30.7:54833 <Nagios Server 2> Query 64 Waiting for table metadata lock OPTIMIZE TABLE nagios_logentries 0 -0

[ssax]

This can happen when the nagios_logentries tables gets really large, you can adjust how often it optimizes under Admin > Performance Settings > Databases > Optimize Interval.

You should also look at adjusting the Max Log Entries Age setting on that page.

[ssax]

Also, check the size of it:

Code: Select all

echo "SELECT table_name AS table_name, round(((data_length + index_length) / 1024 / 1024), 2) Size_in_MB FROM information_schema.TABLES WHERE table_schema = 'nagios' AND table_name = 'nagios_logentries';" | mysql -uroot -pnagiosxi

[chicjo01]

Will the "Performance Settings" work if the Database is offloaded and Nagios XI is used a VIP/Load Balancer to the databases?

[chicjo01]

Additionally, Would the value of 0 mean it does not run or does it mean ever minute? If I wanted to disable it completely and let MySQL do it on it own, how would I do that?

[ssax]

Will the "Performance Settings" work if the Database is offloaded and Nagios XI is used a VIP/Load Balancer to the databases?

Yes

Also, setting the optimize interval to 0 will disable the optimization.

[chicjo01]

Thank you for the information.

[chicjo01]

This issue can be closed.

[mcapra]

Locking this up!