From: Mattias Jonsson Date: January 5 2012 10:02pm Subject: bzr push into mysql-trunk branch (mattias.jonsson:3704 to 3705) WL#4443 List-Archive: http://lists.mysql.com/commits/142310 Message-Id: <201201052203.q05M2xPj028929@acsmt358.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3705 Mattias Jonsson 2012-01-05 WL#4443 Improved pruning of inserts, now only copying the fields belonging to the partitioning expressions when checking for used partiitons. Using bitmap instead of looping through all fields when checking if given fields are used in the partitioning expressions. modified: mysql-test/t/partition_locking.test sql/partition_info.cc sql/sql_base.cc sql/sql_base.h sql/sql_executor.cc sql/sql_insert.cc sql/sql_union.cc sql/sql_update.cc 3704 Mattias Jonsson 2012-01-05 WL#4443 Updated test results after removing locking in create view. modified: mysql-test/include/commit.inc mysql-test/r/commit_1innodb.result mysql-test/suite/binlog/r/binlog_unsafe.result sql/sql_view.cc === modified file 'mysql-test/t/partition_locking.test' --- a/mysql-test/t/partition_locking.test revid:mattias.jonsson@stripped +++ b/mysql-test/t/partition_locking.test revid:mattias.jonsson@stripped @@ -1210,52 +1210,7 @@ DROP TABLE t1; --echo # which makes prune locks impossible. -#from the WL: -# TODO: test if it makes sense to have an upper limit of inserted rows before just -# use/lock all partitions, since the overhead of iterate over all rows to be -# inserted can be higher than the overhead of locking all partitions. -# NOTE: If a AUTO_INCREMENT or TIMESTAMP will be generated and it is a part of the -# partitioning fields then all partitions must be locked (Not yet part of this -# worklog: Optimization may be possible for range partitioning). -# -# INSERT ... VALUES ... ON DUPLICATE KEY will only be pruned for use/lock if no -# partition field is given for update (for optimization, see single-table UPDATE) -# -# INSERT SELECT, for the SELECT clause see SELECT above, for the INSERT table all -# partitions needs to be locked. (One could optimize this if the SELECT will -# return a known/constant set of values for the partitioning key, but that is not -# a part of this worklog). -# -# LOAD will not be pruned, since it is mainly used for inserting a large batch of -# rows. It would need the input file to be read and parsed and each row to -# evaluate its partitioning function to determine which partitions to use prior -# locking. In case of large batches the gain (pruning of locks) seems smaller than -# the cost (reading, parsing and calculate the partition id). (One could optimize -# this if all partitioning fields are static, and only non partitioning fields are -# read from the file. TODO: check if PARTITIONS (p1,...) is supported for LOAD? Not yet part of this worklog. Note: special care with -# REPLACE, see INSERT). -# - -# TODO: ... -# # NOTEs: -# * INSERT DELAYED is not supported by partitioned tables (so it will not need to -# be enhanced). -# * LOCK TABLES cannot prune partition locks. -# * CREATE VIEW will not support partition lock pruning. Too small gain for -# considering to implement it (and is DDL too). # # To investigate: # * effects on replication (statement and row) -# * effects on stored procedures (open_and_lock_tables() are called from sp_head.cc). -# * how to support lock pruning for EXCHANGE PARTITION - is really a DDL, but -# might gain on partition lock pruning. Also true for other ALTER TABLE t ... -# PARTITION, like TRUNCATE etc. -# * should CALL be supported for partitioning lock pruning? -# * should DO be supported for partitioning lock pruning? -# * should SET be supported for partitioning lock pruning? NO - it is a DB Admin -# statement. - - - - === modified file 'sql/partition_info.cc' --- a/sql/partition_info.cc revid:mattias.jonsson@stripped +++ b/sql/partition_info.cc revid:mattias.jonsson@stripped @@ -1880,26 +1880,20 @@ bool partition_info::is_field_in_part_ex List_iterator it(fields); Item *item; Item_field *field; - Field **part_field; DBUG_ASSERT(fields.elements); DBUG_ENTER("is_fields_in_part_expr"); while ((item= it++)) { if (!(field= item->filed_for_view_update())) { - DBUG_ASSERT(0); // Should already be checked? + DBUG_ASSERT(0); // Should already be checked DBUG_RETURN(true); } else { - DBUG_ASSERT(field->field->table == table); // TODO: Check if possible to insert into a view? - for (part_field= table->part_info->full_part_field_array; - *part_field; - part_field++) - { - if (field->field == *part_field) - DBUG_RETURN(true); - } + DBUG_ASSERT(field->field->table == table); + if (!bitmap_is_set(&full_part_field_set, field->field->field_index)) + DBUG_RETURN(true); } } DBUG_RETURN(false); === modified file 'sql/sql_base.cc' --- a/sql/sql_base.cc revid:mattias.jonsson@stripped +++ b/sql/sql_base.cc revid:mattias.jonsson@stripped @@ -8896,26 +8896,24 @@ err_no_arena: /* Fill fields with given items. - SYNOPSIS - fill_record() - thd thread handler - fields Item_fields list to be filled - values values to fill with - ignore_errors TRUE if we should ignore errors - - NOTE - fill_record() may set table->auto_increment_field_not_null and a - caller should make sure that it is reset after their last call to this - function. - - RETURN - FALSE OK - TRUE error occured + @param thd thread handler + @param fields Item_fields list to be filled + @param values values to fill with + @param ignore_errors TRUE if we should ignore errors + @param bitmap Bitmap over fields to fill + + @note fill_record() may set table->auto_increment_field_not_null and a + caller should make sure that it is reset after their last call to this + function. + + @return Operation status + @retval false OK + @retval true Error occured */ bool fill_record(THD * thd, List &fields, List &values, - bool ignore_errors) + bool ignore_errors, MY_BITMAP *bitmap) { List_iterator_fast f(fields),v(values); Item *value, *fld; @@ -8952,6 +8950,9 @@ fill_record(THD * thd, List &field } value=v++; Field *rfield= field->field; + /* If bitmap over wanted fields are set, skip non marked fields. */ + if (bitmap && !bitmap_is_set(bitmap, rfield->field_index)) + continue; table= rfield->table; if (rfield == table->next_number_field) table->auto_increment_field_not_null= TRUE; @@ -8998,34 +8999,33 @@ fill_record_n_invoke_before_triggers(THD Table_triggers_list *triggers, enum trg_event_type event) { - return (fill_record(thd, fields, values, ignore_errors) || + return (fill_record(thd, fields, values, ignore_errors, NULL) || (triggers && triggers->process_triggers(thd, event, TRG_ACTION_BEFORE, TRUE))); } -/* - Fill field buffer with values from Field list - - SYNOPSIS - fill_record() - thd thread handler - ptr pointer on pointer to record - values list of fields - ignore_errors TRUE if we should ignore errors - - NOTE - fill_record() may set table->auto_increment_field_not_null and a - caller should make sure that it is reset after their last call to this - function. +/** + Fill field buffer with values from Field list. - RETURN - FALSE OK - TRUE error occured + @param thd thread handler + @param ptr pointer on pointer to record + @param values list of fields + @param ignore_errors TRUE if we should ignore errors + @param bitmap Bitmap over fields to fill + + @note fill_record() may set table->auto_increment_field_not_null and a + caller should make sure that it is reset after their last call to this + function. + + @return Operation status + @retval false OK + @retval true Error occured */ bool -fill_record(THD *thd, Field **ptr, List &values, bool ignore_errors) +fill_record(THD *thd, Field **ptr, List &values, bool ignore_errors, + MY_BITMAP *bitmap) { List_iterator_fast v(values); Item *value; @@ -9050,6 +9050,9 @@ fill_record(THD *thd, Field **ptr, List< { value=v++; table= field->table; + /* If bitmap over wanted fields are set, skip non marked fields. */ + if (bitmap && !bitmap_is_set(bitmap, field->field_index)) + continue; if (field == table->next_number_field) table->auto_increment_field_not_null= TRUE; if (value->save_in_field(field, 0) < 0) @@ -9093,7 +9096,7 @@ fill_record_n_invoke_before_triggers(THD Table_triggers_list *triggers, enum trg_event_type event) { - return (fill_record(thd, ptr, values, ignore_errors) || + return (fill_record(thd, ptr, values, ignore_errors, NULL) || (triggers && triggers->process_triggers(thd, event, TRG_ACTION_BEFORE, TRUE))); } === modified file 'sql/sql_base.h' --- a/sql/sql_base.h revid:mattias.jonsson@stripped +++ b/sql/sql_base.h revid:mattias.jonsson@stripped @@ -188,9 +188,9 @@ bool setup_fields(THD *thd, Ref_ptr_arra List &item, enum_mark_columns mark_used_columns, List *sum_func_list, bool allow_sum_func); bool fill_record(THD * thd, List &fields, List &values, - bool ignore_errors); + bool ignore_errors, MY_BITMAP *bitmap); bool fill_record(THD *thd, Field **field, List &values, - bool ignore_errors); + bool ignore_errors, MY_BITMAP *bitmap); Field * find_field_in_tables(THD *thd, Item_ident *item, === modified file 'sql/sql_executor.cc' --- a/sql/sql_executor.cc revid:mattias.jonsson@stripped +++ b/sql/sql_executor.cc revid:mattias.jonsson@stripped @@ -1269,7 +1269,7 @@ end_sj_materialize(JOIN *join, JOIN_TAB if (item->is_null()) DBUG_RETURN(NESTED_LOOP_OK); } - fill_record(thd, table->field, sjm->table_cols, 1); + fill_record(thd, table->field, sjm->table_cols, 1, NULL); if (thd->is_error()) DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */ if ((error= table->file->ha_write_row(table->record[0]))) === modified file 'sql/sql_insert.cc' --- a/sql/sql_insert.cc revid:mattias.jonsson@stripped +++ b/sql/sql_insert.cc revid:mattias.jonsson@stripped @@ -713,12 +713,14 @@ static bool set_partition(THD *thd, TABL if (fields.elements || !values.elements) { DBUG_ASSERT(thd->lex->used_tables); - if (fill_record(thd, fields, values, false)) + if (fill_record(thd, fields, values, false, + &table->part_info->full_part_field_set)) DBUG_RETURN(true); } else { - if (fill_record(thd, table->field, values, false)) + if (fill_record(thd, table->field, values, false, + &table->part_info->full_part_field_set)) DBUG_RETURN(true); } if (table->part_info->get_partition_id(table->part_info, &part_id, === modified file 'sql/sql_union.cc' --- a/sql/sql_union.cc revid:mattias.jonsson@stripped +++ b/sql/sql_union.cc revid:mattias.jonsson@stripped @@ -87,7 +87,7 @@ bool select_union::send_data(List unit->offset_limit_cnt--; return 0; } - fill_record(thd, table->field, values, 1); + fill_record(thd, table->field, values, 1, NULL); if (thd->is_error()) return 1; === modified file 'sql/sql_update.cc' --- a/sql/sql_update.cc revid:mattias.jonsson@stripped +++ b/sql/sql_update.cc revid:mattias.jonsson@stripped @@ -2059,7 +2059,7 @@ bool multi_update::send_data(List /* Store regular updated fields in the row. */ fill_record(thd, tmp_table->field + 1 + unupdated_check_opt_tables.elements, - *values_for_table[offset], 1); + *values_for_table[offset], 1, NULL); /* Write row, ignoring duplicated updates to a row */ error= tmp_table->file->ha_write_row(tmp_table->record[0]); No bundle (reason: useless for push emails).