mysqldump导出108353886字节的数据后，hang
本帖最后由 奔跑的犀牛 于 2023-11-17 09:31 编辑环境：
GreatSQL 8.0.32
银河麒麟V10
鲲鹏920
使用mysqldump导出200M左右的数据，命令如下：
mysqldump -uroot --default-character-set=utf8mb4 --max-allowed-packet=1073741824 --socket=/tmp/testdb1.sock --databases testdb \
--set-gtid-purged=OFF \
--routines \
--triggers \
--events \
--skip-lock-tables \
--skip-opt \
--add-drop-table=false \
--create-options \
--quick \
--disable-keys \
-p > testdb_20231110.sql
命令hang。。。
此时观察输出文件大小保持为：
-rw-r--r-- 1 admin admin 108353886 Nov 17 09:07 testdb_20231110.sql
强制退出mysqldump后，发现mysqld的cpu仍然持续很高：
top - 09:23:49 up 94 days, 19:25,3 users,load average: 21.00, 20.48, 14.96
Tasks: 1013 total, 1 running, 1012 sleeping, 0 stopped, 0 zombie
%Cpu(s): 21.9 us,0.0 sy,0.0 ni, 78.1 id,0.0 wa,0.0 hi,0.0 si,0.0 st
MiB Mem : 194714.1 total,20198.8 free, 150804.4 used,23710.9 buff/cache
MiB Swap:65535.9 total,62955.6 free, 2580.3 used.36763.7 avail Mem
PID USER PRNI VIRT RES SHR S%CPU%MEM TIME+ COMMAND
3995768 admin 20 0145.5g 8.3g37888 S2106 4.4 412:41.70 mysqld
登录到greatsql中，可以发现之前强制退出的三个mysqldump，线程仍然还在：
+----+-----------------+-----------------------+--------+---------+------+------------------------+---------------------------------------------------------+---------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Time_ms | Rows_sent | Rows_examined |
+----+-----------------+-----------------------+--------+---------+------+------------------------+---------------------------------------------------------+---------+-----------+---------------+
|5 | event_scheduler | localhost | NULL | Daemon| 2034 | Waiting on empty queue | NULL | 2033492 | 0 | 0 |
| 17 | root | localhost | testdb | Query | 1776 | executing | SELECT /*!40001 SQL_NO_CACHE */ * FROM `audit_job` | 1775668 | 140588 | 0 |
| 28 | root | localhost | testdb | Query | 1290 | executing | SELECT /*!40001 SQL_NO_CACHE */ * FROM `audit_job` | 1289440 | 140443 | 0 |
| 38 | root | localhost | testdb | Query | 1224 | executing | SELECT /*!40001 SQL_NO_CACHE */ * FROM audit_job` | 1223870 | 140588 | 0 |
应该是innodb pq bug引起的，请关闭innodb pq特性，有两个方法：
1. 修改myc.nf，注释掉下面这行
# force_parallel_execute = 1
2. 在线动态修改关闭
> set global force_parallel_execute = 0;
> set persist force_parallel_execute = 0; yejr 发表于 2023-11-17 14:17
应该是innodb pq bug引起的，请关闭innodb pq特性，有两个方法：
1. 修改myc.nf，注释掉下面这行
和我猜测的类似：
PID USER PRNI VIRT RES SHR S%CPU%MEM TIME+ COMMAND
3995847 admin 20 0145.8g 8.3g37952 R99.9 4.4 281:23.88 connection
3996568 admin 20 0145.8g 8.3g37952 R99.9 4.4 281:24.10 parallel_query
3996569 admin 20 0145.8g 8.3g37952 R99.9 4.4 281:24.19 parallel_query
3996570 admin 20 0145.8g 8.3g37952 R99.9 4.4 281:24.05 parallel_query
3996571 admin 20 0145.8g 8.3g37952 R99.9 4.4 281:23.84 parallel_query
3996572 admin 20 0145.8g 8.3g37952 R99.9 4.4 281:24.09 parallel_query
3996573 admin 20 0145.8g 8.3g37952 R99.9 4.4 281:24.08 parallel_query
3998206 admin 20 0145.8g 8.3g37952 R99.9 4.4 273:17.66 connection
3998385 admin 20 0145.8g 8.3g37952 R99.9 4.4 273:17.92 parallel_query
3998386 admin 20 0145.8g 8.3g37952 R99.9 4.4 273:17.95 parallel_query
3998387 admin 20 0145.8g 8.3g37952 R99.9 4.4 273:17.95 parallel_query
3998388 admin 20 0145.8g 8.3g37952 R99.9 4.4 273:17.98 parallel_query
3998389 admin 20 0145.8g 8.3g37952 R99.9 4.4 273:17.93 parallel_query
3998390 admin 20 0145.8g 8.3g37952 R99.9 4.4 273:17.94 parallel_query
3998580 admin 20 0145.8g 8.3g37952 R99.9 4.4 272:12.11 connection
3998583 admin 20 0145.8g 8.3g37952 R99.9 4.4 272:12.22 parallel_query
3998584 admin 20 0145.8g 8.3g37952 R99.9 4.4 272:12.29 parallel_query
3998585 admin 20 0145.8g 8.3g37952 R99.9 4.4 272:12.18 parallel_query
3998586 admin 20 0145.8g 8.3g37952 R99.9 4.4 272:12.34 parallel_query
3998587 admin 20 0145.8g 8.3g37952 R99.9 4.4 272:12.28 parallel_query
3998588 admin 20 0145.8g 8.3g37952 R99.9 4.4 272:12.30 parallel_query
3995768 admin 20 0145.8g 8.3g37952 S 0.0 4.4 0:00.23 mysqld
3995778 admin 20 0145.8g 8.3g37952 S 0.0 4.4 0:00.00 ib_io_ibuf
3995779 admin 20 0145.8g 8.3g37952 S 0.0 4.4 0:00.00 ib_io_log
3995780 admin 20 0145.8g 8.3g37952 S 0.0 4.4 0:00.00 ib_io_rd-1
3995781 admin 20 0145.8g 8.3g37952 S 0.0 4.4 0:00.00 ib_io_rd-2
3995782 admin 20 0145.8g 8.3g37952 S 0.0 4.4 0:00.00 ib_io_rd-3
3995783 admin 20 0145.8g 8.3g37952 S 0.0 4.4 0:00.00 ib_io_rd-4
3995784 admin 20 0145.8g 8.3g37952 S 0.0 4.4 0:00.00 ib_io_rd-5
3995785 admin 20 0145.8g 8.3g37952 S 0.0 4.4 0:00.00 ib_io_rd-6
3995786 admin 20 0145.8g 8.3g37952 S 0.0 4.4 0:00.00 ib_io_rd-7
3995787 admin 20 0145.8g 8.3g37952 S 0.0 4.4 0:00.00 ib_io_rd-8
3995788 admin 20 0145.8g 8.3g37952 S 0.0 4.4 0:00.00 ib_io_rd-9
3995789 admin 20 0145.8g 8.3g37952 S 0.0 4.4 0:00.00 ib_io_rd-10
3995790 admin 20 0145.8g 8.3g37952 S 0.0 4.4 0:00.00 ib_io_rd-11
3995791 admin 20 0145.8g 8.3g37952 S 0.0 4.4 0:00.00 ib_io_rd-12
3995792 admin 20 0145.8g 8.3g37952 S 0.0 4.4 0:00.00 ib_io_rd-13
3995793 admin 20 0145.8g 8.3g37952 S 0.0 4.4 0:00.00 ib_io_rd-14
# innodb parallel query
force_parallel_execute = ON
parallel_default_dop = 8
parallel_max_threads = 24
temptable_max_ram = 8G
奔跑的犀牛 发表于 2023-11-17 15:36
和我猜测的类似：
PID USER PRNI VIRT RES SHR S%CPU%MEM TIME+ COMMAND ...
关掉parallel后，可正常执行。 奔跑的犀牛 发表于 2023-11-17 15:41
关掉parallel后，可正常执行。
是的，下个版本中会修复这个问题，或者给个邮箱，我给你发修复后的二进制包（仅支持centsos 7/8 x86环境）。
页:
[1]