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

InnoDB FULLTEXT index has too strict innodb_ft_result_cache_limit max limit

    Details

      Description

      This is a variation of upstream https://bugs.mysql.com/bug.php?id=86036 on MariaDB 10.x.y.

      The maximum value of innodb_ft_result_cache_limit for all platform types and bit sizes is 2**32-1. This limit applies to all recent versions (inclusing upstream MySQL 5.6.x and MariaDB 10.x.y as well). This is a problem because it seems internally this limit is checked even for the queries that should not need that much memory. Consider the following test case:

      MariaDB [test]> create table ft(id int auto_increment primary key, c1 varchar(255), fulltext key k(c1)) engine=InnoDB;
      Query OK, 0 rows affected (2.63 sec)
       
      MariaDB [test]> insert into ft(c1) values (repeat('order', 50));
      Query OK, 1 row affected (0.00 sec)
       
      MariaDB [test]> insert into ft(c1) values (repeat('order ', 40));
      Query OK, 1 row affected (0.01 sec)
       
      MariaDB [test]> insert into ft(c1) values (repeat('nothin', 40));
      Query OK, 1 row affected (0.00 sec)
       
      MariaDB [test]> insert into ft(c1) select c1 from ft;
      Query OK, 3 rows affected (0.00 sec)
      Records: 3  Duplicates: 0  Warnings: 0
       
      ...
       
      MariaDB [test]> insert into ft(c1) select c1 from ft;
      Query OK, 196608 rows affected (8.65 sec)
      Records: 196608  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> show table status like 'ft'\G
      *************************** 1. row ***************************
                 Name: ft
               Engine: InnoDB
              Version: 10
           Row_format: Compact
                 Rows: 386100
       Avg_row_length: 305
          Data_length: 118095872
      Max_data_length: 0
         Index_length: 7880704
            Data_free: 7340032
       Auto_increment: 524267
          Create_time: 2017-04-21 12:34:57
          Update_time: NULL
           Check_time: NULL
            Collation: latin1_swedish_ci
             Checksum: NULL
       Create_options:
              Comment:
      1 row in set (0.00 sec)
       
      MariaDB [test]> show table status like 'ft'\G
      *************************** 1. row ***************************
                 Name: ft
               Engine: InnoDB
              Version: 10
           Row_format: Compact
                 Rows: 386100
       Avg_row_length: 305
          Data_length: 118095872
      Max_data_length: 0
         Index_length: 7880704
            Data_free: 7340032
       Auto_increment: 524267
          Create_time: 2017-04-21 12:34:57
          Update_time: NULL
           Check_time: NULL
            Collation: latin1_swedish_ci
             Checksum: NULL
       Create_options:
              Comment:
      1 row in set (0.00 sec)
       
      MariaDB [test]> set global innodb_ft_result_cache_limit=10000000;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> explain select * from ft where match(c1) against('order') limit 5;
      +------+-------------+-------+----------+---------------+------+---------+------+------+-------------+
      | id   | select_type | table | type     | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+-------------+-------+----------+---------------+------+---------+------+------+-------------+
      |    1 | SIMPLE      | ft    | fulltext | k             | k    | 0       |      |    1 | Using where |
      +------+-------------+-------+----------+---------------+------+---------+------+------+-------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> \r
      Connection id:    5
      Current database: test
       
      MariaDB [test]> select @@innodb_ft_result_cache_limit;
      +--------------------------------+
      | @@innodb_ft_result_cache_limit |
      +--------------------------------+
      |                       10000000 |
      +--------------------------------+
      1 row in set (0.01 sec)
       
      MariaDB [test]> select * from ft where match(c1) against('order') limit 5;      
      ERROR 128 (HY000): Table handler out of memory
      

      Moreover, we get the same error even if we select juts id column (primary key). The result though is only 5 rows:

      MariaDB [test]> set global innodb_ft_result_cache_limit=100000000;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> \r
      Connection id: 6
      Current database: test
       
      MariaDB [test]> select * from ft where match(c1) against('order') limit 5;
      +----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | id | c1 |
      +----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | 2 | order order order order order order order order order order order order order order order order order order order order order order order order order order order order order order order order order order order order order order order order |
      ...
       
      MariaDB [test]> select id from ft where match(c1) against('order') limit 5;
      +----+
      | id |
      +----+
      | 2 |
      | 5 |
      | 8 |
      | 11 |
      | 15 |
      +----+
      5 rows in set (0.21 sec)
       
      MariaDB [test]> select version();
      +-----------------+
      | version() |
      +-----------------+
      | 10.1.23-MariaDB |
      +-----------------+
      

      In practice it means that if we need just few rows out of many that matches from the big enough table (>4G in total size), FULLTEXT InnoDB index is of limited use. We should either increase the limit, or do some change in optimizer so that LIMIT N clause and columns in SELECT list are taken into account.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                thiru Thirunarayanan Balathandayuthapani
                Reporter:
                valerii Valerii Kravchuk
              • Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: