Details
-
Type:
Task
-
Status: Closed
-
Priority:
Trivial
-
Resolution: Duplicate
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
should be nice know how many time we "lost" in query cache searching a query to tune query cache better
1) total expend time with hits
2) total expend time without hits
1+2) total expend time of query cache search
we could do this per qc query too
when we read a query from cache, we add +1 to query hit counter (using last patch that i sent in mdev-4581)
we could add the time expend to read query from cache to this entry to a total time var
after let's say +- 100hits, we can check if the query cache is faster than executing the query, ex:
query cache: 100hits, 500ms , ~500ms/100hits = 5ms/hit for this entry
expend time from this entry (if we execute the query again): 1ms - this information is in qc entry with the mdev-4581 patch
if expend query time to execute query is slower we could "mark" (just a math x>y) this query entry to be deleted first in a low memory situation
this can be a new performace information (slow query cache entries) to know if our query cache is doing a god or a bad work too
with this information we could tune better with MDEV-4588 with a per tables max queries in cache
Gliffy Diagrams
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
the total qc time was added in last patch of MDEV-4581
i will learn more about qc low memory procedure and i will focus on removing some useless queries in cache to better cache use
two prune global variables could do the job like:
query_cache_lowmem_min_expend_time
this one will remove queries that are fast, leaving just slow queries in cache
time = lock_time + select time
query_cache_lowmem_min_query_hits_per_hours
this one will remove queries that don't have a big hit/hour
time = last hit time-insert time
query_cache_lowmem_min_hit_time
this one will remove queries that don't have hits in last X seconds
time = now - last hit time
bye, i will send some patchs when possible
please close this MDEV, it's done in MDEV-4581
an example from today qc plugin:
select tables,sum(query_rows),count
,sum(SELECT_EXPEND_TIME_MS)/1000
from information_schema.query_cache_queries
group by tables
tables,sum(query_rows),count
,sum(SELECT_EXPEND_TIME_MS)/1000
`dev_comercial`.`impostos_valores`,513597,13881,349.28500000
in other words, in this table we can "recreate" the cache in 350 seconds
if the mean query cache hit time is > 350seconds, we have a bad query cache work
idea on how to tune?
set max query cache of this table to a lower value (513597 queries is a lot of query)
execute a cleanup (flush query cache) to remove some slow queries (marked queries)