Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.6.14, 10.11.4
-
x86_64, Rocky Linux release 8.8 (Green Obsidian)
Description
Hi,
Starting from MariaDB ver 10.6, I experience huge performance loss (like x10)
when loading (via LOAD DATA INFILE) data into table with partitions.
Time of loading depends on number of partitions in table.
This bug appeared on ver. 10.6.
10.11.4 is also affected, although I don't include results here.
All previous versions up to 10.5.21 are not affected.
Let me share results first:
tbl0 - simple table with 3 columns, without partitions
tbl1 - as above, with 1 partition
tbl2 - as above, with 800 partitions
Below are times spent on LOAD DATA INFILE (each test repeated 3 times):
10.6.14 tbl0 = 1.406s 1.384s 1.338s
10.6.14 tbl1 = 1.553s 1.432s 1.429s
10.6.14 tbl2 = 11.692s 10.929s 11.019s
10.5.21 tbl0 = 1.457s 1.332s 1.228s
10.5.21 tbl1 = 1.511s 1.496s 1.249s
10.5.21 tbl2 = 1.500s 1.543s 1.499s
More info about my configuration:
OS: Linux Rocky 8.8, x86_64
my.cnf:
[mysqld]
innodb_buffer_pool_size = 10G
tmpdir = /opt/tmp
log_error
log_warnings
innodb_log_file_size = 6G
innodb_file_per_table = 1
How I build MariaDB:
VER=10.6.14
tar xzf mariadb-$VER.tar.gz
mkdir build-$VER
cd build-$VER
NP=/opt/db.$VER
cmake3 ../mariadb-$VER \
-DCMAKE_INSTALL_PREFIX=$NP \
-DINSTALL_MYSQLDATADIR=$NP/data \
-DINSTALL_SYSCONFDIR=$NP/etc \
-DINSTALL_UNIX_ADDRDIR=$NP/tmp/mysql.sock \
-DCMAKE_CXX_FLAGS=-march=sandybridge \
-DCMAKE_C_FLAGS=-march=sandybridge \
-DPLUGIN_MROONGA=NO \
-DWITHOUT_TOKUDB=1 \
-DCMAKE_BUILD_TYPE=Release
make -j12
make install
Tables - see attached tables.sql for details, but in principle simple table like:
CREATE TABLE `tbl0` (
`id` int(11) NOT NULL,
`tm` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`v` varchar(15) DEFAULT NULL,
PRIMARY KEY (`tm`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin
;
Test scenario:
cat ~/tables.sql | mysql -uroot test
FILE=~/test.txt
perl -e 'print "$_\t2023-07-25\t1234567890ABCD\n" for (1..2e5)' > $FILE
time mysql -uroot -e "LOAD DATA INFILE '$FILE' IGNORE INTO TABLE tbl0" test
time mysql -uroot -e "LOAD DATA INFILE '$FILE' IGNORE INTO TABLE tbl1" test
time mysql -uroot -e "LOAD DATA INFILE '$FILE' IGNORE INTO TABLE tbl2" test
mysql -uroot -e "truncate tbl0; truncate tbl1; truncate tbl2" test
Best Regards,
Karol
Attachments
Issue Links
- causes
-
MDEV-32291 memory leak in innodb.insert_into_empty test
- Closed
- is caused by
-
MDEV-515 innodb bulk insert
- Closed
- relates to
-
MDEV-24818 Concurrent use of InnoDB table is impossible until the first transaction is finished
- Closed
-
MDEV-31985 IGNORE in INSERT and LOAD are being ignored in InnoDB bulk insert
- Confirmed