There is a problem concerning large ALTER or for example adding the index - the whole database comes to pre-commit stage and until the request is executed it will remain in this stage. This means that all other databases which are on the same server can not process requests and are waiting for that request to be executed. Is there any parameter that would allow us not to block the database to which the request does not apply?
I.E. In Db1 ALTER is processed while DB2 can normally handle requests and is not waiting in queue.
You can try 3 methods:
Method 1
execute on each node until all nodes are altered:
set wsrep_osu_method=‘RSU’;
set wsrep_osu_method=‘TOI’;
Method 2
shutdown one node
start the node without wsrep plugin
run the alter
restart the node and let it join the cluster
do the same on other nodes
Method 3
Use pt-online-schema-change https://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html
For 1 and 2, direct all apps to the nodes that aren’t running the alter.
On Percona XtraDB Cluster (PXC) using RSU method or just pt-online-schema-change is better option. This is handy blogpost https://www.percona.com/blog/2015/10/09/online-ddl-percona-xtradb-cluster-5-6/
Thank you!