Re: missing InnoDB (partitioned) tables(s) ?
Posted by:
Miko M
Date: December 12, 2014 01:50AM
Hi Rick,
Many thanks for your feedback. Please find my answers inline.
-------------------------------------------------------
> Unix, not Windows, correct?
[Miko] Yes, it is ;-)
> (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.
[Miko] Very true!
> 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.
[Miko] Very good point. The stored procedure that checks the usage thresholds (and drops the oldest partition(s) as soon as the configurable thresholds are reached) is triggered every 5 min whereas the stored procedure that creates new partitions ahead (configurable value) and drops the oldest one (according to the configurable retention period) is triggered every 30 min.
> 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.)
[Miko] This is exactly how I've coded it and configured to be triggered. The partitioning process works very well and it is fully automated.
> You have an hourly process triggered every 30
> minutes; I hope you are doing as I suggest above.
[Miko] Yes: every 30 min (partition maintenance) and every 5 min ("emergency" partition deletion in case of space problem). And finally, more then one partition are created ahead (I've configured 12, which seems to be even a little bit too much)
> 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.
[Miko] As I've mentioned every (partitioned) table has been configured with its own capacity threshold (these values are stored in another - management table).
None of the events (especially the ones that run the partitioning procedures) are configured to run at the same time. There is at least 1 min. margin.
> 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.
[Miko] I've checked all the logs available, no clue why these .par and .frm files have been deleted.
> 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.
[Miko] Yes, it is 1. Per my understanding dropping a partition (alter table xxx drop partition yyy) triggers the .par file(s) changing along with dropping/recreating the .frm file.
With no space available on a disk these activities might fail (that is the only explanation that comes to my mind).
> Is event_info your own table? Is it partitioned?
> Can your code gracefully do nothing if an INSERT
> into it fails?
[Miko] Yes, it is my table. Yes it is partitioned. I cannot code anything if INSERT into fails since this table is populated from 3rd party (JDBC).