6

Why does MySQLTuner show query_cache_size (=0)?

In the my.cnf, I have

query_cache_type = 1

query_cache_size = 16M

query_cache_limit = 2M

And MySQLTuner's report show this

[!!] Query cache may be disabled by default due to mutex contention.

[OK] Query cache efficiency: 48.5% (2M cached / 5M selects)

[!!] Query cache prunes per day: 28824

[OK] Sorts requiring temporary tables: 0% (23 temp sorts / 9K sorts)

[OK] No joins without indexes

[!!] Temporary tables created on disk: 69% (10K on disk / 15K total)

While MySQLTuner's suggestions show

query_cache_size (=0)

query_cache_type (=0)

query_cache_size (> 16M)

Is my query_cache_size not working due to the suggestion showing "(=0)" and the report showing high query cache prunes per day?

Is this becuase the "Query cache may be disabled by default due to mutex contention." ?

MySQL has been running for 48hrs.

BlueDogRanch
  • 143
  • 1
  • 2
  • 9
  • 3
    The mysql query cache is [deprecated and will be removed entirely in the latest versions](https://dev.mysql.com/doc/refman/5.7/en/query-cache-status-and-maintenance.html). Personally, I'd disable the query cache and spend your time optimizing elsewhere. –  Sep 15 '17 at 17:27
  • Most workloads should have the query cache disabled entirely, as it is almost always slower than running the actual query. Which is the real reason why it's deprecated and being removed. – Michael Hampton Sep 30 '18 at 23:43
  • MySQLTuner detects CPU's with multi cores and suggests disabling Query_Cache to AVOID mutex CONTENTION and conserve CPU cycles used for QC management. – Wilson Hauck Sep 27 '19 at 11:57

1 Answers1

10

Version 8 will not have Query Cache available, they are suggesting avoiding this crutch for poorly performing or simply expensive queries.

Suggestions for your my.cnf-ini [mysqld] section

query_cache_type=0  # for OFF
query_cache_size=0  # to ensure QC is NOT USED

Not using QC reduces some amount of CPU utilization.

Wilson Hauck
  • 472
  • 5
  • 11