Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
10.1.29, 10.0.38, 10.2.26, 10.1.41, 10.3.15, 10.3.17, 10.4.7
-
None
-
CentOS 7.5 & SuSE Leap 15.0
Description
Performing bulk inserts into identical tables using MyISAM, Aria, and InnoDB, the performance of Aria when TRANSACTIONAL=1 (the default) is over 100x slower than MyISAM or InnoDB (single transaction) on 10.4.7.
When TRANSACTIONAL=0, performance is approximately 75% of MyISAM & InnoDB
Setting ROW_FORMAT=FIXED results in a 20% improvement in 10.1, but from 10.3 forward ROW_FORMAT=PAGE loses very little to FIXED.
The problem is that not only does this make Aria with TRANSACTIONAL=1 almost unusable for updates, but since 10.4 uses Aria with TRANSACTIONAL=1 for system tables, system operations in 10.4 are seriously affected. Adding users, for example, is 52% slower in 10.3 than in 10.1, but 511% slower in 10.4.
create table simple (
|
id int(10) unsigned not null auto_increment,
|
modified timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
val int(10) unsigned DEFAULT NULL,
|
blah varchar(511) DEFAULT NULL,
|
PRIMARY KEY (id),
|
KEY simple_modified (modified),
|
KEY simple_blah100 (blah(100))
|
) ENGINE=
|
default charset=utf8mb4 collate utf8mb4_unicode_ci;
|
|
|
followed by:
|
for i in {1..1000000};
|
do
|
echo "insert into simple(val, blah)
|
values( $RANDOM$RANDOM, '$RANDOM$RANDOM$RANDOM$RANDOM$RANDOM$RANDOM$RANDOM$RANDOM$RANDOM$RANDOM');">>popsimp.sql
|
done
|
|
|
cat popsimp.sql | pv -bat | mysql -ABN test
|
|
|
MyISAM / 10.1.37
|
1.52MiB/s
|
|
Aria / 10.1.37
|
23.6KiB/s
|
|
|
MyISAM / 10.3.15
|
1.47MiB/s
|
|
|
Aria / 10.3.15
|
21.8KiB/s
|
|
|
Aria / 10.1.37 / ROW_FORMAT=PAGE TRANSACTIONAL=0
|
1.03MiB/s
|
|
|
Aria / 10.1.37 / ROW_FORMAT=FIXED TRANSACTIONAL=0
|
1.20MiB/s
|
|
|
InnoDB / 10.3.15
|
1.74MiB/s
|
|
|
Aria / 10.3.15 / ROW_FORMAT=FIXED TRANSACTIONAL=0
|
1.30MiB/s
|
|
|
Aria / 10.3.15 / ROW_FORMAT=PAGE TRANSACTIONAL=0
|
1.27MiB/s
|
|
|
MyISAM / 10.4.7
|
2.13MiB/s
|
|
|
Aria / 10.4.7
|
20.9KiB/s
|
|
|
Aria / 10.4.7 / ROW_FORMAT=FIXED TRANSACTIONAL=0
|
1.55MiB/s
|
|
|
Aria / 10.4.7 / ROW_FORMAT=PAGE TRANSACTIONAL=0
|
1.31MiB/s
|
|
|
InnoDB / 10.4.7
|
1.79MiB/s
|
|
|
InnoDB / 10.4.7 / Single Transaction
|
2.17MiB/s
|
|
|
~# time for i in {1..1000}; do mysql -Ae "grant all privileges on *.* to 'test'@'127.0.0.1' identified by 'terriblylongpassword-weo48u' with grant option; flush privileges; drop user 'test'@'127.0.0.1';"; done
|
|
|
10.1.29:
|
real 0m3.388s
|
user 0m2.475s
|
sys 0m0.463s
|
|
|
10.3.15:
|
real 0m5.177s
|
user 0m3.647s
|
sys 0m1.098s
|
|
|
10.4.7:
|
real 0m17.335s
|
user 0m5.412s
|
sys 0m3.418s
|
*All tests performed on 1GB RAM VMs with aria_pagecache_buffer_size=300M, key_buffer_size=300M, and innodb-buffer-pool-size=300M
Attachments
Issue Links
- relates to
-
-
- Closed
-