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

ANALYZE FORMAT=JSON should show join cache buffer use

    XMLWordPrintable

    Details

    • 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)"

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                Created:
                Updated: