revision-id: 2a6d8e1aadfd0a11b2bac283ca07195053d64d49 () parent(s): de1d2679f1e19645ffd44cefca2028e643427b23 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-03-09 15:36:27 +0300 message: 06-* scripts: VARCHAR(n) benchmark, for varying n. --- 06-collect-varchar-results.sh | 18 ++ 06-run-varchar-bench.sh | 20 +++ filesort-bench1/06-make-collect-results.sh | 54 ++++++ filesort-bench1/06-make-varchar-bench.sh | 113 +++++++++++++ filesort-bench1/countries.sql | 255 +++++++++++++++++++++++++++++ prepare-server.sh | 23 ++- 6 files changed, 475 insertions(+), 8 deletions(-) diff --git a/06-collect-varchar-results.sh b/06-collect-varchar-results.sh new file mode 100644 index 0000000..70abf8d --- /dev/null +++ b/06-collect-varchar-results.sh @@ -0,0 +1,18 @@ +#!/bin/bash + +set -e + +RES='result-varchars' + +#mkdir $RES + +bash filesort-bench1/06-make-collect-results.sh > $RES/collect-results.sql + +for SERVER in mariadb-10.5-mdev6915-ext mariadb-10.5 ; do + + bash prepare-server.sh -r -m $SERVER + source $SERVER-vars.sh + + $MYSQL $MYSQL_ARGS test < $RES/collect-results.sql | tee $RES/results-$SERVER.txt + +done diff --git a/06-run-varchar-bench.sh b/06-run-varchar-bench.sh new file mode 100644 index 0000000..9dd4ec9 --- /dev/null +++ b/06-run-varchar-bench.sh @@ -0,0 +1,20 @@ +#!/bin/bash + +set -e + +RES='result-varchars' + +mkdir $RES + +bash filesort-bench1/06-make-varchar-bench.sh > $RES/varchar-bench.sql + +for SERVER in mariadb-10.5-mdev6915-ext mariadb-10.5 ; do + + (cd $SERVER; git log -1) > $RES/tree-$SERVER.txt + + bash prepare-server.sh -m $SERVER + source $SERVER-vars.sh + + $MYSQL $MYSQL_ARGS test < $RES/varchar-bench.sql | tee $RES/varchar-$SERVER.txt + +done diff --git a/filesort-bench1/06-make-collect-results.sh b/filesort-bench1/06-make-collect-results.sh new file mode 100644 index 0000000..be49046 --- /dev/null +++ b/filesort-bench1/06-make-collect-results.sh @@ -0,0 +1,54 @@ +#!/bin/bash + +#for table_size in 100000 500000 1000000 2000000 4000000 8000000 ; do +#for varchar_size in 50 100 150 200 250; do + +cat <<END +drop table if exists rep_by_vcsize; +create table rep_by_vcsize ( + table_size int, +END + +for varchar_size in 50 100 150 200 250; do + echo -n " vc${varchar_size} int, " +done + +cat << END + dummy int); +END + +for table_size in 100000 500000 1000000 2000000 4000000 8000000 ; do + +cat << END +insert into rep_by_vcsize select + $table_size, +END + +for varchar_size in 50 100 150 200 250; do + +cat <<END + (select test_time_ms from test_runs where table_size=$table_size and varchar_size= $varchar_size), +END + +done + +cat << END + 0 +from dual; +END + +done + +heading="select 'table_size" +query_str="select concat(table_size,',', " + +for varchar_size in 50 100 150 200 250; do + heading="$heading,$varchar_size" + query_str="$query_str vc${varchar_size},',', " +done + +heading="$heading' as H;" +query_str="$query_str 0) as H from rep_by_vcsize;" + +echo $heading +echo $query_str diff --git a/filesort-bench1/06-make-varchar-bench.sh b/filesort-bench1/06-make-varchar-bench.sh new file mode 100644 index 0000000..bb1754b --- /dev/null +++ b/filesort-bench1/06-make-varchar-bench.sh @@ -0,0 +1,113 @@ +#!/bin/bash + +set -e +## Setup the test + +cat `dirname ${0}`/countries.sql + +cat <<END +drop table if exists test_runs; +drop table if exists test_run_queries; + +-- +-- Info about test runs +-- +create table test_runs ( + table_size int, + varchar_size int, + 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 ( + table_size int, + varchar_size int, + test_ts timestamp, + test_time_ms bigint, + sort_merge_passes int +); +END + +### + + +for table_size in 100000 500000 1000000 2000000 4000000 8000000 ; do +# 16000000 #32000000 +# if + +for varchar_size in 50 100 150 200 250 ; do + +rand_table_name="t_rand_${table_size}_${varchar_size}" +test_table_name="t_char_${table_size}_${varchar_size}" + +cat <<END +set @n_countries=(select count(*) from Country) - 1; + +drop table if exists $rand_table_name; +create table $rand_table_name (a int) engine=myisam; + +drop table if exists $test_table_name; +create table $test_table_name ( + char_field varchar($varchar_size) character set utf8, b int +) engine=myisam; + +insert into $rand_table_name select 1+floor(rand() * @n_countries) from seq_1_to_$table_size; +insert into $test_table_name +select + (select Name from Country where id=T.a), 1234 +from $rand_table_name T ; + +drop table $rand_table_name; +analyze table $test_table_name; +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="group-by-sort-$table_size" + +#QUERY="select a, b from t_int_$size order by a limit 100;" +QUERY="select char_field, count(distinct b) from $test_table_name group by char_field;" + +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 + (table_size, varchar_size, test_ts, test_time_ms, sort_merge_passes) + values ($table_size, $varchar_size, @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 table_size=$table_size and varchar_size=$varchar_size); +set @sort_buffers= (select group_concat(distinct sort_merge_passes) from test_run_queries + where table_size=$table_size and varchar_size=$varchar_size); +insert into test_runs(table_size, varchar_size, test_ts, test_time_ms, sort_merge_passes) values + ($table_size, $varchar_size, current_timestamp(6), @min_time, @sort_buffers); +drop table $test_table_name; +END + +done + +done + + diff --git a/filesort-bench1/countries.sql b/filesort-bench1/countries.sql new file mode 100644 index 0000000..8f48662 --- /dev/null +++ b/filesort-bench1/countries.sql @@ -0,0 +1,255 @@ +drop table if exists Country; +CREATE TABLE Country ( + id int auto_increment, + Code char(3) NOT NULL default '', + Name char(52) NOT NULL default '', + SurfaceArea float(10,2) NOT NULL default '0.00', + Population int(11) NOT NULL default '0', + Capital int(11) default NULL, + PRIMARY KEY(id), + UNIQUE KEY (Code), + UNIQUE INDEX (Name) +); + +INSERT IGNORE INTO Country (Code, Name, SurfaceArea , Population, Capital) +VALUES +('AFG','Afghanistan',652090.00,22720000,1), +('NLD','Netherlands',41526.00,15864000,5), +('ANT','Netherlands Antilles',800.00,217000,33), +('ALB','Albania',28748.00,3401200,34), +('DZA','Algeria',2381741.00,31471000,35), +('ASM','American Samoa',199.00,68000,54), +('AND','Andorra',468.00,78000,55), +('AGO','Angola',1246700.00,12878000,56), +('AIA','Anguilla',96.00,8000,62), +('ATG','Antigua and Barbuda',442.00,68000,63), +('ARE','United Arab Emirates',83600.00,2441000,65), +('ARG','Argentina',2780400.00,37032000,69), +('ARM','Armenia',29800.00,3520000,126), +('ABW','Aruba',193.00,103000,129), +('AUS','Australia',7741220.00,18886000,135), +('AZE','Azerbaijan',86600.00,7734000,144), +('BHS','Bahamas',13878.00,307000,148), +('BHR','Bahrain',694.00,617000,149), +('BGD','Bangladesh',143998.00,129155000,150), +('BRB','Barbados',430.00,270000,174), +('BEL','Belgium',30518.00,10239000,179), +('BLZ','Belize',22696.00,241000,185), +('BEN','Benin',112622.00,6097000,187), +('BMU','Bermuda',53.00,65000,191), +('BTN','Bhutan',47000.00,2124000,192), +('BOL','Bolivia',1098581.00,8329000,194), +('BIH','Bosnia and Herzegovina',51197.00,3972000,201), +('BWA','Botswana',581730.00,1622000,204), +('BRA','Brazil',8547403.00,170115000,211), +('GBR','United Kingdom',242900.00,59623400,456), +('VGB','Virgin Islands, British',151.00,21000,537), +('BRN','Brunei',5765.00,328000,538), +('BGR','Bulgaria',110994.00,8190900,539), +('BFA','Burkina Faso',274000.00,11937000,549), +('BDI','Burundi',27834.00,6695000,552), +('CYM','Cayman Islands',264.00,38000,553), +('CHL','Chile',756626.00,15211000,554), +('COK','Cook Islands',236.00,20000,583), +('CRI','Costa Rica',51100.00,4023000,584), +('DJI','Djibouti',23200.00,638000,585), +('DMA','Dominica',751.00,71000,586), +('DOM','Dominican Republic',48511.00,8495000,587), +('ECU','Ecuador',283561.00,12646000,594), +('EGY','Egypt',1001449.00,68470000,608), +('SLV','El Salvador',21041.00,6276000,645), +('ERI','Eritrea',117600.00,3850000,652), +('ESP','Spain',505992.00,39441700,653), +('ZAF','South Africa',1221037.00,40377000,716), +('ETH','Ethiopia',1104300.00,62565000,756), +('FLK','Falkland Islands',12173.00,2000,763), +('FJI','Fiji Islands',18274.00,817000,764), +('PHL','Philippines',300000.00,75967000,766), +('FRO','Faroe Islands',1399.00,43000,901), +('GAB','Gabon',267668.00,1226000,902), +('GMB','Gambia',11295.00,1305000,904), +('GEO','Georgia',69700.00,4968000,905), +('GHA','Ghana',238533.00,20212000,910), +('GIB','Gibraltar',6.00,25000,915), +('GRD','Grenada',344.00,94000,916), +('GRL','Greenland',2166090.00,56000,917), +('GLP','Guadeloupe',1705.00,456000,919), +('GUM','Guam',549.00,168000,921), +('GTM','Guatemala',108889.00,11385000,922), +('GIN','Guinea',245857.00,7430000,926), +('GNB','Guinea-Bissau',36125.00,1213000,927), +('GUY','Guyana',214969.00,861000,928), +('HTI','Haiti',27750.00,8222000,929), +('HND','Honduras',112088.00,6485000,933), +('HKG','Hong Kong',1075.00,6782000,937), +('SJM','Svalbard and Jan Mayen',62422.00,3200,938), +('IDN','Indonesia',1904569.00,212107000,939), +('IND','India',3287263.00,1013662000,1109), +('IRQ','Iraq',438317.00,23115000,1365), +('IRN','Iran',1648195.00,67702000,1380), +('IRL','Ireland',70273.00,3775100,1447), +('ISL','Iceland',103000.00,279000,1449), +('ISR','Israel',21056.00,6217000,1450), +('ITA','Italy',301316.00,57680000,1464), +('TMP','East Timor',14874.00,885000,1522), +('AUT','Austria',83859.00,8091800,1523), +('JAM','Jamaica',10990.00,2583000,1530), +('JPN','Japan',377829.00,126714000,1532), +('YEM','Yemen',527968.00,18112000,1780), +('JOR','Jordan',88946.00,5083000,1786), +('CXR','Christmas Island',135.00,2500,1791), +('YUG','Yugoslavia',102173.00,10640000,1792), +('KHM','Cambodia',181035.00,11168000,1800), +('CMR','Cameroon',475442.00,15085000,1804), +('CAN','Canada',9970610.00,31147000,1822), +('CPV','Cape Verde',4033.00,428000,1859), +('KAZ','Kazakstan',2724900.00,16223000,1864), +('KEN','Kenya',580367.00,30080000,1881), +('CAF','Central African Republic',622984.00,3615000,1889), +('CHN','China',9572900.00,1277558000,1891), +('KGZ','Kyrgyzstan',199900.00,4699000,2253), +('KIR','Kiribati',726.00,83000,2256), +('COL','Colombia',1138914.00,42321000,2257), +('COM','Comoros',1862.00,578000,2295), +('COG','Congo',342000.00,2943000,2296), +('COD','Congo, The Democratic Republic of the',2344858.00,51654000,2298), +('CCK','Cocos (Keeling) Islands',14.00,600,2317), +('PRK','North Korea',120538.00,24039000,2318), +('KOR','South Korea',99434.00,46844000,2331), +('GRC','Greece',131626.00,10545700,2401), +('HRV','Croatia',56538.00,4473000,2409), +('CUB','Cuba',110861.00,11201000,2413), +('KWT','Kuwait',17818.00,1972000,2429), +('CYP','Cyprus',9251.00,754700,2430), +('LAO','Laos',236800.00,5433000,2432), +('LVA','Latvia',64589.00,2424200,2434), +('LSO','Lesotho',30355.00,2153000,2437), +('LBN','Lebanon',10400.00,3282000,2438), +('LBR','Liberia',111369.00,3154000,2440), +('LBY','Libyan Arab Jamahiriya',1759540.00,5605000,2441), +('LIE','Liechtenstein',160.00,32300,2446), +('LTU','Lithuania',65301.00,3698500,2447), +('LUX','Luxembourg',2586.00,435700,2452), +('ESH','Western Sahara',266000.00,293000,2453), +('MAC','Macao',18.00,473000,2454), +('MDG','Madagascar',587041.00,15942000,2455), +('MKD','Macedonia',25713.00,2024000,2460), +('MWI','Malawi',118484.00,10925000,2462), +('MDV','Maldives',298.00,286000,2463), +('MYS','Malaysia',329758.00,22244000,2464), +('MLI','Mali',1240192.00,11234000,2482), +('MLT','Malta',316.00,380200,2484), +('MAR','Morocco',446550.00,28351000,2486), +('MHL','Marshall Islands',181.00,64000,2507), +('MTQ','Martinique',1102.00,395000,2508), +('MRT','Mauritania',1025520.00,2670000,2509), +('MUS','Mauritius',2040.00,1158000,2511), +('MYT','Mayotte',373.00,149000,2514), +('MEX','Mexico',1958201.00,98881000,2515), +('FSM','Micronesia, Federated States of',702.00,119000,2689), +('MDA','Moldova',33851.00,4380000,2690), +('MCO','Monaco',1.50,34000,2695), +('MNG','Mongolia',1566500.00,2662000,2696), +('MSR','Montserrat',102.00,11000,2697), +('MOZ','Mozambique',801590.00,19680000,2698), +('MMR','Myanmar',676578.00,45611000,2710), +('NAM','Namibia',824292.00,1726000,2726), +('NRU','Nauru',21.00,12000,2728), +('NPL','Nepal',147181.00,23930000,2729), +('NIC','Nicaragua',130000.00,5074000,2734), +('NER','Niger',1267000.00,10730000,2738), +('NGA','Nigeria',923768.00,111506000,2754), +('NIU','Niue',260.00,2000,2805), +('NFK','Norfolk Island',36.00,2000,2806), +('NOR','Norway',323877.00,4478500,2807), +('CIV','Côte d’Ivoire',322463.00,14786000,2814), +('OMN','Oman',309500.00,2542000,2821), +('PAK','Pakistan',796095.00,156483000,2831), +('PLW','Palau',459.00,19000,2881), +('PAN','Panama',75517.00,2856000,2882), +('PNG','Papua New Guinea',462840.00,4807000,2884), +('PRY','Paraguay',406752.00,5496000,2885), +('PER','Peru',1285216.00,25662000,2890), +('PCN','Pitcairn',49.00,50,2912), +('MNP','Northern Mariana Islands',464.00,78000,2913), +('PRT','Portugal',91982.00,9997600,2914), +('PRI','Puerto Rico',8875.00,3869000,2919), +('POL','Poland',323250.00,38653600,2928), +('GNQ','Equatorial Guinea',28051.00,453000,2972), +('QAT','Qatar',11000.00,599000,2973), +('FRA','France',551500.00,59225700,2974), +('GUF','French Guiana',90000.00,181000,3014), +('PYF','French Polynesia',4000.00,235000,3016), +('REU','Réunion',2510.00,699000,3017), +('ROM','Romania',238391.00,22455500,3018), +('RWA','Rwanda',26338.00,7733000,3047), +('SWE','Sweden',449964.00,8861400,3048), +('SHN','Saint Helena',314.00,6000,3063), +('KNA','Saint Kitts and Nevis',261.00,38000,3064), +('LCA','Saint Lucia',622.00,154000,3065), +('VCT','Saint Vincent and the Grenadines',388.00,114000,3066), +('SPM','Saint Pierre and Miquelon',242.00,7000,3067), +('DEU','Germany',357022.00,82164700,3068), +('SLB','Solomon Islands',28896.00,444000,3161), +('ZMB','Zambia',752618.00,9169000,3162), +('WSM','Samoa',2831.00,180000,3169), +('SMR','San Marino',61.00,27000,3171), +('STP','Sao Tome and Principe',964.00,147000,3172), +('SAU','Saudi Arabia',2149690.00,21607000,3173), +('SEN','Senegal',196722.00,9481000,3198), +('SYC','Seychelles',455.00,77000,3206), +('SLE','Sierra Leone',71740.00,4854000,3207), +('SGP','Singapore',618.00,3567000,3208), +('SVK','Slovakia',49012.00,5398700,3209), +('SVN','Slovenia',20256.00,1987800,3212), +('SOM','Somalia',637657.00,10097000,3214), +('LKA','Sri Lanka',65610.00,18827000,3217), +('SDN','Sudan',2505813.00,29490000,3225), +('FIN','Finland',338145.00,5171300,3236), +('SUR','Suriname',163265.00,417000,3243), +('SWZ','Swaziland',17364.00,1008000,3244), +('CHE','Switzerland',41284.00,7160400,3248), +('SYR','Syria',185180.00,16125000,3250), +('TJK','Tajikistan',143100.00,6188000,3261), +('TWN','Taiwan',36188.00,22256000,3263), +('TZA','Tanzania',883749.00,33517000,3306), +('DNK','Denmark',43094.00,5330000,3315), +('THA','Thailand',513115.00,61399000,3320), +('TGO','Togo',56785.00,4629000,3332), +('TKL','Tokelau',12.00,2000,3333), +('TON','Tonga',650.00,99000,3334), +('TTO','Trinidad and Tobago',5130.00,1295000,3336), +('TCD','Chad',1284000.00,7651000,3337), +('CZE','Czech Republic',78866.00,10278100,3339), +('TUN','Tunisia',163610.00,9586000,3349), +('TUR','Turkey',774815.00,66591000,3358), +('TKM','Turkmenistan',488100.00,4459000,3419), +('TCA','Turks and Caicos Islands',430.00,17000,3423), +('TUV','Tuvalu',26.00,12000,3424), +('UGA','Uganda',241038.00,21778000,3425), +('UKR','Ukraine',603700.00,50456000,3426), +('HUN','Hungary',93030.00,10043200,3483), +('URY','Uruguay',175016.00,3337000,3492), +('NCL','New Caledonia',18575.00,214000,3493), +('NZL','New Zealand',270534.00,3862000,3499), +('UZB','Uzbekistan',447400.00,24318000,3503), +('BLR','Belarus',207600.00,10236000,3520), +('WLF','Wallis and Futuna',200.00,15000,3536), +('VUT','Vanuatu',12189.00,190000,3537), +('VAT','Holy See (Vatican City State)',0.40,1000,3538), +('VEN','Venezuela',912050.00,24170000,3539), +('RUS','Russian Federation',17075400.00,146934000,3580), +('VNM','Vietnam',331689.00,79832000,3770), +('EST','Estonia',45227.00,1439200,3791), +('USA','United States',9363520.00,278357000,3813), +('VIR','Virgin Islands, U.S.',347.00,93000,4067), +('ZWE','Zimbabwe',390757.00,11669000,4068), +('PSE','Palestine',6257.00,3101000,4074), +('ATA','Antarctica',13120000.00,0,NULL), +('BVT','Bouvet Island',59.00,0,NULL), +('IOT','British Indian Ocean Territory',78.00,0,NULL), +('SGS','South Georgia and the South Sandwich Islands',3903.00,0,NULL), +('HMD','Heard Island and McDonald Islands',359.00,0,NULL), +('ATF','French Southern territories',7780.00,0,NULL), +('UMI','United States Minor Outlying Islands',16.00,0,NULL); + diff --git a/prepare-server.sh b/prepare-server.sh index 03c0100..01dbeba 100755 --- a/prepare-server.sh +++ b/prepare-server.sh @@ -12,10 +12,12 @@ echo " -m - Put datadir on /dev/shm" ### Parse options ### -while getopts ":pmcd" opt; do +while getopts ":pmr" opt; do case ${opt} in m ) USE_RAMDISK=1 ;; + r ) RECOVER=1 + ;; \? ) usage; exit 1 @@ -46,15 +48,20 @@ killall -9 mysqld sleep 5 DATA_DIR=$SERVERNAME-data -rm -rf $DATA_DIR -if [[ $USE_RAMDISK ]] ; then - rm -rf /dev/shm/$DATA_DIR - cp -r ${DATA_DIR}.clean /dev/shm/$DATA_DIR - ln -s /dev/shm/$DATA_DIR $DATA_DIR +if [[ $RECOVER ]] ; then + echo "Recovering the existing datadir" else - cp -r ${DATA_DIR}.clean $DATA_DIR -fi + echo "Initializing new datadir" + rm -rf $DATA_DIR + if [[ $USE_RAMDISK ]] ; then + rm -rf /dev/shm/$DATA_DIR + cp -r ${DATA_DIR}.clean /dev/shm/$DATA_DIR + ln -s /dev/shm/$DATA_DIR $DATA_DIR + else + cp -r ${DATA_DIR}.clean $DATA_DIR + fi +fi #exit 0; ./$SERVERNAME/sql/mysqld --defaults-file=./my-${SERVERNAME}.cnf &