Details
-
New Feature
-
Status: In Testing (View Workflow)
-
Minor
-
Resolution: Unresolved
-
None
-
None
-
None
Description
The initial MDEV-37070 scope included 3 per-table options: QUERY_CACHE, ADAPTIVE_HASH_INDEX, BINLOG_ROW.
MDEV-37070 scope was refined: ADAPTIVE_HASH_INDEX became also a per-index option, while QUERY_CACHE and BINLOG_ROW were excluded from the first implementation.
This ticket is opened to track the feature request of the QUERY_CACHE per-table option (values DEFAULT, YES, NO).
From a performance point of view, QUERY_CACHE should not be used with tables that change very frequently.
The effect of the new per-table QUERY_CACHE option would be:
Having this option OFF is the same as using SELECT SQL_NO_CACHE for any query using this table.
Having this option ON is same as using SELECT SQL_CACHE for this table and the query will be cached if no other table has QUERY_CACHE=OFF
The new values for query_cache_type are (0-2 are same as before)
OFF (0) = Query cache off
ON (1) = Query cache on, all queries are cached except queries marked with SQL_NO_CACHE
DEMAND (2) = Query cache on, only queries marked with SQL_CACHE are cached
DEMAND_STRICT (3) = Query cache on, only queries starting with SELECT SQL_CACHE
are cached. This avoids a query cache lock+lookup for every
query.
TABLES (4) = Query cache on, only cache queries marked with SQL_CACHE or
where all used tables are using the create option SQL_CACHE=1
ALWAYS_OFF (5) = To turn of query cache permanently for new open tables. query_cache_type
cannot be changed without a server restart.
The purpose of ALWAYS_OFF is to tell engines that they can totally ignore the query cache. This speed ups engines (especially InnoDB) as they don't have to take into account that query cache can any time switch between on/off.
Other optimizations done:
- Speed up query cache interface to make query_cache_type=OFF code a bit faster.
- Added query_cache_use_rw_lock to allow concurrent query cache lookups
- Setting query_cache_size in a config file will not automatically enable the query cache
- This was done to address user complaints that query_cache_size should not affect query_cache_type (not standard or expected behaviour for MariaDB variables).
As a note, in MDEV-37070 there were suggestion to make QUERY_CACHE automatically toggled per table (link here) as well as counter-arguments in favor of this new per-table option (link here). Please refer to full MDEV-37070 list of comments for further details.
Attachments
Issue Links
- includes
-
MDEV-6631 No cache directive(SQL_CACHE) query still wait for "query cache lock" on query_cache_type=DEMAND mode
-
- In Testing
-
- split from
-
MDEV-37070 Implement table and index options to enable/disable ADAPTIVE_HASH_INDEX
-
- Closed
-
- has action item
-
DOCS-6572 Loading...