7

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?

  • What do you mean by "some indexes contain the wrong number of entries"? If it's that the statistics for the indexes on each nodes show a different number for the number of rows, that's not a problem since those statistics are estimates. OPTIMIZE TABLE is DDL so it does get replicated to the other nodes (usually in order). How are you testing and showing that it's not? – G-Nugget Oct 13 '15 at 16:57
  • For your question #1, I've edited my question to add the output of the mysqlcheck command. For question #2, I've noticed that after fixing a corrupted table on a node, the fix does not propagate to the other nodes. – dr01 Oct 26 '15 at 15:20
3

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.

  • We have observed "Error :Transaction not registered for MySQL 2PC, but transaction is active" in error log after executing above statements with sql_log_bin=0; This is expected and no action required from our side right. I am using 5.7.18 version of PXC. – kasi Aug 1 '18 at 13:57
1

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
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.