I've my hands on a 3-node Percona XtraDB Cluster where, according to
mysqlcheck, some tables are corrupted (some indexes contain the wrong number of entries):
mydb.mytable Warning : InnoDB: Index 'foo' contains 1393929 entries, should be 1393918. Warning : InnoDB: Index 'bar' contains 1393921 entries, should be 1393918. error : Corrupt
What is the best practice to run
OPTIMIZE TABLE on a cluster?
I've done some experiments in a test environment without users, and it appears that an
OPTIMIZE TABLE on a node does not automatically propagate its effect to the other nodes. This is consistent with the fact that this command modifies the indexes and the table's storage space, not its contents or its definition.
What could be the drawbacks in running the command in a production environment in each node, letting it complete before running it in the following node?
What would be the effect on users, considering that MySQL (and Percona XtraDB Cluster, as far as I know) do not support distributed table locks? Would this leave the cluster in a inconsistent state?