Less intrusive RSU ALTER method?

General

Escalation

General

Escalation

Description

With current DDL implementation in Galera/wsrep, there is no way even a simple ALTER will be unobtrusive for the live production traffic. Not even using RSU DDL method nor pt-online-schema-change will save us from some unwanted disruptions.

Below, I would like to present a case, where RSU method is usually a good idea, when we want to avoid cluster wide stall during schema compatible DDL, in this case, noop-ALTER (aka optimize table).

-- session1
node1 > select @@wsrep_OSU_method,@@wsrep_on;
+--------------------+------------+
| @@wsrep_OSU_method | @@wsrep_on |
+--------------------+------------+
| RSU                |          1 |
+--------------------+------------+
1 row in set (0.00 sec)

-- session2
node1 > select count(*) from db1.sbtest1 a join db1.sbtest1 b where a.id<10000;
ERROR 1213 (40001): WSREP detected deadlock/conflict and aborted the transaction. Try restarting the transaction

-- session1
node1 >  alter table db1.sbtest1 engine=innodb;
Query OK, 0 rows affected (1.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

As seen above, a noop alter executed in RSU interrupted simple SELECT query. Both sessions running on the same PXC node!

In the error log, we can see more details:

2018-12-04T15:07:03.039712Z 12 [Note] WSREP: Provider paused at 7bf59bb4-996d-11e8-b3b6-8ed02cd38513:471796 (26)
2018-12-04T15:07:03.109785Z 12 [Note] WSREP: --------- CONFLICT DETECTED --------
2018-12-04T15:07:03.109802Z 12 [Note] WSREP: cluster conflict due to high priority abort for threads:
2018-12-04T15:07:03.109805Z 12 [Note] WSREP: Winning thread: 
 THD: 12, mode: total order, state: executing, conflict: no conflict, seqno: -1
 SQL: alter table db1.sbtest1 engine=innodb
2018-12-04T15:07:03.109807Z 12 [Note] WSREP: Victim thread: 
 THD: 11, mode: local, state: executing, conflict: no conflict, seqno: -1
 SQL: select count(*) from db1.sbtest1 a join db1.sbtest1 b where a.id<10000
2018-12-04T15:07:03.109811Z 12 [Note] WSREP: MDL conflict db=db1 table=sbtest1 ticket=MDL_SHARED_READ solved by abort
2018-12-04T15:07:03.109812Z 12 [Note] WSREP: --------- CONFLICT DETECTED --------
2018-12-04T15:07:03.109814Z 12 [Note] WSREP: cluster conflict due to high priority abort for threads:
2018-12-04T15:07:03.109815Z 12 [Note] WSREP: Winning thread: 
 THD: 12, mode: total order, state: executing, conflict: no conflict, seqno: -1
 SQL: alter table db1.sbtest1 engine=innodb
2018-12-04T15:07:03.109817Z 12 [Note] WSREP: Victim thread: 
 THD: 11, mode: local, state: executing, conflict: must abort, seqno: -1
 SQL: select count(*) from db1.sbtest1 a join db1.sbtest1 b where a.id<10000
2018-12-04T15:07:03.959399Z 12 [Note] WSREP: resuming provider at 26
2018-12-04T15:07:03.959418Z 12 [Note] WSREP: Provider resumed.

Even though such alter in normal MySQL would be online, and just wait for MDL lock to execute, here it causes any ongoing transactions to immediately abort.

Theoretically, set wsrep_desync=1; and set wsrep_on=0; before an ALTER would be simialar to what RSU mode offers, but it is not the case. Disabling wsrep_on brings the normal MySQL behavior here:

-- session1
node1 > set global wsrep_desync=1; set wsrep_on=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
node1 > select @@wsrep_OSU_method,@@wsrep_on,@@wsrep_desync;
+--------------------+------------+----------------+
| @@wsrep_OSU_method | @@wsrep_on | @@wsrep_desync |
+--------------------+------------+----------------+
| TOI | 0 | 1 |
+--------------------+------------+----------------+
1 row in set (0.00 sec)

Now, the selects are not disrupted at all:

-- session2
node1 > select count(*) from db1.sbtest1 a join db1.sbtest1 b where a.id<10000;
+-----------+
| count(*) |
+-----------+
| 423680000 |
+-----------+
1 row in set (13.24 sec)

-- session 1
node1 > alter table db1.sbtest1 engine=innodb;
Query OK, 0 rows affected (12.98 sec)
Records: 0 Duplicates: 0 Warnings: 0

-- session 3
node1 > select id,command,time,state,info from information_schema.processlist where user="root";
+----+---------+------+---------------------------------+-----------------------------------------------------------------------------------------+
| id | command | time | state | info |
+----+---------+------+---------------------------------+-----------------------------------------------------------------------------------------+
| 11 | Query | 13 | Sending data | select count(*) from db1.sbtest1 a join db1.sbtest1 b where a.id<10000 |
| 12 | Query | 12 | Waiting for table metadata lock | alter table db1.sbtest1 engine=innodb |
| 15 | Query | 0 | executing | select id,command,time,state,info from information_schema.processlist where user="root" |
+----+---------+------+---------------------------------+-----------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

I think there is no reason why RSU mode should abort local transactions. With known risks, it should allow the schema compatible DDLs to behave just like on standalone MySQL. This way we could get non-blocking DDLs at least for safe ALTERs, like table optimize, and adding/removing secondary indexes.

Environment

None

Smart Checklist

Activity

Julia Vural March 4, 2025 at 9:28 PM

It appears that this issue is no longer being worked on, so we are closing it for housekeeping purposes. If you believe the issue still exists, please open a new ticket after confirming it's present in the latest release.

aristotle.po July 2, 2024 at 6:10 AM
Edited

Please note that in alternative RSU method

node1 > set global wsrep_desync=1; set wsrep_on=0;

We must ensure that there are no updates(wsrep_local_recv_queue = 0) on same table from the other nodes .

This is because wsrep_desync parameter is used to set whether or not the node participates in Flow Control but replicated events will still be applied on the node.

Otherwise we will have below error

node1 [localhost:8001] {msandbox} (test) > SHOW GLOBAL STATUS LIKE 'wsrep_local_recv_queue';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| wsrep_local_recv_queue | 148   |
+------------------------+-------+
1 row in set (0.00 sec)

node1 [localhost:8001] {msandbox} (test) > ALTER TABLE sbtest1 ADD KEY i_c(c);
ERROR 1317 (70100): Query execution was interrupted   

Logfile

totel@nitro:/nfs/sandboxes/pxc/pxc_msb_8_0_33$ tail -18 node1/data/msandbox.err
2024-07-02T13:07:30.016427+08:00 0 [Note] [MY-000000] [Galera] Member 1.0 (nitro) desyncs itself from group
2024-07-02T13:07:30.016473+08:00 0 [Note] [MY-000000] [Galera] Shifting SYNCED -> DONOR/DESYNCED (TO: 63454)
2024-07-02T13:07:30.016553+08:00 61 [Warning] [MY-000000] [WSREP] Toggling wsrep_on to OFF will affect sql_log_bin. Check manual for more
 details
2024-07-02T13:10:48.981233+08:00 61 [Note] [MY-000000] [WSREP] MDL conflict db=test table=sbtest1 ticket=4 solved by abort
2024-07-02T13:10:48.981271+08:00 61 [Note] [MY-000000] [WSREP] MDL conflict db=test table=sbtest1 ticket=4 solved by abort
2024-07-02T13:10:48.981774+08:00 10 [Note] [MY-000000] [WSREP] MDL conflict db=test table=sbtest1 ticket=4 solved by abort
2024-07-02T13:10:48.985750+08:00 2 [Note] [MY-000000] [WSREP] --------- CONFLICT DETECTED --------
2024-07-02T13:10:48.985776+08:00 2 [Note] [MY-000000] [WSREP] cluster conflict due to high priority abort for threads:

2024-07-02T13:10:48.985788+08:00 2 [Note] [MY-000000] [WSREP] Winning thread:
   THD: 2, mode: high priority, state: exec, conflict: executing, seqno: 70430
   SQL: (null)

2024-07-02T13:10:48.985800+08:00 2 [Note] [MY-000000] [WSREP] Victim thread:
   THD: 61, mode: local, state: exec, conflict: aborted, seqno: -1
   SQL: ALTER TABLE sbtest1 ADD KEY i_c(c)

2024-07-02T13:10:48.985811+08:00 2 [Note] [MY-000000] [WSREP] MDL conflict db=test table=sbtest1 ticket=10 solved by abort

Wait until wsrep_local_recv_queue = 0

node1 [localhost:8001] {msandbox} (test) > SHOW GLOBAL STATUS LIKE 'wsrep_local_recv_queue';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| wsrep_local_recv_queue | 0     |
+------------------------+-------+
1 row in set (0.00 sec)

node1 [localhost:8001] {msandbox} (test) > ALTER TABLE sbtest1 ADD KEY i_c(c);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

Aside from wsrep_local_recv_queue = 0, the wsrep_last_applied or wsrep_last_committed should not be changing as a sign that there are no writes on the cluster effectively saying that no remote update is happening on table sbtest1 that can conflict with our ALTER TABLE sbtest1 ....

Won't Do

Details
Assignee
Unassigned
Reporter
Przemyslaw Malkowski
Affects versions
5.6.41-28.28
5.7.23-31.31
Priority
Medium

Smart Checklist

Created December 4, 2018 at 3:23 PM

Updated March 4, 2025 at 9:28 PM

Resolved March 4, 2025 at 9:28 PM

Less intrusive RSU ALTER method?

Description

Environment

Smart Checklist

Activity

Julia Vural March 4, 2025 at 9:28 PM

aristotle.po July 2, 2024 at 6:10 AM
Edited

Details
Assignee
Unassigned
Reporter
Przemyslaw Malkowski
Affects versions
5.6.41-28.28
5.7.23-31.31
Priority
Medium

Details

Assignee

Reporter

Affects versions

Priority

Smart Checklist

Smart Checklist

Flag notifications

Something's gone wrong

Something's gone wrong

Less intrusive RSU ALTER method?

Description

Environment

Smart Checklist

Activity

Julia Vural March 4, 2025 at 9:28 PM

aristotle.po July 2, 2024 at 6:10 AMEdited

DetailsAssigneeUnassignedUnassignedReporterPrzemyslaw MalkowskiPrzemyslaw MalkowskiAffects versions5.6.41-28.285.7.23-31.31PriorityMedium

Details

Assignee

Reporter

Affects versions

Priority

Smart ChecklistOpen Smart Checklist

Smart Checklist

Flag notifications

Something's gone wrong

Something's gone wrong

aristotle.po July 2, 2024 at 6:10 AM
Edited

Details
Assignee
Unassigned
Reporter
Przemyslaw Malkowski
Affects versions
5.6.41-28.28
5.7.23-31.31
Priority
Medium

Smart Checklist