we'll be updating in company SQL software, and i benchmarked several recent versions of mysql. Basically thanks to some optimizer / internal changes it seems that recent mariadb is like 10 times faster than mysql5.6 and percona in some cases. Also it seems to work better than recent mysql 5.7...
But in some queries i see that 90% of time is wasted copying to temp tables.
This is initial benchmark:
Now there could be some errors in documentation, as far as i understand, increasing aria_pagecache_buffer_size should result in no disk writes, but it doesn't make any difference.
So i played with configuration a little, it seems that increasing
max_heap_table_size = 1024M
tmp_table_size = 1024M
+ setting aria page size to 1KB
Is improving things a lot, run time gets cut by half to about 4 seconds. So maybe docs need to be updated...
The problem is that out of the box, with "default" heap / tmp table settings mysql 5.7 is much faster 5-6s instead of 8-9s (mariadb 10.1.8). I compiled the server from sources disabling ARIA as TMP engine, and it resulted in run times close to 4-5s. So it was faster than mysql. After changing tmp table settings as above it went to 3s.
Out of the box: 8s
TMP engine changed to myisam+optimized: 3s
So im filling it as major bug as having maria as default storage angine for TMP tables seem to degrade performance a lot. Honestly i wasn't able to find queries where it'll improve things. In DOC it says that table scans are faster over myisam than aria. Is there a reason to keep it as default engine and removing ability to set it in server config without compiling from sources?
This is max what i got from internal TMP tables using myisam:
This is aria, after optimization, changing page size, etc.:
In docs it says that doing full table scans it's slower for PAGE (which seems true). Maybe it'd be good to have more control over it, eg. maybe using ROW_FORMAT=Dynamic for all internal aria temporary tables would make aria on par with myisam for internal tmp tables? Maybe just queries im doing don't play nicely with aria, any suggestions?
It seems that not being able to fully control how internal tmp tables behave is having big performance impact.