Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.0.30, 10.1.23
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
- relates to
-
-
- Closed
-
-
-
- Open
-
- links to