Query cache and comments

Update

Ok, as Morgan quickly found out: I'm incredibly stupid. Read his comment and you'll know why. Ok, you'll not know why but you'll know that I am.

Really cool to see Chris taking up blogging as well :)

He has written nice little example about inserting comments into queries to distinguish the client’s IP when they are funneled through the proxy. Reading the comments about this little trick making the query cache not work, I couldn’t help thinking that those are wrong. I vaguely remembered that in some recent version this shortcoming was fixed, so I decided to run a little test on 5.1.30 to verify:

mysql> select concat(@@version_comment, ' ', @@version);
+-------------------------------------------+
| concat(@@version_comment, ' ', @@version) |
+-------------------------------------------+
| MySQL Community Server (GPL) 5.1.30-log   | 
+-------------------------------------------+

mysql> show status like 'Qcache_%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        | 
| Qcache_free_memory      | 16768384 | 
| Qcache_hits             | 0        | 
| Qcache_inserts          | 0        | 
| Qcache_lowmem_prunes    | 0        | 
| Qcache_not_cached       | 0        | 
| Qcache_queries_in_cache | 0        | 
| Qcache_total_blocks     | 1        | 
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> use test;
Database changed

mysql> create table a (a int);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into a values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show status like 'Qcache_%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        | 
| Qcache_free_memory      | 16768384 | 
| Qcache_hits             | 0        | 
| Qcache_inserts          | 0        | 
| Qcache_lowmem_prunes    | 0        | 
| Qcache_not_cached       | 0        | 
| Qcache_queries_in_cache | 0        | 
| Qcache_total_blocks     | 1        | 
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> select \* from a;
+------+
| a    |
+------+
|    1 | 
|    2 | 
+------+
2 rows in set (0.00 sec)

mysql> show status like 'Qcache_queries_in_cache';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_queries_in_cache | 1        | 
+-------------------------+----------+
1 rows in set (0.00 sec)

So far, so good. Query cache is enabled, I’ve got some basic data in it, and am ready to test my frail memory. Here we go:

mysql> /\* 127.0.0.1:11251 \*/ select \* from a;
+------+
| a    |
+------+
|    1 | 
|    2 | 
+------+
2 rows in set (0.00 sec)

Drumroll….

mysql> show status like 'Qcache_%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        | 
| Qcache_free_memory      | 16766848 | 
| Qcache_hits             | 1        | 
| Qcache_inserts          | 1        | 
| Qcache_lowmem_prunes    | 0        | 
| Qcache_not_cached       | 0        | 
| Qcache_queries_in_cache | 1        | 
| Qcache_total_blocks     | 4        | 
+-------------------------+----------+
8 rows in set (0.00 sec)

Aha! So it does use the query cache, when there is a comment in front. In fact, running the same query with any amount of whitespace difference in front will use the query cache. Go try it, it works!

So, comments in front are ok, what about the end of the query text? (I’m omitting the data now, it just isn’t that exciting ;)) First, let’s clean the slate, flushing the status vars, recreating the table and inserting the data again, so we don’t have a problem seeing exactly what’s going on:

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table a;
Query OK, 0 rows affected (0.00 sec)

mysql> create table a (a int);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into a values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show status like 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        | 
| Qcache_free_memory      | 16768384 | 
| Qcache_hits             | 0        | 
| Qcache_inserts          | 0        | 
| Qcache_lowmem_prunes    | 0        | 
| Qcache_not_cached       | 0        | 
| Qcache_queries_in_cache | 0        | 
| Qcache_total_blocks     | 1        | 
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> select \* from a;
[...]
2 rows in set (0.00 sec)

mysql> show status like 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        | 
| Qcache_free_memory      | 16766848 | 
| Qcache_hits             | 0        | 
| Qcache_inserts          | 1        | 
| Qcache_lowmem_prunes    | 0        | 
| Qcache_not_cached       | 0        | 
| Qcache_queries_in_cache | 1        | 
| Qcache_total_blocks     | 4        | 
+-------------------------+----------+
8 rows in set (0.00 sec)

Ok, just what we expected: 1 insert into the query cache (our select) and there’s only one query in the cache at all, also our select \* from a. Let’s run the select with the comment in front again, and then put a different comment at the end.

mysql> /\* from some other host \*/ select \* from a;
[...]
2 rows in set (0.00 sec)

mysql> /\* from some other host \*/ select \* from a; /\* with a comment at the end \*/
[...]
2 rows in set (0.00 sec)

mysql> show status like 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        | 
| Qcache_free_memory      | 16766848 | 
| Qcache_hits             | 2        | 
| Qcache_inserts          | 1        | 
| Qcache_lowmem_prunes    | 0        | 
| Qcache_not_cached       | 0        | 
| Qcache_queries_in_cache | 1        | 
| Qcache_total_blocks     | 4        | 
+-------------------------+----------+
8 rows in set (0.00 sec)

Whooohoooo! :)

This actually works! In case you are wondering, it will also pick up things like: /\* from some other host \*/ select \* from a /\* with a comment at the end \*/ ; (note the place of the semicolon!)

Now, the obvious next question is: What about comments in the middle of the query? Let’s run a test:

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> show status like 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        | 
| Qcache_free_memory      | 16766848 | 
| Qcache_hits             | 0        | 
| Qcache_inserts          | 0        | 
| Qcache_lowmem_prunes    | 0        | 
| Qcache_not_cached       | 0        | 
| Qcache_queries_in_cache | 1        | 
| Qcache_total_blocks     | 4        | 
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> /\* from some other host \*/ select \* /\*and a comment in the middle\*/ from a /\* with a comment at the end \*/ ;
+------+
| a    |
+------+
|    1 | 
|    2 | 
+------+
2 rows in set (0.00 sec)

mysql> show status like 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        | 
| Qcache_free_memory      | 16765824 | 
| Qcache_hits             | 0        | 
| Qcache_inserts          | 1        | 
| Qcache_lowmem_prunes    | 0        | 
| Qcache_not_cached       | 0        | 
| Qcache_queries_in_cache | 2        | 
| Qcache_total_blocks     | 6        | 
+-------------------------+----------+
8 rows in set (0.00 sec)

And as expected, this does not work. Embedding comments still is a no-go (as is changing the case of any of the keywords or identifiers) because the queries need to compare byte for byte. Except, as we’ve found out, for whitespace in front and back of the query text! Remember, comments are usually regarded as whitespace (and most compilers and interpreters collapse them to a single blank character).

So, what’s the deal about this? It simply means that doing what Chris came up with is perfectly ok for deployments relying on the query cache, provided their MySQL version supports this. Now I have no idea when this change went in, but as 5.1.30 is GA, that’s good enough for me right now. And using the simple commands above you can easily check this for the version you are running, it’ll take you about 1 minute to find out ;)

Oh, and if you do that, please leave a short comment :)

Comments:

Hi Kay,

Thanks for following up with real examples ;)

I just wanted to check - are you running the command line client with --comments (see BUG #26215)?

Posted by Morgan Tocker on January 23, 2009 at 08:45 AM CET #

waaaaah!
Ok, I'm stupid :P

Of course, I forgot about --comments when I did all of this.

I guess that's what you get when you test stuff without having enough coffee first.

Posted by Kay Röpke on January 23, 2009 at 09:02 AM CET #

so seems like "working" now is just that comments don't completely bar something from using query cache. however comments are still used and treated as part of the query as far as cache hit/duplication are concerned right?

so:
/\* foo \*/ select \* from a;
and
/\* bar \*/ select \* from a;

are separate and generate two different caches? what a shame. :(

Posted by Eric on February 09, 2009 at 04:52 PM CET #

I was using comments in version 5.1.30, but after install 5.1.31 it stop working. If I put different comments the querys aren't cached.

Posted by Daniel Silva on February 17, 2009 at 09:24 AM CET #

Good information!

Posted by ed hardy on December 07, 2009 at 11:03 PM CET #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Kay Roepke

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today