mysql - 带有 MySQL 的 AWS EC2 未使用可用资源附近的任何地方（Big INSERT..SELECT）
问题描述
我有一个安装了 MySql 8.0.22 的 EC2 (r5.2Xlarge)，并且有 2 个模式，每个模式大约 1.3 TB。表包含 3 亿到 20 亿行。对于磁盘，我使用具有 16,000 IOPS 和 1000 MB/s 的 GP3。所以我认为这很好。 所有表都是 InnoDB。
现在我运行一个查询，我将 830,000,000 行从表 1 复制到表 2，大约需要 5 天才能完成。这真是慢得令人难以置信。
我运行 插入 table2 的查询 (id,value_id) 从 table1 中选择 id,value_id;
此查询以每秒 8000 次插入开始，但几分钟后平均下降到 2500 到 3000 次。
我可以在 AWS 性能监控中看到，cpu 大约为 5%，读取 IO 大约为 20，写入大约 4000。写入性能徘徊在 100mb/s 左右（见下图）。这对我来说是一个迹象，表明我没有最大限度地使用硬件，因为磁盘和 CPU 都没有容量，人们会假设在上述数据量大的查询中，磁盘应该以最大值运行。
有人可以看看下面的摘录，也许会看到一些超出图表的值吗？我花了很长时间在谷歌上搜索，但那里的信息非常矛盾，如果研究没有让我清楚地了解它的作用，我害怕更改设置。
从 innodb 状态中提取
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 139616 srv_active, 0 srv_shutdown, 16834 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3380537
OS WAIT ARRAY INFO: signal count 5555711
RW-shared spins 969285, rounds 1446597, OS waits 477320
RW-excl spins 1510270, rounds 24745577, OS waits 740601
RW-sx spins 2014, rounds 59331, OS waits 1966
Spin rounds per wait: 1.49 RW-shared, 16.38 RW-excl, 29.46 RW-sx
--------
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: complete io for buf page (write thread)
I/O thread 7 state: complete io for buf page (write thread)
I/O thread 8 state: complete io for buf page (write thread)
I/O thread 9 state: complete io for buf page (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [4, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 293064
12836396 OS file reads, 705785528 OS file writes, 251455455 OS fsyncs
1 pending preads, 1 pending pwrites
6085.06 reads/s, 16383 avg bytes/read, 4331.99 writes/s, 1156.48 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 133, free list len 957047, seg size 957181, 145190 merges
merged operations:
insert 422868, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 13546877, node heap has 208 buffer(s)
Hash table size 13546877, node heap has 618 buffer(s)
Hash table size 13546877, node heap has 1 buffer(s)
Hash table size 13546877, node heap has 1 buffer(s)
Hash table size 13546877, node heap has 1 buffer(s)
Hash table size 13546877, node heap has 1 buffer(s)
Hash table size 13546877, node heap has 1 buffer(s)
Hash table size 13546877, node heap has 773 buffer(s)
3259.31 hash searches/s, 3153.10 non-hash searches/s
---
LOG
---
Log sequence number 10458893569770
Log buffer assigned up to 10458893569770
Log buffer completed up to 10458893569770
Log written up to 10458893569381
Log flushed up to 10458893152041
Added dirty pages up to 10458893569770
Pages flushed up to 10458813503591
Last checkpoint at 10458813500688
34272162 log i/o's done, 881.10 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 56044290048
Dictionary memory allocated 891577
Buffer pool size 3342336
Free buffers 6583
Database pages 3316993
Old database pages 1224525
Modified db pages 294704
Pending reads 0
Pending writes: LRU 0, flush list 28, single page 0
Pages made young 91169767, not young 2409531454
0.00 youngs/s, 0.01 non-youngs/s
Pages read 12621539, created 5829939, written 536293398
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 987 / 1000, young-making rate 1 / 1000 not 499 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3316993, unzip_LRU len: 0
I/O sum[1425272]:cur[21320], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 417792
Free buffers 953
Database pages 414480
Old database pages 153021
Modified db pages 36907
Pending reads 0
Pending writes: LRU 0, flush list 6, single page 0
Pages made young 11324843, not young 305126547
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1576101, created 729712, written 66959369
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 988 / 1000, young-making rate 1 / 1000 not 493 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 414480, unzip_LRU len: 0
I/O sum[178159]:cur[2665], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 417792
Free buffers 946
Database pages 414488
Old database pages 153019
Modified db pages 36836
Pending reads 0
Pending writes: LRU 0, flush list 10, single page 0
Pages made young 11180965, not young 302072441
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1568195, created 728393, written 66790908
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 986 / 1000, young-making rate 1 / 1000 not 516 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 414488, unzip_LRU len: 0
I/O sum[178159]:cur[2665], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 417792
Free buffers 1017
Database pages 414457
Old database pages 152972
Modified db pages 36608
Pending reads 0
Pending writes: LRU 0, flush list 6, single page 0
Pages made young 11651343, not young 296801601
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1580150, created 729267, written 67338866
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 986 / 1000, young-making rate 1 / 1000 not 509 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 414457, unzip_LRU len: 0
I/O sum[178159]:cur[2665], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 417792
Free buffers 946
Database pages 414513
Old database pages 153026
Modified db pages 37064
Pending reads 0
Pending writes: LRU 0, flush list 6, single page 0
Pages made young 11549266, not young 300684452
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1596663, created 728153, written 67299820
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 986 / 1000, young-making rate 1 / 1000 not 491 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 414513, unzip_LRU len: 0
I/O sum[178159]:cur[2665], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 417792
Free buffers 726
Database pages 414705
Old database pages 153101
Modified db pages 36600
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 11279236, not young 300731632
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1569208, created 728718, written 66867473
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 986 / 1000, young-making rate 1 / 1000 not 515 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 414705, unzip_LRU len: 0
I/O sum[178159]:cur[2665], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 417792
Free buffers 603
Database pages 414855
Old database pages 153155
Modified db pages 36645
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 11634232, not young 299370124
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1581611, created 728918, written 67297635
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 987 / 1000, young-making rate 1 / 1000 not 470 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 414855, unzip_LRU len: 0
I/O sum[178159]:cur[2665], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 417792
Free buffers 726
Database pages 414706
Old database pages 153100
Modified db pages 37179
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 11385018, not young 300961163
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1571483, created 728056, written 67087236
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 986 / 1000, young-making rate 1 / 1000 not 495 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 414706, unzip_LRU len: 0
I/O sum[178159]:cur[2665], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 417792
Free buffers 666
Database pages 414789
Old database pages 153131
Modified db pages 36865
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 11164864, not young 303783494
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1578128, created 728722, written 66652091
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 986 / 1000, young-making rate 1 / 1000 not 507 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 414789, unzip_LRU len: 0
I/O sum[178159]:cur[2665], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=16686, Main thread ID=140092498769664 , state=sleeping
Number of rows inserted 468088646, updated 10678363, deleted 0, read 890691733
3056.35 inserts/s, 127.56 updates/s, 0.00 deletes/s, 403057.39 reads/s
Number of system rows inserted 248, updated 420, deleted 707, read 33861
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
解决方案
您的问题似乎与检查点年龄有关。当您运行一个巨大的查询时，InnoDB 无法检查写入中继日志的修改，导致 HLL（历史列表长度）无限增加。随着时间的推移，每次读取都会越来越慢。这是因为 MySQL 需要撤消在事务开始后提交的更改，以保证读取的数据与事务的开始时间一致。
通过查看 InnoDB 状态报告，您可以看到您当前的列表长度为80M ( Log written up to- Last checkpoint at)，这是相当庞大的。
我建议切换到批处理方法，以块的形式复制数据，使用索引访问键对批处理进行分区，并避免长时间运行的查询。
永远记住，数据库被设计成真正高效和可靠的应用程序。这里的效率并不一定意味着将消耗所有可用资源。数据库可持续性能成功的关键是平衡资源使用，在不影响性能的情况下最大限度地提高可用性。
参考
推荐阅读
- angular - 即使参数不匹配，点击功能也会触发事件
- python - 将文件夹中的所有 excel 文件连接/合并到一个新的 excile 文件中
- ruby - 在 Rails 中调用类方法很困难。有其他可以正常工作的类方法
- javascript - 如何使用分配给包含 html 的 JS 变量的 EventListener 来获取 window.location.href
- java - 如何测量 ARCore Android 应用的帧速率？
- performance - 现代 x86-64 CPU 是否推测分支目标？
- rust - 如何获得包含除第 N 个元素之外的所有元素的切片？
- javascript - 'var baseUrl = app.get('url').replace(/\/$/, '');' 中的 'url' 在哪里 来自 Loopback 3？
- javascript - react-router v5.1.0 无法读取未定义的属性“位置”，
- python - 使用 Pandas 提取 excel 文档信息并将选定的列保存到新文件