massive updates cause disk full
Posted: Mon Nov 16, 2020 3:02 am
Hi support,
Last week, we found a Nagios XI server CPU load gets high, disk size decreases fast, suddenly and without a reason.
There's no any significant server changes or env changes, all things just as usual.
And finally found that it is the xi_meta table increasing size extremely fast (1G/min).
At the first moment all we can do is to restart database.
It seems like a tmp file, every restart database would release size, but never resolve issue.
It could not slow down the speed until we TRUNCATE-ed the table
(Found from forum; link: https://support.nagios.com/forum/viewto ... =6&t=46425)
And the root cause seems that database wants to update a table with 17438358 rows in the table "xi_mata".
Please advise how to fix this.
Thank you!
Last week, we found a Nagios XI server CPU load gets high, disk size decreases fast, suddenly and without a reason.
There's no any significant server changes or env changes, all things just as usual.
And finally found that it is the xi_meta table increasing size extremely fast (1G/min).
At the first moment all we can do is to restart database.
It seems like a tmp file, every restart database would release size, but never resolve issue.
It could not slow down the speed until we TRUNCATE-ed the table
Code: Select all
truncate table xi_events; truncate table xi_meta; truncate table xi_eventqueue;And the root cause seems that database wants to update a table with 17438358 rows in the table "xi_mata".
Code: Select all
---TRANSACTION 2BEBA539, ACTIVE 15355 sec fetching rows
mysql tables in use 1, locked 1
1107859 lock struct(s), heap size 98662840, 17438358 row lock(s), undo log entries 1
MySQL thread id 863, OS thread handle 0x7f832dffb700, query id 2875205 localhost nagiosxi Updating
UPDATE xi_meta SET keyvalue='1605194641' WHERE metatype_id='0' AND metaobj_id='0' AND keyname='last_nom_nagioscore_checkpoint'
---TRANSACTION 2BEBA539, ACTIVE 15483 sec fetching rows
mysql tables in use 1, locked 1
1114497 lock struct(s), heap size 99252664, 17545150 row lock(s), undo log entries 1
MySQL thread id 863, OS thread handle 0x7f832dffb700, query id 2875205 localhost nagiosxi Updating
UPDATE xi_meta SET keyvalue='1605194641' WHERE metatype_id='0' AND metaobj_id='0' AND keyname='last_nom_nagioscore_checkpoint'Thank you!