Tag: tune

Check current value of open_tables and opened_tables

Run:

show global status  like 'open%';

Output:

MariaDB [(none)]> show global status  like 'open%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| Open_files               | 2258     |
| Open_streams             | 0        |
| Open_table_definitions   | 1888     |
| Open_tables              | 2000     |
| Opened_files             | 10504934 |
| Opened_plugin_libraries  | 28       |
| Opened_table_definitions | 488898   |
| Opened_tables            | 542079   |
| Opened_views             | 18497    |
+--------------------------+----------+

Find out Table cache hit rate

Run:

show global variables like 'table_open_cache';

Output:

MariaDB [(none)]> show global variables like 'table_open_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 2000  |
+------------------+-------+
1 row in set (0.00 sec)

Calculating:

Table cache hit rate = table_open_cache*100/Opened_tables

2000×100÷542079=0.37%

In general it should be more than 50%

Find out total tables of your databases

Run:

SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES