Open_files growing

MySQL & MariaDB
1

Hello,

MySQL 5.7.

The number of open_files has started growing as of a few weeks ago. I am in need to identify precisely what is causing this to increase, as historically it has gone up/down every day in a stable state. Now it continues to grow and will eventually cause us to have to reboot.

What precisely does open_files represent? Are they temporary tables that users/apps explicitly create? Are they temporary tables that mysql implicitly creates? I’m struggling to find a way to identify the cause of open_files continued increase and would appreciate any help that can be provided for further investigation.

I have dug into /proc//fd and see many tmp files which seems to correspond with the number of open_files. Is there any way I can investigate these files to help identify root cause? Any/all suggestions welcome.

show global status like ‘open_files’;
±--------------±------+
| Variable_name | Value |
±--------------±------+
| Open_files | 338 |
±--------------±------+
1 row in set (0.00 sec)

Thanks.

2

Hello @ChrisDK,
Firstly, I hope you are aware that in less than 46 days, 5.7 will EOL and no longer be supported software.

Secondly, 338 open files is an extremely low number of open files. I understand you are concerned, but why? What are you seeing that is making you think this number is causing an issue? To be honest, this value is not something I would be concerned with.

The number of open files themselves is not an issue. “Files” are opened for every connection to the data, for every query, that touches each table. If you connect to MySQL and run a JOIN against 2 tables, the value will increase by 2. When you close your connection, the value drops by 2. If a temp table was needed, that will +1/-1 too.

What you need to pay attention to instead is the rate of opened files. How fast does this stat change? If this stat changes by more than 5/sec, you can look at increasing the table_open_cache by 10% and see if the rate falls down.

3

Hi Matthew,

Thanks for the reminder regarding 5.7 we are aware.

Regarding open files the number is going to grow and will not stop. That is what happened over the past few weeks. There is something happening that appears to be not allowing some processes to release their temp files. The open files will grow into the thousands. This is brand new behavior and this is cause for concern.

It was not doing this until a few weeks ago. The number of open files used to be a range between 50-150 every day like clockwork. However, it is now growing and will not stop. There is definitely something wrong and must be addressed.

Regarding some stats:

Opened_files | 2793715 → taken at 23:35:12
Opened_files | 2794176 → taken at 23:35:24
Here we see a tremendous growth of several hundred over 10 seconds.

Now lets look at some other values:
table_open_cache | 1010

We have less tables than the number set for table_open_cache.

select count() from information_schema.tables;
±---------+
| count() |
±---------+
| 998 |
±---------+

Any other input would be appreciated.

Thanks.

4

Did you upgrade 5.7 since a few weeks ago? If not, then the reason for this change in behavior is your application. When you have high Opened_files, how many connections do you have to the database? (SHOW PROCESSLIST)

How many of your queries are running full table scans? How many queries are doing full joins? How many queries are joining 2, 3, 5, 8+ tables?

That’s typical. The cache is not a 1:1 mapping. You and I (2 separate connections) can run a simple SELECT against 1 table, yet the amount of “open tables” and cache usage will increase by 2 because we each have an instance of the table.

Let me clarify something, Opened_files is a status counter. It will always be increasing. This is why the number itself does not matter, and the reason why you should not be worried about the specific value. This counter will reset to 0 when MySQL restarts and will increase forever while MySQL is running.

We measure uptime for MySQL, typically in the months/years range, so seeing this counter in the hundreds of millions is quite common.

This is a bit old, but still says the same message as what I’m saying:

Unless you are seeing performance degradation, I see no reason to be concerned with this. Can you offer up some concrete evidence that you are seeing an impact in performance and more specifically why this is concerning to you?

5

Thanks for all the input.

No upgrade of 5.7.

It is not opened_files that I am concerned with. I posted opened_files metrics because you had asked for them. However, this metric has never been the concern.

It is open_files that I am concerned with. This number is now perpetually growing. These are files mysql currently has open. As I mentioned previously, the number of open files used to hover around 50-150 like clock work every day. Now, as of recent, this number is consistently growing. We are going to be in the thousands in a few weeks.

This is a concern because:

  1. This is a change in behavior
  2. Too many open files will eventually be a strain on resources and we will hit soft/hard limits on file handling
  3. This seems to indicate MySQL is not letting go of files which is very concerning to me. In /proc/<MySQLProcessID/fd we have tmp files with dates from 2 days ago.

Thanks.

6

Just another bit of info. I have noticed this in mysql’s error log and it is causing problems: (errno: 24 - Too many open files)

7

How many connections in SHOW PROCESSLIST? MySQL will only close files when the connections close.

My apologies. That was my typo mistake.

Have a look at some of the following sys schema tables for insight to the files that are opened:

SELECT * FROM io_global_by_file_by_bytes LIMIT 5;
select * from io_global_by_file_by_latency limit 5;
select * from latest_file_io limit 5;