> During a nightly OPTIMIZE TABLE mysql seemed to have got hung up while optimizing the table.
The kid went to to the doctor and said "It hurts when I do this".
The doctor replied, "Then don't do that."
One solution is to avoid the OPTIMIZE. Or to minimize the frequency of the OPTIMIZE, hence to limit the chance of hitting the bug.
Since no one has answered with bug info by now, you should go to bugs.mysql.com, search it. Suggest you use a search engine (not Oracle's) and enter
site:bugs.mysql.com optimize innodb
If you find nothing relevant, post a bug, with a test case.
Since bugs are not fixed in a timely manner, I try to provide workarounds, etc. If you want to pay, contact Percona, SkySql, Oracle, etc.
> I don't think your really in a position to be giving that kind of advice.
I disagree. I have had hands on experience with thousands tables and thousands of performance issues. I can think of only 2 situations that would benefit significantly from OPTIMIZE, and further analysis on them led me to conclude that monthly was good enough. Both involved MyISAM, and the need for the OPTIMIZE would be eliminated by switching to InnoDB.
I often see InnoDB tables that have had a lot of 'churn', hence "free" space and un-full blocks. But generally that leads to only a slight performance degradation, and rarely even 2x bloat in the disk footprint. Those are rarely worth the downtime for the OPTIMIZE. Also, it is clumsy to get InnoDB to give disk space back to the OS.
So, while I cannot say with absolute certainty that a nightly optimize (or even weekly) is "too often", I _can_ say it with a high probability, based a large background of experience.
Please treat _free_ advice more gently!
PS, here's another approach:
CREATE TABLE new LIKE tbl;
INSERT INTO new SELECT * FROM tbl;
RENAME TABLE tbl TO old, new to tbl;
That will have the effect of doing OPTIMIZE, except for failing to prevent writes during the operation.