revision-id: 7d8e48a51faa941f89031cec16b5a0c07ee2524c (mariadb-10.5.0-274-g7d8e48a51fa) parent(s): 8db623038f7158529e804e9607362939bff37337 author: Varun Gupta committer: Varun Gupta timestamp: 2020-02-28 11:46:13 +0530 message: MDEV-21838: Add information about packed addon fields in ANALYZE FORMAT=JSON It is useful to know whether sorting uses addon fields[packed|unpacked] or ROWID. Provide this information in ANALYZE FORMAT=JSON output. --- mysql-test/main/analyze_format_json.result | 6 +++ mysql-test/main/analyze_stmt_orderby.result | 7 +++ mysql-test/main/order_by_pack_big.result | 73 +++++++++++++++++++++++++++++ mysql-test/main/order_by_pack_big.test | 2 + sql/filesort.cc | 4 ++ sql/sql_analyze_stmt.cc | 20 ++++++++ sql/sql_analyze_stmt.h | 14 +++++- 7 files changed, 125 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/analyze_format_json.result b/mysql-test/main/analyze_format_json.result index ccef3a63592..c505aae563b 100644 --- a/mysql-test/main/analyze_format_json.result +++ b/mysql-test/main/analyze_format_json.result @@ -517,6 +517,7 @@ ANALYZE "r_used_priority_queue": false, "r_output_rows": 0, "r_buffer_size": "REPLACED", + "r_sort_mode": "sort_key,rowid", "temporary_table": { "table": { "table_name": "t2", @@ -548,6 +549,7 @@ ANALYZE "r_used_priority_queue": false, "r_output_rows": 256, "r_buffer_size": "REPLACED", + "r_sort_mode": "sort_key,rowid", "temporary_table": { "table": { "table_name": "t2", @@ -590,6 +592,7 @@ ANALYZE "r_used_priority_queue": false, "r_output_rows": 256, "r_buffer_size": "REPLACED", + "r_sort_mode": "sort_key,rowid", "temporary_table": { "table": { "table_name": "t2", @@ -701,6 +704,7 @@ ANALYZE "r_used_priority_queue": false, "r_output_rows": 0, "r_buffer_size": "REPLACED", + "r_sort_mode": "sort_key,rowid", "temporary_table": { "filesort": { "sort_key": "(subquery#2)", @@ -709,6 +713,7 @@ ANALYZE "r_used_priority_queue": false, "r_output_rows": 0, "r_buffer_size": "REPLACED", + "r_sort_mode": "sort_key,rowid", "temporary_table": { "table": { "table_name": "t2", @@ -824,6 +829,7 @@ ANALYZE "r_limit": null, "r_used_priority_queue": null, "r_output_rows": null, + "r_sort_mode": "sort_key,rowid", "table": { "table_name": "t2", "access_type": "ALL", diff --git a/mysql-test/main/analyze_stmt_orderby.result b/mysql-test/main/analyze_stmt_orderby.result index e23813944f4..47bc856ba53 100644 --- a/mysql-test/main/analyze_stmt_orderby.result +++ b/mysql-test/main/analyze_stmt_orderby.result @@ -44,6 +44,7 @@ ANALYZE "r_limit": 5, "r_used_priority_queue": true, "r_output_rows": 6, + "r_sort_mode": "sort_key,rowid", "table": { "update": 1, "table_name": "t2", @@ -145,6 +146,7 @@ ANALYZE "r_used_priority_queue": false, "r_output_rows": 10000, "r_buffer_size": "REPLACED", + "r_sort_mode": "sort_key,rowid", "table": { "delete": 1, "table_name": "t2", @@ -211,6 +213,7 @@ ANALYZE "r_limit": 4, "r_used_priority_queue": true, "r_output_rows": 4, + "r_sort_mode": "sort_key,rowid", "temporary_table": { "table": { "table_name": "t0", @@ -300,6 +303,7 @@ ANALYZE "r_used_priority_queue": false, "r_output_rows": 10, "r_buffer_size": "REPLACED", + "r_sort_mode": "sort_key,addon_fields", "table": { "table_name": "t0", "access_type": "ALL", @@ -359,6 +363,7 @@ ANALYZE "r_used_priority_queue": false, "r_output_rows": 10, "r_buffer_size": "REPLACED", + "r_sort_mode": "sort_key,rowid", "temporary_table": { "table": { "table_name": "t2", @@ -472,6 +477,7 @@ ANALYZE "r_limit": 1, "r_used_priority_queue": true, "r_output_rows": 2, + "r_sort_mode": "sort_key,rowid", "temporary_table": { "filesort": { "sort_key": "t5.a", @@ -480,6 +486,7 @@ ANALYZE "r_used_priority_queue": false, "r_output_rows": 6, "r_buffer_size": "REPLACED", + "r_sort_mode": "sort_key,rowid", "temporary_table": { "table": { "table_name": "t6", diff --git a/mysql-test/main/order_by_pack_big.result b/mysql-test/main/order_by_pack_big.result index 0efc2ff150f..ea530e8eac6 100644 --- a/mysql-test/main/order_by_pack_big.result +++ b/mysql-test/main/order_by_pack_big.result @@ -82,6 +82,42 @@ select id, generate_random_string(a), generate_random_string(b) from t2; # All records fit in memory # set sort_buffer_size=262144*10; +analyze format=json select id DIV 100 as x, +MD5(group_concat(substring(names,1,3), substring(address,1,3) +order by id)) +FROM t3 +GROUP BY x; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": 158.46, + "read_sorted_file": { + "r_rows": 10000, + "filesort": { + "sort_key": "t3.`id` DIV 100", + "r_loops": 1, + "r_total_time_ms": 65.276, + "r_used_priority_queue": false, + "r_output_rows": 10000, + "r_buffer_size": "2150Kb", + "r_sort_mode": "sort_key,packed_addon_fields", + "table": { + "table_name": "t3", + "access_type": "ALL", + "r_loops": 1, + "rows": 10000, + "r_rows": 10000, + "r_table_time_ms": 33.772, + "r_other_time_ms": 31.087, + "filtered": 100, + "r_filtered": 100 + } + } + } + } +} flush status; select id DIV 100 as x, MD5(group_concat(substring(names,1,3), substring(address,1,3) @@ -203,6 +239,43 @@ set sort_buffer_size=default; # Test for merge_many_buff # set sort_buffer_size=32768; +analyze format=json select id DIV 100 as x, +MD5(group_concat(substring(names,1,3), substring(address,1,3) +order by id)) +FROM t3 +GROUP BY x; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": 283.51, + "read_sorted_file": { + "r_rows": 10000, + "filesort": { + "sort_key": "t3.`id` DIV 100", + "r_loops": 1, + "r_total_time_ms": 106.78, + "r_used_priority_queue": false, + "r_output_rows": 10000, + "r_sort_passes": 4, + "r_buffer_size": "31Kb", + "r_sort_mode": "sort_key,packed_addon_fields", + "table": { + "table_name": "t3", + "access_type": "ALL", + "r_loops": 1, + "rows": 10000, + "r_rows": 10000, + "r_table_time_ms": 38.727, + "r_other_time_ms": 67.97, + "filtered": 100, + "r_filtered": 100 + } + } + } + } +} flush status; select id DIV 100 as x, MD5(group_concat(substring(names,1,3), substring(address,1,3) diff --git a/mysql-test/main/order_by_pack_big.test b/mysql-test/main/order_by_pack_big.test index 89f9f3e539a..0939308593e 100644 --- a/mysql-test/main/order_by_pack_big.test +++ b/mysql-test/main/order_by_pack_big.test @@ -109,6 +109,7 @@ let $query= select id DIV 100 as x, --echo # set sort_buffer_size=262144*10; +eval analyze format=json $query; flush status; eval $query; show status like '%sort%'; @@ -119,6 +120,7 @@ set sort_buffer_size=default; --echo # set sort_buffer_size=32768; +eval analyze format=json $query; flush status; eval $query; show status like '%sort%'; diff --git a/sql/filesort.cc b/sql/filesort.cc index 763f9f59246..ceecc052899 100644 --- a/sql/filesort.cc +++ b/sql/filesort.cc @@ -298,6 +298,10 @@ SORT_INFO *filesort(THD *thd, TABLE *table, Filesort *filesort, tracker->report_sort_buffer_size(sort->sort_buffer_size()); } + if (param.using_addon_fields()) + // report information whether addon fields are packed or not + tracker->report_addon_fields_format(param.using_packed_addons()); + if (open_cached_file(&buffpek_pointers,mysql_tmpdir,TEMP_PREFIX, DISK_BUFFER_SIZE, MYF(MY_WME))) goto err; diff --git a/sql/sql_analyze_stmt.cc b/sql/sql_analyze_stmt.cc index fdabcf1e494..2147d6d7ffc 100644 --- a/sql/sql_analyze_stmt.cc +++ b/sql/sql_analyze_stmt.cc @@ -26,6 +26,7 @@ void Filesort_tracker::print_json_members(Json_writer *writer) { const char *varied_str= "(varied across executions)"; + String str; if (!get_r_loops()) writer->add_member("r_loops").add_null(); @@ -78,6 +79,25 @@ void Filesort_tracker::print_json_members(Json_writer *writer) else writer->add_size(sort_buffer_size); } + + get_data_format(&str); + writer->add_member("r_sort_mode").add_str(str.c_ptr(), str.length()); +} + +void Filesort_tracker::get_data_format(String *str) +{ + str->append("sort_key"); + str->append(","); + + if (r_using_addons) + { + if (r_packed_addon_fields) + str->append("packed_addon_fields"); + else + str->append("addon_fields"); + } + else + str->append("rowid"); } void attach_gap_time_tracker(THD *thd, Gap_time_tracker *gap_tracker, diff --git a/sql/sql_analyze_stmt.h b/sql/sql_analyze_stmt.h index 9d5151c3be2..dfe29517b63 100644 --- a/sql/sql_analyze_stmt.h +++ b/sql/sql_analyze_stmt.h @@ -221,7 +221,9 @@ class Filesort_tracker : public Sql_alloc time_tracker(do_timing), r_limit(0), r_used_pq(0), r_examined_rows(0), r_sorted_rows(0), r_output_rows(0), sort_passes(0), - sort_buffer_size(0) + sort_buffer_size(0), + r_using_addons(false), + r_packed_addon_fields(false) {} /* Functions that filesort uses to report various things about its execution */ @@ -263,6 +265,14 @@ class Filesort_tracker : public Sql_alloc else sort_buffer_size= bufsize; } + + inline void report_addon_fields_format(bool addons_packed) + { + r_using_addons= true; + r_packed_addon_fields= addons_packed; + } + + void get_data_format(String *str); /* Functions to get the statistics */ void print_json_members(Json_writer *writer); @@ -322,6 +332,8 @@ class Filesort_tracker : public Sql_alloc other - value */ ulonglong sort_buffer_size; + bool r_using_addons; + bool r_packed_addon_fields; };