[MDEV-6890] query cache, only invalidate when table change Created: 2014-10-18  Updated: 2015-11-17

Status: Open
Project: MariaDB Server
Component/s: Query Cache
Fix Version/s: None

Type: Task Priority: Minor
Reporter: roberto spadim Assignee: Unassigned
Resolution: Unresolved Votes: 0
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

 Comments   
Comment by Sergei Golubchik [ 2014-11-07 ]

I suspect it's just not worth the troubles. Like, how often UPDATE will leave the table unchanged?

Comment by roberto spadim [ 2014-11-07 ]

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?

Comment by roberto spadim [ 2014-11-07 ]

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)

Comment by roberto spadim [ 2014-11-07 ]

"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...)

Generated at Thu Feb 08 07:15:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.