Stuck when importing data from file using MySQL shell's util.importable function

Question

When I tried to use the MySQL shell's util.importable function to import data from a file, I found that the progress bar was very fast at the beginning, but the speed would immediately drop to 0, and the subsequent speed would only be a few hundred KB. It would finally compelete the task but only with a very low speed. My machine RAM is 32GB and has 48CPUs.

My mysql config file :

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 26112M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
secure-file-priv=""
local_infile = ON

innodb_buffer_pool_size = 26112M
innodb_log_file_size=1048576
innodb_log_buffer_size=1048576
innodb_ddl_threads=48
innodb_parallel_read_threads=48
innodb_ddl_buffer_size=26738688
skip-networking = Off
innodb_flush_log_at_trx_commit=0
innodb_read_io_threads=48
innodb_write_io_threads=48
innodb_dedicated_server=ON

MySQL shell log:

Importing from file '/data/lys/test/test_13.txt' to table `test`.`my_table` in MySQL Server at localhost:3306 using 48 threads
verbose: 2024-05-09T23:39:22Z: Parallel load data...
[Worker039]: test_13.txt: Records: 476625  Deleted: 0  Skipped: 0  Warnings: 0
[Worker043]: test_13.txt: Records: 496457  Deleted: 0  Skipped: 0  Warnings: 0
[Worker047]: test_13.txt: Records: 483948  Deleted: 0  Skipped: 0  Warnings: 0
[Worker042]: test_13.txt: Records: 496279  Deleted: 0  Skipped: 0  Warnings: 0
[Worker011]: test_13.txt: Records: 501300  Deleted: 0  Skipped: 0  Warnings: 0
[Worker038]: test_13.txt: Records: 501498  Deleted: 0  Skipped: 0  Warnings: 0

mysqlsh command:

mysqlsh mysql://root:@localhost:3306 -uroot -p1234 --verbose=4 -- util import-table /data/lys/test/test_13.txt  --schema=test --table=my_table  --bytesPerChunk=10M --showProgress=True --columns={"value","dststring"} --threads=48 --dialect=csv-unix --sessionInitSql="SET autocommit=0,SET SESSION sql_log_bin=0;"

show processlist:

mysql> show processlist;
+-----+-----------------+-----------------+------+---------+--------+------------------------+------------------------------------------------------------------------------------------------------+
| Id  | User            | Host            | db   | Command | Time   | State                  | Info                                                                                                 |
+-----+-----------------+-----------------+------+---------+--------+------------------------+------------------------------------------------------------------------------------------------------+
|   5 | event_scheduler | localhost       | NULL | Daemon  | 256488 | Waiting on empty queue | NULL                                                                                                 |
| 564 | root            | localhost       | test | Query   |      0 | init                   | show processlist                                                                                     |
| 614 | root            | localhost:46034 | NULL | Sleep   |   2386 |                        | NULL                                                                                                 |
| 615 | root            | localhost:46036 | test | Query   |    491 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 616 | root            | localhost:46038 | test | Query   |    500 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 617 | root            | localhost:46040 | test | Query   |    489 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 618 | root            | localhost:46042 | test | Query   |    489 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 619 | root            | localhost:46044 | test | Query   |    464 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 620 | root            | localhost:46048 | test | Query   |    499 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 621 | root            | localhost:46046 | test | Query   |    469 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 622 | root            | localhost:46050 | test | Query   |    514 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 623 | root            | localhost:46052 | test | Query   |    465 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 624 | root            | localhost:46056 | test | Query   |    503 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 625 | root            | localhost:46054 | test | Query   |    554 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 626 | root            | localhost:46058 | test | Query   |    457 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 627 | root            | localhost:46060 | test | Query   |    447 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 628 | root            | localhost:46062 | test | Query   |    496 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 629 | root            | localhost:46064 | test | Query   |    497 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 630 | root            | localhost:46066 | test | Query   |    538 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 631 | root            | localhost:46068 | test | Query   |    486 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 632 | root            | localhost:46070 | test | Query   |    476 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 633 | root            | localhost:46072 | test | Query   |    532 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 634 | root            | localhost:46074 | test | Query   |    533 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 635 | root            | localhost:46076 | test | Query   |    456 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 636 | root            | localhost:46078 | test | Query   |    501 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 637 | root            | localhost:46080 | test | Query   |    484 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 638 | root            | localhost:46082 | test | Query   |    512 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 639 | root            | localhost:46084 | test | Query   |    521 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 640 | root            | localhost:46086 | test | Query   |    489 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 641 | root            | localhost:46088 | test | Query   |    476 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 642 | root            | localhost:46090 | test | Query   |    497 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 643 | root            | localhost:46092 | test | Query   |    553 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 644 | root            | localhost:46094 | test | Query   |    461 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 645 | root            | localhost:46096 | test | Query   |    511 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 646 | root            | localhost:46098 | test | Query   |    476 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 647 | root            | localhost:46100 | test | Query   |    451 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 648 | root            | localhost:46102 | test | Query   |    501 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 649 | root            | localhost:46104 | test | Query   |    490 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 650 | root            | localhost:46106 | test | Query   |    542 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 651 | root            | localhost:46108 | test | Query   |    462 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 652 | root            | localhost:46110 | test | Query   |    535 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 653 | root            | localhost:46114 | test | Query   |    510 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 654 | root            | localhost:46116 | test | Query   |    457 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 655 | root            | localhost:46112 | test | Query   |    502 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 656 | root            | localhost:46118 | test | Query   |    492 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 657 | root            | localhost:46120 | test | Query   |    504 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 658 | root            | localhost:46122 | test | Query   |    508 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 659 | root            | localhost:46124 | test | Query   |    541 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 660 | root            | localhost:46126 | test | Query   |    533 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 661 | root            | localhost:46128 | test | Query   |    527 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
| 662 | root            | localhost:46130 | test | Query   |    507 | executing              | LOAD DATA LOCAL INFILE '/data/lys/test/test_13.txt' IGNORE INTO TABLE `test`.`my_table` FIELDS TERMI |
+-----+-----------------+-----------------+------+---------+--------+------------------------+------------------------------------------------------------------------------------------------------+

iostat:

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
nvme0n1           5.00     0.00   50.00    0.00   220.00     0.00     8.80     0.01    0.10    0.10    0.00   0.38   1.90
sda              58.00  1237.00   14.00  205.00   288.00  5832.00    55.89     0.73    3.39    3.93    3.36   1.78  38.90
sdb              99.00  1064.00   40.00  201.00   556.00  5180.00    47.60     0.83    3.39    5.45    2.99   1.71  41.20
sdd             101.00  1231.00   33.00  232.00   580.00  5952.00    49.30     1.19    4.54    6.00    4.34   1.67  44.20
sdc             129.00  1116.00   35.00  215.00   668.00  5448.00    48.93     0.81    3.23    2.83    3.29   1.83  45.80
md126             0.00     0.00    0.00  493.00     0.00 13048.00    52.93     0.00    0.00    0.00    0.00   0.00   0.00

total time:

File '/data/lys/test/test_13.txt' (1.67 GB) was imported in 1 hour 2 min 18.8153 sec at 447.85 KB/s

I hope that the entire import process will no longer be stuck, but return to the parallel import speed it should be.

Answer 1

Please consider these changes to your my.cnf [mysqld] section

innodb_buffer_pool_size=16G  # from 26112M for 50% of total OS RAM
innodb_log_file_size=1G  # from 1M to delay log rotation
innodb_ddl_threads=10  # from 48 to avoid total saturation
innodb_parallel_read_threads=10  # from 48 to avoid total saturation
innodb_change_buffer_max_size=50  # from 25 percent to speed up inserts

after stop/start of instance,

in your mysqlsh request use two different limits,

bytesPerChunk=200M
threads=10

SHOW FULL PROCESSLIST; when running should not exceed 10 executing processes for loading your data.

What you have now is spreading your CPU too thin with up to 48 executing processes.

There may be additional throttling Global Variables before you are comfortable with this technique of reducing total runtime of more than 1 hour to load your 1.6GB of input data.

Please view my profile for additional assistance.