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)
Attachments
Issue Links
Activity
Im using stock (default) config, with aria page buffer set at 4G.
No changes other than that. Have you did your tests on normal or SSD disks?
Any explaination why when setting row_format to dynamic (or fixed) ARIA is 20 times faster than using default fow_format=PAGE? (i see in docs that it shouldn't write to disk, so that's counter-intuitive than storing data in buffer is so slow).
Is that ARIA buffer split into parts like for myisam, so they can use several mutexes? Im running on 32 not too speedy cores, may that be a problem? Hyperthreading enabled.
>In your tests it doesn't matter (almost) whether a table is temporary or not. May be your Aria engine is seriously mis-configured?
My point is rather that if we don't see any performance gain then there's no point in using aria, however you said it's actually improving speed. So now it's confusing, why my results are so bad...
Another quick comment... im issuing same queries against some other tables, on not loaded (unused) server, and again there's the same behaviour...
Myisam => 1.5s
Aria, PAGE => 12-15s
Aria, DYNAMIC => 1.5s
So when there's no traffic it seems that differences get smaller.
Intel(R) Xeon(R) CPU E5-2650 v2 @ 2.60GHz
32 cores, 128GB Ram. Fedora Core 20.
I also tested reading from tables. It seems that aria with PAGE is about 10% faster than myisam...
Do you have some estimate, how many temporary rows are created in your GROUP-BY temporary table. Maybe that table is just kept in-memory because it's smaller than TMP MEM table limit?
After three years MariaDB start checking an easy to reproduce problem and it seems that it will take 2 more years for a fix 
Sorry for the slow delay, I was just not got aware of this issue.
I am now fixing MDEV-8132 and this should also fix this issue.
Fixed performance problem with Aria by speeding up find_head().
The result of this patch is a up to 2x or more speed up for inserts without keys for
format PAGE.
A note about the original issue:
If you are using Aria transactional tables (CREATE ... TRANSACTIONAL=1) for temporary tables those will be slow as there will be a sync for every insert an that can explain a 10x slowdown. The fix that was done for find_head should make Aria notable faster for internal temporary tables in most cases compared to MyISAM.
There's some confusion in here. The variable aria_used_for_temp_tables doesn't affect tables that you create, they use the default storage engine setting. That variable only affects temporary tables that were created internally (for example, to resolve GROUP BY). And for this, in my tests, Aria was up to three times faster than MyISAM, simply because MyISAM always writes data to disk, while Aria caches them in the page cache and a temporary table can be discarded before any of its pages is ever flushed to disk.
In your tests it doesn't matter (almost) whether a table is temporary or not. May be your Aria engine is seriously mis-configured?