linux poison RSS
linux poison Email

Basic MySQL performance tuning using Query Cache

The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.

The query cache can be useful in an environment where you have tables that do not change very often and for which the server receives many identical queries. This is a typical situation for many Web servers that generate many dynamic pages based on database content.

NOTE: The query cache always contains current and reliable data. Any insert, update, delete, or other modification to a table causes any relevant entries in the query cache to be flushed.

Query Cache Configuration:
To set the size of the query cache, set the query_cache_size system variable.
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;
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:

0 or OFF prevents caching or retrieval of cached results.
1 or ON enables caching except of those statements that begin with SELECT SQL_NO_CACHE.
2 or DEMAND causes caching of only those statements that begin with SELECT SQL_CACHE.
mysql> SET SESSION query_cache_type = ON;
Using Select statement using Query Cache:
SQL_CACHE: The query result is cached if it is cacheable and the value of the query_cache_type system variable is ON or DEMAND.
SQL_NO_CACHE: The query result is not cached.

Examples:
SELECT SQL_CACHE id, name FROM customer;
SELECT SQL_NO_CACHE id, name FROM customer;
Setting the GLOBAL query_cache_type value determines query cache behavior for all clients that connect after the change is made. Individual clients can control cache behavior for their own connection by setting the SESSION query_cache_type value


1 comments:

Post a Comment

Related Posts with Thumbnails