0

Recently I discovered our MySQL 5.6 database is not using any query caching and I wanted to change that.

I added

[mysqld]
query_cache_type = 1
query_cache_size = 50M
query_cache_limit = 2M

to a file called my-default.ini which is located under C:\Program Files\MySQL\MySQL Server 5.6. I could not find any other .ini file or .cnf file in any directory, and this computer does not have a C:\ProgramData directory where I usually would find it.

Running SHOW VARIABLES LIKE 'have_query_cache' does show have_query_cache YES. But SHOW VARIABLES LIKEquery_cache_typeshowsOFF. AndSHOW STATUS LIKE 'qcache%'` shows all zeros.

Is it possible my my.cnf is missing or deleted? Do I have to recreate it, or rename the my-default.ini to just my.ini? I think perhaps a coworker changed the file name or moved it not knowing what it was potentially.

End of the day, how can I ensure that query caching is enabled on my MySQL 5.6 server?

Running it on Windows Server 2003.

3
  • Know what you are doing if enabling it. You didn't mention whether you restarted mysqld after the config change. Since you are on Windows, if you look at the mysqld service properties, it will show the path to the ini file it is using. For example, mine is "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" MySQL56 – topshot May 3 '19 at 19:13
  • Yes I tried restarting it first. Iknow some MySQL changes require a full stop/start (restart doesn't work) so I did that as well. – bjk116 May 3 '19 at 19:20
  • SHOW VARIABLES LIKE 'query_cache%'; and SHOW GLOBAL STATUS LIKE 'Qc%'; will give some clues of how it is going. – Rick James May 3 '19 at 23:41
0

You are right. Your problem is actually very simple.

In Windows, mysqld is looking for my.ini, not my-default.ini

SUGGESTION

Open Windows Command Line (as Administrator) and run the following:

cd "C:\Program Files\MySQL\MySQL Server 5.6"
copy my-default.ini my.ini
net stop MySQL
net start MySQL

Keep in mind that MySQL was running with default settings and no my.ini

So, maybe the lines above

[mysqld]
query_cache_type = 1
query_cache_size = 50M
query_cache_limit = 2M

should be the only lines in my.ini

0

Aside from the my.ini you can also modify the values directly in the console, from the MySQL manual:

SET GLOBAL query_cache_size = 1000000;
SET GLOBAL query_cache_type = 1;
SET GLOBAL query_cache_limit = 2M;

This way, you can check to see how well it performs, and adjust it accordingly, without having to modify your configuration and restarting the server every time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.