In some installations of MySQL, the query cache is disabled by default so you will have to do a little configuration to set things up. The way to check is to log into MySQL and issue the following command:
mysql> show variables like 'query%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
+------------------------------+---------+
While the query_cache_type variable is set to ON, the query_cache_size variable is set to zero, and this effectively disables the query cache. Setting the query_cache_type variable to ON (caches all SELECT queries except for those with the SQL_NO_CACHE hint) or DEMAND (only caches SELECT queries with the SQL_CACHE hint) along with a query_cache_size variable something greater than zero enables the query cache.
At the moment, however, the query cache is disabled so let's issue a particular query that computes the top five commissioned brokers at an investment management company a couple of times and see what happens:
mysql> SELECT A.BROKER_ID,
-> A.BROKER_FIRST_NAME,
-> A.BROKER_LAST_NAME,
-> SUM(BROKER_COMMISSION) TOTAL_COMMISSIONS
->FROM BROKER A,
-> CLIENT_TRANSACTION B
->WHERE A.BROKER_ID = B.BROKER_ID
->GROUP BY A.BROKER_ID,
-> A.BROKER_FIRST_NAME,
-> A.BROKER_LAST_NAME
->ORDER BY 4 DESC
->LIMIT 5;
+-----------+-------------------+------------------+-------------------+
| BROKER_ID | BROKER_FIRST_NAME | BROKER_LAST_NAME | TOTAL_COMMISSIONS |
+-----------+-------------------+------------------+-------------------+
| 20 | STEVE | BOYCE | 3864173.64 |
| 1 | JONATHAN | MORTON | 1584621.39 |
| 13 | JIM | SANDERS | 1369157.73 |
| 4 | DAVE | TUCKER | 1214111.75 |
| 14 | DENISE | SCHWARTZ | 1041040.98 |
+-----------+-------------------+------------------+-------------------+
5 rows in set (0.11 sec)
< execute query again >
+-----------+-------------------+------------------+-------------------+
| BROKER_ID | BROKER_FIRST_NAME | BROKER_LAST_NAME | TOTAL_COMMISSIONS |
+-----------+-------------------+------------------+-------------------+
| 20 | STEVE | BOYCE | 3864173.64 |
| 1 | JONATHAN | MORTON | 1584621.39 |
| 13 | JIM | SANDERS | 1369157.73 |
| 4 | DAVE | TUCKER | 1214111.75 |
| 14 | DENISE | SCHWARTZ | 1041040.98 |
+-----------+-------------------+------------------+-------------------+
5 rows in set (0.11 sec)
Each result set is returned fairly quickly at .11 seconds, which isn't too bad. However, now let's enable the query cache by setting it to 50M (MySQL allows the dynamic setting of many variables without starting/stopping MySQL) and then see what happens when we execute the same query twice again:
mysql> set global query_cache_size=50000000;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'query%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 49999872 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
+------------------------------+----------+
mysql> SELECT A.BROKER_ID,
-> A.BROKER_FIRST_NAME,
-> A.BROKER_LAST_NAME,
-> SUM(BROKER_COMMISSION) TOTAL_COMMISSIONS
->FROM BROKER A,
-> CLIENT_TRANSACTION B
->WHERE A.BROKER_ID = B.BROKER_ID
->GROUP BY A.BROKER_ID,
-> A.BROKER_FIRST_NAME,
-> A.BROKER_LAST_NAME
->ORDER BY 4 DESC
->LIMIT 5;
+-----------+-------------------+------------------+-------------------+
| BROKER_ID | BROKER_FIRST_NAME | BROKER_LAST_NAME | TOTAL_COMMISSIONS |
+-----------+-------------------+------------------+-------------------+
| 20 | STEVE | BOYCE | 3864173.64 |
| 1 | JONATHAN | MORTON | 1584621.39 |
| 13 | JIM | SANDERS | 1369157.73 |
| 4 | DAVE | TUCKER | 1214111.75 |
| 14 | DENISE | SCHWARTZ | 1041040.98 |
+-----------+-------------------+------------------+-------------------+
5 rows in set (0.11 sec)
< execute query again >
+-----------+-------------------+------------------+-------------------+
| BROKER_ID | BROKER_FIRST_NAME | BROKER_LAST_NAME | TOTAL_COMMISSIONS |
+-----------+-------------------+------------------+-------------------+
| 20 | STEVE | BOYCE | 3864173.64 |
| 1 | JONATHAN | MORTON | 1584621.39 |
| 13 | JIM | SANDERS | 1369157.73 |
| 4 | DAVE | TUCKER | 1214111.75 |
| 14 | DENISE | SCHWARTZ | 1041040.98 |
+-----------+-------------------+------------------+-------------------+
5 rows in set (0.00 sec)
mysql> show status like 'qc%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 49988976 |
| Qcache_hits | 1 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 5 |
+-------------------------+----------+
The first query once again takes .11 seconds to execute, however the second identical query takes literally no computed time at all because it is serviced from the query cache. Checking MySQL's status counters that relate to the query cache confirm this, with the Qcache_inserts counter showing a value of 1 (indicating that the first query was loaded into the cache) and the Qcache_hits counter showing a value of 1 indicating one hit/match on an issued query/cached query combination.
The query cache works with raw SQL queries as demonstrated above and also works for queries issued within MySQL stored procedures:
mysql> delimiter //
mysql> create procedure test_query_cache()
-> begin
-> SELECT A.BROKER_ID,
-> A.BROKER_FIRST_NAME,
-> A.BROKER_LAST_NAME,
-> SUM(BROKER_COMMISSION) TOTAL_COMMISSIONS
-> FROM BROKER A,
-> CLIENT_TRANSACTION B
-> WHERE A.BROKER_ID = B.BROKER_ID
-> GROUP BY A.BROKER_ID,
-> A.BROKER_FIRST_NAME,
-> A.BROKER_LAST_NAME
-> ORDER BY 4 DESC
-> LIMIT 5;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> flush status;
mysql> reset query cache;
Query OK, 0 rows affected (0.00 sec)
mysql> call test_query_cache();
+-----------+-------------------+------------------+-------------------+
| BROKER_ID | BROKER_FIRST_NAME | BROKER_LAST_NAME | TOTAL_COMMISSIONS |
+-----------+-------------------+------------------+-------------------+
| 20 | STEVE | BOYCE | 3864173.64 |
| 1 | JONATHAN | MORTON | 1584621.39 |
| 13 | JIM | SANDERS | 1369157.73 |
| 4 | DAVE | TUCKER | 1214111.75 |
| 14 | DENISE | SCHWARTZ | 1041040.98 |
+-----------+-------------------+------------------+-------------------+
5 rows in set (0.11 sec)
mysql> call test_query_cache();
+-----------+-------------------+------------------+-------------------+
| BROKER_ID | BROKER_FIRST_NAME | BROKER_LAST_NAME | TOTAL_COMMISSIONS |
+-----------+-------------------+------------------+-------------------+
| 20 | STEVE | BOYCE | 3864173.64 |
| 1 | JONATHAN | MORTON | 1584621.39 |
| 13 | JIM | SANDERS | 1369157.73 |
| 4 | DAVE | TUCKER | 1214111.75 |
| 14 | DENISE | SCHWARTZ | 1041040.98 |
+-----------+-------------------+------------------+-------------------+
5 rows in set (0.00 sec)
mysql> show status like 'qc%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 49988976 |
| Qcache_hits | 1 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 5 |
+-------------------------+----------+
We first create a stored procedure that issues the same broker commission query we originally used to test the query cache, flush the MySQL status counters to reset the query cache statistics to zero, reset the query cache to remove all queries from the cache, then call the procedure twice to show that MySQL caches the query issued from within the procedure. Note that queries called from views, new in 5.0, work as well. However, SQL statements that utilize input parms from procedures will not make use of the query cache.
Managing the Query Cache
There are times when you may have to tweak the MySQL query cache to ensure optimal performance, so let's review some of the more important query cache-related status counters and configuration variables. To begin, the status counter Qcache_free_blocks indicates the contiguous nature of the memory assigned to the cache. High numbers can indicate fragmentation issues, which may be solved by issuing a FLUSH QUERY CACHE statement. Note that this command does not remove queries from the cache, but coalesces memory free space chunks.
The Qcache_free_memory counter provides insight into the cache's free memory. Low amounts observed vs. total allocated for the cache may indicate an undersized cache, which can be remedied by altering the global variable query_cache_size.
Qcache_hits and Qcache_inserts shows the number of times a query was serviced from the cache and how many queries have been inserted into the cache. Low ratios of hits to inserts indicate little query reuse or a too-low setting of the query_cache_limit, which serves to govern the RAM devoted to each individual query cache entry. Large query result sets will require larger settings of this variable.
Another indicator of poor query reuse is an increasing Qcache_lowmem_prunes value. This indicates how often MySQL had to remove queries from the cache to make use for incoming statements. Other reasons for an increasing number of Qcache_lowmem_prunes are an undersized cache, which can't hold the needed amount of SQL statements and result sets, and memory fragmentation in the cache which may be alleviated by issuing a FLUSH QUERY CACHE statement. You can remove all queries from the cache with the RESET QUERY CACHE command.
The Qcache_not_cached counter provides insight into the number of statements executed against MySQL that were not cacheable, due to either being a non-SELECT statement or being explicitly barred from entry with a SQL_NO_CACHE hint.
Other server variables that you can use to tweak the query cache are:
* query_alloc_block_size - the allocation size of the RAM blocks that are allocated for objects in cache.
* query_cache_min_res_unit - the minimum size for blocks allocated by the cache.
* query_cache_wlock_invalidate - causes the query cache to invalidate any query in the cache if an object it uses has a write lock executed against it.
* query_prealloc_size - the size of the persistent buffer used by the cache for parsing and execution. Complex queries necessitate larger settings.
Finally, in addition to setting query cache variables that globally govern its use, note that the query cache can be individually managed at the client level. For example, a client can turn off the query cache for their own queries by issuing the statement:
mysql> set session query_cache_type=0;
Prerequisites and Notes for MySQL Query Cache Use
Of course, there are prerequisites and limitations regarding MySQL query cache usage, with the most important being:
* Only identical queries may be serviced from the cache. This includes spacing, text case, etc.
* Any modification (DML, etc.) to a table used by a query in the cache causes the query to be invalidated and removed from the cache.
* Many functions, such as CURRENT_DATE, NOW, RAND and others, negate the use of the cache.
* No query that uses bind variables can be reused.
* No query that makes use of user defined functions can be cached.
For a complete up to date list of query cache limitations, see the MySQL manual.
Conclusion
The MySQL query cache is a unique caching strategy that is currently not utilized by other database engines, and one that can greatly enhance the performance of most any system that experiences high degrees of read activity. Because both physical and logical I/O activity is all but eliminated by the query cache, even systems that must bear the brunt of inefficient SQL statements can many times perform faster than those on other database platforms.
No comments:
Post a Comment