Sunday, August 14, 2011

To Enable Query Cache in Mysql

Query caching is a way to increase the mysql performance by caching database queries.
to enable just edit one file, in redhat it’s called /etc/my.cnf

Add the following lines in the mysqld section

[mysqld]
query_cache_limit = 16M
query_cache_size = 256M
query_cache_type = 1

restart the mysql daemon

# /etc/init.d/mysql restart

To verify the cache is enabled

# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36074287
Server version: 5.0.92-community-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+----------+

| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 16777216 |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 268435456|
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
6 rows in set (0.00 sec)