| List: | Internals | « Previous MessageNext Message » | |
| From: | paul | Date: | March 23 2004 5:19pm |
| Subject: | bk commit - mysqldoc tree (paul:1.1335) | ||
| View as plain text | |||
Below is the list of changes that have just been committed into a local mysqldoc repository of paul. When paul does a push these changes will be propagated to the main repository and, within 24 hours after the push, to the public repository. For information on how to access the public repository see http://www.mysql.com/doc/I/n/Installing_source_tree.html ChangeSet 1.1335 04/03/23 10:19:50 paul@stripped +1 -0 Move query cache stuff out of SQL chapter into db admin chapter. Docs/manual.texi 1.1431 04/03/23 10:19:48 paul@stripped +351 -351 Move query cache stuff out of SQL chapter into db admin chapter. # This is a BitKeeper patch. What follows are the unified diffs for the # set of deltas contained in the patch. The rest of the patch, the part # that BitKeeper cares about, is below these diffs. # User: paul # Host: teton.kitebird.com # Root: /home/paul/mysqldoc --- 1.1430/Docs/manual.texi Tue Mar 23 08:40:11 2004 +++ 1.1431/Docs/manual.texi Tue Mar 23 10:19:48 2004 @@ -16999,6 +16999,7 @@ * Localisation:: MySQL Localization and International Usage * Log Files:: The MySQL Log Files * Multiple servers:: Running Multiple MySQL Servers on the Same Machine +* Query Cache:: The MySQL Query Cache @end menu @@ -26292,7 +26293,7 @@ Then make a backup and remove @file{mysql.old}. -@node Multiple servers, , Log Files, MySQL Database Administration +@node Multiple servers, Query Cache, Log Files, MySQL Database Administration @section Running Multiple MySQL Servers on the Same Machine @cindex post-install, multiple servers @@ -26858,6 +26859,354 @@ @end itemize +@node Query Cache, , Multiple servers, MySQL Database Administration +@section The MySQL Query Cache + +@cindex Query Cache +@cindex @code{SELECT}, Query Cache + +From version 4.0.1 on, @code{MySQL Server} features a @code{Query Cache}. +When in use, the query cache stores the text of a @code{SELECT} query +together with the corresponding result that was sent to the client. +If the identical query is received later, the server retrieves +the results from the query cache rather than parsing and executing the +query again. + +@strong{Note:} The query cache does not return stale data. When data +is modified, any relevant entries in the query cache are flushed. + +The query cache is extremely useful in an environment where (some) +tables don't change very often and you have a lot of identical queries. +This is a typical situation for many Web servers that generate a lot of +dynamic pages based on database content. + +Some performance data for the query cache follow. These results were +generated by running the MySQL benchmark suite on a Linux Alpha +2 x 500 MHz with 2GB RAM and a 64MB query cache. + +@itemize @bullet + +@item +If all of the queries you're performing are simple (such as selecting a row +from a table with one row), but still differ so that the queries cannot be +cached, the overhead for having the query cache active is 13%. This could +be regarded as the worst case scenario. In real life, queries tend to be +much more complicated, so the overhead is normally significantly lower. + +@item +Searches for a single row in a single-row table are 238% faster with the +query cache than without it. This can be regarded as close to the minimum +speedup to be expected for a query that is cached. + +@end itemize + +To disable the query cache at server startup, set the +@code{query_cache_size} system variable to 0. By disabling the query cache +code there is no noticeable overhead. Query cache capabilities can be +excluded from the server entirely by using the @code{--without-query-cache} +option to @code{configure} when compiling MySQL. + +@menu +* Query Cache How:: How the Query Cache Operates +* Query Cache in SELECT:: Query Cache @code{SELECT} Options +* Query Cache Configuration:: Query Cache Configuration +* Query Cache Status and Maintenance:: Query Cache Status and Maintenance +@end menu + + +@node Query Cache How, Query Cache in SELECT, Query Cache, Query Cache +@subsection How the Query Cache Operates + +This section describes how the query cache works when it is operational. +@ref{Query Cache Configuration} describes how to control whether or not it +is operational. + +Queries are compared before parsing, so the following two queries are +regarded as different by the query cache: + +@example +SELECT * FROM tbl_name +Select * from tbl_name +@end example + +Queries must +be exactly the same (byte for byte) to be seen as identical. +In addition, query strings that are identical may be treated as different for +otther reasons. +Queries that uses different databases, uses different protocol versions +or different default character sets are considered different +queries and are cached separately. + +If a query result is returned from query cache, the server increments the +@code{Qcache_hits} status variable, not @code{Com_select}. +@xref{Query Cache Status and Maintenance}. + +If a table changes then all cached queries that used the table become +invalid and are removed from the cache. This includes queries that use +@code{MERGE} tables that map to the changed table. A table can be changed +by many types of statements, such as @code{INSERT}, @code{UPDATE}, +@code{DELETE}, @code{TRUNCATE}, @code{ALTER TABLE}, @code{DROP TABLE}, or +@code{DROP DATABASE}. + +Transactional @code{InnoDB} tables that have been changed are invalidated +when a @code{COMMIT} is performed. + +In MySQL 4.0, the query cache is disabled within transactions (it does +not return results). Beginning with MySQL 4.1.1, the query cache also +works within transactions when using @code{InnoDB} tables (it uses the +table version number to detect whether or not its contents are still current). + +Before MySQL 5.0, a query that begins with a leading comment might be cached, +but could not be fetched from the cache. This problem is fixed in MySQL 5.0. + +A query cannot be cached if it contains any of the following functions: + +@multitable @columnfractions .33 .33 .34 +@item @strong{Function} + @tab @strong{Function} + @tab @strong{Function} +@item @code{BENCHMARK()} + @tab @code{CONNECTION_ID()} + @tab @code{CURDATE()} +@item @code{CURRENT_DATE()} + @tab @code{CURRENT_TIME()} + @tab @code{CURRENT_TIMESTAMP()} +@item @code{CURTIME()} + @tab @code{DATABASE()} + @tab @code{ENCRYPT()} with one parameter +@item @code{FOUND_ROWS()} + @tab @code{GET_LOCK()} + @tab @code{LAST_INSERT_ID()} +@item @code{LOAD_FILE()} + @tab @code{MASTER_POS_WAIT()} + @tab @code{NOW()} +@item @code{RAND()} + @tab @code{RELEASE_LOCK()} + @tab @code{SYSDATE()} +@item @code{UNIX_TIMESTAMP()} with no parameters + @tab @code{USER()} +@end multitable + +A query also will not be cached under these conditions: + +@itemize @bullet +@item +It contains user-defined functions (UDFs). + +@item +It contains user variables. + +@item +It refers to the tables in the @code{mysql} system database. + +@item +It is of any of the following forms: + +@example +SELECT ... IN SHARE MODE +SELECT ... INTO OUTFILE ... +SELECT ... INTO DUMPFILE ... +SELECT * FROM ... WHERE autoincrement_col IS NULL +@end example + +The last form is not cached because it is used as the ODBC workaround for +obtaining the last insert ID value. +@xref{ODBC and last_insert_id}. + +@item +It uses @code{TEMPORARY} tables. + +@item +It does not use any tables. + +@item +The user has a column privilege for any of the involved tables. + +Before a query is fetched from the query cache, MySQL checks that +the user has @code{SELECT} privilege for all the involved databases and +tables. If this is not the case, the cached result will not be used. + +@end itemize + +The query cache does work for @code{SELECT SQL_CALC_FOUND_ROWS ...} and +@code{SELECT FOUND_ROWS()} type queries. @code{FOUND_ROWS()} returns +the correct value even if the preceding query was fetched from the cache +because the number of found rows is also stored in the cache. + + +@node Query Cache in SELECT, Query Cache Configuration, Query Cache How, Query Cache +@subsection Query Cache @code{SELECT} Options + +There are two query cache related options that may be +specified in a @code{SELECT} statement: + +@findex SQL_CACHE +@findex SQL_NO_CACHE + +@table @code + +@item SQL_CACHE +The query result is cached if the value of the @code{query_cache_type} +system variable is @code{ON} or @code{DEMAND}. + +@item SQL_NO_CACHE +The query result is not cached. + +@end table + + +@node Query Cache Configuration, Query Cache Status and Maintenance, Query Cache in SELECT, Query Cache +@subsection Query Cache Configuration + +Several @code{mysqld} system variables control query cache operation. +These can be set in an option file or on the command line +when starting @code{mysqld}. +The query cache-related system variables all have names that begin with +@code{query_cache_}. They are described briefly in @ref{Server system +variables}, with additional configuration information given here. + +To set the size of the query cache, set the @code{query_cache_size} system +variable. Setting it to 0 disables the query cache. The default cache size +is 0; that is, the query cache is disabled. + +Assuming the query cache is enabled, the @code{query_cache_type} variable +influences how it works. This variable can be set to the following values: + +@itemize @bullet +@item +A value of @code{0} or @code{OFF} prevents caching or retrieval of cached +results. + +@item +A value of @code{1} or @code{ON} allows caching except of those statements +that begin with @code{SELECT SQL_NO_CACHE}. + +@item +A value of @code{2} or @code{DEMAND} causes caching only of those statements +that begin with @code{SELECT SQL_CACHE}. + +@end itemize + +Setting the @code{GLOBAL} @code{query_cache_type} 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 +@code{SESSION} value of @code{query_cache_type}. For example, a client +can disable use of the query cache for its own queries like this: + +@example +mysql> SET SESSION query_cache_type = OFF; +@end example + +To control the maximum size of individual query results that can be cached, +set the @code{query_cache_limit} variable. The default value is 1MB. + +The result of a query (the data sent to the client) is stored in the query +cache during result retrieval. Therefore the data is usually not handled in +one big chunk. The query cache allocates blocks for storing this data on +demand, so when one block is filled, a new block is allocated. Because +memory allocation operation is costly (time wise), the query cache allocates +blocks with a minimum size given by the @code{query_cache_min_res_unit} +system variable. When a query is executed, the last result block is trimmed +to the actual data size so that unused memory is freed. +Depending on the types of queries your server executes, you might find it +helpful to tune the value of @code{query_cache_min_res_unit}: + +@itemize @bullet + +@item +The default value of @code{query_cache_min_res_unit} is 4KB. This should +be adequate for most cases. + +@item +If you have a lot of queries with small results, the default block size may +lead to memory fragmentation, as indicated by a large number of free blocks. +Fragmentation can cause the query cache to have to delete queries from the +cache due to lack of memory. In this case, you should decrease the value of +@code{query_cache_min_res_unit}. The number of free blocks and queries +removed due to pruning are given by the values of the +@code{Qcache_free_blocks} and @code{Qcache_lowmem_prunes} status variables. + +@item +If you most of your queries have large results (check the +@code{Qcache_total_blocks} and @code{Qcache_queries_in_cache} status +variables), you can increase performance by increasing +@code{query_cache_min_res_unit}. However, be careful to not make it too +large (see the previous item). + +@end itemize + +@code{query_cache_min_res_unit} is present from MySQL 4.1. + + +@node Query Cache Status and Maintenance, , Query Cache Configuration, Query Cache +@subsection Query Cache Status and Maintenance + +@c help_category Administration + +You can check whether the query cache is present in your MySQL server using +the following statement:: + +@example +mysql> SHOW VARIABLES LIKE 'have_query_cache'; ++------------------+-------+ +| Variable_name | Value | ++------------------+-------+ +| have_query_cache | YES | ++------------------+-------+ +@end example + +@c description_for_help_topic FLUSH QUERY CACHE CACHE QUERY FLUSH RESET +You can defragment the query cache to better utilize its memory +with the @code{FLUSH QUERY CACHE} statement. +The statement does not remove any queries from the cache. +The @code{FLUSH TABLES} statement also flushes the query cache. + +The @code{RESET QUERY CACHE} statement removes all query results from the +query cache. +@c end_description_for_help_topic + +To monitor query cache performance, use @code{SHOW STATUS} to view +the cache status variables: + +@example +mysql> SHOW STATUS LIKE 'Qcache%'; ++-------------------------+--------+ +| Variable_name | Value | ++-------------------------+--------+ +| Qcache_free_blocks | 36 | +| Qcache_free_memory | 138488 | +| Qcache_hits | 79570 | +| Qcache_inserts | 27087 | +| Qcache_lowmem_prunes | 3114 | +| Qcache_not_cached | 22989 | +| Qcache_queries_in_cache | 415 | +| Qcache_total_blocks | 912 | ++-------------------------+--------+ +@end example + +Descriptions of each of these variables are given in +@ref{Server status variables}. Some uses for them are described here. + +Total number of queries = +@code{Qcache_inserts} + @code{Qcache_hits} + @code{Qcache_not_cached}. + +The query cache uses variable-length blocks, so @code{Qcache_total_blocks} +and @code{Qcache_free_blocks} may indicate query cache memory fragmentation. +After @code{FLUSH QUERY CACHE} only a single free block remains. + +Note: Every cached query requires a minimum of two blocks (one for the query +text and one or more for the query results). Also, every table that is used +by a query requires one block. However, if two or more queries use the same +table, only one block needs to be allocated. + +The information provided by the @code{Qcache_lowmem_prunes} status variable +can help you tune the query cache size. It counts the number of queries that +have been removed from the cache to free up memory for caching new queries. +The query cache uses a least recently used (LRU) strategy to decide which +queries to remove from the cache. Tuning information is given in @ref{Query +Cache Configuration}. + + @node Replication, MySQL Optimisation, MySQL Database Administration, Top @chapter Replication in MySQL @@ -44703,7 +45052,6 @@ * Database Administration:: Database Administration Statements * Replication SQL:: Replication Statements * Fulltext Search:: MySQL Full-text Search -* Query Cache:: The MySQL Query Cache @end menu @node Data Manipulation, Data Definition, SQL Syntax, SQL Syntax @section Data Manipulation Statements @@ -53251,7 +53599,7 @@ @c TODO: change node name to use "Full-text"; by convention, we use a dash -@node Fulltext Search, Query Cache, Replication SQL, SQL Syntax +@node Fulltext Search, , Replication SQL, SQL Syntax @section MySQL Full-text Search @cindex searching, full-text @@ -53788,354 +54136,6 @@ parameters to @code{FULLTEXT} in @code{CREATE TABLE} and @code{ALTER TABLE} statements). @end itemize - - -@node Query Cache, , Fulltext Search, SQL Syntax -@section The MySQL Query Cache - -@cindex Query Cache -@cindex @code{SELECT}, Query Cache - -From version 4.0.1 on, @code{MySQL Server} features a @code{Query Cache}. -When in use, the query cache stores the text of a @code{SELECT} query -together with the corresponding result that was sent to the client. -If the identical query is received later, the server retrieves -the results from the query cache rather than parsing and executing the -query again. - -@strong{Note:} The query cache does not return stale data. When data -is modified, any relevant entries in the query cache are flushed. - -The query cache is extremely useful in an environment where (some) -tables don't change very often and you have a lot of identical queries. -This is a typical situation for many Web servers that generate a lot of -dynamic pages based on database content. - -Some performance data for the query cache follow. These results were -generated by running the MySQL benchmark suite on a Linux Alpha -2 x 500 MHz with 2GB RAM and a 64MB query cache. - -@itemize @bullet - -@item -If all of the queries you're performing are simple (such as selecting a row -from a table with one row), but still differ so that the queries cannot be -cached, the overhead for having the query cache active is 13%. This could -be regarded as the worst case scenario. In real life, queries tend to be -much more complicated, so the overhead is normally significantly lower. - -@item -Searches for a single row in a single-row table are 238% faster with the -query cache than without it. This can be regarded as close to the minimum -speedup to be expected for a query that is cached. - -@end itemize - -To disable the query cache at server startup, set the -@code{query_cache_size} system variable to 0. By disabling the query cache -code there is no noticeable overhead. Query cache capabilities can be -excluded from the server entirely by using the @code{--without-query-cache} -option to @code{configure} when compiling MySQL. - -@menu -* Query Cache How:: How the Query Cache Operates -* Query Cache in SELECT:: Query Cache @code{SELECT} Options -* Query Cache Configuration:: Query Cache Configuration -* Query Cache Status and Maintenance:: Query Cache Status and Maintenance -@end menu - - -@node Query Cache How, Query Cache in SELECT, Query Cache, Query Cache -@subsection How the Query Cache Operates - -This section describes how the query cache works when it is operational. -@ref{Query Cache Configuration} describes how to control whether or not it -is operational. - -Queries are compared before parsing, so the following two queries are -regarded as different by the query cache: - -@example -SELECT * FROM tbl_name -Select * from tbl_name -@end example - -Queries must -be exactly the same (byte for byte) to be seen as identical. -In addition, query strings that are identical may be treated as different for -otther reasons. -Queries that uses different databases, uses different protocol versions -or different default character sets are considered different -queries and are cached separately. - -If a query result is returned from query cache, the server increments the -@code{Qcache_hits} status variable, not @code{Com_select}. -@xref{Query Cache Status and Maintenance}. - -If a table changes then all cached queries that used the table become -invalid and are removed from the cache. This includes queries that use -@code{MERGE} tables that map to the changed table. A table can be changed -by many types of statements, such as @code{INSERT}, @code{UPDATE}, -@code{DELETE}, @code{TRUNCATE}, @code{ALTER TABLE}, @code{DROP TABLE}, or -@code{DROP DATABASE}. - -Transactional @code{InnoDB} tables that have been changed are invalidated -when a @code{COMMIT} is performed. - -In MySQL 4.0, the query cache is disabled within transactions (it does -not return results). Beginning with MySQL 4.1.1, the query cache also -works within transactions when using @code{InnoDB} tables (it uses the -table version number to detect whether or not its contents are still current). - -Before MySQL 5.0, a query that begins with a leading comment might be cached, -but could not be fetched from the cache. This problem is fixed in MySQL 5.0. - -A query cannot be cached if it contains any of the following functions: - -@multitable @columnfractions .33 .33 .34 -@item @strong{Function} - @tab @strong{Function} - @tab @strong{Function} -@item @code{BENCHMARK()} - @tab @code{CONNECTION_ID()} - @tab @code{CURDATE()} -@item @code{CURRENT_DATE()} - @tab @code{CURRENT_TIME()} - @tab @code{CURRENT_TIMESTAMP()} -@item @code{CURTIME()} - @tab @code{DATABASE()} - @tab @code{ENCRYPT()} with one parameter -@item @code{FOUND_ROWS()} - @tab @code{GET_LOCK()} - @tab @code{LAST_INSERT_ID()} -@item @code{LOAD_FILE()} - @tab @code{MASTER_POS_WAIT()} - @tab @code{NOW()} -@item @code{RAND()} - @tab @code{RELEASE_LOCK()} - @tab @code{SYSDATE()} -@item @code{UNIX_TIMESTAMP()} with no parameters - @tab @code{USER()} -@end multitable - -A query also will not be cached under these conditions: - -@itemize @bullet -@item -It contains user-defined functions (UDFs). - -@item -It contains user variables. - -@item -It refers to the tables in the @code{mysql} system database. - -@item -It is of any of the following forms: - -@example -SELECT ... IN SHARE MODE -SELECT ... INTO OUTFILE ... -SELECT ... INTO DUMPFILE ... -SELECT * FROM ... WHERE autoincrement_col IS NULL -@end example - -The last form is not cached because it is used as the ODBC workaround for -obtaining the last insert ID value. -@xref{ODBC and last_insert_id}. - -@item -It uses @code{TEMPORARY} tables. - -@item -It does not use any tables. - -@item -The user has a column privilege for any of the involved tables. - -Before a query is fetched from the query cache, MySQL checks that -the user has @code{SELECT} privilege for all the involved databases and -tables. If this is not the case, the cached result will not be used. - -@end itemize - -The query cache does work for @code{SELECT SQL_CALC_FOUND_ROWS ...} and -@code{SELECT FOUND_ROWS()} type queries. @code{FOUND_ROWS()} returns -the correct value even if the preceding query was fetched from the cache -because the number of found rows is also stored in the cache. - - -@node Query Cache in SELECT, Query Cache Configuration, Query Cache How, Query Cache -@subsection Query Cache @code{SELECT} Options - -There are two query cache related options that may be -specified in a @code{SELECT} statement: - -@findex SQL_CACHE -@findex SQL_NO_CACHE - -@table @code - -@item SQL_CACHE -The query result is cached if the value of the @code{query_cache_type} -system variable is @code{ON} or @code{DEMAND}. - -@item SQL_NO_CACHE -The query result is not cached. - -@end table - - -@node Query Cache Configuration, Query Cache Status and Maintenance, Query Cache in SELECT, Query Cache -@subsection Query Cache Configuration - -Several @code{mysqld} system variables control query cache operation. -These can be set in an option file or on the command line -when starting @code{mysqld}. -The query cache-related system variables all have names that begin with -@code{query_cache_}. They are described briefly in @ref{Server system -variables}, with additional configuration information given here. - -To set the size of the query cache, set the @code{query_cache_size} system -variable. Setting it to 0 disables the query cache. The default cache size -is 0; that is, the query cache is disabled. - -Assuming the query cache is enabled, the @code{query_cache_type} variable -influences how it works. This variable can be set to the following values: - -@itemize @bullet -@item -A value of @code{0} or @code{OFF} prevents caching or retrieval of cached -results. - -@item -A value of @code{1} or @code{ON} allows caching except of those statements -that begin with @code{SELECT SQL_NO_CACHE}. - -@item -A value of @code{2} or @code{DEMAND} causes caching only of those statements -that begin with @code{SELECT SQL_CACHE}. - -@end itemize - -Setting the @code{GLOBAL} @code{query_cache_type} 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 -@code{SESSION} value of @code{query_cache_type}. For example, a client -can disable use of the query cache for its own queries like this: - -@example -mysql> SET SESSION query_cache_type = OFF; -@end example - -To control the maximum size of individual query results that can be cached, -set the @code{query_cache_limit} variable. The default value is 1MB. - -The result of a query (the data sent to the client) is stored in the query -cache during result retrieval. Therefore the data is usually not handled in -one big chunk. The query cache allocates blocks for storing this data on -demand, so when one block is filled, a new block is allocated. Because -memory allocation operation is costly (time wise), the query cache allocates -blocks with a minimum size given by the @code{query_cache_min_res_unit} -system variable. When a query is executed, the last result block is trimmed -to the actual data size so that unused memory is freed. -Depending on the types of queries your server executes, you might find it -helpful to tune the value of @code{query_cache_min_res_unit}: - -@itemize @bullet - -@item -The default value of @code{query_cache_min_res_unit} is 4KB. This should -be adequate for most cases. - -@item -If you have a lot of queries with small results, the default block size may -lead to memory fragmentation, as indicated by a large number of free blocks. -Fragmentation can cause the query cache to have to delete queries from the -cache due to lack of memory. In this case, you should decrease the value of -@code{query_cache_min_res_unit}. The number of free blocks and queries -removed due to pruning are given by the values of the -@code{Qcache_free_blocks} and @code{Qcache_lowmem_prunes} status variables. - -@item -If you most of your queries have large results (check the -@code{Qcache_total_blocks} and @code{Qcache_queries_in_cache} status -variables), you can increase performance by increasing -@code{query_cache_min_res_unit}. However, be careful to not make it too -large (see the previous item). - -@end itemize - -@code{query_cache_min_res_unit} is present from MySQL 4.1. - - -@node Query Cache Status and Maintenance, , Query Cache Configuration, Query Cache -@subsection Query Cache Status and Maintenance - -@c help_category Administration - -You can check whether the query cache is present in your MySQL server using -the following statement:: - -@example -mysql> SHOW VARIABLES LIKE 'have_query_cache'; -+------------------+-------+ -| Variable_name | Value | -+------------------+-------+ -| have_query_cache | YES | -+------------------+-------+ -@end example - -@c description_for_help_topic FLUSH QUERY CACHE CACHE QUERY FLUSH RESET -You can defragment the query cache to better utilize its memory -with the @code{FLUSH QUERY CACHE} statement. -The statement does not remove any queries from the cache. -The @code{FLUSH TABLES} statement also flushes the query cache. - -The @code{RESET QUERY CACHE} statement removes all query results from the -query cache. -@c end_description_for_help_topic - -To monitor query cache performance, use @code{SHOW STATUS} to view -the cache status variables: - -@example -mysql> SHOW STATUS LIKE 'Qcache%'; -+-------------------------+--------+ -| Variable_name | Value | -+-------------------------+--------+ -| Qcache_free_blocks | 36 | -| Qcache_free_memory | 138488 | -| Qcache_hits | 79570 | -| Qcache_inserts | 27087 | -| Qcache_lowmem_prunes | 3114 | -| Qcache_not_cached | 22989 | -| Qcache_queries_in_cache | 415 | -| Qcache_total_blocks | 912 | -+-------------------------+--------+ -@end example - -Descriptions of each of these variables are given in -@ref{Server status variables}. Some uses for them are described here. - -Total number of queries = -@code{Qcache_inserts} + @code{Qcache_hits} + @code{Qcache_not_cached}. - -The query cache uses variable-length blocks, so @code{Qcache_total_blocks} -and @code{Qcache_free_blocks} may indicate query cache memory fragmentation. -After @code{FLUSH QUERY CACHE} only a single free block remains. - -Note: Every cached query requires a minimum of two blocks (one for the query -text and one or more for the query results). Also, every table that is used -by a query requires one block. However, if two or more queries use the same -table, only one block needs to be allocated. - -The information provided by the @code{Qcache_lowmem_prunes} status variable -can help you tune the query cache size. It counts the number of queries that -have been removed from the cache to free up memory for caching new queries. -The query cache uses a least recently used (LRU) strategy to decide which -queries to remove from the cache. Tuning information is given in @ref{Query -Cache Configuration}. @node Table types, InnoDB, SQL Syntax, Top
| Thread | ||
|---|---|---|
| • bk commit - mysqldoc tree (paul:1.1335) | paul | 23 Mar |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 0.675 sec. using MySQL 5.1.14-beta-log