3

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.

0

3 Answers 3

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

The question is outdated and probably answered for most of the readers. But I'd like to add two things: @eaten-by-a-grue wonders why the the limit for open files LimitNOFILE needs to be much higher than the table_open_cache.

The answer is rather simple, depending on your storage engine, one table consists of multiple files. table.frm and table.ibd for MyISAM, for example.

Also, I found this very good guide how to increase the table_open_cache: https://www.basezap.com/guide-to-raise-ulimit-open-files-and-mysql-open-files-limit/

Bonus information

Instead of changing /lib/systemd/system/mysql.service it might make more sense to add a file /etc/systemd/system/mysql.service.d/override.conf and put the updated configuration there, as described on stack overflow. Otherwise your configuration changes might be overwritten by updates.

I hope this helps.

-1

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)
2
  • @billynoah i updated my answers May 28, 2020 at 3:15
  • @billynoah i reupdated my answers Jun 3, 2020 at 21:01

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .