Query cache with inconsistent results + TTL

Some applications use inconsistent cache results (even with memcache), i don't know the right name but it's something related to Strong consistency and weak consistency (http://en.wikipedia.org/wiki/Cache_coherence) must review technical documentation to use better names here

Today query cache invalidate all queries from query cache when a table change (INSERT/DELETE/UPDATE), the idea is don't invalidate some known queries and only invalidate it with a time to live or a flush command

1) implement a SQL_CACHE_INCONSISTENT keyword or something like it, that tell query cache to not invalidate this query when a table invalidation occur (must check if we will have problems with replication), example:

SELECT SQL_CACHE_INCONSISTENT * FROM table;   (first query cache insert)

UPDATE table SET some_field=new_value WHERE some where clause;

SELECT SQL_CACHE_INCONSISTENT * FROM table;   (return from query cache)

SELECT * FROM table;   (execute as "normal" query)

2) implement a TTL (SQL_CACHE_TTL) query cache (there's a patch, must check if it's nice or not - http://ebergen.net/wordpress/2007/04/23/select-sql_cache_ttl/)
IMHO TTL is only usefull with inconsistent cache, without it TTL only allow a better query cache memory cleanup (remove first TTL queries and after old queries)

• one session variable "query_cache_ttl" with unit as seconds and a float part of microseconds (for example: 3.000001), that will be used when SQL_CACHE_TTL is included at query, i think a default value of 30 seconds is ok
• one session variable "query_cache_max_ttl" that will be used at query cache fetch (check 2.1)
• note don't include TTL as a query cache "flag" (struct Query_cache_query_flags), since queries with SQL_CACHE_TTL are separate from normal queries without SQL_CACHE_TTL since query text is differente
  SELECT * FROM table <is always !=> SELECT SQL_CACHE_TTL FROM table

2.1) something to think about (variable query_cache_max_ttl)

when insert the first TTL query it will have a variable at struct Query_cache_query, something like

ulonglong qc_info_insert_time; /* from (MDEV-4682) to know how many time this query is inside query cache */

ulonglong qc_info_hits_last_time; /*from (MDEV-4682) to know what was the last hit time of this query */

double? qc_ttl; (new variable from this MDEV, must check what kind of variable we should use, double/float/etc)

how to check if query is expired or not (using query_cache_max_ttl):

if query_cache_max_ttl =0{

  if (qc_ttl+qc_info_hits_last_time) < now()) we should remove query from cache

}else{

  if (MAX(qc_ttl,query_cache_ttl)+qc_info_hits_last_time) < now()) we should remove query from cache

}

2.2) when invalidate query cache queries?
2.2.1)at query cache hit of a SQL_CACHE_TTL query (using query_cache_max_ttl or not)
2.2.2)at table invalidation
2.2.3)at low memory, but instead of remove old query first, remove first expired queries and after old queries (must check how to do this fast)

3) implement a FLUSH INCONSISTENT QUERY CACHE command (we could include this at MDEV-4584), it will remove only queries with SQL_CACHE_INCONSISTENT from query cache

4) at query cache internal (sql_cache.cc /.h)
table block, include a counter

...

struct Query_cache_table

...

int32 m_cached_query_count;

int32 m_cached_query_inconsistent_count;  /* new */

...

only remove the table if: m_cached_query_count=m_cached_query_inconsistent_count=0

if table have m_cached_query_inconsistent_count>0, it can't be removed from query cache with a table invalidation, just "consistent" queries should be removed
in this case after table invalidation, m_cached_query_count=m_cached_query_inconsisntent_count>0 and we can remove table from query cache

5) only RESET QUERY CACHE or FLUSH INCONSISTENT QUERY CACHE <WITHOUT_BINLOG> will remove inconsistent queries from query cache
FLUSH TABLES will not remove it

5.1) check what to do with binlog and FLUSH command, for example, we could flush inconsistent query cache at master, but don't replicate this to slaves... must check what's better in this case maybe just add WITHOUT_BINLOG is ok

—
that's all