MySQL Bugs: #115550: Open files exceeds limit

Bug #115550	Open files exceeds limit
Submitted:	9 Jul 2024 14:26	Modified:	13 Jul 2024 15:11
Reporter:	Alfred Ng	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.27	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
error always shows exceeds 6 files regardless increase the Innodb_open_file

at the begining, error log shows Open "files 306 exceeds the limit 300"

2023-12-11T08:51:08.756891Z 36869 [Warning] [MY-012152] [InnoDB] Open files 306 exceeds the limit 300
2023-12-11T08:51:08.757014Z 0 [Warning] [MY-012152] [InnoDB] Open files 306 exceeds the limit 300
2023-12-11T08:51:08.757195Z 0 [Warning] [MY-012152] [InnoDB] Open files 306 exceeds the limit 300

after change the Innodb_open_file from 300 to 1000, error shows "Open files 1006 exceeds the limit 1000"

2024-05-02T05:40:12.622744Z 95 [Warning] [MY-012152] [InnoDB] Open files 1006 exceeds the limit 1000
2024-05-02T05:40:12.622806Z 0 [Warning] [MY-012152] [InnoDB] Open files 1006 exceeds the limit 1000
2024-05-02T16:00:21.207385Z 91 [Warning] [MY-012152] [InnoDB] Open files 1006 exceeds the limit 1000

after change the Innodb_open_file from 1000 to 5000, error shows "Open files 5006 exceeds the limit 5000"

2024-06-27T11:48:04.232744Z 30 [Warning] [MY-012152] [InnoDB] Open files 5006 exceeds the limit 5000
2024-06-27T11:48:04.232902Z 33 [Warning] [MY-012152] [InnoDB] Open files 5006 exceeds the limit 5000

How to repeat:
dont have a specific action to trigger the error. it shows on and off. maybe at that moment there is heavy join queries running

Hi Mr. Ng,

Thank you very much for your bug report.

However, this is not a bug.

There are other open files except innodb_open_files. Here is an excerpt from the system variables dealing with open files;

innodb-open-files              
open-files-limit                                             524288
table-open-cache                                             4000
table-open-cache-instances                                   16

Also , release 8.0.27 is no longer supported. Current release of 8.0 is 8.0.38.

Unsupported.

we have try to adjust the open-files-limit and table-open-cache. but still the log shows the warning "Open files yyyy exceeds the limit xxxx. each time the yyyy is just more than xxxx less than 10. please help to advise how to solve this problem. Thanks.

Hi,

Your chosen numbers are way too low .......

Open files actually means open file handles. 

That means that you should set it much, much higher .......

Please, read our entire Reference Manual, that you ca find on https://dev.mysql.com.