The performance tuning of MySQL is very important for high traffic websites with MySQL as there DB.  The db Admin always looking for ways to speed up query, in mysql we can enable query cache to speed up query performance. Whenever query cache is enable, it will cache the query in memory and boost query performance.

The have_query_cache server system variable indicates whether the query cache is available:

mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

The query cache will improve performance if you are doing few updates on the database.When using a standard MySQL binary, this value is always YES, even if query caching is disabled.

To enable query_cache , you can just edit your my.cnf file and add the following lines:

query_cache_type = 1
query_cache_size = 32M
Once you have done with it you can just restart your MySQL Server to take effect.

To set the size of the query cache, set the query_cache_size system variable. Setting it to 0 disables the query cache. The default size is 0, so the query cache is disabled by default.When you set query_cache_size to a nonzero value, keep in mind that the query cache needs a minimum size of about 40KB to allocate its structures. (The exact size depends on system architecture.) If you set the value too small, you’ll get a warning, as in this example:

mysql> SET GLOBAL query_cache_size = 40000;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1282
Message: Query cache failed to set size 39936;
         new query cache size is 0

mysql> SET GLOBAL query_cache_size = 41984;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 41984 |
+------------------+-------+

For the query cache to actually be able to hold any query results, its size must be set larger:

mysql> SET GLOBAL query_cache_size = 1000000;
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| query_cache_size | 999424 |
+------------------+--------+
1 row in set (0.00 sec)

If the query cache size is greater than 0, the query_cache_type variable influences how it works. This variable can be set to the following values:

A value of 0 or OFF prevents caching or retrieval of cached results.

A value of 1 or ON allows caching except of those statements that begin with SELECT SQL_NO_CACHE.

A value of 2 or DEMAND causes caching of only those statements that begin with SELECT SQL_CACHE.

To check for MySQL  server  query cache status, simply run this query:

SHOW VARIABLES LIKE ‘%query_cache%’;

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 33554432 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
6 rows in set (0.00 sec)

To check if your MySQL query cache is working:

mysql> SHOW STATUS LIKE '%qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 14       |
| Qcache_free_memory      | 33442408 |
| Qcache_hits             | 31       |
| Qcache_inserts          | 226      |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 23       |
| Qcache_queries_in_cache | 51       |
| Qcache_total_blocks     | 119      |
+-------------------------+----------+
8 rows in set (0.01 sec)

To make this article i refered the following sites:

http://dev.mysql.com/doc/refman/5.0/en/query-cache-configuration.html

Post By Editor (2,827 Posts)

Website: →

Connect