[MDEV-4571] CLOSE - Query cache like features Created: 2013-05-24  Updated: 2015-01-21  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

Issue Links:
Duplicate
is duplicated by MDEV-4584 QUERY CACHE - SQL Control Interface Open
is duplicated by MDEV-4682 QUERY CACHE - add STATISTICS per quer... Closed
Relates
relates to MDEV-7486 Condition pushdown from HAVING into W... Closed

 Description   

Hi guys, very nice job with query_cache_information plugin!!!
Could we add some things?

--------
1) more columns... "table, rows, query_id, insert method, hits"
a)tables from one query cache row
maybe something like "database1.table_a, database2.tableb"
database + "." + table name
or maybe a separate table just for this? read (2)
b)show how many rows in query entry
this help to optimize app thinks like...
SELECT * FROM table WHERE ....
SELECT rows FROM query_cache_information WHERE statment_text="SELECT * FROM table WHERE "...
IF return 0 rows
SELECT COUNT(*) FROM table WHERE ...
c)show query entry id like "primary key" of query_cache_information

with this we can:
delete from query_cache_information where query_id = "some_query_key"

d)'insert method' => 'SQL_CACHE' or 'ALL'

e)hits

ok here we will lose some memory per entry (ulong hits)
maybe will lose some time too, since we need to lock and unlock (i'm right?), like:
lock the entry
add +1 to hit var
unlock it
it's a good information to understand what query in a table have more or less 'hits'
maybe a switch or variable could change this feature on/off to avoid time lost
SET query_cache_entry_hit=ON or OFF

--------
2) get queries that have table "x", like
SELECT * FROM query_cache_information WHERE table like '%,x,%' or table like '%,x' or table like 'x,%' or table='x'

the problem is the table string, that can be 'x' or 'x, y' or 'y, x' or 'y, x, z' ...

i don't know if a table string is the better option, or maybe a subquery could do the job...
in this case we should have a table like:
database,table,query_id

and execute:
SELECT * FROM query_cache_information WHERE query_id IN (
SELECT query_id FROM query_cache_tables_query_information (or another table name)
WHERE database='y' AND table='x'
)

since query cache must remove a query that have some table when we do insert / update / delete
i think the new table is a better option than a text column for table name

--------
3) show tables that have query cache, like
SELECT * FROM query_cache_tables_information
should return: database,table,query_count

--------
4) remove a table from query cache
like when "INSERT INTO table", and query cache remove entries with that table

maybe via two options...
DELETE FROM query_cache_information WHERE table = xxx
or
DELETE FROM query_cache_table_information WHERE table = xxx

-------
that's all =)



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

more somethings, that the end of my brain strom about query cache, and i will sleep =]

1)add "query_time" and "rows_examined" to query entry
why?
a)if query time is <1 second or rows_examined<1000 or something like this, we know that this query could be excluded (or not), since it's don't have a big "execution cost"

b)if query time > 30 second or rows_examined>=1000000, it's a bad idea exclude this query, only if it have hits<xxxx we should remove it (maybe not), but this values are nice to know

2)add a new status value about query cache (maybe... it's cost cpu time to get this values)
Qcache_biggest_entry_hit
Qcache_slowest_entry_time
Qcache_fastest_entry

we need to get all query cache values to know the biggest value, maybe a cache of this values can help, but...
maybe we could use information schema information and MAX() MIN() functions

3)maybe in future a dynamic query cache "clean" option:
query_cache_full_memory_clean_strategy="today page remove" or "less expensive" or "any other"...

with biggests values we could know what's the best query entry to delete:
we have this informations:
query time (queries that are fast to execute may be reexecuted fast again... ok fast is relative...)
rows_count, result_size (queries with many rows may consume many memory)
rows_examined (many time in i/o may be a problem to reexecute the query)
hits (very 'popular' entry isn't a good idea to removed since we will need to recache it in near time, very popular => ORDER hits DESC )

well end thanks guys!

Comment by roberto spadim [ 2013-05-26 ]

please close this MDEV, MDEV-4581 solve this one

Comment by roberto spadim [ 2013-06-05 ]

please close this MDEV, MDEV-4584 and MDEV-4581 is what we need

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