mysql - 需要有关挂起大型 MySQL 事务的帮助

Question

我对这类事情有点陌生，所以我提前为我的无知道歉。我和我的团队正在编写一种无监督的非参数方法来对流元素进行聚类，并将特征向量存储在 MySQL 5.7 数据库中以供以后调用。特征向量被算法分解为多个分量(我们这样做的详细推理对于这个问题来说无关紧要)，导致多个向量存储在数据库中的单个数据表中，该数据表描述了输入的元素。这些向量的大小可能有所不同，但我相信最大的向量的尺寸约为 1x60000。我们将每个特征向量存储在同一个数据表中，以在数据库设计中保留第四范式。

问题是，当我们自动插入该向量时，我们会遇到事务问题，其中锁定持续时间异常长。我相信这可能是一个僵局问题，但似乎无法弄清楚僵局可能在哪里。这些操作不应无限期地相互阻塞(操作顺序:将一般信息插入信息数据表 -> 将组件 1 特征向量插入信息数据表 -> 将组件 2 特征向量插入信息数据表 -> ...)。请注意，即使其中一个事务阻塞，它最终也应该解决，并且下一个事务应该开始。这引出了我的下一个假设，似乎每个单独的事务都太大，导致表锁定很长时间。我听说即使没有逻辑原因导致死锁发生，这也可能会导致问题。以下是我在数据库 docker 容器中运行 SHOW ENGINE INNODB STATUS; 的结果。任何帮助或建议将不胜感激。

其他信息:我们使用 python3.7 和 numpy。每个特征向量都是一个 numpy 数组对象，它被字符串化并作为 LONGBLOB 存储在数据库中。由于底层算法的原因，信息是按顺序存储的，也是为了减少数据库服务器上的负载(我们希望从一开始就避免这个问题)。我们考虑过完全删除向量存储机制，但是，它使以后的许多操作和算法显着更快 - 更不用说更容易编写 - 简单地记忆这些信息，而不是从原始数据库重建向量有关输入项目的特征集的信息(想象一下在每批中重新计算训练集的一个热向量......并不理想)。

------------
TRANSACTIONS
------------
Trx id counter 5848
Purge done for trx's n:o < 5800 undo n:o < 0 state: running but idle
History list length 66
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421274805708992, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805702552, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805704392, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805703472, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805701632, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805699792, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805698872, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805694272, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805693352, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805692432, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805688752, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805687832, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805685072, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805684152, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805683232, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805682312, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805680472, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805678632, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805677712, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805676792, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805675872, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805686912, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805697952, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805697032, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805696112, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805695192, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805691512, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805690592, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805689672, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805685992, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421274805681392, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 5845, ACTIVE 1960 sec
6 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 18460, OS thread handle 139799096174336, query id 110111 172.18.0.17 root
---TRANSACTION 5839, ACTIVE 3374 sec
1 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 18561, OS thread handle 139799094281984, query id 110572 172.18.0.20 root
Trx read view will not see trx with id >= 5840, sees < 5799
---TRANSACTION 5838, ACTIVE 3476 sec
1 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 18540, OS thread handle 139799094552320, query id 108421 172.18.0.20 root
Trx read view will not see trx with id >= 5839, sees < 5799
---TRANSACTION 5837, ACTIVE 3577 sec
1 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 18519, OS thread handle 139799095092992, query id 108310 172.18.0.20 root
Trx read view will not see trx with id >= 5838, sees < 5799
---TRANSACTION 5835, ACTIVE 3628 sec
1 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 18497, OS thread handle 139799094822656, query id 108175 172.18.0.20 root
Trx read view will not see trx with id >= 5835, sees < 5799
---TRANSACTION 5802, ACTIVE 3832 sec
1 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 11, OS thread handle 139799379732224, query id 107906 172.18.0.20 root
Trx read view will not see trx with id >= 5799, sees < 5796
--------
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] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
501 OS file reads, 8143 OS file writes, 4776 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 34673, node heap has 2 buffer(s)
Hash table size 34673, node heap has 2 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 2 buffer(s)
Hash table size 34673, node heap has 2 buffer(s)
Hash table size 34673, node heap has 12 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 44613787
Log flushed up to   44613787
Pages flushed up to 44613787
Last checkpoint at  44613778
0 pending log flushes, 0 pending chkp writes
3412 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 924661
Buffer pool size   8191
Free buffers       5612
Database pages     2556
Old database pages 923
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 410, created 2146, written 3707
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2556, 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
5 read views open inside InnoDB
Process ID=1, Main thread ID=139799528855296, state: sleeping
Number of rows inserted 187012, updated 52, deleted 4, read 102952428
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

以下是我们从自动化载体插入系统中得到的错误。

score-service_1                 | ERROR:root:An error occurred.
score-service_1                 | Traceback (most recent call last):
score-service_1                 |   File "/usr/src/app/src/db/mysql.py", line 103, in execute
score-service_1                 |     self.cursor.execute(sql, parameters)
score-service_1                 |   File "/usr/local/lib/python3.7/site-packages/MySQLdb/cursors.py", line 250, in execute
score-service_1                 |     self.errorhandler(self, exc, value)
score-service_1                 |   File "/usr/local/lib/python3.7/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
score-service_1                 |     raise errorvalue
score-service_1                 |   File "/usr/local/lib/python3.7/site-packages/MySQLdb/cursors.py", line 247, in execute
score-service_1                 |     res = self._query(query)
score-service_1                 |   File "/usr/local/lib/python3.7/site-packages/MySQLdb/cursors.py", line 411, in _query
score-service_1                 |     rowcount = self._do_query(q)
score-service_1                 |   File "/usr/local/lib/python3.7/site-packages/MySQLdb/cursors.py", line 374, in _do_query
score-service_1                 |     db.query(q)
score-service_1                 |   File "/usr/local/lib/python3.7/site-packages/MySQLdb/connections.py", line 277, in query
score-service_1                 |     _mysql.connection.query(self, query)
score-service_1                 | _mysql_exceptions.OperationalError: (1205, 'Lock wait timeout exceeded; try restarting transaction')

似乎没有任何表锁。本质上，我的问题是为什么会发生这种情况？我对为什么会遇到挂起交易的猜测是否正确？我可以使用任何诊断工具或方法来尝试进一步评估问题吗？我个人对 SQL 还是有点陌生​​。

Answer 1

没有开始？它是否在 autocommit=OFF 的情况下运行？整个计划是一笔巨大的交易吗？难怪它超时了。

“事务”应该是需要“原子”运行的一小段数据库操作。也就是说，要么全部成功，要么全部失败，没有中间状态。

通常，事务是通过诸如 BEGIN 或 START TRANSACTION 之类的内容显式启动的(接口(interface)有不同的变体)。

当您在事务中包含大量耗时的客户端代码时，(来自其他客户端的)竞争事务可能会停止等待访问锁定的内容。尽量避免这种情况。

一些设计模式的工作原理如下:

1. get a thing
2. do a lot of time-consuming processing on that thing
3. release that thing

虽然很容易在步骤 1 中使用 BEGIN，在步骤 3 中使用 COMMIT。但是步骤 2 表明这并不明智。相反

1a. BEGIN
1b. Find a thing to work on
1c. store (`UPDATE`) an indication that this client has grabbed the thing
1d. COMMIT
2. do a lot of time-consuming processing on that thing
3a. BEGIN
3b. Release (`UPDATE`) the thing
3c. COMMIT

这种模式更具可扩展性。

可以将步骤 1a-1d 折叠成单个自动提交的语句。步骤 3a-3c 同上。

这样就可以扩展排队任务并避免工作人员互相干扰。并在避免“锁定等待超时”方面取得了长足的进步。