• MySQL知识库 :: performance
  • Query Cache Potentially Undersized

  • Description

    MySQL stores text of SQL statements together with its result in the query cache. If you execute an identical statement, one that was just executed, MySQL gives you results from the query cache instead of executing the query again. If any statement changes table, all SELECT statements against this table will be removed from the query cache. As a result, SELECT statements with a big result set and statements which involve tables that aren't changed often are the best candidates to be cached. When the query cache is full, queries are removed from the query cache and the status variable Qcache_lowmem_prunes is incremented.

    It is important that the query cache is large enough to store SELECT statements and their result sets. If Qcache_lowmem_prunes is large, this means you need to increase the value of the query_cache_size variable. If you've been alerted to this situation by MySQL Monitor, it's probably because you have query cache enabled but too many queries were removed from the query cache because of low memory.

    Best Practice

    To prevent query cache from being emptied often, the best practice would be to modify the related system variables. Before changing these variables, run the SHOW VARIABLES statement to see their current values:

    SHOW VARIABLES LIKE 'query_cache%';
    +------------------------------+---------+
    | Variable_name                | Value   |
    +------------------------------+---------+
    | query_cache_limit            | 1048576 | 
    | query_cache_min_res_unit     | 4096    | 
    | query_cache_size             | 0       | 
    | query_cache_type             | ON      | 
    | query_cache_wlock_invalidate | OFF     | 
    +------------------------------+---------+

    You should set the query cache variables in the server's options file (i.e., my.cnf or my.ini, depending on the system) so that the values aren't lost when the system restarts. Put these variables under the [mysqld] section of the options file. Below is an excerpt from the options file showing the related options to add and set:

    [mysqld]
    query_cache_type = 1
    query_cache_size = value
    query_cache_limit = value
    query_cache_min_res_unit = value
    query_cache_wlock_invalidate = value

    Of course, you would replace the values shown above with actual values suited to your system. Increasing the query cache size, though, may not resolve your problem if your tables are changed too often. In this case, it may not be beneficial to use the query cache at all. When finished adding these options, restart the MySQL server for them to take effect.