• Annamalai Gurusami's avatar
    Bug #18806829 OPENING INNODB TABLES WITH MANY FOREIGN KEY REFERENCES IS · b5299f35
    Annamalai Gurusami authored
    SLOW/CRASHES SEMAPHORE
    
    Problem:
    
    There are 2 lakh tables - fk_000001, fk_000002 ... fk_200000.  All of them
    are related to the same parent_table through a foreign key constraint.
    When the parent_table is loaded into the dictionary cache, all the child table
    will also be loaded.  This is taking lot of time.  Since this operation happens
    when the dictionary latch is taken, the scenario leads to "long semaphore wait"
    situation and the server gets killed.
    
    Analysis:
    
    A simple performance analysis showed that the slowness is because of the
    dict_foreign_find() function.  It does a linear search on two linked list
    table->foreign_list and table->referenced_list, looking for a particular
    foreign key object based on foreign->id as the key.  This is called two
    times for each foreign key object.
    
    Solution:
    
    Introduce a rb tree in table->foreign_rbt and table->referenced_rbt, which
    are some sort of index on table->foreign_list and table->referenced_list
    respectively, using foreign->id as the key.  These rbt structures will be
    solely used by dict_foreign_find().  
    
    rb#5599 approved by Vasil
    
    b5299f35
dict0mem.c 11.1 KB