limit of open files: Debian Jessies´ MySQL vs Community Oracle MySQL

Question

I have just upgraded from MySQL Jessie 5.5 to MySQL Oracle Community Edition v5.6.

I have a particular MySQL server, that has around 230 DBs, and > 32k files/tables.

Whilst for two or three years, I had never noticeable problems with the Debian version, the services provided by this server failed a few hours after this upgrade.

Debugging the problem, I found out it was due to the limit of the open files per process.

I solved the problem raising the limits of the open files per process. I added to /etc/sysctl.conf:

fs.file-max=100000

and to /etc/security/limits.conf

* - nofile 100000

Afterwards I restarted MySQL, and that alone restored the normality of the services. For a document talking about this. http://duntuk.com/how-raise-ulimit-open-files-and-mysql-openfileslimit

From my own server:

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

mysql> select @@table_open_cache;
+--------------------+
| @@table_open_cache |
+--------------------+
|              15000 |
+--------------------+
1 row in set (0.00 sec)

After solving the problem, I set out to find if there is any difference in the scripts or code between Debian´s and Oracle´s versions.

In my short analysis, I learnt they both have the open_files_limitin my.cnf and the option "--open-files-limit" in the script /usr/bin/mysqld_safe, which actually led me to find out the document/link I mentioned previously.

However, I did not find out anything more in the code or scripts that led me to justify the difference in behaviour concerning open files limits.

I can see also that in another server with MySQL 5.5 from Jessie the table_open_cache is actually lower.

mysql> select @@table_open_cache;
+--------------------+
| @@table_open_cache |
+--------------------+
|                512 |
+--------------------+
1 row in set (0.00 sec)

Whilst the default table_open_cache increasing could partly explain the problem, I am not sure if it fully justifies it.

Could someone shed some light on this?

Answer 1

This server is a legacy server with light use, hence so many DBs/tables.

In short, what it happened is that the upgrade from the 5.5 MySQL Debian Jessie official package to the 5.6 MySQL Oracle´s version raises the limit of the open files for table caching.

Whilst the open table cache is by default 512 files in 5.5, in 5.6 seems to be at least 2048 files.

Evidently, the problem only manifests itself when you have more than 1000 and so tables already used/opened as the limit per process of files open by non-root users is 1024 by default.

The cache seems to be adjusting itself to around 10% of the open files allowed for the user after raising the limits, will investigate it further.

As final comments, either rising the system limits or adjusting the variable open_files_limit in my.cnf or as a parameter for MySQL solves the problem.

An alternative to raising the system limits in /etc/security/limits.conf, would be then adding in the mysqld section of /etc/mysql/my.cnf

open_files_limit = 100000

It should be duly noted that raising the open file limits is recommended for database, web or email servers that have a lot of tables/vhosts/email users.

The DB responsiveness also seems faster (as more tables are kept open), however I have not conducted any comparative performance tests between the two versions in my particular case.