Error while optimize table

MySQL & MariaDB Other MySQL® Questions
1

Hi,

I have a innodb table with around 60000000 of rows, when i execute optimize table, i obtain the next error:

±----------------------±---------±---------±------------ -------------------------------------------+
| Table | Op | Msg_type | Msg_text |
±----------------------±---------±---------±------------ -------------------------------------------+
| table | optimize | error | Lock wait timeout exceeded; try restarting transaction |
| table | optimize | status | Operation failed |
±----------------------±---------±---------±------------ -------------------------------------------+
2 rows in set, 1 warning (23 min 56.01 sec)

While the optimize was executing some other transactions try to insert in the table and they became locked because table was in use.

is there any variable to tuning to avoid this problem?

Thanks for your help.

2

optimize operation needs a lock on table, so you cant optimize tables in real time application.
You can setup a slave and execute optimization on slave and promote optimized slave as master.

3

We were experiencing an issue with one of our tables where every update resulted in a lock wait timeout. One option we tried in order to resolve this was to optimize the table, however this also resulted in a lock wait time out despite no other operations being made on the table at the same time.

My next attempts where to flush and check the table, which seemed fine, and may have resolved the issue but we went straight on to create a copy of the table, via dump and import and rename the tables effectively replacing the old one with the new one.

This seems to have resolved the issue for the time being and updates are being made in a timely fashion again.

version: 5.0.85-community
tx_isolation read committed