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.

bjk116
  • 225
  • 2
  • 9
  • 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 03 '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 03 '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 03 '19 at 23:41

2 Answers2

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

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
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.

Luis Alberto Barandiaran
  • 289
  • 1
  • 7