We've upgraded to MySQL 5.6 and start seeing the loading of db server increased significantly, and finally found out the query_cache_type
is defaulted to off start from 5.6.
We enabled it again and see the loading decrease, why this value is being disabled by default start from MySQL 5.6? I cannot see the problem in enabled it.
Best Answer
You need the history of InnoDB to understand why. Here it goes:
WAR STORY
InnoDB and the query cache are in a constant state of war. InnoDB tends to be very heavy-handed when inspecting changes in the InnoDB Buffer Pool and then crosschecking the Query Cache for the same changes.
PEACE TREATY
Before MySQL 5.0, the query cache was disabled for InnoDB. Now, InnoDB interacts with it. To simplify matters, you can just disable the Query Cache by setting the query_cache_size to 0.
According to the MySQL Documentation on query_cache_time
TERMS OF SURRENDER
Setting query_cache_size to 0 is not a one-size-fits-all solution.
The reason for the war, in the first place, is overhead. InnoDB will always inspect changes. A bigger query cache will make InnoDB work that much harder. Disabling the query cache let's the InnoDB and Query Cache be happy. However, you (the Developer/DBA) might be a casualty of that war by means bad query performance, even with such a peace treaty in place.
Depending on the following
you should set query_cache_size to whatever number you feel increases performance (This being tantamount to starting an underground movement).
EPILOGUE
In case you are wondering where I came up with this war story, please see my old post
Sep 05, 2012
: Is the overhead of frequent query cache invalidation ever worth it?Read it carefully because I learned this from Pages 209-215 of High Performance MySQL (2nd Edition)
I have recommended disabling the query cache to others before
Sep 25, 2013
: invalidating query cache entries(key)Sep 26, 2013
: query cache hit value is not changing in my databaseDec 23, 2013
: MySQL with high CPU and memory usageNOTE : I realize the question was about the query_cache_type. It does have an effect on the query cache. Disabling the cache squashes InnoDB's dominance over it. Setting the query_cache_type manually simply forces the Developer/DBA to think carefully about the type of queries the query cache will encounter.