3

I've a pretty simple question...

Why is the MySQL query cache disabled by default?

In most packaged versions of MySQL I've come across the defaults values are:

query_cache_type = 1
query_cache_size = 0

This essentially disables the query cache by default.

There are many other buffers and limits which are set with a sensible default value. Since the query cache is completely transparent to applications, why have it disabled?

I find myself enabling the query cache and the slow query log automatically every time I deploy a new MySQL box, so just curious as to the logic of the defaults.

Coops
  • 6,055
  • 1
  • 34
  • 54

1 Answers1

4

It's not always disabled by default (it depends on the version and distributor) but there is a good reason for it: it's not always better for performance to enable the query cache, and at larger cache sizes it can actually be detrimental to performance as cache pruning (pushing less-used data out of memory to make way for new entries) takes longer. When invalidation and pruning take longer than the query takes to execute, you've got serious problems.

It's not a silver bullet for performance problems either. From here:

Query cache is great for certain applications, typically simple applications deployed on limited scale or applications dealing with small data sets.

The most obvious (caveat-heavy) reason for disabling the query cache by default is the distributor deflecting potential flak for defaulting a setting that can possibly cause performance problems dependent on application.

Here is a good primer on the MySQL Query Cache

Andy
  • 5,230
  • 1
  • 24
  • 34
  • 1
    I understand it's not always better, but surely in *most* situations it is better? The default values should reflect the 'norm' no? I thought it may be enabled by default in some cases - I've just never come across them. – Coops Mar 03 '11 at 13:06
  • "Normal" may be hard to define in an application as widely used as MySQL. Chances are everyone's idea of "normal" will be a bit different. I see the default settings as just a minimal/basic configuration that will get things running. – uesp Mar 03 '11 at 14:42