diff options
| -rw-r--r-- | mysql-test/r/stat_tables_innodb_debug.result | 25 | ||||
| -rw-r--r-- | mysql-test/r/stat_tables_myisam_debug.result | 24 | ||||
| -rw-r--r-- | mysql-test/t/stat_tables_innodb_debug.test | 36 | ||||
| -rw-r--r-- | mysql-test/t/stat_tables_myisam_debug.test | 33 | ||||
| -rw-r--r-- | sql/sql_admin.cc | 299 | ||||
| -rw-r--r-- | storage/innobase/handler/ha_innodb.cc | 1 | ||||
| -rw-r--r-- | storage/xtradb/handler/ha_innodb.cc | 2 |
7 files changed, 285 insertions, 135 deletions
diff --git a/mysql-test/r/stat_tables_innodb_debug.result b/mysql-test/r/stat_tables_innodb_debug.result new file mode 100644 index 00000000000..e0c4c78cd29 --- /dev/null +++ b/mysql-test/r/stat_tables_innodb_debug.result @@ -0,0 +1,25 @@ +# +# MDEV-7901: re-implement analyze table for low impact +# +create table t1 (a int, b int, c int, key IDX1(a), key IDX2(a,b)) engine=innodb; +insert into t1 select seq/10, seq/2, seq from seq_0_to_9; +SET DEBUG_SYNC='statistics_collection_start1 SIGNAL analyzing WAIT_FOR written'; +analyze table t1 persistent for all; +connect con1, localhost, root,,; +connection con1; +SET DEBUG_SYNC= 'now WAIT_FOR analyzing'; +select count(*) from t1; +count(*) +10 +insert into t1 values (333,333,333); +update t1 set a=1; +SET DEBUG_SYNC= 'now SIGNAL written'; +connection default; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +select count(*) from t1; +count(*) +11 +set debug_sync='RESET'; +drop table t1; diff --git a/mysql-test/r/stat_tables_myisam_debug.result b/mysql-test/r/stat_tables_myisam_debug.result new file mode 100644 index 00000000000..ae9a001bdfa --- /dev/null +++ b/mysql-test/r/stat_tables_myisam_debug.result @@ -0,0 +1,24 @@ +# +# MDEV-7901: re-implement analyze table for low impact +# +create table t1 (a int, b int, c int, key IDX1(a), key IDX2(a,b)) engine=myisam; +insert into t1 select seq/10, seq/2, seq from seq_0_to_9; +SET DEBUG_SYNC='statistics_collection_start1 SIGNAL analyzing WAIT_FOR written'; +analyze table t1 persistent for all; +connect con1, localhost, root,,; +connection con1; +SET DEBUG_SYNC= 'now WAIT_FOR analyzing'; +select count(*) from t1; +count(*) +10 +insert into t1 values (333,333,333); +SET DEBUG_SYNC= 'now SIGNAL written'; +connection default; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status Table is already up to date +select count(*) from t1; +count(*) +11 +set debug_sync='RESET'; +drop table t1; diff --git a/mysql-test/t/stat_tables_innodb_debug.test b/mysql-test/t/stat_tables_innodb_debug.test new file mode 100644 index 00000000000..f3652fc2312 --- /dev/null +++ b/mysql-test/t/stat_tables_innodb_debug.test @@ -0,0 +1,36 @@ +--source include/have_innodb.inc +--source include/have_sequence.inc +--source include/have_stat_tables.inc +--source include/have_debug_sync.inc +--source include/not_embedded.inc + +--echo # +--echo # MDEV-7901: re-implement analyze table for low impact +--echo # + +create table t1 (a int, b int, c int, key IDX1(a), key IDX2(a,b)) engine=innodb; +insert into t1 select seq/10, seq/2, seq from seq_0_to_9; + + +SET DEBUG_SYNC='statistics_collection_start1 SIGNAL analyzing WAIT_FOR written'; + +send analyze table t1 persistent for all; +connect (con1, localhost, root,,); +--connection con1 + +SET DEBUG_SYNC= 'now WAIT_FOR analyzing'; + +select count(*) from t1; +# insert will work even with MyISAM because it allow to append file +insert into t1 values (333,333,333); +# but update now hang for MyISAM bacause can't get lock (InnoDB/XtraDB works) +update t1 set a=1; + +SET DEBUG_SYNC= 'now SIGNAL written'; + +--connection default +--reap +select count(*) from t1; + +set debug_sync='RESET'; +drop table t1; diff --git a/mysql-test/t/stat_tables_myisam_debug.test b/mysql-test/t/stat_tables_myisam_debug.test new file mode 100644 index 00000000000..e8ef44325b9 --- /dev/null +++ b/mysql-test/t/stat_tables_myisam_debug.test @@ -0,0 +1,33 @@ +--source include/have_sequence.inc +--source include/have_stat_tables.inc +--source include/have_debug_sync.inc +--source include/not_embedded.inc + +--echo # +--echo # MDEV-7901: re-implement analyze table for low impact +--echo # + +create table t1 (a int, b int, c int, key IDX1(a), key IDX2(a,b)) engine=myisam; +insert into t1 select seq/10, seq/2, seq from seq_0_to_9; + + +SET DEBUG_SYNC='statistics_collection_start1 SIGNAL analyzing WAIT_FOR written'; + +send analyze table t1 persistent for all; +connect (con1, localhost, root,,); +--connection con1 + +SET DEBUG_SYNC= 'now WAIT_FOR analyzing'; + +select count(*) from t1; +# insert will work even with MyISAM because it allow to append file +insert into t1 values (333,333,333); + +SET DEBUG_SYNC= 'now SIGNAL written'; + +--connection default +--reap +select count(*) from t1; + +set debug_sync='RESET'; +drop table t1; diff --git a/sql/sql_admin.cc b/sql/sql_admin.cc index 578b78d8f35..ed23d9cccd6 100644 --- a/sql/sql_admin.cc +++ b/sql/sql_admin.cc @@ -296,6 +296,97 @@ static inline bool table_not_corrupt_error(uint sql_errno) } +static bool open_only_one_table(THD* thd, TABLE_LIST* table, + bool repair_table_use_frm, + bool is_view_operator_func) +{ + LEX *lex= thd->lex; + SELECT_LEX *select= &lex->select_lex; + TABLE_LIST *save_next_global, *save_next_local; + bool open_error; + save_next_global= table->next_global; + table->next_global= 0; + save_next_local= table->next_local; + table->next_local= 0; + select->table_list.first= table; + /* + Time zone tables and SP tables can be add to lex->query_tables list, + so it have to be prepared. + TODO: Investigate if we can put extra tables into argument instead of + using lex->query_tables + */ + lex->query_tables= table; + lex->query_tables_last= &table->next_global; + lex->query_tables_own_last= 0; + + /* + CHECK TABLE command is allowed for views as well. Check on alter flags + to differentiate from ALTER TABLE...CHECK PARTITION on which view is not + allowed. + */ + if (lex->alter_info.flags & Alter_info::ALTER_ADMIN_PARTITION || + !is_view_operator_func) + { + table->required_type=FRMTYPE_TABLE; + DBUG_ASSERT(!lex->only_view); + } + else if (lex->only_view) + { + table->required_type= FRMTYPE_VIEW; + } + else if (!lex->only_view && lex->sql_command == SQLCOM_REPAIR) + { + table->required_type= FRMTYPE_TABLE; + } + + if (lex->sql_command == SQLCOM_CHECK || + lex->sql_command == SQLCOM_REPAIR || + lex->sql_command == SQLCOM_ANALYZE || + lex->sql_command == SQLCOM_OPTIMIZE) + thd->prepare_derived_at_open= TRUE; + if (!thd->locked_tables_mode && repair_table_use_frm) + { + /* + If we're not under LOCK TABLES and we're executing REPAIR TABLE + USE_FRM, we need to ignore errors from open_and_lock_tables(). + REPAIR TABLE USE_FRM is a heavy weapon used when a table is + critically damaged, so open_and_lock_tables() will most likely + report errors. Those errors are not interesting for the user + because it's already known that the table is badly damaged. + */ + + Diagnostics_area *da= thd->get_stmt_da(); + Warning_info tmp_wi(thd->query_id, false, true); + + da->push_warning_info(&tmp_wi); + + open_error= (thd->open_temporary_tables(table) || + open_and_lock_tables(thd, table, TRUE, 0)); + + da->pop_warning_info(); + } + else + { + /* + It's assumed that even if it is REPAIR TABLE USE_FRM, the table + can be opened if we're under LOCK TABLES (otherwise LOCK TABLES + would fail). Thus, the only errors we could have from + open_and_lock_tables() are logical ones, like incorrect locking + mode. It does make sense for the user to see such errors. + */ + + open_error= (thd->open_temporary_tables(table) || + open_and_lock_tables(thd, table, TRUE, 0)); + } + thd->prepare_derived_at_open= FALSE; + + table->next_global= save_next_global; + table->next_local= save_next_local; + + return open_error; +} + + /* RETURN VALUES FALSE Message sent to net (admin operation went ok) @@ -317,7 +408,6 @@ static bool mysql_admin_table(THD* thd, TABLE_LIST* tables, HA_CHECK_OPT *)) { TABLE_LIST *table; - SELECT_LEX *select= &thd->lex->select_lex; List<Item> field_list; Item *item; Protocol *protocol= thd->protocol; @@ -392,85 +482,9 @@ static bool mysql_admin_table(THD* thd, TABLE_LIST* tables, /* open only one table from local list of command */ while (1) { - TABLE_LIST *save_next_global, *save_next_local; - save_next_global= table->next_global; - table->next_global= 0; - save_next_local= table->next_local; - table->next_local= 0; - select->table_list.first= table; - /* - Time zone tables and SP tables can be add to lex->query_tables list, - so it have to be prepared. - TODO: Investigate if we can put extra tables into argument instead of - using lex->query_tables - */ - lex->query_tables= table; - lex->query_tables_last= &table->next_global; - lex->query_tables_own_last= 0; - - /* - CHECK TABLE command is allowed for views as well. Check on alter flags - to differentiate from ALTER TABLE...CHECK PARTITION on which view is not - allowed. - */ - if (lex->alter_info.flags & Alter_info::ALTER_ADMIN_PARTITION || - view_operator_func == NULL) - { - table->required_type=FRMTYPE_TABLE; - DBUG_ASSERT(!lex->only_view); - } - else if (lex->only_view) - { - table->required_type= FRMTYPE_VIEW; - } - else if (!lex->only_view && lex->sql_command == SQLCOM_REPAIR) - { - table->required_type= FRMTYPE_TABLE; - } - - if (lex->sql_command == SQLCOM_CHECK || - lex->sql_command == SQLCOM_REPAIR || - lex->sql_command == SQLCOM_ANALYZE || - lex->sql_command == SQLCOM_OPTIMIZE) - thd->prepare_derived_at_open= TRUE; - if (!thd->locked_tables_mode && repair_table_use_frm) - { - /* - If we're not under LOCK TABLES and we're executing REPAIR TABLE - USE_FRM, we need to ignore errors from open_and_lock_tables(). - REPAIR TABLE USE_FRM is a heavy weapon used when a table is - critically damaged, so open_and_lock_tables() will most likely - report errors. Those errors are not interesting for the user - because it's already known that the table is badly damaged. - */ - - Diagnostics_area *da= thd->get_stmt_da(); - Warning_info tmp_wi(thd->query_id, false, true); - - da->push_warning_info(&tmp_wi); - - open_error= (thd->open_temporary_tables(table) || - open_and_lock_tables(thd, table, TRUE, 0)); - - da->pop_warning_info(); - } - else - { - /* - It's assumed that even if it is REPAIR TABLE USE_FRM, the table - can be opened if we're under LOCK TABLES (otherwise LOCK TABLES - would fail). Thus, the only errors we could have from - open_and_lock_tables() are logical ones, like incorrect locking - mode. It does make sense for the user to see such errors. - */ - - open_error= (thd->open_temporary_tables(table) || - open_and_lock_tables(thd, table, TRUE, 0)); - } - thd->prepare_derived_at_open= FALSE; - - table->next_global= save_next_global; - table->next_local= save_next_local; + open_error= open_only_one_table(thd, table, + repair_table_use_frm, + (view_operator_func != NULL)); thd->open_options&= ~extra_open_options; /* @@ -711,7 +725,6 @@ static bool mysql_admin_table(THD* thd, TABLE_LIST* tables, if (operator_func == &handler::ha_analyze) { TABLE *tab= table->table; - Field **field_ptr= tab->field; if (lex->with_persistent_for_clause && tab->s->table_category != TABLE_CATEGORY_USER) @@ -723,58 +736,6 @@ static bool mysql_admin_table(THD* thd, TABLE_LIST* tables, (get_use_stat_tables_mode(thd) > NEVER || lex->with_persistent_for_clause)); - if (collect_eis) - { - if (!lex->column_list) - { - bitmap_clear_all(tab->read_set); - for (uint fields= 0; *field_ptr; field_ptr++, fields++) - { - enum enum_field_types type= (*field_ptr)->type(); - if (type < MYSQL_TYPE_MEDIUM_BLOB || - type > MYSQL_TYPE_BLOB) - bitmap_set_bit(tab->read_set, fields); - else if (collect_eis) - push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, - ER_NO_EIS_FOR_FIELD, - ER_THD(thd, ER_NO_EIS_FOR_FIELD), - (*field_ptr)->field_name); - } - } - else - { - int pos; - LEX_STRING *column_name; - List_iterator_fast<LEX_STRING> it(*lex->column_list); - - bitmap_clear_all(tab->read_set); - while ((column_name= it++)) - { - if (tab->s->fieldnames.type_names == 0 || - (pos= find_type(&tab->s->fieldnames, column_name->str, - column_name->length, 1)) <= 0) - { - compl_result_code= result_code= HA_ADMIN_INVALID; - break; - } - pos--; - enum enum_field_types type= tab->field[pos]->type(); - if (type < MYSQL_TYPE_MEDIUM_BLOB || - type > MYSQL_TYPE_BLOB) - bitmap_set_bit(tab->read_set, pos); - else if (collect_eis) - push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, - ER_NO_EIS_FOR_FIELD, - ER_THD(thd, ER_NO_EIS_FOR_FIELD), - column_name->str); - } - tab->file->column_bitmaps_signal(); - } - } - else - { - DBUG_ASSERT(!lex->column_list); - } if (!lex->index_list) { @@ -812,7 +773,77 @@ static bool mysql_admin_table(THD* thd, TABLE_LIST* tables, if (compl_result_code == HA_ADMIN_OK && collect_eis) { - if (!(compl_result_code= + /* + Here we close and reopen table in read mode because operation of + collecting statistics is long and it will be better do not block + the table completely. + InnoDB/XtraDB will allow read/write and MyISAM read/insert. + */ + trans_commit_stmt(thd); + trans_commit(thd); + thd->open_options|= extra_open_options; + close_thread_tables(thd); + table->table= NULL; + thd->mdl_context.release_transactional_locks(); + table->mdl_request.init(MDL_key::TABLE, table->db, table->table_name, + MDL_SHARED_NO_READ_WRITE, MDL_TRANSACTION); + table->mdl_request.set_type(MDL_SHARED_READ); + + table->lock_type= TL_READ; + DBUG_ASSERT(view_operator_func == NULL); + open_error= open_only_one_table(thd, table, + repair_table_use_frm, FALSE); + thd->open_options&= ~extra_open_options; + + TABLE *tab= table->table; + Field **field_ptr= tab->field; + if (!lex->column_list) + { + bitmap_clear_all(tab->read_set); + for (uint fields= 0; *field_ptr; field_ptr++, fields++) + { + enum enum_field_types type= (*field_ptr)->type(); + if (type < MYSQL_TYPE_MEDIUM_BLOB || + type > MYSQL_TYPE_BLOB) + bitmap_set_bit(tab->read_set, fields); + else + push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, + ER_NO_EIS_FOR_FIELD, + ER_THD(thd, ER_NO_EIS_FOR_FIELD), + (*field_ptr)->field_name); + } + } + else + { + int pos; + LEX_STRING *column_name; + List_iterator_fast<LEX_STRING> it(*lex->column_list); + + bitmap_clear_all(tab->read_set); + while ((column_name= it++)) + { + if (tab->s->fieldnames.type_names == 0 || + (pos= find_type(&tab->s->fieldnames, column_name->str, + column_name->length, 1)) <= 0) + { + compl_result_code= result_code= HA_ADMIN_INVALID; + break; + } + pos--; + enum enum_field_types type= tab->field[pos]->type(); + if (type < MYSQL_TYPE_MEDIUM_BLOB || + type > MYSQL_TYPE_BLOB) + bitmap_set_bit(tab->read_set, pos); + else + push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, + ER_NO_EIS_FOR_FIELD, + ER_THD(thd, ER_NO_EIS_FOR_FIELD), + column_name->str); + } + tab->file->column_bitmaps_signal(); + } + if (!open_error && + !(compl_result_code= alloc_statistics_for_table(thd, table->table)) && !(compl_result_code= collect_statistics_for_table(thd, table->table))) diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index 91284c2c2fc..604adfadae3 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -15502,6 +15502,7 @@ ha_innobase::store_lock( /* Use consistent read for checksum table */ if (sql_command == SQLCOM_CHECKSUM + || (sql_command == SQLCOM_ANALYZE && lock_type == TL_READ) || ((srv_locks_unsafe_for_binlog || trx->isolation_level <= TRX_ISO_READ_COMMITTED) && trx->isolation_level != TRX_ISO_SERIALIZABLE diff --git a/storage/xtradb/handler/ha_innodb.cc b/storage/xtradb/handler/ha_innodb.cc index 2c71488c18b..292d498ebfb 100644 --- a/storage/xtradb/handler/ha_innodb.cc +++ b/storage/xtradb/handler/ha_innodb.cc @@ -16247,7 +16247,7 @@ ha_innobase::store_lock( /* Use consistent read for checksum table */ if (sql_command == SQLCOM_CHECKSUM - || sql_command == SQLCOM_CHECKSUM + || (sql_command == SQLCOM_ANALYZE && lock_type == TL_READ) || ((srv_locks_unsafe_for_binlog || trx->isolation_level <= TRX_ISO_READ_COMMITTED) && trx->isolation_level != TRX_ISO_SERIALIZABLE |