Details
-
Type:
Task
-
Status: Open
-
Priority:
Minor
-
Resolution: Unresolved
-
Fix Version/s: 10.2
-
Component/s: Query Cache
-
Labels:
Description
hi guys... i was testing query_cache_information plugin with production machines, and i'm surprised. there's many queries that expend < 10ms to be executed and return 1 row, that are cached
could we develop some new query cache tunes/prunes?
query_cache_prune_min_expend_time = 20ms
query_cache_prune_min_result_rows = 100
query_cache_prune_min_read_rows = 10000
maybe others variables / functions...
only queries that:
1) expend more than 20ms (expend time = lock time + query time) OR
2) return more than 100 rows OR
3) read more than 100000 rows
4) with SQL_CACHE
are cached
the point is add a new query_cache_type, today we have:
OFF = turn off qc
ON = any query without SQL_NO_CACHE
DEMAND = only query with SQL_CACHE
PRUNED = only query with SQL_CACHE and not pruned by query_cache_prune* variables
maybe could be used query_cache_type=ON and by default:
query_cache_prune_min_expend_time = 0
query_cache_prune_min_result_rows = 0
query_cache_prune_min_read_rows = 0
i think that with this i can better tune app/db without rewriting them
an black list could be used too... it's nice for high intensive query cache server with apps that can't be changed by users this issue was add to another MDEV-4671
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
from
MDEV-4581comment:Jean Weisbuch added a comment
Another idea would be to add a query cache settings (query_cache_type=3 for example) to only cache queries that involved filesort and non-indexed rows retreival.
And adding a new variable that would set a minimal number of rows examined to be put in cache, for example a "query_cache_min_examined_rows".
With these options, only long/expensive queries would be put in cache, it surely wouldnt solve the cache cleaning but it could limit the number of entries in the cache and ease the cleanup process.