我正在运行一个我预计需要很长时间的查询,尽管我不知道需要多长时间。我发现了,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