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

ANALYZE FORMAT=JSON should show buffer sizes

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: 10.1
        • Fix Version/s: 10.1.4
        • Component/s: Optimizer
        • Labels:

          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)

            Gliffy Diagrams

              Attachments

                Issue Links

                relates to

                Bug - A problem which impairs or prevents the functions of the product. 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
                  Hide
                  Permalink
                  psergey Sergei Petrunia added a comment -

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

                  Show
                  psergey Sergei Petrunia added a comment - block-nl-join should also include buffer-size element which should show what size of the buffer was used.
                  Hide
                  Permalink
                  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;
                  Show
                  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;
                  Hide
                  Permalink
                  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

                  —

                  Show
                  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

                    • Assignee:
                      sanja Oleksandr Byelkin
                      Reporter:
                      psergey Sergei Petrunia
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      2 Start watching this issue

                      Dates

                      • Created:
                        Updated:
                        Resolved: