From: Mayank Prasad Date: April 19 2012 10:36am Subject: bzr push into mysql-trunk branch (mayank.prasad:3885 to 3886) Bug#12427262 List-Archive: http://lists.mysql.com/commits/143509 X-Bug: 12427262 Message-Id: <201204191036.q3JAaEYS023422@acsmt356.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3886 Mayank Prasad 2012-04-19 [merge] BUG#12427262 : 60961: SHOW TABLES VERY SLOW WHEN NOT IN SYSTEM DISK CACHE Details: - Merge : 5.5 -> trunk added: mysql-test/r/bug12427262.result mysql-test/t/bug12427262.test modified: sql/sql_show.cc 3885 Erlend Dahl 2012-04-19 Bump the milestone version number. modified: VERSION === added file 'mysql-test/r/bug12427262.result' --- a/mysql-test/r/bug12427262.result 1970-01-01 00:00:00 +0000 +++ b/mysql-test/r/bug12427262.result 2012-04-19 10:29:46 +0000 @@ -0,0 +1,53 @@ +# +# Bug#12427262 : 60961: SHOW TABLES VERY SLOW WHEN NOT IN SYSTEM DISK CACHE. +# +create database show_table_db; +use show_table_db; +create table t1 (c1 int); +create table t2 (c1 int); +create table t3 (c1 int); +create table t4 (c1 int); +create table t5 (c1 int); +create table t6 (c1 int); +create table t7 (c1 int); +create table t8 (c1 int); +create table t9 (c1 int); +create table t10 (c1 int); +select Sum(ALL(COUNT_READ)) from performance_schema.file_summary_by_instance where FILE_NAME +like "%show_table_db/%.frm%" AND EVENT_NAME='wait/io/file/sql/FRM' into @count_read_before; +show tables; +Tables_in_show_table_db +t1 +t10 +t2 +t3 +t4 +t5 +t6 +t7 +t8 +t9 +select Sum(ALL(COUNT_READ)) from performance_schema.file_summary_by_instance where FILE_NAME +like "%show_table_db/%.frm%" AND EVENT_NAME='wait/io/file/sql/FRM' into @count_read_after; +select @count_read_after-@count_read_before; +@count_read_after-@count_read_before +0.000000000000000000000000000000 +show full tables; +Tables_in_show_table_db Table_type +t1 BASE TABLE +t10 BASE TABLE +t2 BASE TABLE +t3 BASE TABLE +t4 BASE TABLE +t5 BASE TABLE +t6 BASE TABLE +t7 BASE TABLE +t8 BASE TABLE +t9 BASE TABLE +select Sum(ALL(COUNT_READ)) from performance_schema.file_summary_by_instance where FILE_NAME +like "%show_table_db/%.frm%" AND EVENT_NAME='wait/io/file/sql/FRM' into @count_read_after; +select @count_read_after-@count_read_before; +@count_read_after-@count_read_before +10.000000000000000000000000000000 +drop table t1; +drop database show_table_db; === added file 'mysql-test/t/bug12427262.test' --- a/mysql-test/t/bug12427262.test 1970-01-01 00:00:00 +0000 +++ b/mysql-test/t/bug12427262.test 2012-04-19 10:29:46 +0000 @@ -0,0 +1,48 @@ +--echo # +--echo # Bug#12427262 : 60961: SHOW TABLES VERY SLOW WHEN NOT IN SYSTEM DISK CACHE. +--echo # + +--source include/not_embedded.inc +--source include/have_perfschema.inc + +--disable_warnings +create database show_table_db; +use show_table_db; +create table t1 (c1 int); +create table t2 (c1 int); +create table t3 (c1 int); +create table t4 (c1 int); +create table t5 (c1 int); +create table t6 (c1 int); +create table t7 (c1 int); +create table t8 (c1 int); +create table t9 (c1 int); +create table t10 (c1 int); +--enable_warnings + +# Query PS to know initial read count for frm file. +select Sum(ALL(COUNT_READ)) from performance_schema.file_summary_by_instance where FILE_NAME +like "%show_table_db/%.frm%" AND EVENT_NAME='wait/io/file/sql/FRM' into @count_read_before; + +show tables; + +# Query PS to know read count for frm file after above query. It should +# not be changed as FRM file will not be opened for above query. +select Sum(ALL(COUNT_READ)) from performance_schema.file_summary_by_instance where FILE_NAME +like "%show_table_db/%.frm%" AND EVENT_NAME='wait/io/file/sql/FRM' into @count_read_after; + +select @count_read_after-@count_read_before; + +show full tables; + +# Query PS to know read count for frm file after above query. COUNT_READ +# will be incremented by 1 as FRM file will be opened for above query. +select Sum(ALL(COUNT_READ)) from performance_schema.file_summary_by_instance where FILE_NAME +like "%show_table_db/%.frm%" AND EVENT_NAME='wait/io/file/sql/FRM' into @count_read_after; + +select @count_read_after-@count_read_before; + +--disable_warnings +drop table t1; +drop database show_table_db; +--enable_warnings === modified file 'sql/sql_show.cc' --- a/sql/sql_show.cc 2012-04-18 17:52:08 +0000 +++ b/sql/sql_show.cc 2012-04-19 10:33:14 +0000 @@ -3491,39 +3491,44 @@ end: static int fill_schema_table_names(THD *thd, TABLE *table, LEX_STRING *db_name, LEX_STRING *table_name, - bool with_i_schema) + bool with_i_schema, + bool need_table_type) { - if (with_i_schema) + /* Avoid opening FRM files if table type is not needed. */ + if (need_table_type) { - table->field[3]->store(STRING_WITH_LEN("SYSTEM VIEW"), - system_charset_info); - } - else - { - enum legacy_db_type not_used; - char path[FN_REFLEN + 1]; - (void) build_table_filename(path, sizeof(path) - 1, db_name->str, - table_name->str, reg_ext, 0); - switch (dd_frm_type(thd, path, ¬_used)) { - case FRMTYPE_ERROR: - table->field[3]->store(STRING_WITH_LEN("ERROR"), - system_charset_info); - break; - case FRMTYPE_TABLE: - table->field[3]->store(STRING_WITH_LEN("BASE TABLE"), - system_charset_info); - break; - case FRMTYPE_VIEW: - table->field[3]->store(STRING_WITH_LEN("VIEW"), + if (with_i_schema) + { + table->field[3]->store(STRING_WITH_LEN("SYSTEM VIEW"), system_charset_info); - break; - default: - DBUG_ASSERT(0); } - if (thd->is_error() && thd->get_stmt_da()->sql_errno() == ER_NO_SUCH_TABLE) + else { - thd->clear_error(); - return 0; + enum legacy_db_type not_used; + char path[FN_REFLEN + 1]; + (void) build_table_filename(path, sizeof(path) - 1, db_name->str, + table_name->str, reg_ext, 0); + switch (dd_frm_type(thd, path, ¬_used)) { + case FRMTYPE_ERROR: + table->field[3]->store(STRING_WITH_LEN("ERROR"), + system_charset_info); + break; + case FRMTYPE_TABLE: + table->field[3]->store(STRING_WITH_LEN("BASE TABLE"), + system_charset_info); + break; + case FRMTYPE_VIEW: + table->field[3]->store(STRING_WITH_LEN("VIEW"), + system_charset_info); + break; + default: + DBUG_ASSERT(0); + } + if (thd->is_error() && thd->get_stmt_da()->sql_errno() == ER_NO_SUCH_TABLE) + { + thd->clear_error(); + return 0; + } } } if (schema_table_store_record(thd, table)) @@ -4062,7 +4067,8 @@ int get_all_tables(THD *thd, TABLE_LIST if (schema_table_idx == SCH_TABLE_NAMES) { if (fill_schema_table_names(thd, tables->table, db_name, - table_name, with_i_schema)) + table_name, with_i_schema, + lex->verbose)) continue; } else No bundle (reason: useless for push emails).