maria-developers team mailing list archive

[Sergey Petrunya <psergey@xxxxxxxxxxxx>]

Hi Sanja

A summary of our discussion on irc/skype:

Please change EXPCACHE_DISK_HITMISS_RATIO to be a "hit ratio", i.e "fraction 
of accesses that result in cache hits", as that's this is the most common 
number people use when they think about cache performance.

Please change one limit into two:
1. A very poor hit ratio that will cause cache to stop working altogether
  (like the below patch does)

2. A moderately poor ratio that will cause the in-memory table to be emptied
  (instead of being converted to on-disk table).

Suggested names: EXPCACHE_MIN_HIT_RATIO_FOR_DISK_TABLE,
EXPCACHE_MIN_HIT_RATIO_FOR_MEM_TABLE.  Please supply both definitions with
comments about their meaning.

On Thu, Jul 28, 2011 at 01:18:56PM +0300, sanja@xxxxxxxxxxxx wrote:
> === modified file 'sql/sql_expression_cache.cc'
> --- a/sql/sql_expression_cache.cc	2011-07-19 20:19:10 +0000
> +++ b/sql/sql_expression_cache.cc	2011-07-28 10:18:55 +0000
> @@ -16,6 +16,8 @@
>  #include "mysql_priv.h"
>  #include "sql_select.h"
>  
> +#define EXPCACHE_DISK_HITMISS_RATIO  2
>  /*
>    Expression cache is used only for caching subqueries now, so its statistic
>    variables we call subquery_cache*.
> @@ -26,7 +28,7 @@ Expression_cache_tmptable::Expression_ca
>                                                       List<Item> &dependants,
>                                                       Item *value)
>    :cache_table(NULL), table_thd(thd), items(dependants), val(value),
> -   inited (0)
> +   hit(0), miss(0), inited (0)
>  {
>    DBUG_ENTER("Expression_cache_tmptable::Expression_cache_tmptable");
>    DBUG_VOID_RETURN;
> @@ -180,10 +182,12 @@ Expression_cache::result Expression_cach
>      if (res)
>      {
>        subquery_cache_miss++;
> +      miss++;
>        DBUG_RETURN(MISS);
>      }
>  
>      subquery_cache_hit++;
> +    hit++;
>      *value= cached_result;
>      DBUG_RETURN(Expression_cache::HIT);
>    }
> @@ -224,12 +228,26 @@ my_bool Expression_cache_tmptable::put_v
>    if ((error= cache_table->file->ha_write_tmp_row(cache_table->record[0])))
>    {
>      /* create_myisam_from_heap will generate error if needed */
> -    if (cache_table->file->is_fatal_error(error, HA_CHECK_DUP) &&
> -        create_internal_tmp_table_from_heap(table_thd, cache_table,
> -                                            cache_table_param.start_recinfo,
> -                                            &cache_table_param.recinfo,
> -                                            error, 1))
> +    if (cache_table->file->is_fatal_error(error, HA_CHECK_DUP))
>        goto err;
> +    else
> +    {
> +      if ((hit/miss) < EXPCACHE_DISK_HITMISS_RATIO)
> +      {
> +        DBUG_PRINT("info", ("hit/miss ratio is not so good to go to disk"));
> +        free_tmp_table(table_thd, cache_table);
> +        cache_table= NULL;
> +        DBUG_RETURN(FALSE);
> +      }
> +      else
> +      {
> +        if (create_internal_tmp_table_from_heap(table_thd, cache_table,
> +                                                cache_table_param.start_recinfo,
> +                                                &cache_table_param.recinfo,
> +                                                error, 1))
> +          goto err;
> +      }
> +    }
>    }
>    cache_table->status= 0; /* cache_table->record contains an existed record */
>    ref.has_record= TRUE; /* the same as above */
> 
> === modified file 'sql/sql_expression_cache.h'
> --- a/sql/sql_expression_cache.h	2011-07-19 20:19:10 +0000
> +++ b/sql/sql_expression_cache.h	2011-07-28 10:18:55 +0000
> @@ -85,6 +85,8 @@ private:
>    List<Item> &items;
>    /* Value Item example */
>    Item *val;
> +  /* hit/miss counters */
> +  uint hit, miss;
Please change to be ulong (same type as subquery_cache_hit/miss) 
>    /* Set on if the object has been succesfully initialized with init() */
>    bool inited;
>  };
> 


-- 
BR
 Sergey
-- 
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog