MySQL Forums
Forum List  »  InnoDB

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).

Options: ReplyQuote


Subject
Views
Written By
Posted
2563
December 10, 2014 02:46AM
Re: missing InnoDB (partitioned) tables(s) ?
1277
December 12, 2014 01:50AM
1165
December 14, 2014 04:59PM
2079
December 15, 2014 06:31PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.