|
First attempt: https://gist.github.com/spetrunia/959639071622dba51c2805d0e922922e
|
|
Variables that affect the way sorting is done:
max_length_for_sort_data=1024
|
max_sort_length=1024
|
sort_buffer_size= 2097152 (2M).
|
|
|
partially-automated benchmarking script: make-filesort-bench2.sh
|
|
https://docs.google.com/spreadsheets/d/1YBHuxG4zMKS1DSqNIU_HtBEc2-SsZncZk17w38VGe_Y/edit#gid=992307373
|
|
https://github.com/spetrunia/sort-benchmark,
git clone https://github.com/spetrunia/sort-benchmark.git
|
cd sort-benchmark
|
bash 01-setup-trees.sh
|
bash 02-run-sort-int-bench.sh
|
|
tail -n10 result-sort-int/sort-int-mariadb-10.5.txt
|
tail -n10 result-sort-int/sort-int-mariadb-10.5-mdev6915-ext.txt
|
Results:
https://docs.google.com/spreadsheets/d/15EmZRzIcWkBszUH8gNNnWyjPOVpa16zeK4qsgXIKF_I/edit?usp=sharing
- On Ubuntu Bionic, the new version is slower. On CentOS 7.7, it is a bit faster.
|
|
Tried with a concurrent workload:
$MYSQLSLAP $MYSQL_ARGS \
|
--create-schema=test --no-drop \
|
--query="select a, b from t_int_$size order by a limit 100" \
|
--concurrency=30 --iterations=400 | tee -a $RES/result-$SERVER.txt
|
"Average number of seconds to run all queries:"
old
|
0.018
|
0.088
|
0.174
|
0.345
|
0.694
|
new
|
0.020
|
0.097
|
0.191
|
0.390
|
0.758
|
Again about 10% difference.
Benchmark script:
commit 7466fe9f82cebb0f64b66dab0b96b84e4ba0a9f1 (HEAD -> master, origin/master)
|
Author: Sergei Petrunia <psergey@askmonty.org>
|
Date: Sat Mar 7 22:52:46 2020 +0300
|
|
A test using mysqlslap
|
|
|
This difference is surprising. Technically, MDEV-21580 code puts an "if" or two on the execution path.
For example, make_sortkey now checks for "using_packed_sortkeys":
static uint make_sortkey(Sort_param *param, uchar *to, uchar *ref_pos,
|
bool using_packed_sortkeys)
|
{
|
uchar *orig_to= to;
|
|
to+= using_packed_sortkeys ?
|
make_packed_sortkey(param, to) :
|
make_sortkey(param, to);
|
...
|
but the idea that 2-3 checks like this cause a 10% difference doesn't seem to be plausible.
|
|
Tried this query pattern that's not using sorting at all:
select avg(a), avg(b) from t_int_32000000
|
The results:
old new '=100*G11/F11
|
8 8 100.00
|
38 39 102.63
|
75 78 104.00
|
155 158 101.94
|
305 316 103.61
|
616 636 103.25
|
1226 1268 103.43
|
2495 2537 101.68
|
|
|
The above hints that the cause of the slowdown is not extra code on the execution path.
I've made an experimental patch:
https://github.com/MariaDB/server/commit/ee6c668f24c4e9e49b742b8a2bcaac4ac2298ac0
It adds the virtual functions added by MDEV-21580, but they are not ever used.
https://github.com/MariaDB/server/commits/10.5-mdev21784-reg1-vfuncs
c5.2xlarge:
old new '100*new/old
|
5 6 120.00
|
27 27 100.00
|
53 54 101.89
|
106 109 102.83
|
211 217 102.84
|
422 434 102.84
|
848 869 102.48
|
1686 1737 103.02
|
blackbox:
old new '100*new/old
|
4 4 100.00
|
18 18 100.00
|
36 37 102.78
|
71 74 104.23
|
143 147 102.80
|
284 295 103.87
|
571 591 103.50
|
1140 1179 103.42
|
One can see that extra virtual functions alone add about 3% ?
Benchmark script revision:
commit 407cc78edcc4f65daf64739ed18ff33e4c50d3d5 (HEAD -> master, origin/master)
|
Author: Sergei Petrunia <psergey@askmonty.org>
|
Date: Mon Mar 9 00:34:34 2020 +0300
|
|
10-sort-int-overhead.sh
|
Benchmark output: result-sort-int.aws-c5.overhead.tgz
|
|
Note that this again was not reproducible on CentOS machine.
data from the two runs:
old new 100*new/old
|
13 12 92.31
|
63 50 79.37
|
99 108 109.09
|
200 189 94.50
|
401 378 94.26
|
796 759 95.35
|
1593 1517 95.23
|
3174 3001 94.55
|
old new 100*new/old
|
13 12 92.31
|
50 48 96.00
|
100 93 93.00
|
252 190 75.40
|
405 377 93.09
|
805 753 93.54
|
1610 1525 94.72
|
3245 3044 93.81
|
here, adding new virtual functions made the queries run slightly faster?
|
|
Varchar benchmark:
https://docs.google.com/spreadsheets/d/1uXGeg7-XkHdo0-yvuK5d0d6zxiLUco_At3FmAhHXQdM/edit#gid=0
- For short varchars, new code is slower.
- For long varchars, it is faster
- THere is an odd maximum at varchar(200)?
|
|
Added another tab:
https://docs.google.com/spreadsheets/d/1uXGeg7-XkHdo0-yvuK5d0d6zxiLUco_At3FmAhHXQdM/edit#gid=1218566530
For latin1 character set,
- sorting data in VARCHAR(N) gets more expensive for bigger values of N.
- with packed sort keys, there's no difference.
|
|
More tabs added:
https://docs.google.com/spreadsheets/d/1uXGeg7-XkHdo0-yvuK5d0d6zxiLUco_At3FmAhHXQdM/edit#gid=1367235574
- utf8_general_ci: The query I've tried is faster than MySQL-8.
- utf8_unicode_ci: the new code is faster than the old one
|
|
More benchmarks https://docs.google.com/spreadsheets/d/1CqDb--d6gK_pWyecqU76wsmh0p-vVjCmB_uoknz65DY/
|
make-filesort-bench2.sh