opened_tables count increasing rapidly

Question

number of opened tables is increasing rapidly after sometime.

MariaDB [(none)]> show global status like 'open%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| Open_files               | 6      |
| Open_streams             | 0      |
| Open_table_definitions   | 1200   |
| Open_tables              | 1200   |
| Opened_files             | 791203 |
| Opened_plugin_libraries  | 0      |
| Opened_table_definitions | 788523 |
| Opened_tables            | 807692 |
| Opened_views             | 0      |
+--------------------------+--------+
9 rows in set (0.00 sec)

MariaDB [(none)]> show global status like 'up%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Update_scan               | 200   |
| Uptime                    | 2028  |
| Uptime_since_flush_status | 2028  |

how global status like '%conn%';
+-----------------------------------------------+-------+
| Variable_name                                 | Value |
+-----------------------------------------------+-------+
| Aborted_connects                              | 2     |
| Connection_errors_accept                      | 0     |
| Connection_errors_internal                    | 0     |
| Connection_errors_max_connections             | 0     |
| Connection_errors_peer_address                | 0     |
| Connection_errors_select                      | 0     |
| Connection_errors_tcpwrap                     | 0     |
| Connections                                   | 151   |
| Max_used_connections                          | 13    |
| Performance_schema_session_connect_attrs_lost | 0     |
| Slave_connections                             | 0     |
| Slaves_connected                              | 0     |
| Ssl_client_connects                           | 0     |
| Ssl_connect_renegotiates                      | 0     |
| Ssl_finished_connects                         | 0     |
| Threads_connected                             | 12    |
| wsrep_connected                               | OFF   |
+-----------------------------------------------+-------+

 select count(*) from information_schema.tables;
+----------+
| count(*) |
+----------+
|     1986 |
+----------+

Can anyone suggest how to investigate why the opened tables count is increasing so rapidly and how to resolve this issue?

Answer 1

would you confirm what load you are running during this 30 mins of database uptime? this is actually accumulative values of the total opened tables and a smaller table open cache or creating temporary tables can increase this value.

paste values of CREATED_TMP_TABLES, TABLE_OPEN_CACHE. Also paste total ram available for this DB on your machine.

Thanks

Answer 2

How many table in your system? This will answer that question and also bump Opened_tables by that number

SELECT COUNT(*), MAX(table_rows) FROM information_schema.TABLES;

My point is that there could be things that you don't know are happening that cause lots of table openings. This can be especially true if you are using some 3rd party software that needs to fetch schema information, perhaps over and over.