Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-38091

Make it easier to analyze mtr failures by mtr providing optimizer trace diffs

    XMLWordPrintable

Details

    Description

      Making changes in the optimizer causes changes in the query plans in mtr.

      Often we only see the changed EXPLAIN and it's not clear whether the change is for the better or not.
      Manual analysis is labor intensive.

      The idea:
      We already have test runs with --mysqld=--optimizer_trace=on.
      Add an mtr option to also get the trace after every query.
      Running the test produces .result file (or .reject file).
      There should be another file (tentative name: .result.with-traces) which will be like .result but with optimizer trace contents after every query which produces a trace.

      Also write a .result.with-traces.mapping file which will have these pairs:

      line_number_in_result_file: line_number_in_result_with_traces_file
      

      The files should probably not be in git.

      Then, when there's a difference between .result and .reject, for example:

      --- /optane/dev-git2/10.6-cur2/mysql-test/main/subselect_sj_jcl6.result
      +++ /optane/dev-git2/10.6-cur2/mysql-test/main/subselect_sj_jcl6.reject
      @@ -2189,10 +2189,10 @@
       explain
       SELECT * FROM t3 WHERE t3.a IN (SELECT t5.a FROM t2, t4, t5 WHERE t2.c = t5.a AND t2.b = t5.b);
       id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
      -1      PRIMARY t5      index   a       a       10      NULL    2       Using where; Using index; LooseScan
      -1      PRIMARY t4      ALL     NULL    NULL    NULL    NULL    3
      ...
      +1      PRIMARY t5      index   a       a       10      NULL    2       Using where; Using index; Start temporary
      +1      PRIMARY t2      ref     b       b       5       test.t5.b       2       Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
      ...
      

      We can see that it affects lines 2189 - (2189 + 10) in the .result file.
      Look up that in the testname.result.with-traces.mapping file will give us the line numbers in the testname.result.with-traces file. The same goes for reject files. We can extract those line ranges into two files named like testname.result.with-traces.2189 and perform a diff.

      (We could also diff the whole testname.{result,reject}.with-traces but those are likely to be
      very large files with some changes not relevant to .result vs .reject changes.

      Attachments

        Activity

          People

            Unassigned Unassigned
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.