[MDEV-5841] Mariadb very poor temporary performance Created: 2014-03-13  Updated: 2021-04-06  Resolved: 2018-01-18

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Aria
Affects Version/s: 5.5.36
Fix Version/s: 10.1.32, 10.2.14, 10.3.5

Type: Bug Priority: Major
Reporter: Slawomir Pryczek Assignee: Michael Widenius
Resolution: Duplicate Votes: 4
Labels: None
Environment:

fc20, standard install

Attachments: Zip Archive raw_stats.zip    
Issue Links:
Relates
relates to MDEV-8132 Temporary Tables using Aria with very... Closed
relates to MDEV-9079 Aria very slow for internal temporary... Closed
Sprint: 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)

 Comments   
Comment by Sergei Golubchik [ 2014-03-13 ]

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?

Comment by Slawomir Pryczek [ 2014-03-13 ]

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...

Comment by Slawomir Pryczek [ 2014-03-13 ]

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?

Comment by Dimitris Chatzis [ 2018-01-04 ]

After three years MariaDB start checking an easy to reproduce problem and it seems that it will take 2 more years for a fix

Comment by Michael Widenius [ 2018-01-18 ]

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.

Comment by Michael Widenius [ 2018-01-18 ]

Comments for fixing this will be in this issue

Comment by Michael Widenius [ 2018-02-17 ]

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.

Comment by Michael Widenius [ 2021-04-06 ]

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.

Generated at Thu Feb 08 07:07:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.