MySql and MariaDB are probably the most popular databases out there for linux based development, however the stock MySql installation in Centos 6 and 7 comes with caching disabled. This results in heavy MySql processor use for sites that start to take off.

Allocating even a small amount of memory for MySql caching will result in a big speedup of your web application and can decrease MySql processor usage by up to 20% for heavy sites.

The MySQL query cache is a very simple, straightforward query-level cache. This means it is caching the results of a specific query, not operating at the table or database level. It is completely separate from the key buffer, InnoDB buffer pool, or other MySQL memory structures

If you want to get optimized and speedy response from your MySQL server then you need to add following two configurations directive to your MySQL server:

query_cache_size=SIZE

The amount of memory (SIZE) allocated for caching query results.
The default value is 0, which disables the query cache.

query_cache_type=OPTION

Set the query cache type. Possible options are as follows:
0 : Don't cache results in or retrieve results from the query cache.
1 : Cache all query results except for those that begin with SELECT S_NO_CACHE.
2 : Cache results only for queries that begin with SELECT SQL_CACHE

To enable Query Caching

Open your mysql config file

Then add the following block, sample values are:

In above example the maximum size of individual query results that can be cached set to 256k using query_cache_limit system variable. Memory size in Kb. Alternate values for larger databases would be

Enable Thread Caching

You should also enable thread caching at the same time if its not enabled. thread_cache_size is defined as how many threads the server should cache for reuse.

When a client disconnects, the client’s threads are put in the cache if there are fewer than thread_cache_size threads there. Requests for threads are satisfied by reusing threads taken from the cache if possible, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections.

Open your mysql config file

Then add the following line

To fine tune this value then you need to log into your MySql database and look at the following values:

The take a look at the following values. If threads_created / Connections is over 0.01, then increase thread_cache_size. At the very least, thread_cache_size should be greater than Max_used_connections.

InnoDB Buffer & Query Cache

Many references on Internet will tell you that query cache is useless if InnoDB is being used. If you are using InnoDB only and have limited RAM, then the InnoDB buffer pool without a doubt should get first priority.

If you have RAM to spare then it is highly recommended to use query_cache especially for WordPress sites. Even for big WordPress sites, a large percentage of SELECT queries will be much higher than INSERT or UPDATE’s.