Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
5.5.36
-
None
-
fc20, standard install
-
5.5.59, 10.0.34
Description
Hello,
I found a very suprising thing in maria actually, by default it's using aria engine with page row format as temporary storage, which is horribly slow.
I have done some quick test, creating temporary table with 200k rows copied, now the results are just SHOCKING.
MEMORY engine <1s
Innodb <4s
myisam <1.5s
tokudb <2.5s
Now look at this:
http://screencast.com/t/dcKdux3d72DR
20 seconds, repeatable. So 20 TIMES slower than myisam. Maybe i don't get something but this looks very bad. SSD disks, 32 CPU cores.
Now prepare for another shock.
http://screencast.com/t/9Am6dGYA
Just changing row_format to dynamic makes the engine almost 20 times faster. And now something strange. Next query, with removed ENGINE=ARIA.
http://screencast.com/t/BYGWZnWMB
Just removing engine=ARIA makes the query almost 5 TIMES slower, where ARIA engine is the default!
This is repeatable, time differences are minimal (0.2-0.5s)...
Now here's the magic:
http://screencast.com/t/uaV6cSHcYB
When ROW_FORMAT is specified, the server will switch temporary DB engine to innoDB... While
aria_used_for_temp_tables = ON
Source table = myisam
http://screencast.com/t/efeTmteO7
CREATE TABLE `raw_stats_value_copy_copy1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`value` varchar(255) NOT NULL,
`__last_used` date NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `value` (`value`)
) ENGINE=MyISAM AUTO_INCREMENT=224384 DEFAULT CHARSET=utf8 PAGE_CHECKSUM=0 TRANSACTIONAL=0;
To sum it up... because of some very weird configuration TEMP tables in mariadb are 5-20 times slower than in mysql. For some tables with only integers it takes 6s to create TMP table with 300k rows with ARIA/PAGE, 1.5s with ARIA/DYNAMIC and 1s with MYISAM, etc. So this isn't limited just to char fields.
Solution: get rid of aria as default TMP engine ( i see no point as even innodb seem much faster ), or set row format to DYNAMIC... or im not getting something?
Attaching the table that makes 1:5 difference (can't attach the better example with text data unfortunately)