[BUG] myloader 0.15.1.3 import speed is slow

[jiugem]

MySQL version 8.0.33
mydumper-0.15.1-3.el7.x86_64.rpm

mydumper -u root-p 123456-h sourceIP -P 3306 --skip-tz-utc --regex 'db1.*|db2.*' -t 4 -G -E -R -c -s 10000000 -o /data/dump_`date +%F`

myloader -u root -p 123456 -h IP -d /data/dump_2023-12-26 -t 4 -o -e -L /data/myloader.log

The number of exported tables was 50,000, and the speed was OK. The import was very slow. After importing more than 180 tables, it got stuck. The amount of data in each table is not large, less than 1GB.

top - 18:28:40 up 204 days,  2:28,  2 users,  load average: 1.67, 1.46, 1.22
Tasks: 265 total,   1 running, 264 sleeping,   0 stopped,   0 zombie
%Cpu(s): 34.8 us,  1.8 sy,  0.0 ni, 63.2 id,  0.0 wa,  0.0 hi,  0.2 si,  0.0 st
KiB Mem :  7989844 total,   361644 free,  6320196 used,  1308004 buff/cache
KiB Swap:        0 total,        0 free,        0 used.   946540 avail Mem

   PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 18881 root      20   0  640948 110728   4316 S  97.0  1.4  16:06.52 myloader

Samples: 30K of event 'cpu-clock', 4000 Hz, Event count (approx.): 6409961886 lost: 0/0 drop: 0/0
Overhead  Shared Object            Symbol
  65.44%  myloader                 [.] compare_dbt_short
  25.98%  libglib-2.0.so.0.5600.1  [.] 0x0000000000047dc9
   3.72%  libglib-2.0.so.0.5600.1  [.] 0x0000000000047db8
   1.52%  libglib-2.0.so.0.5600.1  [.] g_slice_alloc
   1.15%  libglib-2.0.so.0.5600.1  [.] g_slice_free_chain_with_offset

[jiugem]

After importing several tables, it gets stuck.

[davidducos]

Hi @jiugem,
Can you test with latest prerelease?

[jiugem]

@davidducos Hi, I tried mydumper-0.15.2-6.el7.x86_64.rpm
It's a bit faster. Myloader imported 6,000 tables in 3 hours, but I still have nearly 50,000 tables left.

The mydumper export file is not large, totaling 444MB.

[aqueos]

hi,

just to remove the decompression thing perhaps testing by uncompressing all the file before and then import and see if this change the speed ? i saw a x20 times in compression in the dumping in 0.15 and perhaps there is the same lock in loading ?

regards,
Ghislain.

[davidducos]

Hi @jiugem
Were you able to complete the import?
Take into account that MySQL 8 implemented https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html which caused a performance degradation when creating tables.

[jiugem]

Thanks,
I'm not sure it has anything to do with compression. @aqueos
@davidducos
After many tests, it seems that it has something to do with the processing mechanism, but it should have nothing to do with atomic ddl.
The mysqldump export is very slow and takes 5 hours, and the import is relatively slow.
mysqlshell is the fastest, exporting in 4 minutes and importing in 20 minutes.

`util.dumpSchemas(['db1','db3'],'/data/backup')

117% (16.96M rows / ~14.44M rows), 21.20K rows/s, 3.40 MB/s uncompressed, 1.07 MB/s compressed
Dump duration: 00:03:34s
Total duration: 00:03:54s
Schemas dumped: 2
Tables dumped: 55897
Uncompressed data size: 2.24 GB
Compressed data size: 287.81 MB
Compression ratio: 7.8
Rows written: 16962161
Bytes written: 287.81 MB
Average uncompressed throughput: 10.46 MB/s
Average compressed throughput: 1.34 MB/s

util.loadDump("/data/backup")

Executing DDL - done
Executing view DDL - done
Starting data load
2 thds loading \ 100% (2.24 GB / 2.24 GB), 24.95 KB/s, 2138 / 55897 tables done
Recreating indexes - done
Executing common postamble SQL
55961 chunks (16.96M rows, 2.24 GB) for 55897 tables in 2 schemas were loaded in 20 min 51 sec (avg throughput 13.80 MB/s)
0 warnings were reported during the load.

`

[davidducos]

Hi @jiugem, please, can you test again with the latest prerelease?

[jiugem]

Hi @davidducos
I executed the previous myloader command and failed to create any schema other than the following

** (myloader:42826): WARNING **: 11:01:08.738: zstd command not found on any static location, use --exec-per-thread for non default locations

[davidducos]

Hi @jiugem,
That means that zstd command was not found on the default locations. Did you install zstd?

[jiugem]

Installed, but always report this, I don't know what's going on, CentOS 7发自我的 iPhone在 2024年2月26日，21:54，David Ducos ***@***.***> 写道： Hi @jiugem, That means that zstd command was not found on the default locations. Did you install zstd? —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you were mentioned.Message ID: ***@***.***>

[davidducos]

Hi @jiugem,

The standard paths for zstd command are "/usr/bin/zstd", "/bin/zstd". Do you have those? If none of them are there, execute which zstd to know the real location and then can you try for example: --exec-per-thread="/usr/local/bin/zstd -c" --exec-per-thread-extension='.zst' where /usr/local/bin/zstd is what you got from which zstd?

[davidducos]

Hi @jiugem, about the initial issue that you reported. I tested with 50k tables and I was not able to reproduce your issue. Please, can you share a test case?