Category: MySQL SQL SQL Server

The following article describes how to enable the MySQL Query Cache.

There are two main configurations directives as follows:

query_cache_size=

This is the memory allocated for caching query results.
Default = 0, which disables the query cache.

query_cache_type=

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 do the above here is the syntax:

Setup caching as follows:

$ mysql -u root –p
}}}

Output:

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 16 to server version: 4.1.15-Debian_1-log

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.
mysql>
}}}

Set cache size 24Mb:

mysql> SET GLOBAL query_cache_size = 25165824;

Query OK, 0 rows affected (0.00 sec)
}}}

mysql> SHOW VARIABLES LIKE 'query_cache_size';

+——————+———-+
| Variable_name | Value |
+——————+———-+
| query_cache_size | 25165824 |
+——————+———-+
}}}

You can setup them in Redhat: /etc/my.cnf

# vi /etc/my.cnf
}}}

Configuration directives are as follows:

query_cache_size = 25165824
query_cache_type=1
query_cache_limit=1048576
}}}
In above example the maximum size of individual query results that can be cached set to 1048576 using query_cache_limit system variable. Memory size in Kb.

To change the size, use MB with it. Another good configuration for query cache size is listed below:

query_cache_size = 256M
query_cache_type=1
query_cache_limit=5M
}}}
Linux

How useful was this post?

Click on a star to rate it!

Average rating 5 / 5. Vote count: 1