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

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.

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