MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Øystein Grøvlen Date:September 25 2009 1:36pm
Subject:Re: bzr commit into mysql-5.4 branch (epotemkin:2864) Bug#34384
View as plain text  
Evgeny,

Can you explain how this patch is related to the problems described in 
Bug#34384?  After applying your patch, I cannot see that the performance 
issues discussed nor the bug discussed by Sergei has been fixed.

--
Øystein


Evgeny Potemkin wrote:
> #At file:///work/bzrroot/34384-bug-next/ based on
> revid:epotemkin@stripped
> 
>  2864 Evgeny Potemkin	2009-09-19
>       Bug#34384: Slow down on constant conversion.
>       
>       When values of different types are compared they're converted to a type that
>       allows correct comparison. This conversion is done for each comparison and
>       takes some time. When a constant is being compared it's possible to cache the
>       value after conversion to speedup comparison.
>       
>       A test case isn't provided because all changes are internal and isn't visible
>       outside.
>       
>       A flag named uncacheable is added to the Item class. It denies caching of the
>       item.
>       A flag named late_caching is added to the Item_cache class. It's set to TRUE
>       when we need to postpone caching till the first use of cached value.
>       A function named check_late_caching is added to the Item_cache flag. Its
>       purpose is to check state of the late_caching flag and cache value
> accordingly.
>       Item_cache_xxx::val_xxx functions are changed to call it prior to return
>       cached value.
>       The Arg_comparator::set_cmp_func function is changed to cache a value being
>       compared if it's a constant and needs type conversion.
>       Now the Item_func_like::fix_fields function denies caching arguments of the
> LIKE
>       function.
>       The Item_cache::get_cache function is overloaded to allow setting of the
>       late_caching flag and cache type.
>      @ mysql-test/r/range.result
>         Adjusted a test case after fix for the bug#34384.
>      @ sql/item.cc
>         Bug#34384: Slow down on constant conversion.
>         A flag named uncacheable is added to the Item class. It denies caching of
> the
>         item.
>         The Item_cache::get_cache function is overloaded to allow setting of the
>         late_caching flag and cache type.
>         Item_cache_xxx::val_xxx functions are changed to call it prior to return
>         cached value.
>      @ sql/item.h
>         Bug#34384: Slow down on constant conversion.
>         A function named check_late_caching is added to the Item_cache flag. Its
>         purpose is to check state of the late_caching flag and cache value
> accordingly.
>         The Item_cache::get_cache function is overloaded to allow setting of the
>         late_caching flag and cache type.
>      @ sql/item_cmpfunc.cc
>         Bug#34384: Slow down on constant conversion.
>         The Arg_comparator::set_cmp_func function is changed to cache a value being
>         compared if it's a constant and needs type conversion.
>      @ sql/item_cmpfunc.h
>         Bug#34384: Slow down on constant conversion.
>      @ sql/item_xmlfunc.cc
>         Bug#34384: Slow down on constant conversion.
> 
>     modified:
>       mysql-test/r/range.result
>       sql/item.cc
>       sql/item.h
>       sql/item_cmpfunc.cc
>       sql/item_cmpfunc.h
>       sql/item_xmlfunc.cc
> === modified file 'mysql-test/r/range.result'
> --- a/mysql-test/r/range.result	2008-06-10 22:27:52 +0000
> +++ b/mysql-test/r/range.result	2009-09-19 17:55:58 +0000
> @@ -1230,8 +1230,6 @@ c1
>  Warnings:
>  Warning	1366	Incorrect decimal value: 'A' for column 'c1' at row 1
>  Warning	1292	Truncated incorrect DOUBLE value: 'A'
> -Warning	1292	Truncated incorrect DOUBLE value: 'A'
> -Warning	1292	Truncated incorrect DOUBLE value: 'A'
>  DROP TABLE t1;
>  create table t1 (a int,b int,key (b),key (a),key (b,a));
>  insert into t1(a,b) values (1,2),(3,4),(5,6),(7,8);
> 
> === modified file 'sql/item.cc'
> --- a/sql/item.cc	2009-07-23 12:21:41 +0000
> +++ b/sql/item.cc	2009-09-19 17:55:58 +0000
> @@ -376,7 +376,8 @@ int Item::save_str_value_in_field(Field 
>  Item::Item():
>    is_expensive_cache(-1), rsize(0), name(0), orig_name(0), name_length(0),
>    fixed(0), is_autogenerated_name(TRUE),
> -  collation(&my_charset_bin, DERIVATION_COERCIBLE)
> +  collation(&my_charset_bin, DERIVATION_COERCIBLE),
> +  uncacheable(0)
>  {
>    marker= 0;
>    maybe_null=null_value=with_sum_func=unsigned_flag=0;
> @@ -7136,17 +7137,22 @@ bool field_is_equal_to_item(Field *field
>  
>  Item_cache* Item_cache::get_cache(const Item *item)
>  {
> -  switch (item->result_type()) {
> +  return get_cache(item->result_type(), item, FALSE);
> +}
> +
> +Item_cache* Item_cache::get_cache(const Item_result type, const Item *item, bool
> late_caching)
> +{
> +  switch (type) {
>    case INT_RESULT:
> -    return new Item_cache_int();
> +    return new Item_cache_int(late_caching);
>    case REAL_RESULT:
> -    return new Item_cache_real();
> +    return new Item_cache_real(late_caching);
>    case DECIMAL_RESULT:
> -    return new Item_cache_decimal();
> +    return new Item_cache_decimal(late_caching);
>    case STRING_RESULT:
> -    return new Item_cache_str(item);
> +    return new Item_cache_str(item,late_caching);
>    case ROW_RESULT:
> -    return new Item_cache_row();
> +    return new Item_cache_row(late_caching);
>    default:
>      // should never be in real life
>      DBUG_ASSERT(0);
> @@ -7168,6 +7174,8 @@ void Item_cache::print(String *str, enum
>  
>  void Item_cache_int::store(Item *item)
>  {
> +  if (check_late_caching(item))
> +    return;
>    value= item->val_int_result();
>    null_value= item->null_value;
>    unsigned_flag= item->unsigned_flag;
> @@ -7176,6 +7184,8 @@ void Item_cache_int::store(Item *item)
>  
>  void Item_cache_int::store(Item *item, longlong val_arg)
>  {
> +  if (check_late_caching(item))
> +    return;
>    value= val_arg;
>    null_value= item->null_value;
>    unsigned_flag= item->unsigned_flag;
> @@ -7185,6 +7195,7 @@ void Item_cache_int::store(Item *item, l
>  String *Item_cache_int::val_str(String *str)
>  {
>    DBUG_ASSERT(fixed == 1);
> +  check_late_caching(NULL);
>    str->set(value, default_charset());
>    return str;
>  }
> @@ -7193,6 +7204,7 @@ String *Item_cache_int::val_str(String *
>  my_decimal *Item_cache_int::val_decimal(my_decimal *decimal_val)
>  {
>    DBUG_ASSERT(fixed == 1);
> +  check_late_caching(NULL);
>    int2my_decimal(E_DEC_FATAL_ERROR, value, unsigned_flag, decimal_val);
>    return decimal_val;
>  }
> @@ -7200,6 +7212,8 @@ my_decimal *Item_cache_int::val_decimal(
>  
>  void Item_cache_real::store(Item *item)
>  {
> +  if (check_late_caching(item))
> +    return;
>    value= item->val_result();
>    null_value= item->null_value;
>  }
> @@ -7208,6 +7222,7 @@ void Item_cache_real::store(Item *item)
>  longlong Item_cache_real::val_int()
>  {
>    DBUG_ASSERT(fixed == 1);
> +  check_late_caching(NULL);
>    return (longlong) rint(value);
>  }
>  
> @@ -7215,6 +7230,7 @@ longlong Item_cache_real::val_int()
>  String* Item_cache_real::val_str(String *str)
>  {
>    DBUG_ASSERT(fixed == 1);
> +  check_late_caching(NULL);
>    str->set_real(value, decimals, default_charset());
>    return str;
>  }
> @@ -7223,6 +7239,7 @@ String* Item_cache_real::val_str(String 
>  my_decimal *Item_cache_real::val_decimal(my_decimal *decimal_val)
>  {
>    DBUG_ASSERT(fixed == 1);
> +  check_late_caching(NULL);
>    double2my_decimal(E_DEC_FATAL_ERROR, value, decimal_val);
>    return decimal_val;
>  }
> @@ -7230,6 +7247,8 @@ my_decimal *Item_cache_real::val_decimal
>  
>  void Item_cache_decimal::store(Item *item)
>  {
> +  if (check_late_caching(item))
> +    return;
>    my_decimal *val= item->val_decimal_result(&decimal_value);
>    if (!(null_value= item->null_value) && val != &decimal_value)
>      my_decimal2decimal(val, &decimal_value);
> @@ -7239,6 +7258,7 @@ double Item_cache_decimal::val_real()
>  {
>    DBUG_ASSERT(fixed);
>    double res;
> +  check_late_caching(NULL);
>    my_decimal2double(E_DEC_FATAL_ERROR, &decimal_value, &res);
>    return res;
>  }
> @@ -7247,6 +7267,7 @@ longlong Item_cache_decimal::val_int()
>  {
>    DBUG_ASSERT(fixed);
>    longlong res;
> +  check_late_caching(NULL);
>    my_decimal2int(E_DEC_FATAL_ERROR, &decimal_value, unsigned_flag, &res);
>    return res;
>  }
> @@ -7254,6 +7275,7 @@ longlong Item_cache_decimal::val_int()
>  String* Item_cache_decimal::val_str(String *str)
>  {
>    DBUG_ASSERT(fixed);
> +  check_late_caching(NULL);
>    my_decimal_round(E_DEC_FATAL_ERROR, &decimal_value, decimals, FALSE,
>                     &decimal_value);
>    my_decimal2string(E_DEC_FATAL_ERROR, &decimal_value, 0, 0, 0, str);
> @@ -7263,12 +7285,15 @@ String* Item_cache_decimal::val_str(Stri
>  my_decimal *Item_cache_decimal::val_decimal(my_decimal *val)
>  {
>    DBUG_ASSERT(fixed);
> +  check_late_caching(NULL);
>    return &decimal_value;
>  }
>  
>  
>  void Item_cache_str::store(Item *item)
>  {
> +  if (check_late_caching(item))
> +    return;
>    value_buff.set(buffer, sizeof(buffer), item->collation.collation);
>    value= item->str_result(&value_buff);
>    if ((null_value= item->null_value))
> @@ -7293,6 +7318,7 @@ double Item_cache_str::val_real()
>    DBUG_ASSERT(fixed == 1);
>    int err_not_used;
>    char *end_not_used;
> +  check_late_caching(NULL);
>    if (value)
>      return my_strntod(value->charset(), (char*) value->ptr(),
>  		      value->length(), &end_not_used, &err_not_used);
> @@ -7304,6 +7330,7 @@ longlong Item_cache_str::val_int()
>  {
>    DBUG_ASSERT(fixed == 1);
>    int err;
> +  check_late_caching(NULL);
>    if (value)
>      return my_strntoll(value->charset(), value->ptr(),
>  		       value->length(), 10, (char**) 0, &err);
> @@ -7314,6 +7341,7 @@ longlong Item_cache_str::val_int()
>  my_decimal *Item_cache_str::val_decimal(my_decimal *decimal_val)
>  {
>    DBUG_ASSERT(fixed == 1);
> +  check_late_caching(NULL);
>    if (value)
>      string2my_decimal(E_DEC_FATAL_ERROR, value, decimal_val);
>    else
> @@ -7324,6 +7352,7 @@ my_decimal *Item_cache_str::val_decimal(
>  
>  int Item_cache_str::save_in_field(Field *field, bool no_conversions)
>  {
> +  check_late_caching(NULL);
>    int res= Item_cache::save_in_field(field, no_conversions);
>    return (is_varbinary && field->type() == MYSQL_TYPE_STRING &&
>            value->length() < field->field_length) ? 1 : res;
> @@ -7341,6 +7370,8 @@ bool Item_cache_row::allocate(uint num)
>  
>  bool Item_cache_row::setup(Item * item)
>  {
> +  if (check_late_caching(item))
> +    return FALSE;
>    example= item;
>    if (!values && allocate(item->cols()))
>      return 1;
> @@ -7358,6 +7389,8 @@ bool Item_cache_row::setup(Item * item)
>  
>  void Item_cache_row::store(Item * item)
>  {
> +  if (check_late_caching(item))
> +    return;
>    null_value= 0;
>    item->bring_value();
>    for (uint i= 0; i < item_count; i++)
> 
> === modified file 'sql/item.h'
> --- a/sql/item.h	2009-06-24 17:00:59 +0000
> +++ b/sql/item.h	2009-09-19 17:55:58 +0000
> @@ -532,6 +532,7 @@ public:
>                                             of its arguments is or contains a
>                                             subselect. Computed by fix_fields. */
>    Item_result cmp_context;              /* Comparison context */
> +  bool uncacheable;
>    // alloc & destruct is done as start of select using sql_alloc
>    Item();
>    /*
> @@ -2995,14 +2996,24 @@ protected:
>    Field *cached_field;
>    enum enum_field_types cached_field_type;
>  public:
> +  bool late_caching;
>    Item_cache(): 
> -    example(0), used_table_map(0), cached_field(0),
> cached_field_type(MYSQL_TYPE_STRING) 
> +    example(0), used_table_map(0), cached_field(0),
> cached_field_type(MYSQL_TYPE_STRING),
> +    late_caching(0)
> +  {
> +    fixed= 1; 
> +    null_value= 1;
> +  }
> +  Item_cache(bool late_caching_arg): 
> +    example(0), used_table_map(0), cached_field(0),
> cached_field_type(MYSQL_TYPE_STRING),
> +    late_caching(late_caching_arg)
>    {
>      fixed= 1; 
>      null_value= 1;
>    }
>    Item_cache(enum_field_types field_type_arg):
> -    example(0), used_table_map(0), cached_field(0),
> cached_field_type(field_type_arg)
> +    example(0), used_table_map(0), cached_field(0),
> cached_field_type(field_type_arg),
> +    late_caching(0)
>    {
>      fixed= 1;
>      null_value= 1;
> @@ -3026,6 +3037,8 @@ public:
>    enum Type type() const { return CACHE_ITEM; }
>    enum_field_types field_type() const { return cached_field_type; }
>    static Item_cache* get_cache(const Item *item);
> +  static Item_cache* get_cache(const Item_result type, const Item* item,
> +                                    bool late_caching);
>    table_map used_tables() const { return used_table_map; }
>    virtual void keep_array() {}
>    virtual void print(String *str, enum_query_type query_type);
> @@ -3037,6 +3050,18 @@ public:
>    {
>      return this == item;
>    }
> +  inline bool check_late_caching(Item *item)
> +  {
> +    if (!late_caching)
> +      return FALSE;
> +    if (item)
> +      example= item;
> +    else {
> +      late_caching= FALSE;
> +      store(example);
> +    }
> +    return TRUE;
> +  }
>  };
>  
>  
> @@ -3046,13 +3071,24 @@ protected:
>    longlong value;
>  public:
>    Item_cache_int(): Item_cache(), value(0) {}
> +  Item_cache_int(bool late_caching_arg): Item_cache(late_caching_arg), value(0) {}
>    Item_cache_int(enum_field_types field_type_arg):
>      Item_cache(field_type_arg), value(0) {}
>  
>    void store(Item *item);
>    void store(Item *item, longlong val_arg);
> -  double val_real() { DBUG_ASSERT(fixed == 1); return (double) value; }
> -  longlong val_int() { DBUG_ASSERT(fixed == 1); return value; }
> +  double val_real()
> +  {
> +    DBUG_ASSERT(fixed == 1);
> +    check_late_caching(NULL);
> +    return (double) value;
> +  }
> +  longlong val_int()
> +  {
> +    DBUG_ASSERT(fixed == 1);
> +    check_late_caching(NULL);
> +    return value;
> +  }
>    String* val_str(String *str);
>    my_decimal *val_decimal(my_decimal *);
>    enum Item_result result_type() const { return INT_RESULT; }
> @@ -3065,9 +3101,15 @@ class Item_cache_real: public Item_cache
>    double value;
>  public:
>    Item_cache_real(): Item_cache(), value(0) {}
> +  Item_cache_real(bool late_caching_arg): Item_cache(late_caching_arg), value(0) {}
>  
>    void store(Item *item);
> -  double val_real() { DBUG_ASSERT(fixed == 1); return value; }
> +  double val_real()
> +  {
> +    DBUG_ASSERT(fixed == 1);
> +    check_late_caching(NULL);
> +    return value;
> +  }
>    longlong val_int();
>    String* val_str(String *str);
>    my_decimal *val_decimal(my_decimal *);
> @@ -3081,6 +3123,7 @@ protected:
>    my_decimal decimal_value;
>  public:
>    Item_cache_decimal(): Item_cache() {}
> +  Item_cache_decimal(bool late_caching_arg): Item_cache(late_caching_arg) {}
>  
>    void store(Item *item);
>    double val_real();
> @@ -3105,10 +3148,22 @@ public:
>                     MYSQL_TYPE_VARCHAR &&
>                   !((const Item_field *) item)->field->has_charset())
>    {}
> +  Item_cache_str(const Item *item, bool late_caching_arg) :
> +    Item_cache(late_caching_arg), value(0),
> +    is_varbinary(item->type() == FIELD_ITEM &&
> +                 ((const Item_field *) item)->field->type() ==
> +                   MYSQL_TYPE_VARCHAR &&
> +                 !((const Item_field *) item)->field->has_charset())
> +  {}
>    void store(Item *item);
>    double val_real();
>    longlong val_int();
> -  String* val_str(String *) { DBUG_ASSERT(fixed == 1); return value; }
> +  String* val_str(String *)
> +  {
> +    DBUG_ASSERT(fixed == 1);
> +    check_late_caching(NULL);
> +    return value;
> +  }
>    my_decimal *val_decimal(my_decimal *);
>    enum Item_result result_type() const { return STRING_RESULT; }
>    CHARSET_INFO *charset() const { return value->charset(); };
> @@ -3123,6 +3178,9 @@ class Item_cache_row: public Item_cache
>  public:
>    Item_cache_row()
>      :Item_cache(), values(0), item_count(2), save_array(0) {}
> +  Item_cache_row(bool late_caching_arg)
> +    :Item_cache(late_caching_arg), values(0), item_count(2),
> +    save_array(0) {}
>    
>    /*
>      'allocate' used only in row transformer, to preallocate space for row 
> 
> === modified file 'sql/item_cmpfunc.cc'
> --- a/sql/item_cmpfunc.cc	2009-07-03 10:39:01 +0000
> +++ b/sql/item_cmpfunc.cc	2009-09-19 17:55:58 +0000
> @@ -524,6 +524,13 @@ void Item_bool_func2::fix_length_and_dec
>    set_cmp_func();
>  }
>  
> +void Item_bool_func2::set_cmp_func()
> +{
> +  /* Don't need cache for context analysis. */
> +  if (!current_thd->is_context_analysis_only())
> +    tmp_arg[0]->uncacheable= tmp_arg[1]->uncacheable= TRUE;
> +  cmp.set_cmp_func(this, tmp_arg, tmp_arg+1);
> +}
>  
>  int Arg_comparator::set_compare_func(Item_bool_func2 *item, Item_result type)
>  {
> @@ -847,6 +854,7 @@ int Arg_comparator::set_cmp_func(Item_bo
>                                          Item_result type)
>  {
>    ulonglong const_value= (ulonglong)-1;
> +  Item_cache *cache;
>    a= a1;
>    b= a2;
>  
> @@ -906,6 +914,28 @@ int Arg_comparator::set_cmp_func(Item_bo
>        return 1;
>    }
>  
> +  {
> +    /*
> +      When one of items being compared is a constant and its type
> +      differs from comparison type then cache such item to avoid
> +      type conversion of this constant on each evaluation.
> +    */
> +    if ((*a)->const_item() && !(*a)->uncacheable &&
> +        type != (*a)->result_type())
> +    {
> +      cache= Item_cache::get_cache(type, *a, TRUE);
> +      cache->store(*a);
> +      a_cache= cache;
> +      a= (Item **)&a_cache;
> +    } else if ((*b)->const_item() && !(*b)->uncacheable &&
> +               type != (*b)->result_type())
> +    {
> +      cache= Item_cache::get_cache(type, *b, TRUE);
> +      cache->store(*b);
> +      b_cache= cache;
> +      b= (Item **)&b_cache;
> +    }
> +  }
>    return set_compare_func(owner_arg, type);
>  }
>  
> @@ -4465,6 +4495,11 @@ Item_func::optimize_type Item_func_like:
>  bool Item_func_like::fix_fields(THD *thd, Item **ref)
>  {
>    DBUG_ASSERT(fixed == 0);
> +
> +  /* LIKE doesn't compare arguments directly, thus no reason to cache them. */
> +  for (int i= 0; i < arg_count; i++)
> +    args[i]->uncacheable= TRUE;
> +
>    if (Item_bool_func2::fix_fields(thd, ref) ||
>        escape_item->fix_fields(thd, &escape_item))
>      return TRUE;
> 
> === modified file 'sql/item_cmpfunc.h'
> --- a/sql/item_cmpfunc.h	2009-06-22 09:36:50 +0000
> +++ b/sql/item_cmpfunc.h	2009-09-19 17:55:58 +0000
> @@ -335,10 +335,7 @@ public:
>    Item_bool_func2(Item *a,Item *b)
>      :Item_int_func(a,b), cmp(tmp_arg, tmp_arg+1), abort_on_null(FALSE) {}
>    void fix_length_and_dec();
> -  void set_cmp_func()
> -  {
> -    cmp.set_cmp_func(this, tmp_arg, tmp_arg+1);
> -  }
> +  void set_cmp_func();
>    optimize_type select_optimize() const { return OPTIMIZE_OP; }
>    virtual enum Functype rev_functype() const { return UNKNOWN_FUNC; }
>    bool have_rev_func() const { return rev_functype() != UNKNOWN_FUNC; }
> 
> === modified file 'sql/item_xmlfunc.cc'
> --- a/sql/item_xmlfunc.cc	2009-07-28 14:16:37 +0000
> +++ b/sql/item_xmlfunc.cc	2009-09-19 17:55:58 +0000
> @@ -952,6 +952,8 @@ static Item *create_comparator(MY_XPATH 
>      */
>  
>      Item *fake= new Item_string("", 0, xpath->cs);
> +    /* Don't cache fake because its value will be changed during comparison.*/
> +    fake->uncacheable= TRUE;
>      Item_nodeset_func *nodeset;
>      Item *scalar, *comp;
>      if (a->type() == Item::XPATH_NODESET)
> 
> 
> 
> ------------------------------------------------------------------------
> 
> 


-- 
Øystein Grøvlen, Senior Staff Engineer
Sun Microsystems, Database Group
Trondheim, Norway
Thread
bzr commit into mysql-5.4 branch (epotemkin:2864) Bug#34384Evgeny Potemkin19 Sep
  • Re: bzr commit into mysql-5.4 branch (epotemkin:2864) Bug#34384Øystein Grøvlen25 Sep
    • Re: bzr commit into mysql-5.4 branch (epotemkin:2864) Bug#34384Evgeny Potemkin25 Sep
      • Re: bzr commit into mysql-5.4 branch (epotemkin:2864) Bug#34384Øystein Grøvlen28 Sep
        • Re: bzr commit into mysql-5.4 branch (epotemkin:2864) Bug#34384Sergei Golubchik28 Sep
        • Re: bzr commit into mysql-5.4 branch (epotemkin:2864) Bug#34384Evgeny Potemkin29 Sep
  • Re: bzr commit into mysql-5.4 branch (epotemkin:2864) Bug#34384Øystein Grøvlen4 Oct