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

Unreasonably small size for join_buffer_size

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 10.0, 10.1, 10.2, 10.3, 10.4
    • 10.4
    • Optimizer
    • None

    Description

      CREATE TABLE t1 (f1 INT) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (1),(2);
      CREATE TABLE t2 (f2 INT) ENGINE=MyISAM;
      INSERT INTO t2 VALUES (2),(3);
      CREATE TABLE t3 (f3 INT, f4 INT) ENGINE=MyISAM;
      INSERT INTO t3 VALUES (3,3),(4,4);
      

      MariaDB [test]> set optimizer_switch='optimize_join_buffer_size=on';
      Query OK, 0 rows affected (0.001 sec)
       
      MariaDB [test]> ANALYZE FORMAT=JSON  SELECT f4 FROM t2, t3 WHERE f3 IN ( 1, 3 );
      | {
        "query_block": {
          "select_id": 1,
          "r_loops": 1,
          "r_total_time_ms": 0.2752,
          "table": {
            "table_name": "t2",
            "access_type": "ALL",
            "r_loops": 1,
            "rows": 2,
            "r_rows": 2,
            "r_total_time_ms": 0.019,
            "filtered": 100,
            "r_filtered": 100
          },
          "block-nl-join": {
            "table": {
              "table_name": "t3",
              "access_type": "ALL",
              "r_loops": 1,
              "rows": 2,
              "r_rows": 2,
              "r_total_time_ms": 0.0199,
              "filtered": 100,
              "r_filtered": 50,
              "attached_condition": "t3.f3 in (1,3)"
            },
            "buffer_type": "flat",
            "buffer_size": "1",
            "join_type": "BNL",
            "r_filtered": 100
          }
        }
      } |
      

      The buffer_size here shows 1 which is very less, shouldn't we have some min size that
      we always allocate always irrespectiving of number of records being estimated.

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            varun Varun Gupta (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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