Optimize table is not replicated in XtraDB 5.6

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Hi,
we’re running a small Percona XtraDB cluster version 5.6.15-25.5 with Galera 3.3.5-1, executing all the quety against the same node.
We’re experience issue on “optimize table”: once a day, there’s a script that delete many rows from una table and, after the delete, run an optimize table.
After about two month of life of this infrastructure, we have a difference of 30/40% on table size between node “master” and the others, like the optimize table aren’t replicate over all nodes.

There’s a way to correct this behaviour?

Thanks,
bye

Erik

2

Hi Selyon;

Check your current setting for wsrep_OSU_method, which controls how DDL statements are processed, and may be the culprit here. The default is to have the statements propagated out to all nodes (TOI), but you may have it set to RSU which means you would have to run the statement individually on each node. Both settings have their advantages and disadvantages, so make sure to research it fully and discuss with the necessary people on your team before changing it.

See the below for more details:

[url]http://galeracluster.com/documentation-webpages/schemaupgrades.html[/url]
[url]Index of wsrep system variables

-Scott

3

Hi Scott,
thanks for your suggestion, we have wsrep_OSU_method=TOI on all nodes.
I’ve looking over Percona and Galera documentation and i haven’t find any direct information about this statement…

4

Hi Selyon;

The optimize table runs an ALTER basically, so I thought it would use that variable, but that may not be the case. We’ll have to wait for someone who uses XtraDB Cluster more to help then as I do not use it myself and have limited knowledge in that area.

Will let you know if I happen to find anything useful though. =)

-Scott