Застрял при импорте данных из файла с помощью функции util.importable оболочки MySQL.

Застрял при импорте данных из файла с помощью функции util.importable оболочки MySQL. ⇐ Linux

1 сообщение • Страница 1 из 1

Anonymous

Застрял при импорте данных из файла с помощью функции util.importable оболочки MySQL.

Цитата

Сообщение Anonymous » 09 май 2024, 19:44

Когда я попытался использовать функцию util.importable оболочки MySQL для импорта данных из файла, я обнаружил, что индикатор выполнения вначале был очень быстрым, но скорость сразу упала до 0 , и последующая скорость составит всего несколько сотен КБ. В конце концов, он выполнит задачу, но только с очень низкой скоростью.
Оперативная память моей машины составляет 32 ГБ и имеет 48 процессоров.
Мой файл конфигурации MySQL:

Код: Выделить всё

# 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:

Код: Выделить всё

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:

Код: Выделить всё

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;"

показать список процессов:

Код: Выделить всё

mysql>  s h o w   p r o c e s s l i s t ; < b r   / > + - - - - - + - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - - - - - + - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + < b r   / > |   I d     |   U s e r                         |   H o s t                         |   d b       |   C o m m a n d   |   T i m e       |   S t a t e                                     |   I n f o                                                                                                                                                                                                   | < b r   / > + - - - - - + - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - - - - - + - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + < b r   / > |       5   |   e v e n t _ s c h e d u l e r   |   l o c a l h o s t               |   N U L L   |   D a e m o n     |   2 5 6 4 8 8   |   W a i t i n g   o n   e m p t y   q u e u e   |   N U L L                                                                                                                                                                                                   | < b r   / > |   5 6 4   |   r o o t                         |   l o c a l h o s t               |   t e s t   |   Q u e r y       |             0   |   i n i t                                       |   s h o w   p r o c e s s l i s t                                                                                                                                                                           | < b r   / > |   6 1 4   |   r o o t                         |   l o c a l h o s t : 4 6 0 3 4   |   N U L L   |   S l e e p       |       2 3 8 6   |                                                 |   N U L L                                                                                                                                                                                                   | < b r   / > |   6 1 5   |   r o o t                         |   l o c a l h o s t : 4 6 0 3 6   |   t e s t   |   Q u e r y       |         4 9 1   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 1 6   |   r o o t                         |   l o c a l h o s t : 4 6 0 3 8   |   t e s t   |   Q u e r y       |         5 0 0   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 1 7   |   r o o t                         |   l o c a l h o s t : 4 6 0 4 0   |   t e s t   |   Q u e r y       |         4 8 9   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 1 8   |   r o o t                         |   l o c a l h o s t : 4 6 0 4 2   |   t e s t   |   Q u e r y       |         4 8 9   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 1 9   |   r o o t                         |   l o c a l h o s t : 4 6 0 4 4   |   t e s t   |   Q u e r y       |         4 6 4   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 2 0   |   r o o t                         |   l o c a l h o s t : 4 6 0 4 8   |   t e s t   |   Q u e r y       |         4 9 9   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 2 1   |   r o o t                         |   l o c a l h o s t : 4 6 0 4 6   |   t e s t   |   Q u e r y       |         4 6 9   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 2 2   |   r o o t                         |   l o c a l h o s t : 4 6 0 5 0   |   t e s t   |   Q u e r y       |         5 1 4   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 2 3   |   r o o t                         |   l o c a l h o s t : 4 6 0 5 2   |   t e s t   |   Q u e r y       |         4 6 5   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 2 4   |   r o o t                         |   l o c a l h o s t : 4 6 0 5 6   |   t e s t   |   Q u e r y       |         5 0 3   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 2 5   |   r o o t                         |   l o c a l h o s t : 4 6 0 5 4   |   t e s t   |   Q u e r y       |         5 5 4   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 2 6   |   r o o t                         |   l o c a l h o s t : 4 6 0 5 8   |   t e s t   |   Q u e r y       |         4 5 7   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 2 7   |   r o o t                         |   l o c a l h o s t : 4 6 0 6 0   |   t e s t   |   Q u e r y       |         4 4 7   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 2 8   |   r o o t                         |   l o c a l h o s t : 4 6 0 6 2   |   t e s t   |   Q u e r y       |         4 9 6   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 2 9   |   r o o t                         |   l o c a l h o s t : 4 6 0 6 4   |   t e s t   |   Q u e r y       |         4 9 7   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 3 0   |   r o o t                         |   l o c a l h o s t : 4 6 0 6 6   |   t e s t   |   Q u e r y       |         5 3 8   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 3 1   |   r o o t                         |   l o c a l h o s t : 4 6 0 6 8   |   t e s t   |   Q u e r y       |         4 8 6   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 3 2   |   r o o t                         |   l o c a l h o s t : 4 6 0 7 0   |   t e s t   |   Q u e r y       |         4 7 6   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 3 3   |   r o o t                         |   l o c a l h o s t : 4 6 0 7 2   |   t e s t   |   Q u e r y       |         5 3 2   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 3 4   |   r o o t                         |   l o c a l h o s t : 4 6 0 7 4   |   t e s t   |   Q u e r y       |         5 3 3   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 3 5   |   r o o t                         |   l o c a l h o s t : 4 6 0 7 6   |   t e s t   |   Q u e r y       |         4 5 6   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 3 6   |   r o o t                         |   l o c a l h o s t : 4 6 0 7 8   |   t e s t   |   Q u e r y       |         5 0 1   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 3 7   |   r o o t                         |   l o c a l h o s t : 4 6 0 8 0   |   t e s t   |   Q u e r y       |         4 8 4   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 3 8   |   r o o t                         |   l o c a l h o s t : 4 6 0 8 2   |   t e s t   |   Q u e r y       |         5 1 2   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 3 9   |   r o o t                         |   l o c a l h o s t : 4 6 0 8 4   |   t e s t   |   Q u e r y       |         5 2 1   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / 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

общее время:

Код: Выделить всё

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

Я надеюсь, что весь процесс импорта больше не будет зависать, а вернется к той скорости параллельного импорта, которая должна быть.

Подробнее здесь: https://stackoverflow.com/questions/784 ... e-function

1715273089

Anonymous

Когда я попытался использовать функцию util.importable оболочки MySQL для импорта данных из файла, я обнаружил, что индикатор выполнения вначале был очень быстрым, но скорость [b]сразу упала до 0[/b] , и последующая скорость составит всего несколько сотен КБ. В конце концов, он выполнит задачу, но только с очень низкой скоростью.
Оперативная память моей машины составляет [b]32 ГБ[/b] и имеет [b]48 процессоров[/b].
Мой файл конфигурации MySQL:
[code]# 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
[/code]
Журнал оболочки MySQL:
[code]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
[/code]
команда mysqlsh:
[code]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;"
[/code]
показать список процессов:
[code]mysql>  s h o w   p r o c e s s l i s t ; < b r   / > + - - - - - + - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - - - - - + - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + < b r   / > |   I d     |   U s e r                         |   H o s t                         |   d b       |   C o m m a n d   |   T i m e       |   S t a t e                                     |   I n f o                                                                                                                                                                                                   | < b r   / > + - - - - - + - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - - - - - + - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + < b r   / > |       5   |   e v e n t _ s c h e d u l e r   |   l o c a l h o s t               |   N U L L   |   D a e m o n     |   2 5 6 4 8 8   |   W a i t i n g   o n   e m p t y   q u e u e   |   N U L L                                                                                                                                                                                                   | < b r   / > |   5 6 4   |   r o o t                         |   l o c a l h o s t               |   t e s t   |   Q u e r y       |             0   |   i n i t                                       |   s h o w   p r o c e s s l i s t                                                                                                                                                                           | < b r   / > |   6 1 4   |   r o o t                         |   l o c a l h o s t : 4 6 0 3 4   |   N U L L   |   S l e e p       |       2 3 8 6   |                                                 |   N U L L                                                                                                                                                                                                   | < b r   / > |   6 1 5   |   r o o t                         |   l o c a l h o s t : 4 6 0 3 6   |   t e s t   |   Q u e r y       |         4 9 1   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 1 6   |   r o o t                         |   l o c a l h o s t : 4 6 0 3 8   |   t e s t   |   Q u e r y       |         5 0 0   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 1 7   |   r o o t                         |   l o c a l h o s t : 4 6 0 4 0   |   t e s t   |   Q u e r y       |         4 8 9   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 1 8   |   r o o t                         |   l o c a l h o s t : 4 6 0 4 2   |   t e s t   |   Q u e r y       |         4 8 9   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 1 9   |   r o o t                         |   l o c a l h o s t : 4 6 0 4 4   |   t e s t   |   Q u e r y       |         4 6 4   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 2 0   |   r o o t                         |   l o c a l h o s t : 4 6 0 4 8   |   t e s t   |   Q u e r y       |         4 9 9   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 2 1   |   r o o t                         |   l o c a l h o s t : 4 6 0 4 6   |   t e s t   |   Q u e r y       |         4 6 9   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 2 2   |   r o o t                         |   l o c a l h o s t : 4 6 0 5 0   |   t e s t   |   Q u e r y       |         5 1 4   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 2 3   |   r o o t                         |   l o c a l h o s t : 4 6 0 5 2   |   t e s t   |   Q u e r y       |         4 6 5   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 2 4   |   r o o t                         |   l o c a l h o s t : 4 6 0 5 6   |   t e s t   |   Q u e r y       |         5 0 3   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 2 5   |   r o o t                         |   l o c a l h o s t : 4 6 0 5 4   |   t e s t   |   Q u e r y       |         5 5 4   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 2 6   |   r o o t                         |   l o c a l h o s t : 4 6 0 5 8   |   t e s t   |   Q u e r y       |         4 5 7   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 2 7   |   r o o t                         |   l o c a l h o s t : 4 6 0 6 0   |   t e s t   |   Q u e r y       |         4 4 7   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 2 8   |   r o o t                         |   l o c a l h o s t : 4 6 0 6 2   |   t e s t   |   Q u e r y       |         4 9 6   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 2 9   |   r o o t                         |   l o c a l h o s t : 4 6 0 6 4   |   t e s t   |   Q u e r y       |         4 9 7   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 3 0   |   r o o t                         |   l o c a l h o s t : 4 6 0 6 6   |   t e s t   |   Q u e r y       |         5 3 8   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 3 1   |   r o o t                         |   l o c a l h o s t : 4 6 0 6 8   |   t e s t   |   Q u e r y       |         4 8 6   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 3 2   |   r o o t                         |   l o c a l h o s t : 4 6 0 7 0   |   t e s t   |   Q u e r y       |         4 7 6   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 3 3   |   r o o t                         |   l o c a l h o s t : 4 6 0 7 2   |   t e s t   |   Q u e r y       |         5 3 2   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 3 4   |   r o o t                         |   l o c a l h o s t : 4 6 0 7 4   |   t e s t   |   Q u e r y       |         5 3 3   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 3 5   |   r o o t                         |   l o c a l h o s t : 4 6 0 7 6   |   t e s t   |   Q u e r y       |         4 5 6   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 3 6   |   r o o t                         |   l o c a l h o s t : 4 6 0 7 8   |   t e s t   |   Q u e r y       |         5 0 1   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 3 7   |   r o o t                         |   l o c a l h o s t : 4 6 0 8 0   |   t e s t   |   Q u e r y       |         4 8 4   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 3 8   |   r o o t                         |   l o c a l h o s t : 4 6 0 8 2   |   t e s t   |   Q u e r y       |         5 1 2   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / l y s / t e s t / t e s t _ 1 3 . t x t '   I G N O R E   I N T O   T A B L E   ` t e s t ` . ` m y _ t a b l e `   F I E L D S   T E R M I   | < b r   / > |   6 3 9   |   r o o t                         |   l o c a l h o s t : 4 6 0 8 4   |   t e s t   |   Q u e r y       |         5 2 1   |   e x e c u t i n g                             |   L O A D   D A T A   L O C A L   I N F I L E   ' / d a t a / 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 |
+-----+-----------------+-----------------+------+---------+--------+------------------------+------------------------------------------------------------------------------------------------------+

[/code]
iostat:
[code]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
[/code]
общее время:
[code]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
[/code]
Я надеюсь, что весь процесс импорта больше не будет зависать, а вернется к той скорости параллельного импорта, которая должна быть. 

Подробнее здесь: [url]https://stackoverflow.com/questions/78455876/stuck-when-importing-data-from-file-using-mysql-shells-util-importable-function[/url]