Skip to content

Instantly share code, notes, and snippets.

@jdeathe
Created August 10, 2017 18:26
  • Star 2 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save jdeathe/f46accb46023a018fbc87cda6db859d8 to your computer and use it in GitHub Desktop.
How to Enable MySQL Query Cache on CentOS-6

MySQL Query Cache

How to add a 32M query cache with an item limit of 1M.

Enable on a Running MySQL Server

NOTE: You might need to add '-p' to the following command if 'root'@'localhost' requires a password on your environment.

# mysql \
  -e "SET GLOBAL query_cache_limit = 1048576;" \
  -e "SET GLOBAL query_cache_size = 33554432;" \
  -e "SET GLOBAL query_cache_type = 1;"

Verify

# mysql \
  -e "SHOW VARIABLES LIKE '%query_cache%';" \
  -e "SHOW STATUS LIKE 'qcache%';"

Add to MySQL Configuration

To ensure the changes persist a service restart append the settings to the MySQL configuration.

# if ! grep -qP -- '^query[_-]cache[_-]limit[ ]*=[ ]*[0-9]+$' /etc/my.cnf; then \
    sed -i -r \
      -e '/\[mysqld\]/,/\[mysqld_safe\]/ s~(\[mysqld_safe\])~query_cache_limit=1048576\n\n\1~' \
      /etc/my.cnf; \
  else \
    sed -i -r \
      -e '/\[mysqld\]/,/\[mysqld_safe\]/ s~^(query[_-]cache[_-]limit[ ]*=[ ]*).*$~\11048576~' \
      /etc/my.cnf; \
  fi; \
  if ! grep -qP -- '^query[_-]cache[_-]size[ ]*=[ ]*[0-9]+$' /etc/my.cnf; then \
    sed -i -r \
      -e '/\[mysqld\]/,/\[mysqld_safe\]/ s~^(query[_-]cache[_-]limit[ ]*=[ ]*[0-9]+)$~\1\nquery_cache_size=33554432~' \
      /etc/my.cnf; \
  else \
    sed -i -r \
      -e '/\[mysqld\]/,/\[mysqld_safe\]/ s~(query[_-]cache[_-]size[ ]*=[ ]*)[0-9]+$~\133554432~' \
      /etc/my.cnf; \
  fi; \
  if ! grep -qP -- '^query[_-]cache[_-]type[ ]*=[ ]*.*$' /etc/my.cnf; then \
    sed -i -r \
      -e '/\[mysqld\]/,/\[mysqld_safe\]/ s~^(query[_-]cache[_-]size[ ]*=[ ]*[0-9]+)$~\1\nquery_cache_type=1~' \
      /etc/my.cnf; \
  else \
    sed -i -r \
      -e '/\[mysqld\]/,/\[mysqld_safe\]/ s~(query[_-]cache[_-]type[ ]*=[ ]*).*$~\11~' \
      /etc/my.cnf; \
  fi;

Restart MySQL service for to reload changes from the configuration file. This step is not necessary if using the method above to apply the settings to a running instance of MySQL however it is necessary to prove the configuration file changes won't cause the service to fail to start on next server/service restart.

# service mysqld restart
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment