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

            valerii, in MySQL 5.7 the LIMIT is being pushed down to the InnoDB fulltext search engine:
            Bug#22709692 FTS QUERY EXCEEDS RESULT CACHE LIMIT.
            As I noted in MDEV-14085, there was a bug fix in MySQL 5.7.20 related to it (without any commit message).
            This fix does not apply to MariaDB, because the LIMIT pushdown was never implemented. I removed the dead code that had been merged from MySQL 5.7.9 to MariaDB 10.2.2.

            If we implement LIMIT pushdown to the InnoDB fulltext search engine, we have to be very careful. The implementation in MySQL 5.7 looks error-prone.

            marko Marko Mäkelä added a comment - valerii , in MySQL 5.7 the LIMIT is being pushed down to the InnoDB fulltext search engine: Bug#22709692 FTS QUERY EXCEEDS RESULT CACHE LIMIT . As I noted in MDEV-14085 , there was a bug fix in MySQL 5.7.20 related to it (without any commit message). This fix does not apply to MariaDB, because the LIMIT pushdown was never implemented. I removed the dead code that had been merged from MySQL 5.7.9 to MariaDB 10.2.2. If we implement LIMIT pushdown to the InnoDB fulltext search engine, we have to be very careful. The implementation in MySQL 5.7 looks error-prone.

            We can do a few things in MariaDB without risking breaking correctness, roughly in increasing order of difficulty:

            1. Make innodb_ft_result_cache_limit 64 bits on 64-bit platforms.
            2. Make query->total_size and fetch->total_memory (which are being checked against the limit) less overestimated.
            3. Push down the enforcement of the limit to the lower level, closer to where the memory is actually being allocated.
            4. Change the API so that results would be written into an internal temporary table that is managed by the query executor, and remove the buffering inside InnoDB.
            marko Marko Mäkelä added a comment - We can do a few things in MariaDB without risking breaking correctness, roughly in increasing order of difficulty: Make innodb_ft_result_cache_limit 64 bits on 64-bit platforms. Make query->total_size and fetch->total_memory (which are being checked against the limit) less overestimated. Push down the enforcement of the limit to the lower level, closer to where the memory is actually being allocated. Change the API so that results would be written into an internal temporary table that is managed by the query executor, and remove the buffering inside InnoDB.

            Pushed the fix that makes innodb_ft_result_cache_limit 64 bits on 64-bit platforms.
            Worked on (2) suggestion as well i.e make memory less overestimation. Tried to remove f_n_char from fts_string_t.
            But it could affect the performance of the query.

            thiru Thirunarayanan Balathandayuthapani added a comment - Pushed the fix that makes innodb_ft_result_cache_limit 64 bits on 64-bit platforms. Worked on (2) suggestion as well i.e make memory less overestimation. Tried to remove f_n_char from fts_string_t. But it could affect the performance of the query.

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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