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@askmonty.org 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