[MDEV-7835] ANALYZE FORMAT=JSON should show buffer sizes Created: 2015-03-25  Updated: 2015-04-09  Resolved: 2015-04-09

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1
Fix Version/s: 10.1.4

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: analyze-stmt

Issue Links:
Relates
relates to MDEV-7648 Extra data in ANALYZE FORMAT=JSON $stmt Open
relates to MDEV-7958 ANALYZE FORMAT=JSON should show join ... Stalled

 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)

 Comments   
Comment by Sergei Petrunia [ 2015-03-25 ]

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

Comment by Oleksandr Byelkin [ 2015-04-08 ] 

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;

Comment by Oleksandr Byelkin [ 2015-04-08 ]

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

—

Generated at Thu Feb 08 07:22:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.