Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2011
    Posts
    393

    Cron Job to FLUSH QUERY CACHE

    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.

  2. #2
    Join Date
    Apr 2007
    Posts
    3,513
    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...
    - Buying up websites, side-projects and companies - PM Me! -

  3. #3
    Join Date
    Mar 2011
    Posts
    393
    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

  4. #4
    Join Date
    Mar 2011
    Posts
    41
    I think you would need to use -e:
    mysql -u username -ppassword databasename -e "FLUSH QUERY CACHE"

  5. #5
    Join Date
    Apr 2008
    Location
    United States, MI
    Posts
    766
    Quote Originally Posted by zahirw View Post
    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.
    Steven Crothers
    No BS cloud engineer and Red Hat architect.

  6. #6
    Join Date
    Mar 2011
    Posts
    393
    Quote Originally Posted by nafirici View Post
    I think you would need to use -e:
    mysql -u username -ppassword databasename -e "FLUSH QUERY CACHE"
    Doesn't work...

  7. #7
    Join Date
    Oct 2010
    Location
    ON, Canada
    Posts
    960
    '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.
    GG Web Hosting
    Backup, Digital (SSL) Certificates, Domains, Hosting, Privacy Policy, Software
    S/W: Peel Ads, Softaculous, WHMCS Addons (Abuse Mon., Affiliate Peel Ads, Client Stat., Ord. Prevention, Ord. Stat., Softaculous NOC, Sys. Cleanup)
    Authorized Reseller Program

  8. #8
    Join Date
    Mar 2011
    Posts
    393
    Yes, how do I execute the flush query cache query in shell

  9. #9
    Join Date
    Oct 2010
    Location
    ON, Canada
    Posts
    960
    Quote Originally Posted by zahirw View Post
    Yes, how do I execute the flush query cache query in shell
    It should be something like this:
    Code:
    mysql -u {username} -p{password} -e "FLUSH QUERY CACHE"
    GG Web Hosting
    Backup, Digital (SSL) Certificates, Domains, Hosting, Privacy Policy, Software
    S/W: Peel Ads, Softaculous, WHMCS Addons (Abuse Mon., Affiliate Peel Ads, Client Stat., Ord. Prevention, Ord. Stat., Softaculous NOC, Sys. Cleanup)
    Authorized Reseller Program

  10. #10
    Join Date
    Mar 2011
    Posts
    393
    Didn't work

  11. #11
    Join Date
    Oct 2010
    Location
    ON, Canada
    Posts
    960
    Quote Originally Posted by zahirw View Post
    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?
    Last edited by GGWH-James; 04-26-2011 at 04:02 AM.
    GG Web Hosting
    Backup, Digital (SSL) Certificates, Domains, Hosting, Privacy Policy, Software
    S/W: Peel Ads, Softaculous, WHMCS Addons (Abuse Mon., Affiliate Peel Ads, Client Stat., Ord. Prevention, Ord. Stat., Softaculous NOC, Sys. Cleanup)
    Authorized Reseller Program

  12. #12
    Join Date
    Mar 2011
    Posts
    41
    Quote Originally Posted by zahirw View Post
    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.

Similar Threads

  1. query cache denied?
    By elfz in forum Hosting Security and Technology
    Replies: 5
    Last Post: 04-14-2010, 06:52 AM
  2. cron to flush ip from csf deny list except those marked as do not delete
    By sharmaine1111 in forum Hosting Security and Technology
    Replies: 6
    Last Post: 12-05-2009, 11:05 PM
  3. setting up crontab flush query cache?
    By acctman in forum Programming Discussion
    Replies: 7
    Last Post: 10-07-2009, 06:40 PM
  4. mysql: schedule flush query cache
    By hpham in forum Hosting Security and Technology
    Replies: 1
    Last Post: 12-05-2007, 10:45 PM
  5. Flush Cache on CentOS
    By MziB in forum Hosting Security and Technology
    Replies: 2
    Last Post: 06-15-2007, 03:59 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •