# Too many open tables and increasing indefinately **URL:** \ **Category:** Percona Server for MySQL 5.7\ **Tags:** percona\ **Created:** [November 22, 2021, 3:51pm UTC](https://forums.percona.com/t/too-many-open-tables-and-increasing-indefinately/13163 "2021-11-22T15:51:52Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Hussain\_Patel](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/hussain_patel/32/17808_2.png) [@Hussain\_Patel](https://forums.percona.com/u/Hussain_Patel)\ **Post date:** [November 22, 2021, 3:51pm UTC](https://forums.percona.com/t/too-many-open-tables-and-increasing-indefinately/13163/1 "2021-11-22T15:51:52Z")
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 ---
**Author:** ![Hussain\_Patel](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/hussain_patel/32/17808_2.png) [@Hussain\_Patel](https://forums.percona.com/u/Hussain_Patel)\ **Post date:** [November 22, 2021, 3:55pm UTC](https://forums.percona.com/t/too-many-open-tables-and-increasing-indefinately/13163/2 "2021-11-22T15:55:25Z")
| 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, ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [November 23, 2021, 2:24pm UTC](https://forums.percona.com/t/too-many-open-tables-and-increasing-indefinately/13163/3 "2021-11-23T14:24:50Z")
What do you have for _table\_open\_cache_ and what is the `ulimit -n` for the mysql OS user? ---
**Author:** ![Hussain\_Patel](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/hussain_patel/32/17808_2.png) [@Hussain\_Patel](https://forums.percona.com/u/Hussain_Patel)\ **Post date:** [November 23, 2021, 11:03pm UTC](https://forums.percona.com/t/too-many-open-tables-and-increasing-indefinately/13163/4 "2021-11-23T23:03:11Z")
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 ? ---
**Author:** ![Hussain\_Patel](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/hussain_patel/32/17808_2.png) [@Hussain\_Patel](https://forums.percona.com/u/Hussain_Patel)\ **Post date:** [November 30, 2021, 5:27pm UTC](https://forums.percona.com/t/too-many-open-tables-and-increasing-indefinately/13163/5 "2021-11-30T17:27:00Z")
Can anyone please help here. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [December 2, 2021, 4:50pm UTC](https://forums.percona.com/t/too-many-open-tables-and-increasing-indefinately/13163/6 "2021-12-02T16:50:03Z")
What is your value for `table_definition_cache` ? Are you 100% InnoDB? How many tables in the database? Any tables partitioned? ---
**Author:** ![Hussain\_Patel](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/hussain_patel/32/17808_2.png) [@Hussain\_Patel](https://forums.percona.com/u/Hussain_Patel)\ **Post date:** [December 2, 2021, 4:58pm UTC](https://forums.percona.com/t/too-many-open-tables-and-increasing-indefinately/13163/7 "2021-12-02T16:58:45Z")
table\_definition\_cache 2000 yes we are 100% innodb no tables partitioned 544 tables ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [December 2, 2021, 6:03pm UTC](https://forums.percona.com/t/too-many-open-tables-and-increasing-indefinately/13163/8 "2021-12-02T18:03:53Z")
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? ---
**Author:** ![Hussain\_Patel](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/hussain_patel/32/17808_2.png) [@Hussain\_Patel](https://forums.percona.com/u/Hussain_Patel)\ **Post date:** [December 2, 2021, 6:11pm UTC](https://forums.percona.com/t/too-many-open-tables-and-increasing-indefinately/13163/9 "2021-12-02T18:11:37Z")
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 🙂 ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [December 2, 2021, 6:14pm UTC](https://forums.percona.com/t/too-many-open-tables-and-increasing-indefinately/13163/10 "2021-12-02T18:14:14Z")
You didn’t answer my questions. ---
**Author:** ![Hussain\_Patel](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/hussain_patel/32/17808_2.png) [@Hussain\_Patel](https://forums.percona.com/u/Hussain_Patel)\ **Post date:** [December 2, 2021, 6:15pm UTC](https://forums.percona.com/t/too-many-open-tables-and-increasing-indefinately/13163/11 "2021-12-02T18:15:07Z")
yes app is closing the connections properly. But they use lots of joins in queries with many tables. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [December 2, 2021, 6:21pm UTC](https://forums.percona.com/t/too-many-open-tables-and-increasing-indefinately/13163/12 "2021-12-02T18:21:34Z")
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. ---
**Author:** ![Hussain\_Patel](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/hussain_patel/32/17808_2.png) [@Hussain\_Patel](https://forums.percona.com/u/Hussain_Patel)\ **Post date:** [December 2, 2021, 6:22pm UTC](https://forums.percona.com/t/too-many-open-tables-and-increasing-indefinately/13163/13 "2021-12-02T18:22:30Z")
yes we tried to increase those values but it would not help. ---
**Author:** ![Hussain\_Patel](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/hussain_patel/32/17808_2.png) [@Hussain\_Patel](https://forums.percona.com/u/Hussain_Patel)\ **Post date:** [December 2, 2021, 7:32pm UTC](https://forums.percona.com/t/too-many-open-tables-and-increasing-indefinately/13163/14 "2021-12-02T19:32:50Z")
how do i get the crash report? ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [December 2, 2021, 7:36pm UTC](https://forums.percona.com/t/too-many-open-tables-and-increasing-indefinately/13163/15 "2021-12-02T19:36:27Z")
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. ---
**Author:** ![Hussain\_Patel](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/hussain_patel/32/17808_2.png) [@Hussain\_Patel](https://forums.percona.com/u/Hussain_Patel)\ **Post date:** [December 2, 2021, 7:37pm UTC](https://forums.percona.com/t/too-many-open-tables-and-increasing-indefinately/13163/16 "2021-12-02T19:37:40Z")
There is nothing in error log for the crash. The servers just seems to hang. How do i get the core dump? ---
**Author:** ![RDM1](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/rdm1/32/5245_2.png) [@RDM1](https://forums.percona.com/u/RDM1)\ **Post date:** [December 5, 2021, 6:01pm UTC](https://forums.percona.com/t/too-many-open-tables-and-increasing-indefinately/13163/17 "2021-12-05T18:01:34Z")
Any chance you have cyclic triggers, triggers firing in a loop? Might continue opening table descriptors… Not sure. ---
**Author:** ![Hussain\_Patel](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/hussain_patel/32/17808_2.png) [@Hussain\_Patel](https://forums.percona.com/u/Hussain_Patel)\ **Post date:** [December 15, 2021, 5:20pm UTC](https://forums.percona.com/t/too-many-open-tables-and-increasing-indefinately/13163/18 "2021-12-15T17:20:59Z")
No, I feel like this is the same bug in 8.x in Oracle MySQL [Bug 29305186](https://support.oracle.com/epmos/faces/BugDisplay?parent=DOCUMENT&sourceId=2534605.1&id=29305186) - GROWING NUMBER OF OPEN TEMP FILES UNTIL MAXIMUM IS REACHED, THEN HANGS. [BUG:28039829](https://support.oracle.com/epmos/faces/BugDisplay?parent=DOCUMENT&sourceId=2534605.1&id=28039829) - SELECT QUERY WITH COMPLEX JOINS LEAKS FILE HANDLES ---
**Author:** ![Hussain\_Patel](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/hussain_patel/32/17808_2.png) [@Hussain\_Patel](https://forums.percona.com/u/Hussain_Patel)\ **Post date:** [December 16, 2021, 10:05pm UTC](https://forums.percona.com/t/too-many-open-tables-and-increasing-indefinately/13163/19 "2021-12-16T22:05:05Z")
Can someone help here ---
**Author:** ![Sanjay\_Sheoran](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/sanjay_sheoran/32/5351_2.png) [@Sanjay\_Sheoran](https://forums.percona.com/u/Sanjay_Sheoran)\ **Post date:** [December 17, 2021, 12:17am UTC](https://forums.percona.com/t/too-many-open-tables-and-increasing-indefinately/13163/20 "2021-12-17T00:17:36Z")
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