[MDEV-4589] CLOSE - query cache expent time searching query Created: 2013-05-27  Updated: 2013-06-19  Resolved: 2013-06-18

Status: Closed
Project: MariaDB Server
Component/s: None
Fix Version/s: None

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

Issue Links:
Duplicate
is duplicated by MDEV-4682 QUERY CACHE - add STATISTICS per quer... Closed

 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

 Comments   
Comment by roberto spadim [ 2013-05-27 ]

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)

Comment by roberto spadim [ 2013-05-28 ]

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

Comment by roberto spadim [ 2013-06-05 ]

please close this MDEV, it's done in MDEV-4581

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