[#MDEV-10286] Adjustment of table_open_cache according to system limits does not work when open-files-limit option is provided

table_open_cache is supposed to be adjusted (decreased) automatically if system limit on open files does not allow the desired value. It works all right until we also provide open-files-limit option. The option itself does not really do much (as explained here, for example: https://bugs.mysql.com/bug.php?id=34870), but it somehow affects the adjustment; so, table_open_cache can end up with a higher value than the system allows, which leads to obvious problems.

MySQL 5.6 and 5.7 are not affected.
10.1 and 10.2 are affected the most, because default value for table_open_cache there is 2000, and it remains such in the described scenario.
10.0 is also affected, but since the default value there is 400, it is less of a problem.

Example:

$ ulimit -n

Server started with all defaults, no extra options - OK
+-----------------------+-------------------+--------------------+
\| @@version \| @@max_connections \| @@table_open_cache \|
+-----------------------+-------------------+--------------------+
\| 10.1.15-MariaDB-debug \| 151 \| 431 \|
+-----------------------+-------------------+--------------------+

Server started with all defaults and --open-files-limit=1024 - Problem
+-----------------------+-------------------+--------------------+
\| @@version \| @@max_connections \| @@table_open_cache \|
+-----------------------+-------------------+--------------------+
\| 10.1.15-MariaDB-debug \| 151 \| 2000 \|
+-----------------------+-------------------+--------------------+

MySQL servers started with all defaults and --open-files-limit=1024 - OK
+--------------+-------------------+--------------------+
\| @@version \| @@max_connections \| @@table_open_cache \|
+--------------+-------------------+--------------------+
\| 5.6.31-debug \| 151 \| 431 \|
+--------------+-------------------+--------------------+
+--------------+-------------------+--------------------+
\| @@version \| @@max_connections \| @@table_open_cache \|
+--------------+-------------------+--------------------+
\| 5.7.13-debug \| 151 \| 431 \|
+--------------+-------------------+--------------------+

Consequences of the problem are very easily reproducible on 10.1 with MTR, because MTR quite conveniently a) sets system limit to 1024 for any platform, and b) has open-files-limit=1024 in the default config template.

MTR test case
--let $cnt= 2000

while ($cnt)
{
eval CREATE TABLE t_$cnt (i INT) ENGINE=MyISAM;
eval INSERT INTO t_$cnt VALUES (1);
--dec $cnt
}

....

INSERT INTO t_1518 VALUES (1);

CREATE TABLE t_1517 (i INT) ENGINE=MyISAM;

INSERT INTO t_1517 VALUES (1);

CREATE TABLE t_1516 (i INT) ENGINE=MyISAM;

bug.t1                                   [ fail ]

        Test ended at 2016-06-25 21:15:21

CURRENT_TEST: bug.t1

mysqltest: At line 8: query 'CREATE TABLE t_$cnt (i INT) ENGINE=MyISAM' failed: 1005: Can't create table `test`.`t_1516` (errno: 24 "Too many open files")

[MDEV-10286] Adjustment of table_open_cache according to system limits does not work when open-files-limit option is provided Created: 2016-06-25 Updated: 2018-04-09 Resolved: 2018-03-26
Status:	Closed
Project:	MariaDB Server
Component/s:	Server
Affects Version/s:	10.1
Fix Version/s:	10.1.32

[MDEV-10286] Adjustment of table_open_cache according to system limits does not work when open-files-limit option is provided Created: 2016-06-25 Updated: 2018-04-09 Resolved: 2018-03-26