Nagios Support Forum

Posted: **Thu May 19, 2016 4:13 pm**

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

Posted: **Thu May 19, 2016 4:44 pm**

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.

Posted: **Thu May 19, 2016 4:49 pm**

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

Posted: **Fri May 20, 2016 7:52 am**

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

Posted: **Fri May 20, 2016 7:53 am**

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?

Posted: **Fri May 20, 2016 2:01 pm**

chicjo01 wrote: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.

Posted: **Fri May 20, 2016 2:20 pm**

Thank you for the information.

Posted: **Fri May 20, 2016 3:55 pm**

This issue can be closed.

Posted: **Mon May 23, 2016 10:08 am**

Locking this up!

Nagios Support Forum

OPTIMIZE TABLE causing DB problems

OPTIMIZE TABLE causing DB problems

Re: OPTIMIZE TABLE causing DB problems

Re: OPTIMIZE TABLE causing DB problems

Re: OPTIMIZE TABLE causing DB problems

Re: OPTIMIZE TABLE causing DB problems

Re: OPTIMIZE TABLE causing DB problems

Re: OPTIMIZE TABLE causing DB problems

Re: OPTIMIZE TABLE causing DB problems

Re: OPTIMIZE TABLE causing DB problems