Query Cache

[Dani]

I am using MySQL 4 and trying to cache my queries per this tutorial:

http://dev.mysql.com/doc/refman/4.1/en/query-cache-configuration.html

Right now, my query_cache_size is set to 0. I would like to set both the query_cache_size and query_cache_limit parameters.

However, I'm receiving the following error message:

mysql >
ERROR 1227: Access denied. You need the SUPER privilege for this operation

What's the next step?

- Dani

P.S. I was reading another tutorial that had me set the variables in the /etc/my.cnf file but even after restarting mysqld, nothing happened??

[kub365]

You can set it in your my.cnf file.

[Dani]

Yes, that was my P.S. in my original post. I tried setting it in the my.cnf file, restarted the server, it didn't work. In any case, I figured out the problem. I had inserted the lines at the bottom of the my.cnf file, which ended up being under the [safe_mysqld] section. Hence why restarting mysqld didn't show any difference. I moved the lines up under the [myqld] section and it now works.

Which is a really good thing because the situation was getting very dire. We were averaging a consistant load average of 4 and The Planet, where daniweb is hosted, was out of stock on the database server I want to buy. (Right now everything is on one server). As soon as I enabled the query cache, server load average went down to a comfortable 1. It is at 0.6 right now, and I don't think I've seen it that low in ... well, I can't remember how long it's been.

[Dani]

Update:

I just remembered my admin MySQL username and password :) I was able to run the command via the mysql prompt as well as via the file edit. Yay! Best of all, changing the variable via the command line instead of file editing doesn't require restarting the MySQL server.