Re: Optimize Table and Flush Table contention on different databases

[Aftab Khan]

mysql> Flush tables;
closes all open tables, forces all tables in use to be closed

For MyISAM tables, OPTIMIZE TABLE works as follows:

1.If the table has deleted or split rows, repair the table.
2.If the index pages are not sorted, sort them.
3.If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.

it is certainly not possible to for both FLUSH TABLE statment and OPTIMIZE TABLE statement to wait for each other. However it is possible if you run OPITMIZE TABLE on a big table and after a while you run FLUSH TABLES command, in this case FLUSH TABLES statement will wait for optimize table command to end.

When this happens, provide us output of this command:

mysql> show full processlist\G