如何在TRUNCATE TABLE上解决MySQL Innodb“等待表元数据锁定”?

Devy:

在GitLab CI服务器上运行包含数百个应用程序单元测试的测试套件。在进行了10次测试后,它总是卡在TRUNCATE TABLE上,等待表元数据锁定,这是一个拆卸步骤。

我知道SHOW ENGINE INNODB STATUS命令。以下是一些诊断日志:

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.6.30, for Linux (x86_64) using  EditLine wrapper

Connection id:      190
Current database:   
Current user:       root@localhost
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.6.30 MySQL Community Server (GPL)
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:        /var/run/mysqld/mysqld.sock
Uptime:         51 min 28 sec

Threads: 4  Questions: 3859  Slow queries: 0  Opens: 715  Flush tables: 1  Open tables: 131  Queries per second avg: 1.249
--------------

mysql> show processlist;
+-----+------+----------------+------------+---------+------+---------------------------------+-----------------------------+
| Id  | User | Host           | db         | Command | Time | State                           | Info                        |
+-----+------+----------------+------------+---------+------+---------------------------------+-----------------------------+
|   1 | root | 10.0.2.1:52773 | test_3926  | Query   | 2961 | Waiting for table metadata lock | TRUNCATE TABLE `capability` |
| 188 | root | 10.0.2.1:53658 | test_3926  | Sleep   | 2962 |                                 | NULL                        |
| 189 | root | 10.0.2.1:53660 | test_3926  | Sleep   | 2962 |                                 | NULL                        |
| 190 | root | localhost      | NULL       | Query   |    0 | init                            | show processlist            |
+-----+------+----------------+------------+---------+------+---------------------------------+-----------------------------+
4 rows in set (0.00 sec)


2016-05-18 16:10:37 7f03be9ba700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 7 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 126 srv_active, 0 srv_shutdown, 3047 srv_idle
srv_master_thread log flush and writes: 3173
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2408
OS WAIT ARRAY INFO: signal count 2525
Mutex spin waits 988, rounds 24557, OS waits 747
RW-shared spins 1339, rounds 45580, OS waits 1518
RW-excl spins 3, rounds 5283, OS waits 113
Spin rounds per wait: 24.86 mutex, 34.04 RW-shared, 1761.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 7574
Purge done for trx's n:o < 7493 undo n:o < 0 state: running but idle
History list length 778
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 190, OS thread handle 0x7f03be9ba700, query id 3941 localhost root init
SHOW ENGINE INNODB STATUS
---TRANSACTION 7489, not started
MySQL thread id 188, OS thread handle 0x7f03bea3c700, query id 3824 10.0.2.1 root cleaning up
---TRANSACTION 7548, not started
MySQL thread id 1, OS thread handle 0x7f03bea7d700, query id 3855 10.0.2.1 root Waiting for table metadata lock
TRUNCATE TABLE `capability`
---TRANSACTION 7490, ACTIVE 3047 sec
MySQL thread id 189, OS thread handle 0x7f03be9fb700, query id 3840 10.0.2.1 root cleaning up
Trx read view will not see trx with id >= 7491, sees < 7491
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
173 OS file reads, 6858 OS file writes, 6022 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 276671, node heap has 2 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 10549488
Log flushed up to   10549488
Pages flushed up to 10549488
Last checkpoint at  10549488
0 pending log writes, 0 pending chkp writes
2555 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 545426
Buffer pool size   8191
Free buffers       7354
Database pages     835
Old database pages 288
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4257, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 160, created 4341, written 863
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 835, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 1, id 139654053570304, state: sleeping
Number of rows inserted 1187, updated 37, deleted 0, read 650
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT

现在我的问题是,为什么TRUNCATE表会卡在表metalock上?以及如何解决?

迈克尔-sqlbot:

这里的问题似乎很简单。

---TRANSACTION 7490, ACTIVE 3047 sec
MySQL thread id 189, OS thread handle 0x7f03be9fb700, query id 3840 10.0.2.1 root cleaning up
Trx read view will not see trx with id >= 7491, sees < 7491
---

线程189(客户端连接)处于空闲状态,并且已经有一段时间了,但是它使事务处于运行状态。这可能是使用数据库的代码中的一个错误,因为将正在运行的事务运行近一个小时没有意义。

mysql> KILL 189;

那应该释放元数据锁定...但是您需要找出发生这种情况的原因。如果应用程序的运行状况不尽如人意,则将发生Bad Things™。

另外...您的应用程序不应以方式连接root如果这就是问题,则与问题无关,但不好。

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章

MariaDB InnoDB表:如何查找导致“等待表元数据锁定”的语句

MySQL Dump等待表元数据锁定

什么是Mysql表元数据锁定等待队列排序

如何找到哪个事务导致“等待表元数据锁定”状态?

如何在MySQL InnoDB表中的行上放置长期锁定?

如何在Django模板上解决从字符串到数据的转换问题

如何在Laravel上解决从Data Excel到MySQL的更改值?

如何在UnityDecoration上解决此问题?

如何在Weka上解决此错误?

如何在 SonarCloud 上解决此问题?

如何在Laragon上解决phpmyadmin 403?

如果未启用每个文件的innodb,如何在mysql 5.6中查找innodb表的数据长度和索引长度

找出什么锁定了MySQL InnoDB表行

如何解决InnoDB:无法锁定./ibdata1 mysql错误?

如何在旧的移动设备上解决过期的Sectigo证书?

如何在Python上解决“ ImportError:DLL加载失败:”?

如何在Linux来宾系统上解决DNS问题

如何在本地系统上解决Cors问题?

如何在登录页面上解决此设计错误?

如何在Ruby上解决未定义的方法“ []”?

如何在TikZ上解决此标签定位问题?

如何在命令行上解决此新错误?

如何在网络上解决编码问题

我如何在 Android Studio 上解决这个问题?

如何在不锁定MYSQL 6.2中的表的情况下从另一个表复制数据?

如何在Sails.js上获取mysql表的元数据

如何在MySQL 5.5.x的单独表中基于元数据对主表进行排序?

如何在mysql中加载数据来解决此错误?

如何在vuejs或任何其他解决方案上解决此数组问题?