Hi Expert,
I checked in process list there are so many connection which stuck in checking permissions state
mysql>show processlist;
| 12211246 | myuser1 | ...:14919 | itbc | Query | 7861 | checking permissions | CREATE TABLE IF NOT EXISTS zones ( name varchar(255) not null primary key, status varchar
mysql> select count() from mysql.user;
±---------+
| count() |
±---------+
| 41 |
±---------+
1 row in set (0.00 sec)
mysql> select count() from mysql.db;
±---------+
| count() |
±---------+
| 55 |
±---------+
please help me on this.
Hi, these could mean a lot of different things unfortunately. Mostly related to contention issues. Check show engine innodb status to see if anything stands out. Also I suggest installing PMM if you get a chance, having metrics will help you find out where is the problem more effectively.
@@igroene I found in show engine innodb status output
and there are 100 connection state showing wsrep: initiating replication for write set (-1)
—TRANSACTION 8236194995, ACTIVE 37273 sec
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 12201711, OS thread handle 140594681710336, query id 3876413072 hostname myuser1 wsrep: initiating replication for write set (-1)
UPDATE zxc
SET
toAt = ‘2020-12-04 08:04:31.814’,
fromAt = null,
class = null,
goodTypeId = 1,
badTypeId = NULL,
samId = NULL
WHERE
id = 4738276
LIMIT 1
—TRANSACTION 8236194994, ACTIVE 37273 sec
Ok from the output it seems you have some uncomitted transactions from long ago. I suggest dealing with that problem first, it is possible it is related to the checking permissions issue