Innodb parent table Open causes crash

Case Description:

In InnoDB, the parent and child tables are associated with foreign constraint because the check foreign KEY constraint is required to update the data, and if the parent table is reference by a large number of child tables,
Then open all the child table and all the foreign constraint, resulting in a long time, resulting in long semaphore wait.

Analysis Process:

Case Use cases:

CREATE TABLE ' t1 ' (  int(one) not NULL,  PRIMARY KEY (' F1 ')) ENGINE=  InnoDB CREATE TABLE ' fk_1 ' (  int(one) not NULL,  PRIMARY KEY (' F1 '),  CONSTRAINT ' PC1 ' FOREIGN KEY (' F1 ') REFERENCES ' t1 ' (' F1 ') on DELETE CASCADE on UPDATE CASCADE) ENGINE=InnoDB. .... The fk_[0-10000] table is built here.

1. Background: InnoDB data dictionary

InnoDB uses the system table space to save table-related data dictionaries, and the system's data dictionaries include:

• Sys_tables
• Sys_indexes
• Sys_columns
• Sys_fields
• Sys_foreign
• Sys_foreign_cols
• Sys_stats

When you load a table, save the table-related index,column, Index_field, foreign, foreign_col data from these tables to the dictionary cache, respectively.
The corresponding memory objects are: dict_col_struct,dict_field_struct,dict_index_struct,dict_table_struct,dict_foreign_struct.
These objects are globally unique, retain only one copy of the dictionary cache, and use Dict_sys->mutex for synchronous protection, and all open handler are referenced to those objects.

Note: The system's data dictionary is also InnoDB clustered index table and protected by redo.

　　

2. Open process

Entry function Ha_innobase::open ()
Dict_load_table:

1. Through the Sys_tables system table, load table related definitions
2. Through the Sys_indexes system table, according to table_id load all relevant index
3. Through the Sys_columns system table, according to table_id load all columns
4. Through the Sys_fields system table, according to index_id load all index field
5. Through the Sys_foreign system table, load all associated tables and foreign key

In the load of these definitions, the process is:
1. Find the System Dictionary table through a field
2. Create a Memory object based on the record of the query and add it to the global cache

But there are some differences when load foreign, because foreign key is associated with two tables. The following is a detailed description of the load foreign process.

3. Detailed process of load foreign

The associated relationship as shown in foreign:

　　

Steps for Load:

3.1: Find sys_foreign based on table name T1. The Sys_foreign table has a total of three indexes:

Index_1 (ID): Cluster_index
Index_2 (for_name): Secondary_index
Index_3 (ref_name): Secondary_index

So, according to For_name= ' T1 ', ref_name= ' T1 ' retrieves all the relevant foreign_id.

3.2: Based on 3.1 retrieved foreign_id, load foreign object and open related fk_[1-10000] table. 3.3: Join the cache

Because no special cache,foreign were added to For_name->foreign_list, ref_name->refence_list
The key to the problem: because foreign is globally unique, but foreign is also associated with two tables, it is possible to open other tables
has been opened, so, after the Create foreign object, you need to determine the following four list, whether it already exists, and use it directly if it exists.

Dict_foreign_find: Each of the four lists is queried, and if it already exists, free creates a new foreign object that references the existing one.
For_name->foreign_list
For_name->refence_list
Ref_name->foreign_list
Ref_name->refence_list

If it does not exist, add the newly created foreign to the For_name->foreign_list,ref_name->refence_list list.

4. The cause of the problem:

Because the first load, so find is not found, but these four are list, with open more and more, the cost of retrieval is getting bigger.
And the whole process, have been holding Trx_sys->mutex, eventually led to long semaphore wait.

5. How to improve the problem:

　　

In MySQL version 5.5.39, the fix was made, except for the foreign_list,referenced_list.
There are also two additional red_black tree, as shown in the following source code:

structdict_table_struct{table_id_t ID; /*!< ID of the table*/mem_heap_t* HEAP;/*!< Memory Heap*/Char* NAME;/*!< Table name*/ut_list_base_node_t (dict_foreign_t) foreign_list; /*!< List of foreign key constraints in the table, these refer to columns in other tables*/ut_list_base_node_t (dict_foreign_t) referenced_list;/*!< List of foreign key constraints which refer to this table*/ib_rbt_t* FOREIGN_RBT;    /*!< a rb-tree of all foreign keys listed in Foreign_list, sorted by Foreign->id */ib_rbt_t* REFERENCED_RBT; /*!< a rb-tree of all foreign keys listed in Referenced_list, sorted by Foreign->id * /

Initializes a copy of the Red_black tree when the list is initialized. In the process of dict_foreign_find, we find the RBT directly, so the cost of the query is greatly reduced.