Threads stuck in permanent statistics state with innodb_thread_concurrency>0 and RW workload on two nodes
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC |
Fix Committed
|
Undecided
|
Unassigned | ||
5.7 |
Fix Committed
|
Undecided
|
Unassigned |
Bug Description
A PXC node gets completely locked when I execute OLTP workload against two nodes, with concurrency higher then innodb_
Simple SELECT queries enter a state where they NEVER finish while the server is not doing anything and there are no blocking queries.
How to reproduce:
* make a 3 node cluster with recent PXC 5.7, and limit the innodb_
mysql> select @@version,
*******
@@innodb_
1 row in set (0.00 sec)
* prepare sysbench table (I tried both sysbench 0.5 and 1.0):
~# sysbench /usr/share/
* run the test against two nodes in the same time:
1)
sysbench /usr/share/
2)
sysbench /usr/share/
As a result, one test finishes, but other is stuck. One node looks like this:
mysql> show status like 'ws%flow%';
+------
| Variable_name | Value |
+------
| wsrep_flow_
| wsrep_flow_
| wsrep_flow_
| wsrep_flow_
| wsrep_flow_
| wsrep_flow_
| wsrep_flow_
| wsrep_flow_
+------
8 rows in set (0.01 sec)
mysql> show status like 'Threads%';
+------
| Variable_name | Value |
+------
| Threads_cached | 7 |
| Threads_connected | 44 |
| Threads_created | 51 |
| Threads_running | 44 |
+------
4 rows in set (0.00 sec)
mysql> select id,db,command,
+-----+
| id | db | command | time | state | info |
+-----+
| 1 | NULL | Sleep | 404 | wsrep: aborter idle | NULL |
| 2 | NULL | Sleep | 198 | wsrep: committed write set (1332479) | NULL |
| 5 | NULL | Sleep | 198 | wsrep: committed write set (1332480) | NULL |
| 57 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 58 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 59 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 60 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 61 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 62 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 63 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 64 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 65 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 66 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 68 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 69 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 71 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 72 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 73 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 74 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 75 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 76 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 77 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 78 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 79 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 80 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 81 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 82 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 83 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 84 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 85 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 86 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 88 | test | Execute | 206 | Sending data | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ? |
| 89 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 91 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 93 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 94 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 95 | test | Execute | 206 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 96 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 97 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 98 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 99 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 100 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 101 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 103 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 105 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
| 106 | test | Execute | 205 | statistics | SELECT c FROM sbtest1 WHERE id=? |
+-----+
46 rows in set (0.00 sec)
mysql> pager grep 'queries inside InnoDB'
PAGER set to 'grep 'queries inside InnoDB''
mysql> show engine innodb status\G
6 queries inside InnoDB, 44 queries in queue
1 row in set (0.00 sec)
mysql> select * from information_
*******
trx_
trx_
trx_
trx_
trx_last_
trx_adaptive_
trx_adaptive_
trx_autocommit_
1 row in set (0.00 sec)
mysql> pager grep 421623406094864
PAGER set to 'grep 421623406094864'
mysql> show engine innodb status\G
1 row in set (0.00 sec)
Threads don't respond to kill, and the only way to stop the node is kill -9 on pid.
I was NOT able to reproduce the same issue on standalone MySQL nor on semi-sync replication pair.
Changed in percona-xtradb-cluster: | |
status: | New → Confirmed |
commit 74bf8771d7dd639 ee2100ffa578226 3f5a4ab2aa
Author: Krunal Bauskar <email address hidden>
Date: Wed Oct 11 18:46:47 2017 +0530
- PXC#878: Threads stuck in permanent statistics state with innodb_ thread_ concurrency> 0
* Upstream merge accidentally got a wrong code that looks like a copy-paste
mistake.
* When the threads leaves InnoDB it should mark exit from innodb server
concurrency. It was doing exactly opposite