Too many open tables and increasing indefinately

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 :slight_smile:

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.

1 Like

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