PlanetMysql.ru — информация о СУБД MySQL

I recently noticed both MariaDB and XtraDB (not MySQL yet) have a (newer) variable query_cache_strip_comments.

This variable is great for those who want to append comments to various queries, but still want the query cache to be able to serve such queries. Unfortunately, with MySQL, this is not currently possible.

In the past, I wrote a post on using MySQL Proxy which described a technique of monitoring queries through the proxy by appending IP addresses to the queries so one could track where they originated from. However, one pitfall to that was the MySQL query cache *does not* ignore the comment and treats them all as different queries (see the user comments for further discussion). (I did subsequently enhance that functionality implementing the SHOW PROXY PROCESSLIST command (often used in the Proxy Admin module), in large part because of this limitation.)

To enable it (in MariaDB 5.3+ and XtraDB), just add query_cache_strip_comments under the [mysqld] section in your my.cnf file and restart mysqld.

Alternatively, you can also set it dynamically:

mysql> set @@global.query_cache_strip_comments=1;
Query OK, 0 rows affected (0.04 sec)

mysql> show global variables like 'query_cache_strip_comments';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| query_cache_strip_comments | ON    |
+----------------------------+-------+

mysql> select version();
+---------------+
| version()     |
+---------------+
| 5.3.3-MariaDB |
+---------------+

Fwiw, from examining the source code, both implementations seemed to differ, but the end result is the same, and it’s a welcome addition, if you ask me.

PlanetMySQL Voting: Vote UP / Vote DOWN

Just how effective is the Query Cache on performance? If you are not comfortable reading the MySQL code you can cheat very easily with the SHOW PROFILE command. This demonstration will show you at a high level the relative impact without and with the Query Cache. First let us confirm the Query Cache is not used.

mysql> SHOW GLOBAL 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     |
+------------------------------+---------+
5 rows in set (0.00 sec)

We now enable profiling.

mysql> SET PROFILING=1;

We run our sample query.

mysql> select name,id,competitions from olympic_games where host_city='Sydney'\G

We can then enable the query cache and re-run the query.

mysql> SET GLOBAL query_cache_size=1024*1024*16;
mysql> select name,id,competitions from olympic_games where host_city='Sydney'\G
mysql> select name,id,competitions from olympic_games where host_city='Sydney'\G

NOTE: We run the query twice after enabling the cache, the first time, the query is cached, the second time it is retrieved from the cache. Now let us look at the profiling information.

mysql> SHOW PROFILES;
+----------+------------+-------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                   |
+----------+------------+-------------------------------------------------------------------------+
|        1 | 0.00096100 | select name,id,competitions from olympic_games where host_city='Sydney' |
|        2 | 0.00029700 | SET GLOBAL query_cache_size=1024*1024*16                                |
|        3 | 0.00837900 | select name,id,competitions from olympic_games where host_city='Sydney' |
|        4 | 0.00009500 | select name,id,competitions from olympic_games where host_city='Sydney' |
+----------+------------+-------------------------------------------------------------------------+

mysql> SHOW PROFILE SOURCE FOR QUERY 3;
+--------------------------------+----------+---------------------------+---------------+-------------+
| Status                         | Duration | Source_function           | Source_file   | Source_line |
+--------------------------------+----------+---------------------------+---------------+-------------+
| starting                       | 0.000033 | NULL                      | NULL          |        NULL |
| checking query cache for query | 0.000088 | send_result_to_client     | sql_cache.cc  |        1262 |
| Opening tables                 | 0.000025 | open_tables               | sql_base.cc   |        4482 |
| System lock                    | 0.000006 | mysql_lock_tables         | lock.cc       |         258 |
| Table lock                     | 0.000081 | mysql_lock_tables         | lock.cc       |         269 |
| init                           | 0.000037 | mysql_select              | sql_select.cc |        2350 |
| optimizing                     | 0.000016 | optimize                  | sql_select.cc |         772 |
| statistics                     | 0.000021 | optimize                  | sql_select.cc |         954 |
| preparing                      | 0.000021 | optimize                  | sql_select.cc |         964 |
| executing                      | 0.000005 | exec                      | sql_select.cc |        1648 |
| Sending data                   | 0.000500 | exec                      | sql_select.cc |        2190 |
| end                            | 0.000008 | mysql_select              | sql_select.cc |        2395 |
| query end                      | 0.000005 | mysql_execute_command     | sql_parse.cc  |        4821 |
| freeing items                  | 0.007489 | mysql_parse               | sql_parse.cc  |        5827 |
| storing result in query cache  | 0.000028 | query_cache_end_of_result | sql_cache.cc  |         813 |
| logging slow query             | 0.000007 | log_slow_statement        | sql_parse.cc  |        1628 |
| cleaning up                    | 0.000009 | dispatch_command          | sql_parse.cc  |        1595 |
+--------------------------------+----------+---------------------------+---------------+-------------+
17 rows in set (0.00 sec)

mysql> SHOW PROFILE SOURCE FOR QUERY 4;
+--------------------------------+----------+-----------------------+--------------+-------------+
| Status                         | Duration | Source_function       | Source_file  | Source_line |
+--------------------------------+----------+-----------------------+--------------+-------------+
| starting                       | 0.000035 | NULL                  | NULL         |        NULL |
| checking query cache for query | 0.000014 | send_result_to_client | sql_cache.cc |        1262 |
| checking privileges on cached  | 0.000010 | send_result_to_client | sql_cache.cc |        1346 |
| sending cached result to clien | 0.000026 | send_result_to_client | sql_cache.cc |        1441 |
| logging slow query             | 0.000005 | log_slow_statement    | sql_parse.cc |        1628 |
| cleaning up                    | 0.000005 | dispatch_command      | sql_parse.cc |        1595 |
+--------------------------------+----------+-----------------------+--------------+-------------+
6 rows in set (0.00 sec)

It does not take a rocket scientist to determine that 6 steps within the MySQL kernel is better then 17, regardless of what those steps are, and how different in timing they may be.

I’m not wanting to represent how much saving you may have here, there are many factors such as a realistic example, a loaded warmed up environment etc. You should try this in your own environment with your own queries.

This information was to provide an introduction into looking a little deeper at the Query Cache path within MySQL.

PlanetMySQL Voting: Vote UP / Vote DOWN

Maximize your strengths, minimize your weaknesses.

You can apply this approach to many things in life, I apply it to describing and using MySQL the product, and it’s components. The Query Cache like many features in MySQL, and indeed features in many different RDBMS products (don’t get me started on Oracle *features*) have relative benefits. In one context it can be seen as ineffective, or even detrimental to your performance, however it’s course grain nature makes it both trivial to disable dynamically (SET GLOBAL query_cache_size=0;), and also easy to get basic statistics on current performance (SHOW GLOBAL STATUS LIKE ‘QCache%’;) to determine effectiveness and action appropriately.

The Query Cache is course grained, that is it is rather simple/dumb in nature. When you understand the path of execution of a query within the MySQL kernel you learn a few key things.

• When enabled, by default the Query Cache will cache all SELECT statements within certain defined system parameter conditions. There are of course exceptions such as non-deterministic functions, prepared statements in earlier versions etc.
• Any DML/DDL statement for a table that has a query cached, flushes all query cache results that pertain to this table.
• You can use SQL_CACHE and SQL_NO_CACHE as hints however you can’t configure on a table by table, or query basis.
• The query cache works on an exact match of the query (including spaces and case) and other settings such the client character set, and protocol version. If a match is found, data is returned in preformed network packets.<.li>

The Query Cache was not good when set to large values (e.g. > 128M) due to in-efficient cache invalidation. I’m not certain of the original source of this condition however Bug #21074, fixed in 5.0.50 and 5.1.21 is likely the reason.

My advice is to disable the Query Cache by default, especially for testing. As a final stress test you can enable to determine if there is a benefit.

I wish MySQL would spend time in improving key features, for example the Query Cache lacks sufficient instrumentation like what queries are in the cache, what tables are in the cache, and also lack all the sufficient system parameters exposed to fine tune. I believe there is a patch to show the queries for example, but I was unable to find via a google search.

It is a powerful and easy technology if you use it well. It involves architecting your solution appropriately, and knowing when the Query Cache is ineffective.

I have a number of circumstances where the query cache is extremely effective, or could be with simple modifications. A recommendation to a recent client with a 1+TB database was to split historical and current data into two different instances. The data was already in separated tables, the application already performed dual queries, so the change was a simple as a new connection pool. The benefits were huge, not only would the backup process be more efficient, some 500GB of data now only had to be backed up once (as is was 100% static), the scaling and recovery process improved, but the second MySQL instance could enable the query cache and the application would get a huge performance improvement with ZERO code changes for caching. That’s a quick and easy win.

On a side note, I wanted to title this “The MySQL Query Cache is not useless”. When I was a MySQL employee I got reprimanded (twice) for blogging anything about MySQL that wasn’t positive. This blog post is in direct response to Konstantin, a Sun/MySQL employee who actually works on the actually MySQL server code who wrote Query cache = useless?. In my view it is not useless.

PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL's query cache is somewhat unique and can be very beneficial to certain applications, such as web apps that run the same queries over and over. If you're unfamiliar with the query cache, here are some key points:

• The query cache holds SELECT statements and their result sets. By "SELECT statements" I mean the entire query is considered 1 entry in the cache. Subselects or queries that are part of a UNION are not cached individually.
  
  
• This cache is optional. In fact it is off by default in most MySQL binaries. Although query_cache_type might be set to "ON", if query_cache_size=0 then the cache is effectively off.
  
  
• This is not the only cache MySQL has. The storage engines often have lower level caches (e.g.,InnoDB caches data and index pages in its cache configured with setting innodb_buffer_pool_size and MyISAM uses key_buffer_size for caching index pages)
  
  
• When enabled, the query cache is before the parser. This means that in order for your query to be a cache "hit", it must match a cached query exactly (e.g., it is case-sensitive and even white space matters).
  
  
• The cache will never serve stale data (unless you are running multiple mysqlds pointing to the same data directory - in that case do not use the query cache). So queries need to be invalidated when data changes. In order to make this fast and easy, it's done on a table-by-table basis; when a table changes, all queries that used that table are removed.
  
  
• Since the query cache is before the parser, you might wonder how authorization is done. When the query was originally parsed and inserted in the cache, MySQL figured out which table(s) were used by the query (which is useful for invalidating queries, see above). By knowing the tables involved, a user's privileges can be checked. However, if a user has any column-level privileges on those tables, the query cache is not useful. I tend to avoid column-level privileges (since 5.0 you can use views instead).
  
  
• Do not set the query cache too large (I'd say no more than 256MB) because it becomes less efficient.
  
  
• Monitor its effectiveness: Use SHOW GLOBAL STATUS to look at qcache_hits vs. com_select (query cache misses). Also, if you have Qcache_lowmen_prunes, you may want to increase the query_cache_size (though heed the earlier advice about not setting it too large). Lots of Qcache_inserts may be an indicator that queries are being invalidated and re-cached frequently.
  
  
• MySQL Cluster can use the query cache, however, each MySQL server has its own query cache. Queries will be invalidated if data in the cluster changes. To incur less overhead, you can configure ndb_cache_check_time which tell NDB how often to check the cache for invalidation.
  

PlanetMySQL Voting: Vote UP / Vote DOWN