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

ANALYZE FORMAT=JSON should show buffer sizes

Details

    Description

      Take a test dataset from MDEV-7833. Let's run it so that join buffer is used:

      analyze format=json select * from t1, t2 where t2.key1+1 between t1.lb and t1.rb;
      		 
      {
      		 
        "query_block": {
      		 
          "select_id": 1,
      		 
          "r_loops": 1,
      		 
          "r_total_time_ms": 49.407,
      		 
          "table": {
      		 
            "table_name": "t1",
      		 
            "access_type": "ALL",
      		 
            "r_loops": 1,
      		 
            "rows": 3,
      		 
            "r_rows": 3,
      		 
            "r_total_time_ms": 0.2984,
      		 
            "filtered": 100,
      		 
            "r_filtered": 100
      		 
          },
      		 
          "block-nl-join": {
      		 
            "table": {
      		 
              "table_name": "t2",
      		 
              "access_type": "ALL",
      		 
              "r_loops": 1,
      		 
              "rows": 1010,
      		 
              "r_rows": 1010,
      		 
              "r_total_time_ms": 20.465,
      		 
              "filtered": 100,
      		 
              "r_filtered": 100
      		 
            },
      		 
            "buffer_type": "flat",
      		 
            "join_type": "BNL",
      		 
            "attached_condition": "((t2.key1 + 1) between t1.lb and t1.rb)",
      		 
            "r_filtered": 33.201
      		 
          }
      		 
        }
      		 
      } |

      Here, we see that t2.r_loops=1. This is the effect of join buffering.
      However, it would be nice to see join what join buffer was used. (TODO: it is always the same, right? If it is a subquery, it allocates the buffer once and never frees, right? Check this)

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-7648 Extra data in ANALYZE FORMAT=JSON $stmt

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-7958 ANALYZE FORMAT=JSON should show join cache buffer use

          • Major - Major loss of function.
          • Stalled

          Activity

            Ascending order - Click to sort in descending order
            psergei Sergei Petrunia added a comment -

            block-nl-join should also include buffer-size element which should show what size of the buffer was used.

            psergei Sergei Petrunia added a comment - block-nl-join should also include buffer-size element which should show what size of the buffer was used.
            sanja Oleksandr Byelkin added a comment - 

            create table t3(a int);
            		 
            insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
            		 
             
            		 
            create table t4(a int);
            		 
            insert into t4 select A.a + B.a* 10 + C.a * 100 from t3 A, t3 B, t3 C;
            		 
             
            		 
            create table t1 (lb int, rb int);
            		 
             
            		 
            insert into t1 values (1,2);
            		 
            insert into t1 values (3,5);
            		 
            insert into t1 values (10, 20);
            		 
             
            		 
            create table t2 (key1 int, col1 int, key(key1));
            		 
            insert into t2 select a,a from t3;
            		 
            insert into t2 select 15,15 from t4;
            		 
            analyze format=json select * from t1, t2 where t2.key1+1 between t1.lb and t1.rb;
            		 
            drop table t1,t2,t3,t4;

            sanja Oleksandr Byelkin added a comment - create table t3(a int); insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);   create table t4(a int); insert into t4 select A.a + B.a* 10 + C.a * 100 from t3 A, t3 B, t3 C;   create table t1 (lb int, rb int);   insert into t1 values (1,2); insert into t1 values (3,5); insert into t1 values (10, 20);   create table t2 (key1 int, col1 int, key(key1)); insert into t2 select a,a from t3; insert into t2 select 15,15 from t4; analyze format=json select * from t1, t2 where t2.key1+1 between t1.lb and t1.rb; drop table t1,t2,t3,t4;
            sanja Oleksandr Byelkin added a comment -

            revision-id: ac53c41c7fb91f5e8cbce79efc31a8b16fd824a5
            parent(s): 2b475b567e93c9a2438a835f2236ec3672e7769c
            committer: Oleksandr Byelkin
            branch nick: server
            timestamp: 2015-04-08 20:14:48 +0200
            message:

            MDEV-7835: ANALYZE FORMAT=JSON should show buffer sizes

            —

            sanja Oleksandr Byelkin added a comment - revision-id: ac53c41c7fb91f5e8cbce79efc31a8b16fd824a5 parent(s): 2b475b567e93c9a2438a835f2236ec3672e7769c committer: Oleksandr Byelkin branch nick: server timestamp: 2015-04-08 20:14:48 +0200 message: MDEV-7835 : ANALYZE FORMAT=JSON should show buffer sizes —

            People

              sanja Oleksandr Byelkin
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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