Hi Team,
i am facing issue where the tables keep on opening and reaches a limit and server hangs
Server version: 5.7.31-34-log Percona Server (GPL), Release 34, Revision 2e68637
Can you guide if there is any bug in the version above?
Thanks
Hussain
2 Likes
| Open_tables | 519 |
| Opened_files | 2684039 |
| Opened_table_definitions | 2639611 |
| Opened_tables | 21159909 |
| Slave_open_temp_tables | 0 |
| Table_open_cache_hits | 521344287 |
| Table_open_cache_misses | 21159909 |
| Table_open_cache_overflows | 12750 |
±---------------------------±----------+
Some stats. we continously flush tables to keep it from hanging,
What do you have for table_open_cache and what is the ulimit -n for the mysql OS user?
3 Likes
ulimit -n is 131072
table_open_cache 4000
we have tweeked this variables to more than above values but would not help.
Do you think it could be a bug ?
1 Like
Can anyone please help here.
1 Like
What is your value for table_definition_cache ? Are you 100% InnoDB? How many tables in the database? Any tables partitioned?
1 Like
table_definition_cache 2000
yes we are 100% innodb
no tables partitioned
544 tables
1 Like
Ok. 544 tables is not a lot of tables. Your parameters look fine. Is your application properly closing connections? Or are you using a connection pooler of some kind that isn’t closing/recycling connections?
1 Like
we are manually flushing tables now using a cron to keep the server from hanging. we dont even get any errors or alerts in error log 
1 Like
You didn’t answer my questions.
2 Likes
yes app is closing the connections properly. But they use lots of joins in queries with many tables.
1 Like
So even table_open_cache 8000 and table_open_cache_instances 8, doesn’t do anything to help?
It may be a bug, but I would need to see a crash report.
1 Like
yes we tried to increase those values but it would not help.
1 Like
how do i get the crash report?
1 Like
Should be in your error log when MySQL crashes. Also, google how to enable core dumps as our dev team will 100% need one of those to do true debugging.
3 Likes
There is nothing in error log for the crash. The servers just seems to hang. How do i get the core dump?
1 Like
Any chance you have cyclic triggers, triggers firing in a loop? Might continue opening table descriptors… Not sure.
1 Like
No, I feel like this is the same bug in 8.x in Oracle MySQL
Bug 29305186 - GROWING NUMBER OF OPEN TEMP FILES UNTIL MAXIMUM IS REACHED, THEN HANGS.
BUG:28039829 - SELECT QUERY WITH COMPLEX JOINS LEAKS FILE HANDLES
1 Like
Can someone help here
1 Like
is there any tool which can capture details about root cause of DB hung , it will not generate crash report as DB is not crashing, its just hanging, can you please guide how to debug hanging DB issue
1 Like