Proper way to perform schema upgrade when node1 is master, node2 started with bootstrap service - MySQL 8

MySQL & MariaDB Percona XtraDB Cluster 8.x
1

In the last application upgrade, we learned the hard way that the best approach for executing ALTER queries (schema upgrades) is to shut down the two replication nodes, perform the ALTER query on the master node, then sequentially restart and sync the replication nodes. This method has proven effective.

Currently, I have a new application upgrade that includes a schema change, and I need to clarify the best approach for performing this upgrade. The current setup is as follows:

  • Node1 (master) started with systemctl start mysql
  • Node2 (replication node) was previously started with mysql@bootstrap
  • Node3 (replication node) started with systemctl start mysql

Given this configuration, I am concerned about the proper procedure for the schema upgrade. My plan is to:

  1. Stop Node2 using mysql@bootstrap stop
  2. Stop Node3 using systemctl stop mysql
  3. Perform the ALTER query on Node1 (master)
  4. Restart Node2 using systemctl start mysql and wait for it to sync and join the cluster
  5. Restart Node3 using systemctl start mysql and wait for it to sync and join the cluster

Throughout this process, the application will remain stopped. My main concern is the previous use of mysql@bootstrap for Node2. Is this the correct and best approach for handling the schema upgrade in this scenario?

Thank you for your assistance.

2

Hello @steve.original,
The main issue you are facing is wsrep_osu_method=TOI which is the default. This locks/blocks all activity on your PXC during the ALTER. This is expected behavior. Your current process is quite drastic. We can improve this.

  1. Use pt-online-schema-change to make schema changes. This will make the schema changes without blocking/locking up the cluster.
  2. Use ‘rolling schema upgrades’. Set wsrep_osu_method=RSU on node3. Alter table on node3. After that is done. Repeat for node2. After done, repeat for node1. Your schema change MUST BE DONE before application code is rolled out.
  3. Try wsrep_osu_method=NBO which is a new feature we introduced to help lessen TOI blocks.
1 Like
3

Ok, tnx.

You are right related to default setting for wsrep_osu_method.

I have worked with pt-online-schema-change before so I am familiar with that.

But related to point 2 from your reply, after i perform upgrade with RSU, rolling schema upgrades on all three nodes, should i return wsrep_osu_method=TOI or leave it on RSU? If I should return to TOI, when to do that?

4

SET wsrep_osu_method=RSU will only change the variable for the current session; it will not change it server-wide.

5

Curious but is wsrep_osu_method=NBO only available in enterprise subscription-based licenses? or has it transitioned to the free opened-sourced versions?

6

There is no such thing as “enterprise” or “subscription-based licenses” with any Percona software :slight_smile: All of our software is 100% free and open-source.

7

Yes, this is working very well now. Tnx.

Also what is best way to optimize table in same case? I have tables that are aprox. 20GB large.
Percona Toolkit? Should app be down during that time?

8

That isn’t a very large table, in my opinion. Optimizing a table is used to remove/reclaim empty space within the table. You might reclaim a few hundred MBs, as InnoDB will always reuse already allocated pages before requesting new ones. I wouldn’t worry about it if I was your DBA. :slight_smile: