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.

Improve this question
3
  • 3
    The mysql query cache is deprecated and will be removed entirely in the latest versions. Personally, I'd disable the query cache and spend your time optimizing elsewhere.
    – user143703
    Sep 15, 2017 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, 2018 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, 2019 at 11:57

1 Answer 1

Reset to default
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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .