MySQL Proxy. Playing with the tutorials

I was playing with the 5 sample tutorial Lua scripts available here with the MySQL Proxy , but I was doing something a little inefficiently.

I started mysqld, then I started the MySQL Proxy with the lua script, then connected to MySQL via the proxy. To test a different script I was actually killing the MySQL Proxy and restarting with appropriate script, but this is unnecessary. MySQL Proxy will re-read the lua script, as specified with –proxy-lua-script on new connection. All I need to do is copy in the file in question and get a new mysql client connection.

The tech version of the right way:

$ cp tutorial-basic.lua running.lua
$ ./mysql-proxy --proxy-lua-script=running.lua &
$ mysql -uusr -p -P4040 -h127.0.0.1
mysql> # do my stuff
mysql> exit;
$ cp tutorial-inject.lua running.lua
$ mysql -uusr -p -P4040 -h127.0.0.1

In the tutorial-resultset.lua example, we see the creation of a new command, show querycounter which gives you the number of queries executed for the connection. Immediately I can see that people will be creating pseudo Com_insert,Com_select,… variables per connection so you can get a better granularity of information then what default status variables can provide.

It was interesting however to see the counter be a value of 20 when you open a connection. What you may ask, but we forget the little things sometimes when running the mysql client. On invocation it actually executed 20 commands to the server, that is 20 round trips.

There are indeed: SHOW DATABASES; SHOW TABLES; then 16 Field Lists for each mysql table (e.g. host,db,func…), then a SELECT @@version_comment. Most of these as a result of the rehash or tab-completion capabilities in mysql client.

Wow, I also learned something new, there is a @@version_comment, I just knew about @@version. You learn some new trivial point every day!

mysql> select @@version,@@version_comment;
+------------+------------------------------+
| @@version  | @@version_comment            |
+------------+------------------------------+
| 5.0.37-log | MySQL Community Server (GPL) |
+------------+------------------------------+
1 row in set (0.00 sec)

MySQL Proxy. Get it here .

Tagged with: Databases MySQL MySQL Proxy

Related Posts

Fixing Old Software Bugs Without Thinking

Fixing Old Software Bugs Without Thinking

I previously came across an issue in my benchmarking work where I relied on the Latency Histogram section of sysbench output. On Linux it worked fine — I generally ran my workload there, so it never mattered.

Read more
Curating DuckDB Datasets Leveraging AI

Curating DuckDB Datasets Leveraging AI

In Curated MySQL Data Sets for Realistic Testing I described datasets assembled the manual way — download, schema, load, validate, document — over hours or days per source. This post is the follow-up I promised: what changes when AI assists the same workflow, using DuckDB as the target engine and GeoNames as the first example.

Read more
Why INSERT IGNORE should not be used

Why INSERT IGNORE should not be used

Let’s say you’re building a reference table from source data, e.g. a silver medallion table from a primary source. In this example I am using a file of random locations on the globe extracted from OpenStreetMap (OSM) as my primary source.

Read more