Re: missing InnoDB (partitioned) tables(s) ?
Posted by:
Rick James
Date: December 10, 2014 07:11PM
Unix, not Windows, correct?
(I'm can't address your specific problem, but here are some other issues...)
Keep in mind that a deleted file (rm) will not free up the space until all processes close the file. This can lead to confusion when trying to compare df, du, ls, etc, to see how much disk space is free.
Using an EVENT (or cron) for periodic maintenance is risky... If the machine is down at the moment when the EVENT/cron is supposed to run, that occurrence will never be run. If the code depends on the script being run every hour without fail, what will happen if an our is missed? You should always write a script to handle whatever it finds.
In particular, I like to run a periodic script at least twice as often as necessary. That way, if it misses, it will soon fix itself. (Note, it is coded to gracefully do nothing when there is nothing to do.)
You have an hourly process triggered every 30 minutes; I hope you are doing as I suggest above.
The procedure for freeing up space...
* Needs to free up extra space, since it is hard to predict how much will be consumed for the next firing.
* Could be fooled by the rm problem I mentioned.
* Should fire out-of-phase relative to the other scripts (eg at :15 and :45 if the others are firing at :00 and :30). This avoids contention, and the rm issue.
Check the size of event_info, slowlog, general log, binlogs, etc. Note that the binlog and relay logs (if you have replication) may fluctuate by 1GB -- this relates to "freeing up extra space".
Consider doing FLUSH LOGS -- some logs get freed faster by doing such.
What is the value of innodb_file_per_table? It should be 1 so that DROP PARTITION will actually give space back to the OS. Data_free is a kludgy way to see if a table were created with file_per_table on versus off.
Is event_info your own table? Is it partitioned? Can your code gracefully do nothing if an INSERT into it fails?