我正在运行一个我预计需要很长时间的查询,尽管我不知道需要多长时间。我发现了,SHOW ENGINE INNODB STATUS
但我不确定它是否真的在做某事,或者它是否卡在某个地方。这是我试图找出的:
系统监视器: 通过查看系统监视器,我注意到 MySQL 进程并没有真正使用太多内存和 CPU。然而,我有 1 个 CPU 连续 100% 使用,所以一些进程正在做某事,尽管列表上没有任何进展似乎对此负责。数据库是 3GB,查询应该访问几乎所有的数据库(2400 万行)。难道是资源没有显示在mysql进程上,而是显示在某个子线程上?
显示引擎 INNODB 状态 \G
这是输出。
SHOW ENGINE INNODB STATUS \G
1. row
Type: InnoDB
Name:
Status:
=====================================
2023-04-23 12:53:09 0x7ff1c440c700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 33 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 98 srv_active, 0 srv_shutdown, 5181 srv_idle
srv_master_thread log flush and writes: 5279
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2305
OS WAIT ARRAY INFO: signal count 2040
RW-shared spins 123, rounds 2059, OS waits 60
RW-excl spins 113, rounds 1833, OS waits 49
RW-sx spins 224, rounds 6342, OS waits 209
Spin rounds per wait: 16.74 RW-shared, 16.22 RW-excl, 28.31 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 9498291
Purge done for trxs n:o < 9498290 undo n:o < 0 state: running
History list length 58
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422151665308056, ACTIVE 1235 sec fetching rows
mysql tables in use 3, locked 3
209503 lock struct(s), heap size 26271864, 26067821 row lock(s)
MySQL thread id 39, OS thread handle 140676356118272, query id 127 localhost root Sending data
UPDATE events
INNER JOIN sessions_view_3
ON events.user_id = sessions_view_3.user_id
AND events.timestamp = sessions_view_3.timestamp
AND events.kind = sessions_view_3.kind
SET events.session_id_3 = sessions_view_3.global_session_id_3
---TRANSACTION 422151665303832, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
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
6622929 OS file reads, 239404 OS file writes, 8494 OS fsyncs
4944.03 reads/s, 16384 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 810, seg size 812, 1 merges
merged operations:
insert 0, delete mark 1, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 468 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
143846.58 hash searches/s, 43181.66 non-hash searches/s
---
LOG
---
Log sequence number 43884780118
Log flushed up to 43884780118
Pages flushed up to 43884780118
Last checkpoint at 43884780109
0 pending log flushes, 0 pending chkp writes
2498 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 167772160
Dictionary memory allocated 41208
Buffer pool size 8027
Free buffers 1
Database pages 5955
Old database pages 2194
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2123, not young 529215348
0.00 youngs/s, 504929.52 non-youngs/s
Pages read 6593275, created 201671, written 202463
4944.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 992 / 1000, young-making rate 0 / 1000 not 772 / 1000
Pages read ahead 21.33/s, evicted without access 21.00/s, Random read ahead 0.00/s
LRU len: 5955, unzip_LRU len: 0
I/O sum[237723]:cur[2426], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1131, Main thread ID=140676039636736, state: sleeping
Number of rows inserted 0, updated 0, deleted 0, read 261938483
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 187028.15 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
查询
查询正在根据也读取整个表的视图的结果更新整个表。我还没有找到更优化的方法。
UPDATE events
INNER JOIN sessions_view_3
ON events.user_id = sessions_view_3.user_id
AND events.timestamp = sessions_view_3.timestamp
AND events.kind = sessions_view_3.kind
SET events.session_id_3 = sessions_view_3.global_session_id_3;
CREATE VIEW sessions_view_3 AS
SELECT user_id, `timestamp`, kind,
SUM(is_new_session) OVER (ORDER BY user_id, `timestamp`) AS global_session_id_3,
SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY `timestamp`) AS user_session_id_3
FROM (
SELECT *,
CASE WHEN
last_event_timestamp IS NULL OR
(page_type != 'theory' AND page_type != 'theory_section' AND exercise_id IS NOT NULL
AND NOT EXISTS(SELECT 1 FROM events e2
WHERE (session_id = e2.session_id
AND exercise_id = e2.exercise_id
AND e2.timestamp < timestamp)
OR (session_id = e2.session_id
AND last_event_exercise_id = e2.exercise_id
AND e2.timestamp > last_event_timestamp)
)
)
THEN 1 ELSE 0 END AS is_new_session
FROM (
SELECT *,
LAG(`timestamp`,1) OVER (PARTITION BY session_id ORDER BY `timestamp`) AS last_event_timestamp,
LAG(`exercise_id`,1) OVER (PARTITION BY session_id ORDER BY `timestamp`) AS last_event_exercise_id
FROM events
) e
) final
;
我的结论
什么看起来不错:
- 事务似乎正在运行并处于活动状态
- 在 ROW OPERATIONS 中,总读取数和读取数/秒不断增加/变化,因此它似乎在做某事
奇怪的是:
- 在行操作中说
state:sleeping
- 在 ROW OPERATIONS 中表示队列中有 0 个查询
- 所有线程都是
waiting for completed aio requests
,这是否意味着没有线程在做任何事情? - 所有表和行都被锁定。自更新以来这是正常的,但我担心更新会锁定视图需要读取的行。虽然我相信 MySQL 应该首先从视图中检索数据,然后再更新。
- 没有进程使用太多内存的事实。我的总内存使用量只有 6GB,其中大约 3 个被 PyCharm 使用
更新:添加top
输出
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1131 mysql 20 0 2222320 389264 22700 S 100,0 1,2 422:15.44 mysqld
关于“状态:睡眠”的行指的是 InnoDB 主线程,而不是运行查询的用户线程。主线程运行一个循环来执行后台任务,例如将页面刷新到磁盘。即使您在其他线程上正在进行查询,睡眠也是一种正常状态。
想一想风笛演奏者,他必须向吹嘴吹气以保持袋子充气,但这与他在管子上演奏的任何音符是异步的。有时演奏者甚至可以在演奏音符时喘口气。
它还在该块的其他几个字段中显示零。
您可能会反复运行 SHOW ENGINE INNODB STATUS?请记住,当您运行该命令时,此状态报告的某些数字会重置为 0。因此,如果您快速连续运行它,则自上次重置以来没有足够的数据来重新计算速率和平均值。所以你有时会得到奇怪的数字。查找状态输出顶部附近的行,例如“从最近 20 秒计算的每秒平均值”。如果持续时间是 0 或 1 秒,我会对数字持保留态度。
即使您没有查询状态,InnoDB 状态监视器也会每 20 秒 (IIRC) 自行重置一次,因此当您查看状态数据时,状态数据的数量可能会有所不同。InnoDB 监控器不像 DataDog 这样的适当监控服务,它会保留数周的状态值。
看起来您的系统很忙。字符串“waiting for completed aio requests”表示它有正在进行的 aio 请求,它正在等待一个完成。
你有很多 I/O 线程被占用并等待 I/O 完成这一事实告诉我它确实很忙。如果线程空闲,它们会说“等待输入/输出请求”。
我有点惊讶你的写入线程也被占用,尽管显示写入 I/O 速率的行显示“0.00 次写入/秒”。
CPU 活动较低是有道理的,因为线程正在等待 I/O。这两种资源通常具有相反的关系。在等待 I/O 完成时,CPU 无事可做。同样,如果 CPU 很忙,则 I/O 很可能已完成。
您可以使用
top
命令(如果您的服务器是 Linux)来查看各个线程,并且您可以启用一个列来显示每个线程当前正在使用哪个 CPU。您还可以打开单个 CPU 状态的显示。我不确定您使用的是 Linux。获得锁的线程仍然可以读写该行。如果子查询将其锁定,则不会发生冲突。换句话说,线程拥有锁,而不是查询。
它可能正在创建大型临时表作为查询的副产品。我猜这个查询有多个临时文件,因为它使用了一个视图,并且还使用了窗口函数。如果临时表超过
tmp_table_size
,它们将被假脱机到磁盘。这也可能是高 I/O 速率的原因。我看到这个
这个索引
events
应该有助于UPDATE
运行得更快:如果表上没有(或不足)索引,这可以解释高 CPU。
至于
SELECT
在VIEW
...我想知道是否
GROUP BY
会比OVER
.这些可能会有所帮助:
您需要所有列 (
*
) 吗?如果没有,请拼出所需的列。请提供`SHOW CREATE TABLE 事件。
由于
last_event_timestamp
计算和使用的方式,最好将其分成SELECT
两部分,然后添加小计。VIEW
自己运行需要多长时间?我有一种预感,这需要很长时间。将Select 写在Exists 中可能会更好。