MySQL information_schema Table query causes memory spikes

Case: The following SQL statement causes the MySQL instance memory to skyrocket:

　　SELECT * FROM tables where table_name not IN (select table_name from partitions GROUP BY TABLE_NAME have COUNT (*) >1);

　　MySQL 5.5, 1w+ 's InnoDB table.

Let's look at the results of the survey:

1. Execution of SQL and memory allocation:

Step1 : Constructing information_schema.tables temporary table

1.1 construct a temporary table tables structure:

Description: func=create_schema_table; Engine=heap

Memory: Tables is the heap engine's table, temporarily constructed, using heap memory; statement end Close_tmp_tables released.

1.2 populating temporary tables tables data: A total of three types of tables to populate tables memory

1. Memory Engine:

Description: Information_schema The table below, create a temporary table,

Memory: Uses heap memory, close_tmp_tables after the data is populated, and frees up memory.

2. Mysiam Engine:

Description: Information_schema The next section of the table, which is a temporary table for the Mysiam engine.

Memory: Use heap memory, create disk temporary files, close_tmp_tables, free memory, delete temporary files.

3. InnoDB engine and others:

Description: Use the normal open_tables function to create a table,table_share, handler object.

Memory: Using heap memory

Step2 : Constructing information_schema.partition temporary table

The steps are the same as STEP1, but partition because of the BLOB, when the system was created, it specified a temporary table for the Mysiam engine, not the memory heap temp table.

Such as:

  2. overhead of constructing two temporary tables:

The example is a total of 1w tables, plus a system table, about 10200, in order to construct these two temporary tables:

A) altogether open approximately 10200*2 times, plus create temporary table, temporary disk file.

b) and Table_cache set 400, so opened_table basically no multiplexing, opened immediately after closing.

Two temporary tables that do not exist, all constructed, and the heap memory used by the open large number of tables to construct the temporary table is now released.

3. Below you can execute SQL

SQL The Implementation plan is:

1 information_schema.tables

n Nest Loops information_schema.partitions

Nest Loop that is, for tables each record is scanned once patitions.

4. The key issues are:

The execution plan calls the following function stack:

Mysql_select

Join::exec

Do_select

Sub_select

Evaluate_join_record

Item_subselect::exec

Subselect_single_select_engine::exec

Join::exec

In Join::exec there are the following judgments:

     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; }      Elsetable_list->table->file->stats.records=0; if(do_fill_table(THD, table_list, tab)) {

That is: Subselect sub-query if it is schema_table, and in the execution state, it is necessary to delete all the data in partition, each nest loop do_fill_table again.

The result of the execution is:

A) to construct two temporary tables, open the 10200*2 secondary table,

b) also for each Nest loop, deleted and constructed 10,200 times partition table, altogether open 10200*10200 times table.

The Table_cache can be completely ignored.

 

But why is it taking up a lot of memory?

Throughout the construction process:

1. Heap Memory: After all tables are open, the data is filled to the temporary table, free, without waiting for the statement to end.

2. Thread memory: In order to construct a field, the table list, which is allocated from the thd->mem_root thread, needs to be released at the end of the statement.

as follows, each time the subquery executes once, Thd->mem_root increases the memory block;

GDB) p * ( This->thd->mem_root) $4= {Min_malloc = +, block_size =8160,Block_num = 748, First_block_usage =0, Breakpoint1, Join::exec ( This=0x7f9a2c01f508) at sql/sql_select.cc:1843(GDB) p*( This->thd->mem_root) $5= {Min_malloc = +, block_size =8160,block_num = 758, First_block_usage =0, Breakpoint1, Join::exec ( This=0x7f9a2c01f508) at sql/sql_select.cc:1843(GDB) p*( This->thd->mem_root) $6= {F Min_malloc = +, block_size =8160,Block_num = 767, First_block_usage =0,

so: This SQL , because open too many tables, the execution time is too long, and THD memory Because the statement does not end, can not be freed, the memory has been rising, when the statement is finished, Thd->mem_root's memory is all released through free.