WL#1468: Query cache performance improvements
Affects: Server-7.1 — Status: Un-Assigned — Priority: Medium
This task is combined on user complains/experience with performance of current query cache performance. It shall be spit to serveral tasks upon implementation. 1) RESET QUERY CACHE to really reset query cache and reinitialize hashes. Currently it removes queries one by one which may make it to execute 30+ seconds for large query caches, blocking server during this time. 2) FLUSH QUERY CACHE Query cache requires this command now to work effieiently on many loads as in other case fragmentation will make your suffer. We shall make this command to run without locking query cache fully or even better eliminate the need by local online defragmenting (holes merging) 3) In some cases for workloads with rare query cache hits, pushing query_cache_low_mem_prunes (rate table invalidation) may have huge overhead due to query cache enabled (3x and more slowdown). This shall be profiled and if possible fixed (we have test case from the customer) 4) Insert in tables which have many query cache entries corresponding to them may take a lot of time due to query cache purging. We can solve it for example by having "version" for each table content and refreshing each query if it has earlier version of the table than current one + having background remover for purging old queries. It will be anyway needed if we implement queries with timeout. The same thread could do local query cache consolidation to avoid fragmentation issue. 5) See also BUG#28241 "Query cache stores duplicates" 6) See also WL#4210 "new storage layer for QC page allocation" 7) See also WL#1293 QueryCache: Do not spawn second query execution if it is already running 8) see also BUG#37844 "Fix SMP performance for the query cache" 9) See also WL#1211 "Multiple query caches"
Copyright (c) 2000, 2017, Oracle Corporation and/or its affiliates. All rights reserved.