How to run OPTIMIZE TABLE on a 3-node cluster?

Question

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?

Answer 1

If there is a need to run OPTIMIZE TABLE in PXC, you could achieve this by denying its recording into binary logs into one of three(3) ways:

METHOD #1

SET sql_log_bin = 0;
OPTIMIZE TABLE mydb.mytable;
SET sql_log_bin = 1;

METHOD #2

OPTIMIZE NO_WRITE_TO_BINLOG TABLE mydb.mytable;

METHOD #3

OPTIMIZE LOCAL TABLE mydb.mytable;

SUGGESTION

Run the one of the above OPTIMIZE TABLE scenarios on one PXC node at a time while redirecting reads and writes from the cluster. If the data is huge, remove the PXC node from the cluster, run OPTIMIZE TABLE at will, and add the PXC back into the cluster.

Answer 2

On a three node cluster best approach would be to use pt-online-schema-change.

1. make sure table engine is innodb.
2. make sure "Innodb_file_per_table is ON", if it is not ON then space cannot be reclaimed.
3. make sure "wsrep_OSU_method" must be set to TOI, will replicate optimize to other nodes.

Then run following.

pt-online-schema-change --execute --alter "ENGINE=InnoDB" h=localhost,u=root,p=password,D=dbname,t=tablename

Answer 3

The best practice for OPTIMIZE TABLE on any InnoDB system: do not run it. It almost never is worth the effort.

If you must do it in a Galera cluster (such as PXC), treat it as an ALTER: use TOI (for simplicity) if it is small table; RSU (to avoid blocking) if it is big.

Since OPTIMIZE does not change the contents of the table, just the layout, your questions letting it complete, inconsistency are irrelevant.