HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
nodeoptimizehowtableruncluster

Problem

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?

Solution

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.

Code Snippets

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

Context

StackExchange Database Administrators Q#117747, answer score: 5

Revisions (0)

No revisions yet.