revision-id: e01fbb6ce1b65f068f0e81d8373c86f192c94729 () parent(s): e6ec7389b0d53bd63730b02deead2291d2740de3 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-03-07 17:39:03 +0300 message: First run: 01-run-sort-int-bench.sh --- 01-run-sort-int-bench.sh | 20 ++++++ filesort-bench1/01-make-sort-int-bench.sh | 101 ++++++++++++++++++++++++++++++ 2 files changed, 121 insertions(+) diff --git a/01-run-sort-int-bench.sh b/01-run-sort-int-bench.sh new file mode 100644 index 0000000..eb3c86d --- /dev/null +++ b/01-run-sort-int-bench.sh @@ -0,0 +1,20 @@ +#!/bin/bash + +set -e + +RES='result-sort-int' + +mkdir $RES + +bash filesort-bench1/01-make-sort-int-bench.sh > $RES/sort-int-bench.sql + +bash prepare-server.sh -m mariadb-10.5 +source mariadb-10.5-vars.sh + +$MYSQL $MYSQL_ARGS test < $RES/sort-int-bench.sql | tee $RES/sort-int-mariadb-10.5.txt + +source mariadb-10.5-mdev6915-ext-vars.sh +bash prepare-server.sh -m mariadb-10.5-mdev6915-ext +$MYSQL $MYSQL_ARGS test < $RES/sort-int-bench.sql | tee $RES/sort-int-mariadb-10.5-mdev6915-ext.txt + + diff --git a/filesort-bench1/01-make-sort-int-bench.sh b/filesort-bench1/01-make-sort-int-bench.sh new file mode 100644 index 0000000..1b38adf --- /dev/null +++ b/filesort-bench1/01-make-sort-int-bench.sh @@ -0,0 +1,101 @@ +#!/bin/bash + +## Setup the test + +cat <<END +drop table if exists test_runs; +drop table if exists test_run_queries; + +-- +-- Info about test runs +-- +create table test_runs ( + test_name varchar(255), + test_ts timestamp, + test_time_ms bigint, + sort_merge_passes varchar(255) +); + + +-- Individual queries that are ran as part of the test +create table test_run_queries ( + test_name varchar(255), + test_ts timestamp, + test_time_ms bigint, + sort_merge_passes int +); +END + +### + + +for size in 100000 500000 1000000 2000000 4000000 8000000 16000000 32000000; do + +# if + +cat <<END +drop table if exists t_int_$size; +create table t_int_$size ( + a int, b int +) engine=myisam; + +insert into t_int_$size +select + floor(rand() * 25), 1234 +from seq_1_to_$size; + +analyze table t_int_$size; +END + +for i in 1 2 3 4 5 6 7 8 9 10 ; do + +### query_start.sql here: +cat <<END +select variable_value into @query_start_smp from information_schema.session_status where variable_name like 'sort_merge_passes'; +select current_timestamp(6) into @query_start_time; +END +### + +### THE QUERY: + +TEST_NAME="sort-int-limit-$size" + +QUERY="select a, b from t_int_$size order by a limit 100;" +#select a, count(distinct b) from t_int_$size group by a; + +echo $QUERY + +### query-end.sql here: +cat << END +set @test_name='$TEST_NAME'; +set @query_time_ms= timestampdiff(microsecond, @query_start_time, current_timestamp(6))/1000; +select variable_value into @query_end_smp from information_schema.session_status where variable_name like 'sort_merge_passes'; +set @query_merge_passes = @query_end_smp - @query_start_smp; +insert into test_run_queries + (test_name, test_ts, test_time_ms, sort_merge_passes) + values (@test_name, @query_start_time, @query_time_ms, @query_merge_passes); +END + + +done + +# Summarize results from multiple runs of one query: +cat <<END +set @min_time = (select min(test_time_ms) from test_run_queries where test_name=@test_name); +set @sort_buffers= (select group_concat(distinct sort_merge_passes) from test_run_queries where test_name=@test_name); +insert into test_runs(test_name, test_ts, test_time_ms, sort_merge_passes) values + (@test_name, current_timestamp(6), @min_time, @sort_buffers); +END + + +done + +cat <<END +select '${QUERY/'/\\'}'; +select test_name,test_time_ms,sort_merge_passes from test_runs; +select concat(test_name, ',', + test_time_ms, ',', + sort_merge_passes) +from test_runs; +END +