How to check Data Dictionary size in MySql

Question

How to check my current data dictionary cache size?

FYI: -> Data Dictionary cache - which stores open tables information. So that the data dictionary cache increases depends upon the number of open tables.

-> table_open_cache is an variable which holds a value that mysql can have total number of open tables. Im asking the current data dictionary size which has the data of open tables. (Status of current data dictionary size).

MySql version - 5.7.18

Mown

Answer 1

The number of table definitions that can be stored in the definition cache. If you use a large number of tables, you can create a large table definition cache to speed up opening of tables. The table definition cache takes less space and does not use file descriptors, unlike the normal table cache. The minimum value is 400. The default value is based on the following formula, capped to a limit of 2000:

MIN(400 + table_open_cache / 2, 2000)

More details to here https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_table_definition_cache

Smallman

Ravi, table_open_cache is an variable which holds a value that mysql can have total number of open tables. Im asking the current data dictionary size which has the data of open tables. (Status of current data dictionary size). – Mown

Answer 2

These VARIABLES may be relevant:

table_open_cache
table_open_cache_instances
table_definition_cache
schema_definition_cache  (new in 8.0)
tablespace_definition_cache  (new in 8.0)

Each cache is a fixed size, configurable at startup time. (In some cases, on newer versions, it can be dynamically changed.) The docs for 5.7 say, for example, that table_open_cache is "Dynamic"

These GLOBAL STATUS values may be relevant:

Open_table_definitions
Opened_table_definitions
Opened_tables
Opened_files
Table_open_cache_hits
Table_open_cache_misses
Table_open_cache_overflows

The hits/misses/overflows give you good clues as to whether the table_open_cache is big enough, but not what the optimal size is.

In 5.7 and before, the data dictionary comes from the .frm files scattered on disk. In 8.0, it is in a set of InnoDB tables. Presumably, a suitable query could provide some relevant metrics.

See also things like performance_schema_max_table_handles.

Concierge

Rick, Show engine innodb status; It displays some status, in that under buffer pool memory - (data dictionary memory allocated) a variable is there. Isn't it my current data dictionary size. – Mown