Wsrep: deleting row for write-set

MySQL & MariaDB Percona XtraDB Cluster 8.x
1

I have a multi master cluster but I am only writing to node 1. Should I see wsrep: deleting row for write-set on node 2? I do however use stored procedures that insert and delete to temp tables, could that cause the wsrep statements?

2 Likes
2

Do you see this message in the pricesslist? If this is a reader note this is absolutely OK, because status indicates that write from the writing node received and read-only node is applying it. See also this post: Improved wsrep-stages and related instrumentation in Percona XtraDB Cluster - Percona Database Performance Blog

2 Likes
3

yes it was in the processlist however it was blocking other transactions.
From SHOW ENGINE innodb status I get the below. And the delete row was blocking other transactions.

---TRANSACTION 421192568866568, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 824034113, ACTIVE 1891 sec updating or deleting
mysql tables in use 1, locked 1
37315 lock struct(s), heap size 8069240, 8015767 row lock(s), undo log entries 8007988
MySQL thread id 16, OS thread handle 139717569754880, query id 94171565 wsrep: deleting row for write-set (1123372)

More from processlist.

system user| |NULL|Query|4533 |wsrep: committed write set (1123370)|NULL|1959074|0|0|
system user| |garage|Query|4597|wsrep: deleting row for write-set (1123372) |NULL|4|0|0|
system user| |NULL|Query|4639|wsrep: committed write set (1123356)|NULL|1982240|0|0|
system user| |NULL|Query|4642|wsrep: committed write set (1123354)|NULL|1982555|0|0|
root|localhost:34162|datastore|Query|4439|wsrep: initiating TOI for write set (-1)|drop table if exists file_search|4438597|0|0|
root|localhost:35530|datastore|Query|4388|wsrep: initiating TOI for write set (-1)|drop table if exists file_search|4387752|0|0|
root|localhost:36464|datastore|Query|4415|wsrep: initiating TOI for write set (-1)|drop table if exists file_search|4414853 |0|0|
root|localhost:47886|datastore|Query|4434|wsrep: initiating TOI for write set (-1)|drop table if exists file_search|4433324|0|0|
2 Likes
4

Do you see this on the read-only node?

2 Likes
5

Yes those came from the read only node. Technically someone can write to it, but no one is. Because I control all of the scripts.

2 Likes
6

In this case, this is absolutely OK. This status means that the node received an update from the writer and applies it.

To find out why this particular DELETE operation takes so long time you need to investigate which exact changes your writer node sent to its peers.

1 Like
7

Where can I find that information at? Is there a table that catalogs that information. I have a lot of DML statements so it would be almost impossible without shutting down all crons and enabling them a day at a time.

1 Like
8

You can check binary logs or general query log on the writer node if enabled.

1 Like