Details
-
Bug
-
Status: Open (View Workflow)
-
Critical
-
Resolution: Unresolved
-
10.11, 11.4, 11.6
-
None
Description
Found by accident, trying to understand why sysbench loads its data faster than
mariadb-import --parallel. Mostly likely present before 11.6, did not try.
TLDR: for me, Bulk load turns out to be 1.5 times slower (on fast NVME), and more than 15 times more IO intensive.
I tried on Windows, and it is just slow, and this case is below
I tried on Linux, hoping that maybe BULK relies on temp directory being in memory, but got crash (tmpdir exhausted), or when
I allowed tmpfs to be 32GB (whole memory), then server process got killed, probably Linux peculiarity the OOM killer.
If tmpdir points to persistent disk, effects are about the same on Linux as they are on Windows
Below, my test case:
Step 0 - server parameters
We run load "in-memory", and both bufferpool and redo log will be larger than the IBD file.
[mysqld]
|
innodb_log_file_size=20G
|
innodb_buffer_pool_size=20G
|
innodb-buffer-pool-load-at-startup=OFF
|
innodb_buffer_pool_dump_at_shutdown=OFF
|
innodb_stats_auto_recalc=0
|
Step 1. Using sysbench, load a single table with 50M rows (~11GB ibd file)
sysbench oltp_read_write --mysql-user=root --tables=1 --table-size=50000000 --threads=1 prepare
|
Elapsed time (seconds): 273.673
Process IO Read Bytes : 14387837038
Process IO Write Bytes : 19291281550
Step 2: backup sbtest.sbtest1 table
mariadb -uroot -e "select * into outfile 'C:/work/sbtest1.data' from sbtest.sbtest1" |
Elapsed time (seconds): 36.482
restart server
Step 3: Load this data again, without bulk optimization
mariadb -uroot -e "truncate sbtest.sbtest1; load data infile 'C:/work/sbtest1.data' into table sbtest.sbtest1" |
Elapsed time (seconds): 240.996
Process Read Bytes 9916879781
Process Write Bytes 15149246158
Restart server again,
Step 4: Load the same data once more, with bulk optimization (enforced by setting unique_checks=0,foreign_key_checks=0)
mariadb -uroot -e "truncate sbtest.sbtest1; set unique_checks=0,foreign_key_checks=0, load data infile 'C:/work/sbtest1.data' into table sbtest.sbtest1" |
This is the step that is very slow, pegs the disk at ~1GB reads and writes, and this is where it crashes on Linux, if /tmp is memory.
When it is slow, CPU is at about 3%, busy with some reads and writes to temp. files.
Elapsed time (seconds): 371.664
Process Read Bytes: 167433868189
Process Write Bytes: 170475124430
Compare execution times and IO usage from Step 3 (without bulk) and Step 4(with bulk) , and sysbench "prepare"
Execution Time | IO-read Gbytes | IO-write Gbytes | |
---|---|---|---|
Sysbench prepare | 273 | 13.4 | 17.97 |
LOAD DATA NO-Bulk | 240 | 9.24 | 14.11 |
LOAD DATA Bulk | 371 | 155.93 | 158.77 |
Attachments
Issue Links
- relates to
-
MDEV-24621 In bulk insert, pre-sort and build indexes one page at a time
- Closed
-
MDEV-34719 Disable purge for LOAD DATA INFILE into empty table
- In Progress
-
MDEV-34832 Support adding AUTO_INCREMENT flag to existing numeric using INPLACE
- Open
-
MDEV-33188 Enhance mariadb-dump and mariadb-import capabilities similar to MyDumper
- Stalled