Thursday, August 9, 2012

Speeding up MySQL database with query_cache and improving the performance



Is your MySQL database getting slower? Your server overloading? You might need to optimize your MySQL database server by enabling query caching which will increase the performance and increase the access time.

Remember that the hard-disk is the slowest part in every computer or server. By enabling caching, you have a higher chance of retrieving the data or part of that data from the memory rather than from the disk.


There are two ways to enable database caching:
  1. From MySQL command or phpMyAdmin.
  2. From MySQL configuration file, my.cnf.

1- From MySQL Command

First, let's check if query_cache is enabled:

SHOW VARIABLES LIKE 'query_cache%';

The three values that we're going to work on are:

query_cache_type 

  • A value of 0 or OFF prevents caching or retrieval of cached results.
  • A value of 1 or ON enables caching except of those statements that begin with SELECT SQL_NO_CACHE.
  • A value of 2 or DEMAND causes caching of only those statements that begin with SELECT SQL_CACHE.

query_cache_size


This is the cache size in bytes, if we want to add 32MB cache, we write the following value:
query_cache_size = 33554432
or
query_cache_size = 32MB

query_cache_limit

This is the maximum size of individual query results that can be cached. If you have big query results, you might need to increase this value. The default value is 1MB.

Most cPanel installations, virtualmin, webmin, plaxo or plesk have database caching enabled, but have query_cache_size set to 0, which basically mean the queries are not getting cached.

To enable query cache for 32MB issue the following MySQL command:

SET GLOBAL query_cache_size = 33554432;

To make sure that the value is set correctly, write the following:

SHOW VARIABLES LIKE 'query_cache_size';


The advantage of this method is it's quick and easy to do, however the disadvantage is if you restart your MySQL server, you will have to re-enable the query_cache, and optimize the database again, and in case you forget, you will lose some performance.

2- From MySQL Configuration File "my.cnf"

This method might be harder than the previous one, because you will have to change the configuration file, however in this method the configurations won't be lost even if the MySQL database server is restarted.

You need to be logged in as root to change the configuration file. if root user is disabled, you can use the su command. More about linux commands can be found in here.

The MySQL database configuration file is called my.cnf

You can find the MySQL configuration file my.cnf in the following directories, and they are read in the given order in linux:
/etc/my.cnf
/etc/mysql/my.cnf
/usr/etc/my.cnf
~/.my.cnf

In windows
C:\Windows\my.ini
C:\Windows\my.cnf
C:\my.ini
C:\my.cnf
C:\Program Files\MySQL\MySQL Server 5.5\my.ini
C:\Program Files\MySQL\MySQL Server 5.5\my.cnf



To enable caching just open the my.cnf file (in linux/ssh):
vi /etc/my.cnf

And edit the following 3 lines:
query_cache_size = 33554432
query_cache_type=1
query_cache_limit=1048576

If those value don't exist in the configuration file, you can append them. Just make sure you're appending them in the [mysqld] section.


Make sure to use "Insert" to edit the file, and "ESC" to exit editing mode, and ":w" to write/save the changes.

Don't forget to restart your MySQL after applying the changes:
To restart MySQL in CentOS:
service mysqld restart


Any questions or suggestions, please leave a comment below.