Do not trust other peoples benchmarks!

Because they do NOT reflect your problems.

One of our customers upgraded last month from MariaDB 10.2 to MariaDB 10.5. In the same change he also converted all his data warehouse (DWH)/BI tables from MyISAM to Aria. An all this, naturally, without testing. And it miserably failed! And then we were under heavy time pressure to make things working again...

What has changed:

• MariaDB version: MariaDB optimizer got a lot of changes between these 4 major release series (10.2, 10.3, 10.4 and 10.5)!
• Storage Engine change from MyISAM to Aria.
• MariaDB Server System Variable aria_pagecache_buffer_size was not tested and sized properly. In combination with a MariaDB documentation bug.
• A newly introduced MariaDB bug (MDEV-25308)? caused also some confusion.

Literature research

Instead of testing and benchmarking on his own our customer relied on benchmarks done by some other people:

• Benchmarking Aria: These benchmarks, which are older than 2016, claim, that MariaDB is partly faster than MyISAM for internal temporary tables. And partly slower. And they got better results with non default Aria block size (aria_block_size). And they did not benchmark joins (because of internal temporary tables but joins happen quite often in real world).
• Further a MariaDB marketing/sales article by Roger Eisentrager also claims that Aria is partially faster than MyISAM (it was done by a Sales Engineer).
• Then we found another benchmark from 2016 by Oļegs Čapligins and Andrejs Ermuiža showing that Aria is faster as well. Unfortunately we could not see exactly which workload/queries was tested.
• Then we found and article by Denis Szalkowski who came to different results in 2018: Performances comparées des moteurs MyISAM, Aria, InnoDB

Do your own benchmarks

And finally we did our own tests. The following query is just one example out of several others SELECT queries:

EXPLAIN
SELECT SQL_NO_CACHE a.`c6`
     , SUM(ap.`c59`) AS `c59`
     , SUM(ap.`c11`) AS `c11`
  FROM auftragsposition ap
  LEFT JOIN auftrag a ON (a.`c3` = ap.`c3`)
 WHERE a.`c6` BETWEEN '2020-01-01' AND '2020-01-31'
   AND a.`c33` BETWEEN 20 AND 80 AND a.`c33` != 22
   AND a.`c16` != 21
 GROUP BY a.`c6` WITH ROLLUP;

+------+-------------+-------+-------+-----------------+---------+---------+--------------+-------+------------------------------------+
| id   | select_type | table | type  | possible_keys   | key     | key_len | ref          | rows  | Extra                              |
+------+-------------+-------+-------+-----------------+---------+---------+--------------+-------+------------------------------------+
|    1 | SIMPLE      | a     | range | PRIMARY,A-Datum | A-Datum | 3       | NULL         | 48418 | Using index condition; Using where |
|    1 | SIMPLE      | ap    | ref   | PRIMARY         | PRIMARY | 12      | test.a.Order |     5 |                                    |
+------+-------------+-------+-------+-----------------+---------+---------+--------------+-------+------------------------------------+

And here the latency results for our test query:

1. ¹ MyISAM key_buffer_size = 128M
2. ² aria_pagecache_buffer_size = 1G
3. ³ aria_pagecache_buffer_size = 2G
4. ⁴ innodb_buffer_pool_size = 2G
5. ⁵ Table was corrupted because of full disk during conversion from Aria to InnoDB and thus we lost a significant amount of data...

Why are peer reviews good

In our first test series we completely underestimated the footprint of Aria page caching. So our results where worse that they could be (aria_pagecache_buffer_size = 1G). After some discussions we got a hint from a peer and then we got better and thus more realistic results in our second test series (aria_pagecache_buffer_size = 2G).

Conclusion

What can we say so far: It depends! :-) Test your upgrades carefully and do your own benchmarks!

• Aria with wrong configuration is dramatically worse than MyISAM. The impact is much worse than it would be with wrong MyISAM setting.
• Aria for our test join query is about 40% slower than MyISAM.
• Aria for our test join query is still about 20% slower than InnoDB.
• MariaDB 10.5 seems to do some things better than MariaDB 10.4 and earlier. At least for this query. And mostly for InnoDB and Aria with disk reads.
• We still have to fight in some cases with the newer MariaDB 10.5 optimizer which calculates sometimes different query execution plans than MariaDB 10.2. We have to invest more time into the new Engine-Independent Table Statistics (EITS).
• Converting tables from MyISAM to Aria and back to MyISAM caused us a dramatic slow down on MyISAM tables after conversion. This is still under ongoing investigation (MDEV-25308).