Details
-
Task
-
Status: Closed (View Workflow)
-
Trivial
-
Resolution: Duplicate
-
None
-
None
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 =)
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!