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

Unreasonably small size for join_buffer_size

    XMLWordPrintable

    Details

      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

            Assignee:
            varun Varun Gupta
            Reporter:
            varun Varun Gupta
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated: