WL#9817: Change default query_cache_size to zero
Affects: Server-8.0 — Status: Complete
In MySQL 5.6 we changed the default query_cache_size to 1M, with query_cache_type to OFF. From a usability perspective, this made sense: having to enable two options together to work is confusing. pfs shows that the Query cache mutex is still acquired when the size is non-zero: https://www.percona.com/blog/2016/11/11/is-your-query-cache-really-disabled/ Suggested default for 8.0 is zero. User Documentation ================== * http://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-1.html#mysqld-8-0-1-configuration * http://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_query_cache_size
Functional Requirement ====================== Default value of variable 'query_cache_size' at server start should be : 0 (zero)
The worklog changes the default value of variable : query_cache_size - 0 ( new default value ) 1048576 ( old default value ) Some discussions about Query Cache: ==================================== From Links - https://bugs.mysql.com/bug.php?id=65336 and - http://dev.mysql.com/doc/refman/5.7/en/query-cache-configuration.html "If the server is started with query_cache_type set to 0, it does not acquire the query cache mutex at all, which means that the query cache cannot be enabled at runtime and there is reduced overhead in query execution." Changing the setting while the server is running does reduce the cost of the query cache because queries aren't cached and much checking time is reduced but there is still overhead compared to having it off at startup. That overhead is greater with row-based replication. Summary : Query cache mutex performs solely based on the value of query_cache_type variable at the time of server startup. If query_cache_type is ON while starting the server, changing the variable value at runtime will not reduce the mutex overhead. The change in default value of query_cache_size will not affect this behavior.
Copyright (c) 2000, 2020, Oracle Corporation and/or its affiliates. All rights reserved.