Comments - Can't optimize database in cluster?
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
I would recommend setting wsrep_OSU_method to "RSU" before executing the command, so the OPTIMIZE TABLE command does not get replicated to the other nodes. That would help prevent all nodes from being overloaded at the same time. If you want the table to be optimized on all nodes, just execute OPTIMIZE TABLE on each node, one node at a time.
http://galeracluster.com/documentation-webpages/schemaupgrades.html
That's more of a work around than a solution to the problem, unfortunately it won't help since some of the applications also run a optimize job by themself. Which means I can't set the OSU method before the optimization is executed.
It is probably a very bad practice to let the application take care of database maintenance, so if I were you I would disable this, assuming you have control of the source.
Another solution is to use pt-online-schema-change:
https://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html
For mysqldump, make sure you use the --single-transaction option. Omitting this option will use table locks.