WL#1212: Query cache auto defragmentation
Affects: Server-7.1 — Status: Un-Assigned — Priority: Medium
Currently query cache in any cases starts to work inefficiently over time due to fragmentation - a lot of memory is free but still queries are purged out as there are no large enough queries. FLUSH QUERY CACHE is not good enough solution for two reasons 1) It locks query cache for possibly long time (unbound) 2) It conflicts with Zero Administration and ease of use concepts. User shall not setup some chron tasks to be able to get good performance. The solution is auto defragmentation. There are three approaches: 1) Do full defragmentation optimally initiated from timer thread in case some condition is met (ie a lot of lowmem prunes while planty of free memory) 2) Local defragmentation during normal operation. If "fragmentaion" condition is met query cache could move several query data blocks to join several free blocks in one (similar to index merge process) I like second solution better, while it might be a bit complex to implement. Sabja: IMHO it should be stand alone program (mysql server management daemon WL#305 for example) 3) MySQL Network Monitor (Merlin) saves data in a buffer for periodical analysis. When the low memory prunes spike ("fragmentation" condition), Merlin will issue a FLUSH QUERY CACHE and add extra value for enterprise customers.
Copyright (c) 2000, 2017, Oracle Corporation and/or its affiliates. All rights reserved.