[MDEV-4599] SELECT - Query cache only result Created: 2013-05-29  Updated: 2014-10-09  Resolved: 2014-10-09

Status: Closed
Project: MariaDB Server
Component/s: Query Cache
Fix Version/s: N/A

Type: Task Priority: Trivial
Reporter: roberto spadim Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: querycache, querycache_result

Issue Links:
PartOf
is part of MDEV-4584 QUERY CACHE - SQL Control Interface Open

 Description   

This MDEV add new flag in queries to return only if the query was found in query cache
example:

QUERY:

SELECT SQL_CACHE * FROM SOME_TABLE WHERE SOME_FIELD='XXX'

now we want the result only if it's in cache... this first one will work only if we set strip comments variable "query_cache_strip_comments=on"

/*SQL_CACHE_ONLY*/SELECT SQL_CACHE * FROM SOME_TABLE WHERE SOME_FIELD='XXX'

or

SET result_from_query_cache_only=ON / AUTO;
 
SELECT SQL_CACHE * FROM SOME_TABLE WHERE SOME_FIELD='XXX' 
SET result_from_query_cache_only=OFF; /* if set to AUTO after the query being executed set to OFF again */

if query was not found in cache, it will return an error "QUERY NOT FOUND IN QUERY CACHE" like today "table don't exists" or another kind of error

why this?
when a query is cached we can return it very fast (6us some times), since table can change a lot and query cache is pruned when table change (not when result of query cache change), the query cache is lost very often, some apps cache the return in a external (normally memcache) and check if the query result is inside query, this is a problem, since we do:

1) get cache from memcache (10us)  -> if we found here we expend 10us
 
2) get cache from database (10us)    -> if we found here we expend 20us
 
3) execute query in database (>1000us)   -> if we found here we expend 20us + query time

instead of:

1) get cache from database (10us) -> if we found here we expend 10us (faster than 20us)
 
2) get cache com memcache (10us) -> if we found here we expend 20us (slower than 10us, but it's a second source of cache)
 
3) execute query in database (>1000us) -> if we found here we expend the same 20us + query time

today we expend 10us, but could expend it only in database instead of memcache (we have a connection openned at memcache + a connection openned at mysql/mariadb)

that is not a MUST do task, it's just a feature of how to use query cache and get a better cache hit rate

 Comments   
Comment by Jeremy Cole [ 2013-07-09 ]

Roberto,

I don't think you've properly explained what you find wrong with the first approach, reading from memcache first. If you're okay with the second approach, you must be implicitly okay with reading from memcache, and in that case you can get rid of the query cache completely, and just have effectively a two-tier system (memcache + execute in db). One challenge, of course, is how to properly invalidate memcache, but that can be solved in many ways in the application. If anything, a feature could be made to allow some sort of more intelligent cache invalidation strategy for MySQL, but I'm not convinced it's worth it in the real world. In the real world, the MySQL query cache actually performs rather terribly due to the per-table invalidation strategy and the coarse locking.

Comment by roberto spadim [ 2013-07-09 ]

Hi jeremy i agree with you, but it's a feature, we could or couldn't implement it, i think it's easy to implement, with time i send a patch
I prefer hiting query cache first instead of memcache, no problem about poor performace with cache, it's something that we can improve with time

Comment by roberto spadim [ 2013-07-10 ]

linked as part of query cache control mdev (MDEV-4584)

Comment by roberto spadim [ 2014-09-22 ]

Please close this MDEV, it will be executed by MDEV-4584

Generated at Thu Feb 08 06:57:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.