Details
-
Type:
Task
-
Status: Closed
-
Priority:
Trivial
-
Resolution: Duplicate
-
Fix Version/s: N/A
-
Component/s: Query Cache
-
Labels:
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
Gliffy Diagrams
Attachments
Issue Links
- is part of
-
-
- Open
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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
linked as part of query cache control mdev (MDEV-4584)
Please close this MDEV, it will be executed by MDEV-4584
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.