From: Dmitry Lenev Date: May 11 2011 4:30am Subject: Re: bzr commit into mysql-5.1-bugteam branch (Dmitry.Shulga:3527) Bug#11749345 List-Archive: http://lists.mysql.com/commits/137038 Message-Id: <20110511043007.GA4626@bandersnatch> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Hello Dmitry! * Dmitry Shulga [11/04/15 10:21]: > #At file:///Users/shulga/projects/mysql/5.1-bugteam-bug38813/ based on revid:luis.soares@stripped > > 3527 Dmitry Shulga 2011-04-15 > Fixed bug#11749345 (formerly #38813) - increasing memory consumption > when selecting from I_S and views exist, in SP > > The problem was that when view is being opened its LEX is incorporated > into SQL-statement's LEX. > > The solution is to create temporal LEX when make opening of tables. I think this ChangeSet deserves more elaborate comment. We can start by describing problem in terms which can be understood by MySQL users. For example: " Re-execution of prepared statement (or statement in a stored routine) which read from one of I_S tables and which in order to fill this I_S table had to open a view led to increasing memory consumption. " Then we can describe what was the problem on technical level. For example, by writing something like: " What happened in this situation was that during the process of view opening for purpose of I_S filling view-related structures (like its LEX) were allocated on persistent MEM_ROOT of prepared statement (or stored routine). Since this MEM_ROOT is not freed until prepared statement deallocation (or expulsion of stored routine from the cache) and code responsible for filling I_S is not able to re-use results of view opening from previous executions this allocation ended up in memory hogging. " Then we can explain how this patch solves the problem and what additional steps we had to take to make it working. For example: " This patch solves the problem by ensuring that when a view opened for the purpose of I_S filling all its structures are allocated on non-persistent runtime MEM_ROOT. This is achieved by activating a temporary Query_arena bound to this MEM_ROOT. Since this step makes impossible linking of view structures into LEX of our prepared statement (or stored routine statement) this patch also changes code filling I_S table to install a proxy LEX before trying to open a view or a table. Consequently some code which was responsible for backing-up/restoring parts of LEX when view/table was opened during filling of I_S table became redundant and was removed. " Finally we should explain why this patch doesn't contain any test case: "This patch doesn't contain test case for this bug as it is hard to test memory hogging in our test suite." > @ mysql-test/r/information_schema.result > Previous result set for explain plan statement was invalid. > @ sql/sql_show.cc > Added last argument to fill_schema_show_cols_or_idxs() that contains > pointer to TABLE_LIST to SHOW. > get_all_tables() was modified: created temporal LEX object before ^^^^^^^^^ temporary > processing tables. This LEX objects destroyed before return from > get_all_tables() function and clean up data associated with itself. > As well temporal arena object was created and assigned as default > arena for successive processing. Memory allocated into this arena > cleaned up before return from get_all_tables(). Please see my suggestions below. I am afraid this comment will have to be adjusted. > > modified: > mysql-test/r/information_schema.result > sql/sql_show.cc > === modified file 'mysql-test/r/information_schema.result' > --- a/mysql-test/r/information_schema.result 2010-11-30 17:51:25 +0000 > +++ b/mysql-test/r/information_schema.result 2011-04-15 06:12:12 +0000 > @@ -1342,7 +1342,7 @@ id select_type table type possible_keys > 1 SIMPLE tables ALL NULL NULL NULL NULL NULL Open_frm_only; Scanned all databases; Using filesort > explain select * from (select table_name from information_schema.tables) as a; > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY system NULL NULL NULL NULL 0 const row not found > +1 PRIMARY ALL NULL NULL NULL NULL 54 > 2 DERIVED tables ALL NULL NULL NULL NULL NULL Skip_open_table; Scanned all databases > drop view v1; > create table t1 (f1 int(11)); After discussing your patch with Sergey Gluhov I think that the above change in test result is a bad thing. It means that this EXPLAIN tried to materialize and actually filled I_S table with 54 rows. And this should be avoided. This can be easily done with your current patch by making proxy LEX inherit value of LEX::describe member from old LEX, but I have more radical suggestion instead. See below. ... > > === modified file 'sql/sql_show.cc' > --- a/sql/sql_show.cc 2010-12-14 09:33:03 +0000 > +++ b/sql/sql_show.cc 2011-04-15 06:12:12 +0000 > @@ -3021,18 +3021,17 @@ make_table_name_list(THD *thd, List static int > fill_schema_show_cols_or_idxs(THD *thd, TABLE_LIST *tables, > ST_SCHEMA_TABLE *schema_table, > - Open_tables_state *open_tables_state_backup) > + Open_tables_state *open_tables_state_backup, > + TABLE_LIST *show_table_list) > { > LEX *lex= thd->lex; > bool res; > LEX_STRING tmp_lex_string, tmp_lex_string1, *db_name, *table_name; > enum_sql_command save_sql_command= lex->sql_command; > - TABLE_LIST *show_table_list= tables->schema_select_lex->table_list.first; > TABLE *table= tables->table; > int error= 1; > DBUG_ENTER("fill_schema_show"); > > - lex->all_selects_list= tables->schema_select_lex; > /* > Restore thd->temporary_tables to be able to process > temporary tables(only for 'show index' & 'show columns'). > @@ -3300,7 +3299,6 @@ int get_all_tables(THD *thd, TABLE_LIST > { > LEX *lex= thd->lex; > TABLE *table= tables->table; > - SELECT_LEX *old_all_select_lex= lex->all_selects_list; > enum_sql_command save_sql_command= lex->sql_command; > SELECT_LEX *lsel= tables->schema_select_lex; > ST_SCHEMA_TABLE *schema_table= tables->schema_table; > @@ -3312,21 +3310,42 @@ int get_all_tables(THD *thd, TABLE_LIST > List db_names; > List_iterator_fast it(db_names); > COND *partial_cond= 0; > - uint derived_tables= lex->derived_tables; > int error= 1; > Open_tables_state open_tables_state_backup; > - uint8 save_context_analysis_only= lex->context_analysis_only; > Query_tables_list query_tables_list_backup; > #ifndef NO_EMBEDDED_ACCESS_CHECKS > Security_context *sctx= thd->security_ctx; > #endif > + > uint table_open_method; > + Query_arena i_s_arena(thd->mem_root, > + Query_arena::CONVENTIONAL_EXECUTION), > + backup_arena, *old_arena; > + LEX *old_lex= thd->lex, temp_lex; > DBUG_ENTER("get_all_tables"); > > - lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_VIEW; > + old_arena= thd->stmt_arena; > + // use temporal arena for statement execution > + thd->stmt_arena= &i_s_arena; > + // and use temporal LEX > + thd->lex= &temp_lex; > + lex_start(thd); > + // copy some attributes from original LEX > + lex= thd->lex; > + lex->sql_command= old_lex->sql_command; > + lex->wild= old_lex->wild; > + lex->select_lex.db= old_lex->select_lex.db; > + > + lex->context_analysis_only= CONTEXT_ANALYSIS_ONLY_VIEW; > lex->reset_n_backup_query_tables_list(&query_tables_list_backup); > > /* > + assign temporal arena to THD object and save original arena'a > + value for successive restoring > + */ > + thd->set_n_backup_active_arena(&i_s_arena, &backup_arena); > + > + /* > We should not introduce deadlocks even if we already have some > tables open and locked, since we won't lock tables which we will > open and will ignore possible name-locks for these tables. After a bit more thinking I have realized that keeping proxy LEX active for the most of duration of get_all_tables() can be risky. The reason for this is that at some places in this function we call code which might require old LEX to be active as this code works with objects associated with old LEX. For example, when we call partial_cond->val_int() method we are evaluating expression represented by Item tree which actually belongs to original I_S query and thus in theory should be associated with old LEX. It is going to be fairly hard to prove that in practice active LEX doesn't matter (code dealing with sub-selects looks particularly suspicious). And even if we do it there is no guarantee that this won't change in future. Another example is LEX::describe member, which value we have forgotten to inherit from old LEX which results in changed output of EXPLAIN. So I suggest to to play it safe and limit the duration for which we install temporary LEX in get_all_tables() to the moment when we call open_normal_and_derived_tables(), call process_table() function and close tables. I have came up with a patch implementing this idea, which I provide below. As a nice side-effect this patch removes some duplicate and redundant code. Please let me know what you think about this idea and the patch suggested! ... === modified file 'sql/sql_show.cc' --- sql/sql_show.cc 2011-04-04 13:04:15 +0000 +++ sql/sql_show.cc 2011-05-11 04:23:23 +0000 @@ -2428,12 +2428,11 @@ bool schema_table_store_record(THD *thd, } -int make_table_list(THD *thd, SELECT_LEX *sel, - LEX_STRING *db_name, LEX_STRING *table_name) +static int make_table_list(THD *thd, SELECT_LEX *sel, + LEX_STRING *db_name, LEX_STRING *table_name) { Table_ident *table_ident; table_ident= new Table_ident(thd, *db_name, *table_name, 1); - sel->init_query(); if (!sel->add_table_to_list(thd, table_ident, 0, 0, TL_READ)) return 1; return 0; @@ -3003,79 +3002,178 @@ make_table_name_list(THD *thd, Listlex; - bool res; - LEX_STRING tmp_lex_string, tmp_lex_string1, *db_name, *table_name; - enum_sql_command save_sql_command= lex->sql_command; - TABLE_LIST *show_table_list= tables->schema_select_lex->table_list.first; - TABLE *table= tables->table; - int error= 1; - DBUG_ENTER("fill_schema_show"); +static bool +fill_schema_table_by_open(THD *thd, bool is_show_fields_or_keys, + TABLE *table, ST_SCHEMA_TABLE *schema_table, + LEX_STRING *db_name, LEX_STRING *table_name, + Open_tables_state *open_tables_state_backup) +{ + Query_arena i_s_arena(thd->mem_root, + Query_arena::CONVENTIONAL_EXECUTION), + backup_arena, *old_arena; + LEX *old_lex= thd->lex, temp_lex, *lex; + LEX_STRING orig_db_name, orig_table_name; + TABLE_LIST *table_list; + bool result= true; - lex->all_selects_list= tables->schema_select_lex; /* - Restore thd->temporary_tables to be able to process - temporary tables(only for 'show index' & 'show columns'). - This should be changed when processing of temporary tables for - I_S tables will be done. + When a view is opened its structures are allocated on permanent + statement arena and linked into LEX tree for the current statement + (this happens even in cases when view is handled through TEMPTABLE + algorithm). + + To prevent this process from unnecessary hogging memory in permanent + arena of our I_S query and to avoid damaging its LEX we use temporary + arena and LEX for table/view opening. + + Use temporary arena instead of statement permanent arena. Also make + it active arena and save original one for successive restoring. */ - thd->temporary_tables= open_tables_state_backup->temporary_tables; + old_arena= thd->stmt_arena; + thd->stmt_arena= &i_s_arena; + thd->set_n_backup_active_arena(&i_s_arena, &backup_arena); + + /* Prepare temporary LEX. */ + thd->lex= lex= &temp_lex; + lex_start(thd); + + /* Disable constant subquery evaluation as we won't be locking tables. */ + lex->context_analysis_only= CONTEXT_ANALYSIS_ONLY_VIEW; + + /* + Some of process_table() functions rely on wildcard being passed from + old LEX (or at least being initialized). + */ + lex->wild= old_lex->wild; + + /* + Strings in db_name and table_name might be changed by make_table_list() + function. In order to pass their unaltered values to process_table() + call we save them here. + */ + if (!thd->make_lex_string(&orig_db_name, db_name->str, + db_name->length, FALSE) || + !thd->make_lex_string(&orig_table_name, table_name->str, + table_name->length, FALSE)) + goto end; + + /* + Create table list element for table to be open. Link it with the + temporary LEX. The latter is required to correctly open views and + produce table describing their structure. + */ + if (make_table_list(thd, &lex->select_lex, db_name, table_name)) + goto end; + + table_list= lex->select_lex.table_list.first; + + if (is_show_fields_or_keys) + { + /* + Restore thd->temporary_tables to be able to process + temporary tables(only for 'show index' & 'show columns'). + This should be changed when processing of temporary tables for + I_S tables will be done. + */ + thd->temporary_tables= open_tables_state_backup->temporary_tables; + } + else + { + /* + Apply optimization flags for table opening which are relevant for + this I_S table. We can't do this for SHOW COLUMNS/KEYS because of + backward compatibility. + */ + table_list->i_s_requested_object= schema_table->i_s_requested_object; + } + /* Let us set fake sql_command so views won't try to merge themselves into main statement. If we don't do this, SELECT * from information_schema.xxxx will cause problems. - SQLCOM_SHOW_FIELDS is used because it satisfies 'only_view_structure()' + SQLCOM_SHOW_FIELDS is used because it satisfies + 'only_view_structure()'. */ lex->sql_command= SQLCOM_SHOW_FIELDS; - res= open_normal_and_derived_tables(thd, show_table_list, - MYSQL_LOCK_IGNORE_FLUSH); - lex->sql_command= save_sql_command; + + result= open_normal_and_derived_tables(thd, table_list, + MYSQL_LOCK_IGNORE_FLUSH); + /* - get_all_tables() returns 1 on failure and 0 on success thus - return only these and not the result code of ::process_table() + Restore old value of sql_command back as it is being looked at in + process_table() function. + */ + lex->sql_command= old_lex->sql_command; + + /* + XXX: show_table_list has a flag i_is_requested, + and when it's set, open_normal_and_derived_tables() + can return an error without setting an error message + in THD, which is a hack. This is why we have to + check for res, then for thd->is_error() only then + for thd->main_da.sql_errno(). - We should use show_table_list->alias instead of - show_table_list->table_name because table_name - could be changed during opening of I_S tables. It's safe - to use alias because alias contains original table name - in this case(this part of code is used only for - 'show columns' & 'show statistics' commands). + Again we don't do this for SHOW COLUMNS/KEYS because + of backward compatibility. */ - table_name= thd->make_lex_string(&tmp_lex_string1, show_table_list->alias, - strlen(show_table_list->alias), FALSE); - if (!show_table_list->view) - db_name= thd->make_lex_string(&tmp_lex_string, show_table_list->db, - show_table_list->db_length, FALSE); - else - db_name= &show_table_list->view_db; - - - error= test(schema_table->process_table(thd, show_table_list, - table, res, db_name, - table_name)); - thd->temporary_tables= 0; - close_tables_for_reopen(thd, &show_table_list); - DBUG_RETURN(error); + if (!is_show_fields_or_keys && result && thd->is_error() && + thd->main_da.sql_errno() == ER_NO_SUCH_TABLE) + { + /* + Hide error for not existing table. + This error can occur for example when we use + where condition with db name and table name and this + table does not exist. + */ + result= 0; + thd->clear_error(); + } + else + { + result= schema_table->process_table(thd, table_list, + table, result, + &orig_db_name, + &orig_table_name); + } + +end: + lex->unit.cleanup(); + + /* + For safety reset list of open temporary tables before closing + all tables open within this Open_tables_state. + */ + thd->temporary_tables= 0; + close_thread_tables(thd); + + /* Restore original LEX value, statement's arena and THD arena values. */ + lex_end(thd->lex); + thd->lex= old_lex; + + if (i_s_arena.free_list) + i_s_arena.free_items(); + + thd->stmt_arena= old_arena; + thd->restore_active_arena(&i_s_arena, &backup_arena); + + return result; } @@ -3300,11 +3398,8 @@ int get_all_tables(THD *thd, TABLE_LIST { LEX *lex= thd->lex; TABLE *table= tables->table; - SELECT_LEX *old_all_select_lex= lex->all_selects_list; - enum_sql_command save_sql_command= lex->sql_command; SELECT_LEX *lsel= tables->schema_select_lex; ST_SCHEMA_TABLE *schema_table= tables->schema_table; - SELECT_LEX sel; LOOKUP_FIELD_VALUES lookup_field_vals; LEX_STRING *db_name, *table_name; bool with_i_schema; @@ -3312,20 +3407,14 @@ int get_all_tables(THD *thd, TABLE_LIST List db_names; List_iterator_fast it(db_names); COND *partial_cond= 0; - uint derived_tables= lex->derived_tables; int error= 1; Open_tables_state open_tables_state_backup; - uint8 save_context_analysis_only= lex->context_analysis_only; - Query_tables_list query_tables_list_backup; #ifndef NO_EMBEDDED_ACCESS_CHECKS Security_context *sctx= thd->security_ctx; #endif uint table_open_method; DBUG_ENTER("get_all_tables"); - lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_VIEW; - lex->reset_n_backup_query_tables_list(&query_tables_list_backup); - /* We should not introduce deadlocks even if we already have some tables open and locked, since we won't lock tables which we will @@ -3340,8 +3429,18 @@ int get_all_tables(THD *thd, TABLE_LIST */ if (lsel && lsel->table_list.first) { - error= fill_schema_show_cols_or_idxs(thd, tables, schema_table, - &open_tables_state_backup); + LEX_STRING db_name, table_name; + + db_name.str= lsel->table_list.first->db; + db_name.length= lsel->table_list.first->db_length; + + table_name.str= lsel->table_list.first->table_name; + table_name.length= lsel->table_list.first->table_name_length; + + error= fill_schema_table_by_open(thd, TRUE, + table, schema_table, + &db_name, &table_name, + &open_tables_state_backup); goto err; } @@ -3399,12 +3498,6 @@ int get_all_tables(THD *thd, TABLE_LIST it.rewind(); /* To get access to new elements in basis list */ while ((db_name= it++)) { - LEX_STRING orig_db_name; - - /* db_name can be changed in make_table_list() func */ - if (!thd->make_lex_string(&orig_db_name, db_name->str, - db_name->length, FALSE)) - goto err; #ifndef NO_EMBEDDED_ACCESS_CHECKS if (!(check_access(thd,SELECT_ACL, db_name->str, &thd->col_access, 0, 1, with_i_schema) || @@ -3466,64 +3559,14 @@ int get_all_tables(THD *thd, TABLE_LIST continue; } - int res; - LEX_STRING tmp_lex_string; - /* - Set the parent lex of 'sel' because it is needed by - sel.init_query() which is called inside make_table_list. - */ thd->no_warnings_for_error= 1; - sel.parent_lex= lex; - if (make_table_list(thd, &sel, db_name, table_name)) - goto err; - TABLE_LIST *show_table_list= sel.table_list.first; - lex->all_selects_list= &sel; - lex->derived_tables= 0; - lex->sql_command= SQLCOM_SHOW_FIELDS; - show_table_list->i_s_requested_object= - schema_table->i_s_requested_object; + DEBUG_SYNC(thd, "before_open_in_get_all_tables"); - res= open_normal_and_derived_tables(thd, show_table_list, - MYSQL_LOCK_IGNORE_FLUSH); - lex->sql_command= save_sql_command; - /* - XXX: show_table_list has a flag i_is_requested, - and when it's set, open_normal_and_derived_tables() - can return an error without setting an error message - in THD, which is a hack. This is why we have to - check for res, then for thd->is_error() only then - for thd->main_da.sql_errno(). - */ - if (res && thd->is_error() && - thd->main_da.sql_errno() == ER_NO_SUCH_TABLE) - { - /* - Hide error for not existing table. - This error can occur for example when we use - where condition with db name and table name and this - table does not exist. - */ - res= 0; - thd->clear_error(); - } - else - { - /* - We should use show_table_list->alias instead of - show_table_list->table_name because table_name - could be changed during opening of I_S tables. It's safe - to use alias because alias contains original table name - in this case. - */ - thd->make_lex_string(&tmp_lex_string, show_table_list->alias, - strlen(show_table_list->alias), FALSE); - res= schema_table->process_table(thd, show_table_list, table, - res, &orig_db_name, - &tmp_lex_string); - close_tables_for_reopen(thd, &show_table_list); - } - DBUG_ASSERT(!lex->query_tables_own_last); - if (res) + + if (fill_schema_table_by_open(thd, FALSE, + table, schema_table, + db_name, table_name, + &open_tables_state_backup)) goto err; } } @@ -3539,11 +3582,7 @@ int get_all_tables(THD *thd, TABLE_LIST error= 0; err: thd->restore_backup_open_tables_state(&open_tables_state_backup); - lex->restore_backup_query_tables_list(&query_tables_list_backup); - lex->derived_tables= derived_tables; - lex->all_selects_list= old_all_select_lex; - lex->context_analysis_only= save_context_analysis_only; - lex->sql_command= save_sql_command; + DBUG_RETURN(error); } -- Dmitry Lenev, Software Developer Oracle Development SPB/MySQL, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification