1

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?

0

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

  • table open cache size is 60000. I compared the test results against mariadb 10.1.25 and 10.1.38. Didn't face this issue on mariadb 10.1.25. There number of open tables didnt increase too much but created_tmp_tables count was increasing. whereas with mariadb 10.1.38, created_tmp_tables count was very low and open tables count kept on increasing. Throughput with mariadb 10.1.25 was much better than with 10.1.38. Not sure whats the reason behind difference in the behavior with different versions of database. – Sprasad Apr 26 at 4:35
  • @Sprasad - changes to Created_tmp_tables is possibly a result of changes to the Optimizer. – Rick James Apr 27 at 4:35
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.