Member Avatar for 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??

  • 2 Contributors
  • 3 Replies
  • 162 Views
  • 21 Hours Discussion Span
  • Latest Post Latest Post by Dani
Member Avatar for kub365

You can set it in your my.cnf file.

Member Avatar for 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.

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.