MySQL information_schema表查询导致内存暴涨

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释放掉。

MySQL information_schema表查询导致内存暴涨,布布扣,bubuko.com

MySQL information_schema表查询导致内存暴涨

原文：http://www.cnblogs.com/xpchild/p/3863778.html