patternsqlMinor
MySQL stuck on "wsrep: initiating replication for write set (-1)"
Viewed 0 times
stuckwritereplicationmysqlinitiatingforwsrepset
Problem
I ran into a situation with MySQL recently which I hadn't seen before. We have a Percona cluster with 3 nodes. The master stopped processing queries and the PHP FPM web application we host became unresponsive. When I checked
This was the case on both the primary, where PHP was directing all queries to, as well as one of the two secondaries. Here's the output I saw from
```
mysql> show processlist;
+-------+-------------+--------------------+-----------------+---------+-------+--------------------------------------------------------+--------------------------------------------------------------------------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+-------+-------------+--------------------+-----------------+---------+-------+--------------------------------------------------------+--------------------------------------------------------------------------------------+-----------+---------------+
| 1 | system user | | NULL | Sleep | 2171 | wsrep: committing write set (542480920) | NULL | 0 | 0 |
| 2 | system user | | NULL | Sleep | 17169 | wsrep: aborter idle | NULL | 0 | 0 |
| 4 | system user | | NULL | Sleep | 3250 | wsrep: deleting row for write-set (542480919) | NULL
SHOW PROCESSLIST, MySQL processes were stuck in state: wsrep: initiating replication for write set (-1)This was the case on both the primary, where PHP was directing all queries to, as well as one of the two secondaries. Here's the output I saw from
SHOW PROCESSLIST:```
mysql> show processlist;
+-------+-------------+--------------------+-----------------+---------+-------+--------------------------------------------------------+--------------------------------------------------------------------------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+-------+-------------+--------------------+-----------------+---------+-------+--------------------------------------------------------+--------------------------------------------------------------------------------------+-----------+---------------+
| 1 | system user | | NULL | Sleep | 2171 | wsrep: committing write set (542480920) | NULL | 0 | 0 |
| 2 | system user | | NULL | Sleep | 17169 | wsrep: aborter idle | NULL | 0 | 0 |
| 4 | system user | | NULL | Sleep | 3250 | wsrep: deleting row for write-set (542480919) | NULL
Solution
There seems like total crashes like this may have been due to some bug, probably introduced in 5.7.17 and probably fixed in 5.7.20, ref https://jira.percona.com/browse/PXC-877
I have noticed "blip"s in our systems with lots of queries hanging with the same status message, but the problem resolved itself within a minute or two, so probably not the same bug. It seems this happens with one (or more) of the masters if another master is doing some big write operation. I did indeed take down the whole cluster once to try to clear the backlog - this was a mistake, caused quite some downtime, the more proper fix would have been to redirect the mysql traffic to the one server that wasn't clogged and wait for the others to calm down by themselves.
$ mysqld --version
mysqld Ver 5.7.23-23-57 for Linux on x86_64 (Percona XtraDB Cluster (GPL), Release rel23, Revision f5578f0, WSREP version 31.31, wsrep_31.31)
I have noticed "blip"s in our systems with lots of queries hanging with the same status message, but the problem resolved itself within a minute or two, so probably not the same bug. It seems this happens with one (or more) of the masters if another master is doing some big write operation. I did indeed take down the whole cluster once to try to clear the backlog - this was a mistake, caused quite some downtime, the more proper fix would have been to redirect the mysql traffic to the one server that wasn't clogged and wait for the others to calm down by themselves.
$ mysqld --version
mysqld Ver 5.7.23-23-57 for Linux on x86_64 (Percona XtraDB Cluster (GPL), Release rel23, Revision f5578f0, WSREP version 31.31, wsrep_31.31)
Context
StackExchange Database Administrators Q#175487, answer score: 2
Revisions (0)
No revisions yet.