MySQL information_schema表查詢導致記憶體暴漲

標籤：style   blog   http   color   使用   os   

case：下面的一條sql語句，導致mysql執行個體記憶體暴漲：

　　select * from tables where table_name not in(select table_name from partitions group by table_name having count(*)>1 );

　　mysql 5.5， 1w+的innodb表。 

 

下面看下調查的結果：

 

1.  sql的執行情況以及記憶體配置：

 

step1： 構造information_schema.tables暫存資料表

 

1.1  構造暫存資料表tables結構：

說明：func=create_schema_table; engine=heap

        記憶體: tables是heap引擎的表，臨時構造，使用堆記憶體；語句結束close_tmp_tables釋放。

 

1.2 填充暫存資料表tables資料：一共由三類表來填充tables的記憶體

  1. memory引擎：

     說明：information_schema下的表，建立臨時table，

     記憶體： 使用堆記憶體，填充完資料後 close_tmp_tables，釋放記憶體。

  2. mysiam引擎：

     說明：information_schema下一部分表，是mysiam引擎的暫存資料表。

     記憶體： 使用堆記憶體，建立磁碟臨時檔案，close_tmp_tables，釋放記憶體，刪除臨時檔案。

3. innodb引擎和其它：

     說明：使用正常的open_tables函數，建立table，table_share, handler對象。

     記憶體： 使用堆記憶體

 

step2：構造information_schema.partition暫存資料表

  步驟和step1一樣，但partition因為blob的原因，系統建立的時候，指定了mysiam引擎的暫存資料表，而非記憶體heap暫存資料表。

如：

   

 

  2.  構造兩張暫存資料表的開銷：

執行個體一共1w張表，加上系統資料表，大約10200張，為了構造這兩個暫存資料表：

  a)  一共open了大約 10200*2 次，加上建立暫存資料表，臨時磁碟檔案。

  b)  而table_cache設定的400，所以opened_table基本沒有複用，開啟後馬上關閉了。

兩張並不存在的暫存資料表，全部構造完成，以上為了構造暫存資料表而open大量表所使用的堆記憶體，現在已經釋放。

 

3.  下面可以執行sql

 

sql的執行計畫是：

    1   information_schema.tables                

    n   nest loops information_schema.partitions

nest loop即：對於tables每一條記錄要掃描一次patitions。

 

4.  關鍵的問題是：

 

執行計畫調用如下函數棧：

  mysql_select

   JOIN::exec

    do_select

      sub_select

      evaluate_join_record

        Item_subselect::exec

         subselect_single_select_engine::exec

          JOIN::exec

   

在JOIN::exec有以下的判斷：

     if (table_list->schema_table_state && is_subselect)      {        table_list->table->file->extra(HA_EXTRA_NO_CACHE);        table_list->table->file->extra(HA_EXTRA_RESET_STATE);        table_list->table->file->ha_delete_all_rows();        free_io_cache(table_list->table);        filesort_free_buffers(table_list->table,1);        table_list->table->null_row= 0;      }      else        table_list->table->file->stats.records= 0;      if (do_fill_table(thd, table_list, tab))      {

 

即： subselect子查詢如果是schema_table, 並且在執行狀態中， 需要全部刪除 partition裡的資料，每次nest loop都重新do_fill_table。

 

執行的結果就是：

a)  為了構造兩個暫存資料表，open了10200*2次表，

b)  又為了每次nest loop，刪除並構造了10200次partition表，一共open了10200*10200次表。

table_cache可以完全無視了。

  

 

但為什麼會佔用大量的記憶體？

 

在整個構造的過程中：

1. 堆記憶體 ： 在open所有表後，往暫存資料表填充完資料，就free了，不用等語句結束。

2. 線程記憶體： 為了構造欄位，table list這些，記憶體都是從thd->mem_root線程中分配的，需要等語句結束才釋放。

 

如下，每次子查詢執行一次，thd->mem_root增加的memory block；

gdb) p *(this->thd->mem_root)$4 = { min_malloc = 32, block_size = 8160, block_num = 748, first_block_usage = 0,Breakpoint 1, JOIN::exec (this=0x7f9a2c01f508) at sql/sql_select.cc:1843(gdb) p *(this->thd->mem_root)$5 = { min_malloc = 32, block_size = 8160, block_num = 758, first_block_usage = 0,Breakpoint 1, JOIN::exec (this=0x7f9a2c01f508) at sql/sql_select.cc:1843(gdb) p *(this->thd->mem_root)$6 = {f min_malloc = 32, block_size = 8160, block_num = 767, first_block_usage = 0,

 

所以：這個sql，因為open太多表，執行時間過長， 而thd記憶體因為語句沒有結束，無法釋放，記憶體一直往上漲, 等語句結束，thd->mem_root的記憶體全部通過free釋放掉。