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

ANALYZE FORMAT=JSON should show join cache buffer use

    Details

    • Type: Bug
    • Status: Stalled
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.1
    • Fix Version/s: 10.1
    • Component/s: Optimizer
    • Labels:
    • Sprint:
      10.1.7-1

      Description

      After MDEV-7835, we can know how the sizes of buffers that were allocated for "Using join buffer".

      It would be also nice to know how much space in the buffer was actually used.

      This can be done as follows:

      analyze format=json select * from one_k t1, t2 where t1.a+t2.a<1000;
      
      ANALYZE: {
        "query_block": {
          "select_id": 1,
          "r_loops": 1,
          "r_total_time_ms": 5818.7,
          "table": {
            "table_name": "t1",
            "access_type": "ALL",
            "r_loops": 1,
            "rows": 1000,
            "r_rows": 1000,
            "r_total_time_ms": 21.695,
            "filtered": 100,
            "r_filtered": 100
          },
          "block-nl-join": {
            "table": {
              "table_name": "t2",
              "access_type": "ALL",
              "r_loops": 1,
              "rows": 1000,
              "r_rows": 1000,
              "r_total_time_ms": 18.274,
              "filtered": 100,
              "r_filtered": 100
            },
            "buffer_type": "flat",
            "buffer_size": "128Kb",
            "join_type": "BNL",
            "attached_condition": "((t1.a + t2.a) < 1000)",
            "r_filtered": 50.05
          }
        }
      }
      
      Here we see that t2 has r_loops=1, which means t2 was accessed one time.
      Is this sufficient to indicate that the buffer was filled only once?
      
      My suggestion for the first approach: let ANALYZE show another field:
      r_buffer_used. It should print
      
        r_buffer_used: size
      
      when the buffer was used one time, and
      
        r_buffer_used: "multiple times"
      
      when the buffer was used multiple times.
      
      I have a similar solution for ANALYZE+filesort().  If filesort was invoked once,
      it prints:
      
        r_sort_passes: nnnn,
      ...
      
      and if this is a subquery and filesort was run many times, it prints
      
        r_sort_passes: "(varied across executions)"
      

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

              There are no comments yet on this issue.

                People

                • Assignee:
                  psergey Sergei Petrunia
                  Reporter:
                  psergey Sergei Petrunia
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  1 Start watching this issue

                  Dates

                  • Created:
                    Updated:

                    Agile