MySQL Proxy Web-Seminar

I just realized something else Chris has mentioned:

Since MySQL Proxy 0.7.0 is soon to be released, I thought another brief tutorial would be helpful.

Indeed, we are looking to properly release 0.7.0 soon™.

In case you are wondering what’s happening with MySQL Proxy and what the deal with the code’s new home is, please consider making some time for the web-seminar next week.

It’s free but registration is required.

We will also have Carlo Cabanilla talking about his project and I will talk about where we are with Proxy, what our plans are, how you can get involved and what the next steps are. Please bring your questions

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

MySQL Proxy code now live

It’s done!

Even if it took a while, too long, really, we finally have the MySQL Proxy code out in the open, where it belongs.

As part of this effort, we would also like to make our development process more transparent. To that effect, we’ve also created a Launchpad team that serves as a discussion hub. It comes with a mailing list, reachable at mysql-proxy-discuss@lists.launchpad.net, where we hope to have some interesting discussions about where to take Proxy, what to implement in what way, and of course to offer advice to users.

There’s not much in terms of “process” yet and quite possibly (and hopefully) it will stay this way, but let me outline some of my hopes and plans for going forward:

Pushing directly into the branches that make up the lp:mysql-proxy project is restricted to those in the MySQL Proxy Developers team, only because of licensing issues.

However, since everyone here feels deeply about open source there is a relatively painless way of getting contributions in:

1. Talk to us on mysql-proxy-discuss@lists.launchpad.net to make sure
   • the idea is sound
   • no one else is already doing it
   • you know what’s involved
   • other people know what’s happening.
2. Get the code from lp:mysql-proxy and do the changes you’d like to do.
3. Then push your branch back to Launchpad.
4. Propose a merge to let us know that you have something you think we should be looking at.
5. Pending a code review from us (either Jan or me have to approve it at least) we will merge the changes back to trunk and it will eventually see the light of day in a release.

6. The above actually has a prerequisite: The contributor has to agree to the Sun Contributor Agreement, as described by the MySQL Forge Wiki page.

   I think you will find the SCA to be very friendly, at least I wish I could’ve signed it back in the day. (Kudos to Jay Pipes have to be given here!).

   Unfortunately we have to require people to sign it, otherwise we cannot accept anything other than the most trivial patches, again due to licensing issues.

7. There is no point 7.

Let also note that the above (sometimes even with contributor agreements) is nothing totally unnatural in the Open Source world and in fact most projects I’ve contributed to had some kind of a “gatekeeper” mechanism, so please do not be discouraged by it. In fact, cool kids are doing it, too

There are a couple of changes to the layout of the code coming up, but I’ll talk about it when the time comes. In the meantime, please give the latest code a try and report any bugs you find on our bugs system, we are not using Launchpad for them, to avoid losing the overview…