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