Details
-
Type:
Task
-
Status: Open
-
Priority:
Minor
-
Resolution: Unresolved
-
Fix Version/s: 10.2
-
Component/s: Query Cache
-
Labels:None
Description
hi guys... well again query cache...
check this...
create table ae(b enum('a','b','c') not null);
insert into ae values ('a'),('b'),('c');
select * from information_schema.QUERY_CACHE_QUERIES; /* no query, nice */
select * from ae;
select * from information_schema.QUERY_CACHE_QUERIES; /* one query, nice too */
update ae set b='d' where b='d'; /* no row changed */
select * from information_schema.QUERY_CACHE_QUERIES; /* no queries ?! */
i didn't checked how each engine remove queries from query cache, but... should it only remove queries from query cache, if one+ row(s) change? this reduce table invalidation, increase query cache hit
idea of this MDEV: create a 'delayed' query cache invalidation, in other words, instead of invalidating when one UPDATE/DELETE/INSERT occur, only invalidate if one row changed, ok this is a problem with transactions where we don't know if it should or not be invalidated outside transaction, but, if update/delete don't change anything, why should it invalidate query cache?
—
idea: instead of invalidating... first mark table to 'not allow new queries at query cache', after update/insert/delete, if row changed/affected>0 execute the invalidation, if <=0 'unlock' the table from query cache (allow new queries use this table), we will have some queries not cached cause we 'locked' insert into query cache using update/delete/insert table , but this increase query cache hit, and allow a lower resource consume invalidating always queries without rows changed
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Hi Sergei!
hum you are talking about possible read uncommited while executing other select? or something like it?
i didn't read a lot about the source code to give many help, i checked the "user side" of query cache only
maybe instead of invalidate the table from query cache at first update/delete/insert, we could "lock" the query cache table (something like exclusive lock) and if a new select execute, it will not return to client using query cache cause the table used with the cached query is 'locked'
something like:
1) SELECT from t1...
insert into query cache
2) UPDATE start ...
table t1 query cache is locked, using qc_info plugin, that's the QUERY_CACHE_TABLES table
3) SELECT from t1...
search the query at query cache, check the t1 table query cache status - it's locked, execute the query without query cache, using qc_info plugin this is something like QUERY_CACHE_TABLES + QUERY_CACHE_QUERIES_TABLES
4) ... many years after :) ....
UPDATE end ...
no rows changed
5) SELECT from t1...
search the query at query cache, check the t1 table query cache status - it's not locked, execute the query with query cache
this add more cpu cicles and increase a bit memory consume (each query cache table must have a lock information now)
maybe could be a query cache option (query_cache_instantaneous_invalidate = on/off)
i think it's a nice feature to increase hit rate, it reduce some possible table invalidation (good), but increase cpu cicles while getting query from cache (bad), the mix of good/bad i think we could leave to user/developer/dba
—
i don't know if i got what you told about troubles, is something that i'm missing?
sorry many rewrite....
i was thinking about 2 updates (or two transactions)
update 1.... (qc_table locked) update 2.... update 2 ended with rows changed... (qc_table invalidated) update 1... (qc_table don't exists)
and
select (qc insert) update 1.... (qc_table locked) update 2.... update 2 ended with rows changed... (qc_table invalidated) select (qc don't fetch cause update 1 and qc is invalidated, qc insert - must know that table is locked... here i think storage engine know when a query is being executed and return false to query cache insert) update 1 ends... (qc_table must be 'locked', if rows changed - invalidade, if no rows changed check if we could unlock? for example two updates running only unlock at the last update end)
"I suspect it's just not worth the troubles. Like, how often UPDATE will leave the table unchanged?"
sorry, now i looked the problem...
you are talking about... 90% (just a number, not a exact value) of updates change the table, right?
yes i agree, but some cases it don't update, updating statistics tables, transaction rollback, maybe others cases
(that's not just UPDATE, that's DELETE, INSERT, REPLACE, etc...)
I suspect it's just not worth the troubles. Like, how often UPDATE will leave the table unchanged?