If you plan to run a DDL query (OPTIMIZE is) you need to check the queries that are already running there. Let me explain why. Let’s say the table to optimize is called “tickets”. And there is a long running query there, that will need 20 minutes to finish. That SELECT is not blocking other queries, so everything works as expected. When you run OPTIMIZE it will have to wait, since it needs metadata lock to be able to run, and that lock cannot be acquired if there is query accessing it. So, the OPTIMIZE waits to the long SELECT to finish. The problem is that if there is a query waiting for the metadata lock, all the new queries that comes after it will also have to wait, because this is a queue. You have:
- Long SELECT running for 20 minutes.
- OPTIMIZE waiting on metadata lock.
- All other queries that try to access the table will have to wait until OPTIMIZE finishes.
In other words, all your queries trying to access that table will be locked during 20 minutes + the time OPTIMIZE will need to run.
When such queries like OPTIMIZE or ANALYZE are executed, you need to find that there are no other long running queries accessing the same table, to avoid this problem.
Regards.