Home / ubuntu / Questions / 1241013
In Process
Eaten by a Grue
Asked: 2020-05-19 06:55:38 +0800 CST

Cannot increase MySQL table_open_cache in Ubuntu 20.04

  • 772

I've read both of these related questions and still stuck:

MySQL table_open_cache is set to 8000 in /etc/mysql/mysql.conf.d/mysqld.cnf and this value is not defined in any of the other loaded cnf files:

table_open_cache = 8000

Also, in /lib/systemd/system/mysql.service I have the default entry:

LimitNOFILE=10000

When I query variables from the cli I still get this:

mysql> show variables like 'table_open_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 4745  |
+------------------+-------+

Also there's this:

$ ulimit -n
1024

Which is confusing because if the open files limit was being restricted by system shouldn't I be capped at 1024 instead of 4745? I don't understand where 4745 is even coming from.

Can someone please explain what's going on here? Is this a MySQL configuration problem or a limit being imposed by the operating system? I just migrated from Ubuntu 16.04 with nearly identical config and this was not a problem before.

OS is Ubuntu 20.04. MySQL version is 8.0.20.

systemd mysql 20.04

2 Answers

  1. I've discovered something that will provide at least a partial answer to my question. The LimitNOFILE setting inside /lib/systemd/system/mysql.service does play a role here but unlike other environments I've worked in, the setting is not a 1:1 relationship and needs to be much higher than your target table_open_cache limit.

    Through trial and error, I found that increasing LimitNOFILE only results in some fraction of the value being delegated to the table_open_cache limit, so I did a bit of experimenting and arrived at this:

    • LimitNOFILE of 1310 gets the first 400 to table_open_cache
    • Above 1310, table_open_cache increases by 50% for each LimitNOFILE
    • Mysteriously, if LimitNOFILE is below 510, table_open_cache appears to be uncapped by systemd.

    Using the above I got the following equation:

    (($table_open_cache - 400) * 2) + 1310 = $LimitNOFILE

    So if you want table_open_cache of 8000, you'd need to set LimitNOFILE to 16510:

    ((8000 - 400) * 2) + 1310 = 16510

    I can't make heads or tails of what's going on here but I was able to reproduce the same exact behaviour on different hosts with different databases and mysqld configurations.

    If someone can provide an authoritative explanation of the reason behind all of this, I will be happy to accept the answer and award a second round of bounty.

    • 1

  2. You have some kind of constraint between open_files_limit and table_open_cache and max_connections .

    For each connection you need a file descriptor and for each table in the cache you need 1 or 2 files descriptor .

    If you are not bellow or above some boundaries the formula will be

    table_cache_size = (requested_open_files - 10 - max_connections) / 2

    All theses parameters are computed in these 3 functions .

    function adjust_table_cache_size

    https://github.com/mysql/mysql-server/blob/7d10c82196c8e45554f27c00681474a9fb86d137/sql/mysqld.cc#L7717

    function adjust_max_connections

    https://github.com/mysql/mysql-server/blob/7d10c82196c8e45554f27c00681474a9fb86d137/sql/mysqld.cc#L7704

    function adjust_open_files_limit

    from https://github.com/mysql/mysql-server/blob/7d10c82196c8e45554f27c00681474a9fb86d137/sql/mysqld.cc#L7663

    In you case you need to increase the max open_files to a bigger value .

    1) run the command systemctl edit mysql and this 

    [Service]
LimitNOFILE=20000

    2) systemctl stop mysql

    3) systemctl start mysql

    Check the new value of max open files

    1. MYSQL WAY to check

    Default value when you installed mysql-server-8.0 on Ubntu 20.4 

    mysql> show global variables like '%open_files_limit%'  ;
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 20000 |
+------------------+-------+
1 row in set (0.00 sec)

    In this documentation you will find a formula to compute the right way to set this value .

    https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_open_files_limit

    1. GENERIC WAY to check

      • identify the process id of your mysql daemon
      • check the output of /proc/THE_PID/limits by running

      cat /proc/THE_PID/limits | grep -e '^Limit' -e open

    And after check your opencache

    mysql> show variables like '%open_cache%'  ;
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| table_open_cache           | 8000  |
| table_open_cache_instances | 16    |
+----------------------------+-------+
2 rows in set (0.01 sec)
    • -1