Last night I did some maintenance work on our production cluster. I wanted to regain some space from a bulk delete operation. I already knew that OPTIMIZE TABLE is not online when using PXC but I tried it on our test system and it finished in a reasonable time. No Problem to do it in the middle of the night.
On the production system there was still some traffic going on. After issuing the command all nodes started rebuilding the table at the same time because TOI mode was set. I have three nodes. I issued the command on node1. Node2 finished first and was fully operational after 8 minutes. Node1 and Node2 showed multiple “waiting for metadata lock” messages. Also on the OPTIMIZE TABLE command. The had the old and the new table in their datadir. Both with the same size. CPU load of MySQL was jumping around between 100 and 300%.
After FC kicked in I killed node1 and node3 to get out of trouble. On restart of node1 and node3 they did a SST.
Out of curiosity I optimized the table again with “OPTIMIZE LOCAL TABLE” one node after the other. This lead to no problems at all. Not even hanging transactions on the node where the process was running. The expected behavior of an online optimize.
Do you have any explanation for this behavior?