What is the dictionary memory and where does it stores, is it uses the memory of buffer pool or a seperate memory, does buffer pool stores metadata pages also or only user table and indexes, in both the older and new version of mysql
1 Answer
The buffer pool holds pages corresponding to anything stored in tablespace files, and also other data structures such as the InnoDB lock table.
The InnoDB data dictionary is stored in tablespace files both in older (pre-8.0) versions of MySQL and current versions of MySQL, so yes, it does occupy part of the buffer pool memory. It's likely a small fraction of the size of your data.
Old MySQL:
https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_data_dictionary says:
data dictionary
Metadata that keeps track of InnoDB-related objects such as tables, indexes, and table columns. This metadata is physically located in the InnoDB system tablespace. For historical reasons, it overlaps to some degree with information stored in the .frm files.
Current MySQL:
https://dev.mysql.com/doc/refman/8.4/en/glossary.html#glos_data_dictionary says:
data dictionary
Metadata that keeps track of database objects such as tables, indexes, and table columns. For the MySQL data dictionary, introduced in MySQL 8.0, metadata is physically located in InnoDB file-per-table tablespace files in the mysql database directory. For the InnoDB data dictionary, metadata is physically located in the InnoDB system tablespace.
You can read more details about the Data Dictionary in the MySQL Manual: https://dev.mysql.com/doc/refman/en/data-dictionary.html
