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?
| 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?
ulimit -n is 131072
we have tweeked this variables to more than above values but would not help.
Do you think it could be a bug ?
Can anyone please help here.
What is your value for
table_definition_cache ? Are you 100% InnoDB? How many tables in the database? Any tables partitioned?
yes we are 100% innodb
no tables partitioned
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?
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
You didn’t answer my questions.
yes app is closing the connections properly. But they use lots of joins in queries with many tables.
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.
yes we tried to increase those values but it would not help.
how do i get the crash report?
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.
There is nothing in error log for the crash. The servers just seems to hang. How do i get the core dump?
Any chance you have cyclic triggers, triggers firing in a loop? Might continue opening table descriptors… Not sure.
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
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