Friday Jan 23, 2009

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 :)

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