[Commits] 93e964a8f55: Better Optimize Trace support for LATERAL DERIVED optimization

Sergei Petrunia psergey at askmonty.org
Fri Jul 30 22:53:00 EEST 2021


revision-id: 93e964a8f5545c1688fcf1101ea298403cc6dbe1 (mariadb-10.5.11-55-g93e964a8f55)
parent(s): 91e925e199ce61623f8413bfa789d0e7098c3d72
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-07-30 22:53:00 +0300
message:

Better Optimize Trace support for LATERAL DERIVED optimization

---
 sql/opt_split.cc  | 86 ++++++++++++++++++++++++++++++++++++++++++-------------
 sql/sql_array.h   |  2 ++
 sql/sql_select.cc |  5 ++++
 3 files changed, 73 insertions(+), 20 deletions(-)

diff --git a/sql/opt_split.cc b/sql/opt_split.cc
index 41b8acf5dcb..61e4eed1b6f 100644
--- a/sql/opt_split.cc
+++ b/sql/opt_split.cc
@@ -188,6 +188,7 @@
 #include "mariadb.h"
 #include "sql_select.h"
 #include "opt_trace.h"
+#include "sql_test.h"  /* for print_keyuse_array_for_trace */
 
 /* Info on a splitting field */
 struct SplM_field_info
@@ -343,6 +344,9 @@ bool JOIN::check_for_splittable_materialized()
   if (!partition_list)
     return false;
 
+  Json_writer_object trace_wrapper(thd);
+  Json_writer_object trace_split(thd, "check_lateral_derived");
+
   ORDER *ord;
   Dynamic_array<SplM_field_ext_info> candidates(PSI_INSTRUMENT_MEM);
 
@@ -388,8 +392,10 @@ bool JOIN::check_for_splittable_materialized()
     }
   }
   if (candidates.elements() == 0)  // no candidates satisfying (8.1) && (8.2)
+  {
+    trace_split.add("not_applicable", "group list has no candidates");
     return false;
-
+  }
   /*
     For each table from this join find the keys that can be used for ref access
     of the fields mentioned in the 'array candidates'
@@ -447,7 +453,11 @@ bool JOIN::check_for_splittable_materialized()
   }
 
   if (!spl_field_cnt)  // No candidate field can be accessed by ref => !(9)
+  {
+    trace_split.add("not_applicable", 
+                    "no candidate field can be accessed through ref");
     return false;
+  }
 
   /*
     Create a structure of the type SplM_opt_info and fill it with
@@ -465,16 +475,20 @@ bool JOIN::check_for_splittable_materialized()
   spl_opt_info->tables_usable_for_splitting= 0;
   spl_opt_info->spl_field_cnt= spl_field_cnt;
   spl_opt_info->spl_fields= spl_field;
-  for (cand= cand_start; cand < cand_end; cand++)
   {
-    if (!cand->is_usable_for_ref_access)
-      continue;
-    spl_field->producing_item= cand->producing_item;
-    spl_field->underlying_field= cand->underlying_field;
-    spl_field->mat_field= cand->mat_field;
-    spl_opt_info->tables_usable_for_splitting|=
-    cand->underlying_field->table->map;
-    spl_field++;
+    Json_writer_array trace_range(thd, "split_variants");
+    for (cand= cand_start; cand < cand_end; cand++)
+    {
+      if (!cand->is_usable_for_ref_access)
+        continue;
+      spl_field->producing_item= cand->producing_item;
+      trace_range.add(cand->producing_item);
+      spl_field->underlying_field= cand->underlying_field;
+      spl_field->mat_field= cand->mat_field;
+      spl_opt_info->tables_usable_for_splitting|=
+      cand->underlying_field->table->map;
+      spl_field++;
+    }
   }
 
   /* Attach this info to the table T */
@@ -738,7 +752,16 @@ void JOIN::add_keyuses_for_splitting()
 
   spl_opt_info->unsplit_cost= best_positions[table_count-1].read_time +
                               oper_cost;
-
+  {
+    Json_writer_object obj(thd);
+    {
+      //Json_writer_object(thd, "added_keyuses");
+      //print_keyuse_array_for_trace(thd, &ext_keyuses_for_splitting);
+    }
+    obj.add("unsplit_cost", spl_opt_info->unsplit_cost);
+    obj.add("unsplit_card", spl_opt_info->unsplit_card);
+    obj.add("rec_len", (ulonglong) rec_len);
+  }
   if (!(save_qep= new Join_plan_state(table_count + 1)))
     goto err;
 
@@ -894,7 +917,10 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count,
   SplM_plan_info *spl_plan= 0;
   uint best_key= 0;
   uint best_key_parts= 0;
-
+  
+  Json_writer_array spl_trace(thd, "lateral_plan_choice");
+  Json_writer_array wrapper(thd);
+  Json_writer_array trace_indexes(thd, "indexes_for_splitting");
   /*
     Check whether there are keys that can be used to join T employing splitting
     and if so, select the best out of such keys
@@ -939,6 +965,13 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count,
                  key_info->actual_rec_per_key(keyuse_ext->keypart);
         if (rec_per_key < best_rec_per_key)
 	{
+          Json_writer_object trace(thd);
+          trace.add_table_name(keyuse_ext->table);
+          trace.add("index",
+                    keyuse_ext->table->key_info[keyuse_ext->key].name.str);
+          trace.add("parts", (longlong)keyuse_ext->keypart + 1);
+          trace.add("rec_per_key", rec_per_key);
+
           best_table= keyuse_ext->table;
           best_key= keyuse_ext->key;
 	  best_key_parts= keyuse_ext->keypart + 1;
@@ -951,17 +984,19 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count,
     }
     while (keyuse_ext->table == table);
   }
+  trace_indexes.end();
   spl_opt_info->last_plan= 0;
+
   if (best_table)
   {
     /*
       The key for splitting was chosen, look for the plan for this key
       in the cache
     */
-    Json_writer_array spl_trace(thd, "choose_best_splitting");
     spl_plan= spl_opt_info->find_plan(best_table, best_key, best_key_parts);
     if (!spl_plan)
     {
+      Json_writer_array spl_trace(thd, "build_split_plan");
       /*
         The plan for the chosen key has not been found in the cache.
         Build a new plan and save info on it in the cache
@@ -1010,12 +1045,9 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count,
       if (unlikely(thd->trace_started()))
       {
         Json_writer_object wrapper(thd);
-        Json_writer_object find_trace(thd, "best_splitting");
-        find_trace.add("table", best_table->alias.c_ptr());
-        find_trace.add("key", best_table->key_info[best_key].name);
-        find_trace.add("record_count", record_count);
+        Json_writer_object find_trace(thd, "found_split_plan");
         find_trace.add("cost", spl_plan->cost);
-        find_trace.add("unsplit_cost", spl_opt_info->unsplit_cost);
+        find_trace.add("output_cardinality", split_card);
       }
       memcpy((char *) spl_plan->best_positions,
              (char *) join->best_positions,
@@ -1023,8 +1055,19 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count,
       reset_validity_vars_for_keyuses(best_key_keyuse_ext_start, best_table,
                                       best_key, remaining_tables, false);
     }
+    else
+    {
+      Json_writer_object wrapper(thd);
+      Json_writer_object find_trace(thd, "cached_split_plan_found");
+      find_trace.add("cost", spl_plan->cost);
+    }
     if (spl_plan)
     {
+      Json_writer_object wrapper(thd);
+      Json_writer_object choice(thd, "split_plan_choice");
+      choice.add("split_cost", spl_plan->cost);
+      choice.add("record_count_for_split", record_count);
+      choice.add("unsplit_cost", spl_opt_info->unsplit_cost);
       if(record_count * spl_plan->cost < spl_opt_info->unsplit_cost - 0.01)
       {
         /*
@@ -1032,7 +1075,10 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count,
           the plan without splitting
 	*/
         spl_opt_info->last_plan= spl_plan;
+        choice.add("split_chosen", true);
       }
+      else
+        choice.add("split_chosen", false);
     }
   }
 
@@ -1044,9 +1090,9 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count,
     startup_cost= record_count * spl_plan->cost;
     records= (ha_rows) (records * spl_plan->split_sel);
 
-    Json_writer_object trace(thd, "lateral_derived");
+    Json_writer_object trace(thd, "chosen_lateral_derived");
     trace.add("startup_cost", startup_cost);
-    trace.add("splitting_cost", spl_plan->cost);
+    trace.add("lateral_cost", spl_plan->cost);
     trace.add("records", records);
   }
   else
diff --git a/sql/sql_array.h b/sql/sql_array.h
index b6de1b18d78..244d4a5be06 100644
--- a/sql/sql_array.h
+++ b/sql/sql_array.h
@@ -289,6 +289,8 @@ template <class Elem> class Dynamic_array
   {
     my_qsort2(array.buffer, array.elements, sizeof(Elem), (qsort2_cmp)cmp_func, data);
   }
+
+  DYNAMIC_ARRAY *impl() { return &array; }
 };
 
 typedef Bounds_checked_array<Item*> Ref_ptr_array;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 7057ed1b5e1..538696b2c7a 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -5484,7 +5484,12 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
         s->scan_time();
 
       if (s->table->is_splittable())
+      {
+        Json_writer_object trace(thd);
+        trace.add_table_name(s);
+        Json_writer_object trace_lateral(thd, "lateral_derived");
         s->add_keyuses_for_splitting();
+      }
 
       /*
         Set a max range of how many seeks we can expect when using keys


More information about the commits mailing list