[MDEV-12547] InnoDB FULLTEXT index has too strict innodb_ft_result_cache_limit max limit Created: 2017-04-21  Updated: 2020-08-25  Resolved: 2018-10-19

Status: Closed
Project: MariaDB Server
Component/s: Full-text Search, Optimizer, Storage Engine - InnoDB
Affects Version/s: 10.0.30, 10.1.23
Fix Version/s: 10.0.37, 10.1.37, 10.2.19

Type: Bug Priority: Critical
Reporter: Valerii Kravchuk Assignee: Thirunarayanan Balathandayuthapani
Resolution: Fixed Votes: 0
Labels: upstream

Issue Links:
Relates
relates to MDEV-14085 Merge new release of InnoDB MySQL 5.7... Closed
relates to MDEV-17497 Reduce memory usage (query->total_siz... Open

 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.

 Comments   
Comment by Marko Mäkelä [ 2017-12-12 ]

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.

Comment by Marko Mäkelä [ 2018-01-18 ]

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.
Comment by Thirunarayanan Balathandayuthapani [ 2018-10-19 ]

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.

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