- commits - lists.mariadb.org

[Commits] 96c4695c89e: Fix compile failure: P_S-related code should be inside #ifdef
by psergey 10 Mar '20

10 Mar '20

revision-id: 96c4695c89e76c4007da73c9d5ed83c3216a4720 (mariadb-10.4.4-750-g96c4695c89e) parent(s): 9df8918e697c5f201f68810eaf9b68defce0b3b9 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-03-10 01:22:21 +0300 message: Fix compile failure: P_S-related code should be inside #ifdef --- storage/xpand/ha_xpand.cc | 2 ++ 1 file changed, 2 insertions(+) diff --git a/storage/xpand/ha_xpand.cc b/storage/xpand/ha_xpand.cc index bd16a6e2722..3c8c891f51d 100644 --- a/storage/xpand/ha_xpand.cc +++ b/storage/xpand/ha_xpand.cc @@ -73,7 +73,9 @@ static MYSQL_SYSVAR_ENUM ); //current list of clx hosts +#ifdef HAVE_PSI_INTERFACE static PSI_rwlock_key key_xpand_hosts; +#endif mysql_rwlock_t xpand_hosts_lock; xpand_host_list *xpand_hosts;

1 0

[Commits] 3c82e458a01: XPand PR#73: Better behavior with offline cluster
by psergey 10 Mar '20

10 Mar '20

revision-id: 3c82e458a01faaaf17b03a2cd8994f02043aea91 (mariadb-10.5.0-246-g3c82e458a01) parent(s): cd9571467dd00f97ecbbfa95859886066e9cffe2 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-03-09 22:43:59 +0300 message: XPand PR#73: Better behavior with offline cluster Patches from Michael Erickson: remove my_error and my_printf_error make table discovery work add more debug attempt to fix dup key error --- storage/xpand/ha_xpand.cc | 22 +++++++++++++------- storage/xpand/xpand_connection.cc | 43 +++++++++++---------------------------- 2 files changed, 27 insertions(+), 38 deletions(-) diff --git a/storage/xpand/ha_xpand.cc b/storage/xpand/ha_xpand.cc index 78a84b7c049..72605fad230 100644 --- a/storage/xpand/ha_xpand.cc +++ b/storage/xpand/ha_xpand.cc @@ -114,7 +114,7 @@ static void update_hosts(MYSQL_THD thd, struct st_mysql_sys_var *var, int error_code = list->fill(from_save); if (error_code) { my_free(list); - my_printf_error(error_code, "Unhandled error setting xpand hostlist", MYF(0)); + sql_print_error("Unhandled error %d setting xpand hostlist", error_code); DBUG_VOID_RETURN; } @@ -1173,7 +1173,7 @@ int ha_xpand::rnd_pos(uchar * buf, uchar *pos) int ha_xpand::rnd_end() { - DBUG_ENTER("ha_xpand::rnd_end()"); + DBUG_ENTER("ha_xpand::rnd_end"); int error_code = 0; THD *thd = ha_thd(); if (thd->lex->sql_command == SQLCOM_UPDATE) @@ -1439,31 +1439,39 @@ static int xpand_discover_table_names(handlerton *hton, LEX_CSTRING *db, MY_DIR *dir, handlerton::discovered_list *result) { + DBUG_ENTER("xpand_discover_table_names"); xpand_connection *xpand_net = new xpand_connection(); int error_code = xpand_net->connect(); - if (error_code) + if (error_code) { + if (error_code == HA_ERR_NO_CONNECTION) + error_code = 0; goto err; + } - xpand_net->populate_table_list(db, result); + error_code = xpand_net->populate_table_list(db, result); err: delete xpand_net; - return error_code; + DBUG_RETURN(error_code); } int xpand_discover_table(handlerton *hton, THD *thd, TABLE_SHARE *share) { + DBUG_ENTER("xpand_discover_table"); xpand_connection *xpand_net = new xpand_connection(); int error_code = xpand_net->connect(); - if (error_code) + if (error_code) { + if (error_code == HA_ERR_NO_CONNECTION) + error_code = HA_ERR_NO_SUCH_TABLE; goto err; + } error_code = xpand_net->discover_table_details(&share->db, &share->table_name, thd, share); err: delete xpand_net; - return error_code; + DBUG_RETURN(error_code); } static int xpand_init(void *p) diff --git a/storage/xpand/xpand_connection.cc b/storage/xpand/xpand_connection.cc index 28e545fff6d..a4eb18c0bb6 100644 --- a/storage/xpand/xpand_connection.cc +++ b/storage/xpand/xpand_connection.cc @@ -156,7 +156,7 @@ int xpand_connection::connect() mysql_rwlock_rdlock(&xpand_hosts_lock); //search for available host - int error_code = ER_BAD_HOST_ERROR; + int error_code = HA_ERR_NO_CONNECTION; for (int i = 0; i < xpand_hosts->hosts_len; i++) { char *host = xpand_hosts->hosts[(start + i) % xpand_hosts->hosts_len]; error_code = connect_direct(host); @@ -164,9 +164,6 @@ int xpand_connection::connect() break; } mysql_rwlock_unlock(&xpand_hosts_lock); - if (error_code) - my_error(error_code, MYF(0), "clustrix"); - DBUG_RETURN(error_code); } @@ -214,12 +211,9 @@ int xpand_connection::connect_direct(char *host) NULL, xpand_port, xpand_socket, CLIENT_MULTI_STATEMENTS)) { - error_code = mysql_errno(&xpand_net); + sql_print_error("Error connecting to xpand: %s", mysql_error(&xpand_net)); disconnect(); - } - - if (error_code && error_code != ER_CON_COUNT_ERROR) { - error_code = ER_CONNECT_TO_FOREIGN_DATA_SOURCE; + error_code = HA_ERR_NO_CONNECTION; } DBUG_RETURN(error_code); @@ -243,8 +237,6 @@ int xpand_connection::begin_command(uchar command) int xpand_connection::send_command() { - my_bool com_error; - /* Please note: * The transaction state is set before the command is sent because rolling @@ -260,32 +252,18 @@ int xpand_connection::send_command() */ trans_state = XPAND_TRANS_STARTED; - com_error = simple_command(&xpand_net, - (enum_server_command)XPAND_SERVER_REQUEST, - command_buffer, command_length, TRUE); - - if (com_error) - { - int error_code = mysql_errno(&xpand_net); - my_printf_error(error_code, "Xpand error: %s", MYF(0), - mysql_error(&xpand_net)); - return error_code; - } - + if (simple_command(&xpand_net, + (enum_server_command)XPAND_SERVER_REQUEST, + command_buffer, command_length, TRUE)) + return mysql_errno(&xpand_net); return 0; } int xpand_connection::read_query_response() { - my_bool comerr = xpand_net.methods->read_query_result(&xpand_net); int error_code = 0; - if (comerr) - { + if (xpand_net.methods->read_query_result(&xpand_net)) error_code = mysql_errno(&xpand_net); - my_printf_error(error_code, "Xpand error: %s", MYF(0), - mysql_error(&xpand_net)); - } - auto_commit_closed(); return error_code; } @@ -422,8 +400,11 @@ int xpand_connection::write_row(ulonglong xpand_table_oid, uchar *packed_row, if ((error_code = send_command())) return error_code; - if ((error_code = read_query_response())) + if ((error_code = read_query_response())) { + if (error_code == ER_DUP_ENTRY) + return HA_ERR_FOUND_DUPP_KEY; return error_code; + } *last_insert_id = xpand_net.insert_id; return error_code;

1 0

[Commits] 2a6d8e1: 06-* scripts: VARCHAR(n) benchmark, for varying n.
by psergey 09 Mar '20

09 Mar '20

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 dIvoire',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 &

1 0

[Commits] de1d267: 10-sort-int-overhead.sh: fix typo
by psergey 09 Mar '20

09 Mar '20

revision-id: de1d2679f1e19645ffd44cefca2028e643427b23 () parent(s): 407cc78edcc4f65daf64739ed18ff33e4c50d3d5 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-03-09 13:35:58 +0300 message: 10-sort-int-overhead.sh: fix typo --- 10-sort-int-overhead.sh | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/10-sort-int-overhead.sh b/10-sort-int-overhead.sh index a2b2417..15d8da3 100644 --- a/10-sort-int-overhead.sh +++ b/10-sort-int-overhead.sh @@ -8,7 +8,7 @@ mkdir $RES bash filesort-bench1/01-make-sort-int-bench.sh > $RES/sort-int-bench.sql -for SERVER in 10.5-mdev21784-reg1-base 10.5-mdev21784-reg1-vfuncs ; do +for SERVER in mariadb-10.5-mdev21784-reg1-base mariadb-10.5-mdev21784-reg1-vfuncs ; do (cd $SERVER; git log -1) > $RES/tree-$SERVER.txt

1 0

[Commits] 407cc78: 10-sort-int-overhead.sh
by psergey 09 Mar '20

09 Mar '20

revision-id: 407cc78edcc4f65daf64739ed18ff33e4c50d3d5 () parent(s): 8d669d6a36d798f9d4f2162789b702a227484474 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-03-09 00:34:34 +0300 message: 10-sort-int-overhead.sh --- .gitignore | 1 + 10-setup-trees-reg1.sh | 6 ++++++ 10-sort-int-overhead.sh | 29 +++++++++++++++++++++++++++++ 3 files changed, 36 insertions(+) diff --git a/.gitignore b/.gitignore index cac097e..0469c25 100644 --- a/.gitignore +++ b/.gitignore @@ -9,3 +9,4 @@ mariadb-10.5-data mariadb-10.5-vars.sh my-mariadb-10.5.cnf mysql-vars.sh +mariadb-10.4 diff --git a/10-setup-trees-reg1.sh b/10-setup-trees-reg1.sh new file mode 100644 index 0000000..46fe82d --- /dev/null +++ b/10-setup-trees-reg1.sh @@ -0,0 +1,6 @@ +#!/bin/bash + +./setup-server/setup-mariadb-current.sh 10.5-mdev21784-reg1-base +./setup-server/setup-mariadb-current.sh 10.5-mdev21784-reg1-vfuncs + + diff --git a/10-sort-int-overhead.sh b/10-sort-int-overhead.sh new file mode 100644 index 0000000..a2b2417 --- /dev/null +++ b/10-sort-int-overhead.sh @@ -0,0 +1,29 @@ +#!/bin/bash + +set -e + +RES='result-sort-int' + +mkdir $RES + +bash filesort-bench1/01-make-sort-int-bench.sh > $RES/sort-int-bench.sql + +for SERVER in 10.5-mdev21784-reg1-base 10.5-mdev21784-reg1-vfuncs ; 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/sort-int-bench.sql | tee $RES/sort-int-$SERVER.txt + + echo "TEST_RUN:,$SERVER" >> $RES/summary.txt + tail -n 10 $RES/sort-int-$SERVER.txt >> $RES/summary.txt +done + +cat $RES/summary.txt + + + + +

1 0

[Commits] ee6c668f24c: MDEV-21784: Performance testing for packed sort keys
by psergey 09 Mar '20

09 Mar '20

revision-id: ee6c668f24c4e9e49b742b8a2bcaac4ac2298ac0 (mariadb-10.5.0-275-gee6c668f24c) parent(s): 0c35e80dc9ff24bcb8e710cb8cb16428c8c9986f author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-03-09 00:29:48 +0300 message: MDEV-21784: Performance testing for packed sort keys A piece of cumulative patch for MDEV-21580 Allow packed sort keys in sort buffer which just adds some virtual functions (without any calls to them) --- plugin/type_inet/sql_type_inet.cc | 23 ++++ plugin/type_inet/sql_type_inet.h | 3 + sql/field.cc | 61 ++++++++++ sql/field.h | 27 ++++- sql/filesort.cc | 231 ++++++++++++++++++++++++++++++++++++++ sql/sql_class.h | 5 + sql/sql_type.h | 45 ++++++++ 7 files changed, 394 insertions(+), 1 deletion(-) diff --git a/plugin/type_inet/sql_type_inet.cc b/plugin/type_inet/sql_type_inet.cc index 6803bdba434..a660df053a8 100644 --- a/plugin/type_inet/sql_type_inet.cc +++ b/plugin/type_inet/sql_type_inet.cc @@ -1377,6 +1377,29 @@ void Type_handler_inet6::make_sort_key(uchar *to, Item *item, memcpy(to, tmp.ptr(), tmp.length()); } +uint +Type_handler_inet6::make_packed_sort_key(uchar *to, Item *item, + const SORT_FIELD_ATTR *sort_field, + Sort_param *param) const +{ + DBUG_ASSERT(item->type_handler() == this); + NativeBufferInet6 tmp; + item->val_native_result(current_thd, &tmp); + if (item->maybe_null) + { + if (item->null_value) + { + *to++=0; + return 0; + } + *to++= 1; + } + DBUG_ASSERT(!item->null_value); + DBUG_ASSERT(Inet6::binary_length() == tmp.length()); + DBUG_ASSERT(Inet6::binary_length() == sort_field->length); + memcpy(to, tmp.ptr(), tmp.length()); + return tmp.length(); +} void Type_handler_inet6::sortlength(THD *thd, const Type_std_attributes *item, diff --git a/plugin/type_inet/sql_type_inet.h b/plugin/type_inet/sql_type_inet.h index b483ff94e8d..dd4f530dcf8 100644 --- a/plugin/type_inet/sql_type_inet.h +++ b/plugin/type_inet/sql_type_inet.h @@ -516,6 +516,9 @@ class Type_handler_inet6: public Type_handler void make_sort_key(uchar *to, Item *item, const SORT_FIELD_ATTR *sort_field, Sort_param *param) const override; + uint make_packed_sort_key(uchar *to, Item *item, + const SORT_FIELD_ATTR *sort_field, + Sort_param *param) const override; void sortlength(THD *thd, const Type_std_attributes *item, SORT_FIELD_ATTR *attr) const override; diff --git a/sql/field.cc b/sql/field.cc index 1ce49b0bdfa..62dafd55b57 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -1029,6 +1029,61 @@ void Field::make_sort_key(uchar *buff,uint length) } +/* + @brief + Create a packed sort key + + @param buff buffer where values are written + @param sort_field sort column structure + + @retval + length of the bytes written, does not include the NULL bytes +*/ +uint +Field::make_packed_sort_key(uchar *buff, const SORT_FIELD_ATTR *sort_field) +{ + if (maybe_null()) + { + if (is_null()) + { + *buff++= 0; + return 0; // For NULL values don't write any data + } + *buff++=1; + } + sort_string(buff, sort_field->original_length); + return sort_field->original_length; +} + + +uint +Field_longstr::make_packed_sort_key(uchar *buff, + const SORT_FIELD_ATTR *sort_field) +{ + if (maybe_null()) + { + if (is_null()) + { + *buff++= 0; + return 0; // For NULL values don't write any data + } + *buff++=1; + } + uchar *end= pack_sort_string(buff, sort_field); + return static_cast<int>(end-buff); +} + + +uchar* +Field_longstr::pack_sort_string(uchar *to, const SORT_FIELD_ATTR *sort_field) +{ + String buf; + val_str(&buf, &buf); + return to + sort_field->pack_sort_string(to, buf.lex_cstring(), + field_charset()); +} + + /** @brief Determine the relative position of the field value in a numeric interval @@ -8531,6 +8586,12 @@ uint32 Field_blob::sort_length() const } +uint32 Field_blob::sort_suffix_length() const +{ + return field_charset() == &my_charset_bin ? packlength : 0; +} + + void Field_blob::sort_string(uchar *to,uint length) { String buf; diff --git a/sql/field.h b/sql/field.h index 4a8eec35b05..47abae87c1f 100644 --- a/sql/field.h +++ b/sql/field.h @@ -33,6 +33,7 @@ #include "compat56.h" #include "sql_type.h" /* Type_std_attributes */ #include "field_comp.h" +#include "filesort.h" class Send_field; class Copy_field; @@ -1075,6 +1076,12 @@ class Field: public Value_source virtual uint32 data_length() { return pack_length(); } virtual uint32 sort_length() const { return pack_length(); } + /* + sort_suffix_length() return the length bytes needed to store the length + for binary charset + */ + virtual uint32 sort_suffix_length() const { return 0; } + /* Get the number bytes occupied by the value in the field. CHAR values are stripped of trailing spaces. @@ -1410,7 +1417,17 @@ class Field: public Value_source return bytes; } + /* + Create mem-comparable sort keys + */ void make_sort_key(uchar *buff, uint length); + + /* + create a compact sort key which can be compared with a comparison + function. They are called packed sort keys + */ + virtual uint make_packed_sort_key(uchar *buff, + const SORT_FIELD_ATTR *sort_field); virtual void make_send_field(Send_field *); virtual void sort_string(uchar *buff,uint length)=0; virtual bool optimize_range(uint idx, uint part) const; @@ -2140,7 +2157,10 @@ class Field_longstr :public Field_str bool can_optimize_range(const Item_bool_func *cond, const Item *item, bool is_eq_func) const; - bool is_packable() { return true; } + bool is_packable() override { return true; } + uint make_packed_sort_key(uchar *buff, + const SORT_FIELD_ATTR *sort_field)override; + uchar* pack_sort_string(uchar *to, const SORT_FIELD_ATTR *sort_field); }; /* base class for float and double and decimal (old one) */ @@ -4045,6 +4065,10 @@ class Field_varstring :public Field_longstr { return (uint32) field_length + (field_charset() == &my_charset_bin ? length_bytes : 0); } + virtual uint32 sort_suffix_length() const override + { + return (field_charset() == &my_charset_bin ? length_bytes : 0); + } Copy_func *get_copy_func(const Field *from) const override; bool memcpy_field_possible(const Field *from) const override; void update_data_type_statistics(Data_type_statistics *st) const override @@ -4353,6 +4377,7 @@ class Field_blob :public Field_longstr { { return (uint32) (packlength); } uint row_pack_length() const override { return pack_length_no_ptr(); } uint32 sort_length() const override; + uint32 sort_suffix_length() const override; uint32 value_length() override { return get_length(); } virtual uint32 max_data_length() const override { diff --git a/sql/filesort.cc b/sql/filesort.cc index 34fecd516b4..71556a17cc6 100644 --- a/sql/filesort.cc +++ b/sql/filesort.cc @@ -1176,6 +1176,24 @@ Type_handler_timestamp_common::make_sort_key(uchar *to, Item *item, } +void +Type_handler::store_sort_key_longlong(uchar *to, bool unsigned_flag, + longlong value) const +{ + to[7]= (uchar) value; + to[6]= (uchar) (value >> 8); + to[5]= (uchar) (value >> 16); + to[4]= (uchar) (value >> 24); + to[3]= (uchar) (value >> 32); + to[2]= (uchar) (value >> 40); + to[1]= (uchar) (value >> 48); + if (unsigned_flag) /* Fix sign */ + to[0]= (uchar) (value >> 56); + else + to[0]= (uchar) (value >> 56) ^ 128; /* Reverse signbit */ +} + + void Type_handler::make_sort_key_longlong(uchar *to, bool maybe_null, @@ -1207,6 +1225,25 @@ Type_handler::make_sort_key_longlong(uchar *to, } +uint +Type_handler::make_packed_sort_key_longlong(uchar *to, bool maybe_null, + bool null_value, bool unsigned_flag, + longlong value, + const SORT_FIELD_ATTR *sort_field) const +{ + if (maybe_null) + { + if (null_value) + { + *to++= 0; + return 0; + } + *to++= 1; + } + store_sort_key_longlong(to, unsigned_flag, value); + DBUG_ASSERT(sort_field->original_length == sort_field->length); + return sort_field->original_length; +} void Type_handler_decimal_result::make_sort_key(uchar *to, Item *item, const SORT_FIELD_ATTR *sort_field, @@ -2375,3 +2412,197 @@ SORT_INFO::~SORT_INFO() free_data(); DBUG_VOID_RETURN; } + +/* + TODO varun: add comments here +*/ +uint +SORT_FIELD_ATTR::pack_sort_string(uchar *to, const LEX_CSTRING &str, + CHARSET_INFO *cs) const +{ + DBUG_ASSERT(0); + return 0; +/* + uchar *orig_to= to; + size_t length, data_length; + length= str.length; + + if (length + suffix_length <= original_length) + data_length= length; + else + data_length= original_length - suffix_length; + + // length stored in lowendian form + store_lowendian(data_length + suffix_length, to, length_bytes); + to+= length_bytes; + // copying data length bytes to the buffer + memcpy(to, (uchar*)str.str, data_length); + to+= data_length; + + if (cs == &my_charset_bin && suffix_length) + { + // suffix length stored in bigendian form + store_bigendian(str.length, to, suffix_length); + to+= suffix_length; + } + return static_cast<uint>(to - orig_to);*/ +} +uint +Type_handler_string_result::make_packed_sort_key(uchar *to, Item *item, + const SORT_FIELD_ATTR *sort_field, + Sort_param *param) const +{ + CHARSET_INFO *cs= item->collation.collation; + bool maybe_null= item->maybe_null; + + if (maybe_null) + *to++= 1; + + String tmp(param->tmp_buffer, param->sort_length, cs); + String *res= item->str_result(&tmp); + if (!res) + { + if (maybe_null) + { + *(to-1)= 0; + return 0; + } + else + { + /* purecov: begin deadcode */ + /* + This should only happen during extreme conditions if we run out + of memory or have an item marked not null when it can be null. + This code is here mainly to avoid a hard crash in this case. + */ + DBUG_ASSERT(0); + DBUG_PRINT("warning", + ("Got null on something that shouldn't be null")); + memset(to, 0, sort_field->length); // Avoid crash + /* purecov: end */ + return sort_field->original_length; + } + } + return sort_field->pack_sort_string(to, res->lex_cstring(), cs); +} + + +uint +Type_handler_int_result::make_packed_sort_key(uchar *to, Item *item, + const SORT_FIELD_ATTR *sort_field, + Sort_param *param) const +{ + longlong value= item->val_int_result(); + return make_packed_sort_key_longlong(to, item->maybe_null, + item->null_value, item->unsigned_flag, + value, sort_field); +} + + +uint +Type_handler_decimal_result::make_packed_sort_key(uchar *to, Item *item, + const SORT_FIELD_ATTR *sort_field, + Sort_param *param) const +{ + my_decimal dec_buf, *dec_val= item->val_decimal_result(&dec_buf); + if (item->maybe_null) + { + if (item->null_value) + { + *to++=0; + return 0; + } + *to++= 1; + } + dec_val->to_binary(to, item->max_length - (item->decimals ? 1 : 0), + item->decimals); + DBUG_ASSERT(sort_field->original_length == sort_field->length); + return sort_field->original_length; +} + + +uint +Type_handler_real_result::make_packed_sort_key(uchar *to, Item *item, + const SORT_FIELD_ATTR *sort_field, + Sort_param *param) const +{ + double value= item->val_result(); + if (item->maybe_null) + { + if (item->null_value) + { + *to++=0; + return 0; + } + *to++= 1; + } + change_double_for_sort(value, to); + DBUG_ASSERT(sort_field->original_length == sort_field->length); + return sort_field->original_length; +} + + +uint +Type_handler_temporal_result::make_packed_sort_key(uchar *to, Item *item, + const SORT_FIELD_ATTR *sort_field, + Sort_param *param) const +{ + MYSQL_TIME buf; + // This is a temporal type. No nanoseconds. Rounding mode is not important. + DBUG_ASSERT(item->cmp_type() == TIME_RESULT); + static const Temporal::Options opt(TIME_INVALID_DATES, TIME_FRAC_NONE); + if (item->get_date_result(current_thd, &buf, opt)) + { + DBUG_ASSERT(item->maybe_null); + DBUG_ASSERT(item->null_value); + return make_packed_sort_key_longlong(to, item->maybe_null, true, + item->unsigned_flag, 0, sort_field); + } + return make_packed_sort_key_longlong(to, item->maybe_null, false, + item->unsigned_flag, pack_time(&buf), + sort_field); +} + + +uint +Type_handler_timestamp_common::make_packed_sort_key(uchar *to, Item *item, + const SORT_FIELD_ATTR *sort_field, + Sort_param *param) const +{ + THD *thd= current_thd; + uint binlen= my_timestamp_binary_length(item->decimals); + Timestamp_or_zero_datetime_native_null native(thd, item); + if (native.is_null() || native.is_zero_datetime()) + { + // NULL or '0000-00-00 00:00:00' + if (item->maybe_null) + { + *to++=0; + return 0; + } + else + { + bzero(to, binlen); + return binlen; + } + } + else + { + if (item->maybe_null) + *to++= 1; + if (native.length() != binlen) + { + /* + Some items can return native representation with a different + number of fractional digits, e.g.: GREATEST(ts_3, ts_4) can + return a value with 3 fractional digits, although its fractional + precision is 4. Re-pack with a proper precision now. + */ + Timestamp(native).to_native(&native, item->datetime_precision(thd)); + } + DBUG_ASSERT(native.length() == binlen); + memcpy((char *) to, native.ptr(), binlen); + return binlen; + } +} + diff --git a/sql/sql_class.h b/sql/sql_class.h index 13b2659789d..fce35bff2f5 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -6255,6 +6255,11 @@ struct SORT_FIELD_ATTR { uint length; /* Length of sort field */ uint suffix_length; /* Length suffix (0-4) */ + /* Max. length of the original value, in bytes */ + uint original_length; + + uint pack_sort_string(uchar *to, const LEX_CSTRING &str, + CHARSET_INFO *cs) const; }; diff --git a/sql/sql_type.h b/sql/sql_type.h index ce87c8e9d93..b047871a706 100644 --- a/sql/sql_type.h +++ b/sql/sql_type.h @@ -29,6 +29,7 @@ #include "sql_type_string.h" #include "sql_type_real.h" #include "compat56.h" +#include "filesort.h" C_MODE_START #include <ma_dyncol.h> C_MODE_END @@ -86,6 +87,7 @@ class handler; struct Schema_specification_st; struct TABLE; struct SORT_FIELD_ATTR; +struct SORT_FIELD; class Vers_history_point; class Virtual_column_info; class Conv_source; @@ -3332,6 +3334,14 @@ class Type_handler bool maybe_null, bool null_value, bool unsigned_flag, longlong value) const; + void store_sort_key_longlong(uchar *to, bool unsigned_flag, + longlong value) const; + + uint make_packed_sort_key_longlong(uchar *to, bool maybe_null, + bool null_value, bool unsigned_flag, + longlong value, + const SORT_FIELD_ATTR *sort_field) const; + bool Item_func_or_sum_illegal_param(const char *name) const; bool Item_func_or_sum_illegal_param(const Item_func_or_sum *) const; bool check_null(const Item *item, st_value *value) const; @@ -3758,6 +3768,15 @@ class Type_handler virtual void sortlength(THD *thd, const Type_std_attributes *item, SORT_FIELD_ATTR *attr) const= 0; + virtual bool is_packable() const { return false; } + + /* + create a compact sort key which can be compared with a comparison + function. They are called packed sort keys + */ + virtual uint make_packed_sort_key(uchar *to, Item *item, + const SORT_FIELD_ATTR *sort_field, + Sort_param *param) const=0; virtual uint32 max_display_length(const Item *item) const= 0; virtual uint32 Item_decimal_notation_int_digits(const Item *item) const { return 0; } @@ -4155,6 +4174,13 @@ class Type_handler_row: public Type_handler { DBUG_ASSERT(0); } + uint make_packed_sort_key(uchar *to, Item *item, + const SORT_FIELD_ATTR *sort_field, + Sort_param *param) const override + { + DBUG_ASSERT(0); + return 0; + } void sortlength(THD *thd, const Type_std_attributes *item, SORT_FIELD_ATTR *attr) const override { @@ -4483,6 +4509,9 @@ class Type_handler_real_result: public Type_handler_numeric const override; void make_sort_key(uchar *to, Item *item, const SORT_FIELD_ATTR *sort_field, Sort_param *param) const override; + uint make_packed_sort_key(uchar *to, Item *item, + const SORT_FIELD_ATTR *sort_field, + Sort_param *param) const override; void sortlength(THD *thd, const Type_std_attributes *item, SORT_FIELD_ATTR *attr) const override; @@ -4590,6 +4619,9 @@ class Type_handler_decimal_result: public Type_handler_numeric const override; void make_sort_key(uchar *to, Item *item, const SORT_FIELD_ATTR *sort_field, Sort_param *param) const override; + uint make_packed_sort_key(uchar *to, Item *item, + const SORT_FIELD_ATTR *sort_field, + Sort_param *param) const override; void Column_definition_attributes_frm_pack(const Column_definition_attributes *at, uchar *buff) const override; @@ -4840,6 +4872,9 @@ class Type_handler_int_result: public Type_handler_numeric TABLE_SHARE *share) const override; void make_sort_key(uchar *to, Item *item, const SORT_FIELD_ATTR *sort_field, Sort_param *param) const override; + uint make_packed_sort_key(uchar *to, Item *item, + const SORT_FIELD_ATTR *sort_field, + Sort_param *param) const override; void Column_definition_attributes_frm_pack(const Column_definition_attributes *at, uchar *buff) const override; @@ -4947,6 +4982,9 @@ class Type_handler_temporal_result: public Type_handler uchar *buff) const override; void make_sort_key(uchar *to, Item *item, const SORT_FIELD_ATTR *sort_field, Sort_param *param) const override; + uint make_packed_sort_key(uchar *to, Item *item, + const SORT_FIELD_ATTR *sort_field, + Sort_param *param) const override; void sortlength(THD *thd, const Type_std_attributes *item, SORT_FIELD_ATTR *attr) const override; @@ -5036,9 +5074,13 @@ class Type_handler_string_result: public Type_handler CHARSET_INFO *cs) const override; void make_sort_key(uchar *to, Item *item, const SORT_FIELD_ATTR *sort_field, Sort_param *param) const override; + uint make_packed_sort_key(uchar *to, Item *item, + const SORT_FIELD_ATTR *sort_field, + Sort_param *param) const override; void sortlength(THD *thd, const Type_std_attributes *item, SORT_FIELD_ATTR *attr) const override; + bool is_packable()const override { return true; } bool union_element_finalize(const Item * item) const override; uint calc_key_length(const Column_definition &def) const override; bool Column_definition_prepare_stage1(THD *thd, @@ -6232,6 +6274,9 @@ class Type_handler_timestamp_common: public Type_handler_temporal_with_date const override; void make_sort_key(uchar *to, Item *item, const SORT_FIELD_ATTR *sort_field, Sort_param *param) const override; + uint make_packed_sort_key(uchar *to, Item *item, + const SORT_FIELD_ATTR *sort_field, + Sort_param *param) const override; void sortlength(THD *thd, const Type_std_attributes *item, SORT_FIELD_ATTR *attr) const override;

1 0

[Commits] 8d669d6: Code cleanup; print more details
by psergey 09 Mar '20

09 Mar '20

revision-id: 8d669d6a36d798f9d4f2162789b702a227484474 () parent(s): 7466fe9f82cebb0f64b66dab0b96b84e4ba0a9f1 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-03-09 00:20:43 +0300 message: Code cleanup; print more details --- 02-run-sort-int-bench.sh | 17 +++++++++++------ 1 file changed, 11 insertions(+), 6 deletions(-) diff --git a/02-run-sort-int-bench.sh b/02-run-sort-int-bench.sh index eb3c86d..5d6499b 100644 --- a/02-run-sort-int-bench.sh +++ b/02-run-sort-int-bench.sh @@ -8,13 +8,18 @@ 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 +for SERVER in mariadb-10.5-mdev6915-ext mariadb-10.5 ; do + + (cd $SERVER; git log -1) > $RES/tree-$SERVER.txt -$MYSQL $MYSQL_ARGS test < $RES/sort-int-bench.sql | tee $RES/sort-int-mariadb-10.5.txt + bash prepare-server.sh -m $SERVER + source $SERVER-vars.sh -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 + $MYSQL $MYSQL_ARGS test < $RES/sort-int-bench.sql | tee $RES/sort-int-$SERVER.txt + echo "TEST_RUN:,$SERVER" >> $RES/summary.txt + tail -n 10 $RES/sort-int-$SERVER.txt >> $RES/summary.txt +done + +cat $RES/summary.txt

1 0

[Commits] 7466fe9: A test using mysqlslap
by psergey 08 Mar '20

08 Mar '20

revision-id: 7466fe9f82cebb0f64b66dab0b96b84e4ba0a9f1 () parent(s): 0a239bae9791c327ecca1e25d9a082466956bc38 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-03-07 22:52:46 +0300 message: A test using mysqlslap --- 03-run-sort-int-mysqlslap.sh | 33 ++++++++++++++++++++++++ filesort-bench1/02-make-clean-sort-int-tables.sh | 11 ++++++++ filesort-bench1/02-make-fill-sort-int-tables.sh | 21 +++++++++++++++ setup-server/setup-mariadb-current.sh | 3 ++- 4 files changed, 67 insertions(+), 1 deletion(-) diff --git a/03-run-sort-int-mysqlslap.sh b/03-run-sort-int-mysqlslap.sh new file mode 100644 index 0000000..65d1b1f --- /dev/null +++ b/03-run-sort-int-mysqlslap.sh @@ -0,0 +1,33 @@ +#!/bin/bash + +set -e + +RES='result-02-sort-int-mysqlslap' + +mkdir $RES + +# Generate the scripts +bash filesort-bench1/02-make-fill-sort-int-tables.sh > $RES/fill-sort-int-tables.sql +bash filesort-bench1/02-make-clean-sort-int-tables.sh > $RES/clean-sort-int-tables.sql + +for SERVER in mariadb-10.5 mariadb-10.5-mdev6915-ext ; do + +bash prepare-server.sh -m $SERVER +source $SERVER-vars.sh + +$MYSQL $MYSQL_ARGS test < $RES/fill-sort-int-tables.sql | tee $RES/fill-log-$SERVER.txt + +for size in 100000 500000 1000000 2000000 4000000 ; do + +$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 + +done + +$MYSQL $MYSQL_ARGS test < $RES/clean-sort-int-tables.sql | tee $RES/clean-log-$SERVER.txt + +done +exit 0; + diff --git a/filesort-bench1/02-make-clean-sort-int-tables.sh b/filesort-bench1/02-make-clean-sort-int-tables.sh new file mode 100644 index 0000000..9260bec --- /dev/null +++ b/filesort-bench1/02-make-clean-sort-int-tables.sh @@ -0,0 +1,11 @@ +#!/bin/bash + +# Drop the tables + +for size in 100000 500000 1000000 2000000 4000000 8000000 16000000 32000000; do + +cat <<END +drop table if exists t_int_$size; +END + +done diff --git a/filesort-bench1/02-make-fill-sort-int-tables.sh b/filesort-bench1/02-make-fill-sort-int-tables.sh new file mode 100644 index 0000000..99800af --- /dev/null +++ b/filesort-bench1/02-make-fill-sort-int-tables.sh @@ -0,0 +1,21 @@ +#!/bin/bash + +# Just create the tables + +for size in 100000 500000 1000000 2000000 4000000 8000000 16000000 32000000; do + +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 + +done diff --git a/setup-server/setup-mariadb-current.sh b/setup-server/setup-mariadb-current.sh index 12b8b55..9f1b3bb 100755 --- a/setup-server/setup-mariadb-current.sh +++ b/setup-server/setup-mariadb-current.sh @@ -81,7 +81,8 @@ innodb_buffer_pool_size=8G EOF cat > mysql-vars.sh <<EOF -MYSQL="./mariadb-$BRANCH/client/mysql" +MYSQL="`pwd`/mariadb-$BRANCH/client/mysql" +MYSQLSLAP="`pwd`/mariadb-$BRANCH/client/mysqlslap" MYSQL_SOCKET="--socket=$SOCKETNAME" MYSQL_USER="-uroot" MYSQL_ARGS="\$MYSQL_USER \$MYSQL_SOCKET"

1 0

[Commits] 0a239ba: mv 01-run-sort-int-bench.sh 02-run-sort-int-bench.sh
by psergey 08 Mar '20

08 Mar '20

revision-id: 0a239bae9791c327ecca1e25d9a082466956bc38 () parent(s): e947318405a673ca5c88cd399a9c7b6541c1bd8b author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-03-07 21:10:59 +0300 message: mv 01-run-sort-int-bench.sh 02-run-sort-int-bench.sh --- 01-run-sort-int-bench.sh => 02-run-sort-int-bench.sh | 0 1 file changed, 0 insertions(+), 0 deletions(-) diff --git a/01-run-sort-int-bench.sh b/02-run-sort-int-bench.sh similarity index 100% rename from 01-run-sort-int-bench.sh rename to 02-run-sort-int-bench.sh

1 0

[Commits] e947318: update .gitignore
by psergey 07 Mar '20

07 Mar '20

revision-id: e947318405a673ca5c88cd399a9c7b6541c1bd8b () parent(s): e01fbb6ce1b65f068f0e81d8373c86f192c94729 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-03-07 17:39:41 +0300 message: update .gitignore --- .gitignore | 10 ++++++++++ 1 file changed, 10 insertions(+) diff --git a/.gitignore b/.gitignore index 1222055..cac097e 100644 --- a/.gitignore +++ b/.gitignore @@ -1 +1,11 @@ mariadb-10.5-mdev6915-ext +mariadb-10.5-mdev6915-ext-data +mariadb-10.5 +mariadb-10.5-mdev6915-ext-vars.sh +my-mariadb-10.5-mdev6915-ext.cnf +mariadb-10.5-mdev6915-ext-data.clean +mariadb-10.5-data.clean +mariadb-10.5-data +mariadb-10.5-vars.sh +my-mariadb-10.5.cnf +mysql-vars.sh

1 0