Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : Cron Job to FLUSH QUERY CACHE

[zahirw]

My query cache is set quite high but it gets fragmented quite fast. I'd like to routinely flush the cache every 2 hours.

I ran this command in shell logged in as root however, it just gave me the list of mysql options instead.

mysql -u username -ppassword databasename "FLUSH QUERY CACHE"

What am I doing wrong? I'd like to save this as a shell script and run it via cron.

[iTom]

Cache is good and there to help so you should be flushing it.

If it using too much RAM simply change your MySQL configuration to reduce the amount of RAM allocated to cache.

That way MySQL will keep it's self in check, without cron's etc...

[zahirw]

Hey Tom
I think you misunderstood me. We are using query cache and want to continue doing so. Once the cache fills up though, it starts fragmenting. To avoid that, we'd like to flush it say every 6 hours via cron

[nafirici]

I think you would need to use -e:
mysql -u username -ppassword databasename -e "FLUSH QUERY CACHE"

[Crothers]

Originally Posted by zahirw

Hey Tom
I think you misunderstood me. We are using query cache and want to continue doing so. Once the cache fills up though, it starts fragmenting. To avoid that, we'd like to flush it say every 6 hours via cron

I do believe that bug is for older versions of MySQL, 5.5 does not have that problem. You're only going to degrade performance with constant flushing.

[zahirw]

Originally Posted by nafirici

I think you would need to use -e:
mysql -u username -ppassword databasename -e "FLUSH QUERY CACHE"

Doesn't work...

[GGWH-James]

'FLUSH QUERY CACHE' doesn't actually remove any queries form the cache, it defragments it. However, 'RESET QUERY CACHE' will remove all query results from the query cache.

[zahirw]

Yes, how do I execute the flush query cache query in shell

[GGWH-James]

Originally Posted by zahirw

Yes, how do I execute the flush query cache query in shell

It should be something like this:

mysql -u {username} -p{password} -e "FLUSH QUERY CACHE"

[zahirw]

Didn't work

[GGWH-James]

Originally Posted by zahirw

Didn't work

What I gave you is the correct command line to run the query; unless you need to connect to specify a hostname that is. We run that on a few of our servers every several hours. Does the user that you're passing have the proper permissions to perform that? Do you receive an error?

[nafirici]

Originally Posted by zahirw

Doesn't work...

I've also tested this and worked fine for me. Do you have the correct permissions? Is there some sort of an error that you're getting? I would also recommend using RESET QUERY CACHE because GGWH-James is correct, FLUSH QUERY CACHE doesn't remove any queries from the cache and RESET does.