LinuxHelps.com

A blog for Linux Lovers.

Posted by sibu on March 22, 2009

Enable query cache in MySQL.

This query cache in mysql will help to improve my mysql performance.

query_cache_size=SIZE


The amount of memory (SIZE) allocated for caching query results.

Options are:

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

How to Enable.

Login to mysql as root user.

mysql -u root –p

Setup cache size 16Mb:

mysql> SET GLOBAL query_cache_size = 16777216;

To check this

mysql> SHOW VARIABLES LIKE 'query_cache_size'; 

Result:

 +------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 16777216 |
+------------------+----------+

Then open my.cnf file, Append config directives as follows:

query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576

In this example maximum size of individual query results that can be cached
set to 1048576 using query_cache_limit system variable

Add A Comment