After some delay we are proud to present MySQL Proxy 0.6.1.

This is a pure bug-fix release and fixes some assert()oins and the win32 support. It was branched off in December and it took a while to clean up the windows package until it passed the tests.

Download it from:

http://dev.mysql.com/downloads/mysql-proxy/index.html

Changes:

• added new features to run-tests.lua (see README.TESTS)
• fixed error handling for socket functions on win32
• fixed sending fake server-greetings in connect_server()
• fixed assert()ions on write-errors

Some time ago I have shown that the proxy can do connection pooling and can keep server-side connections open to be reused by another client later. The keepalive tutorial shows how this can be implemented.

When it comes to reusing a server-connection for multiple client connections we have to face a small problem:

SQL connections aren't stateless (temporary tables, session variables, ...)

We have to handle this somehow.

When you use this feature the proxy will clean up for you by default, by issuing a COM_CHANGE_USER as the first command that is sent to the server. It basicly resets the connection and re-authenticates the user. You always get a clean environment and can't really tell that the server-side connection was open all the time.

But you don't really win performance either. A small benchmark shall give you an idea.

# connecting the client to the server ...
11:58:41.241956 …

On December 13 (it means today for most of the readers) at 15:00 CET (14:00 UTC), the MySQL University lecture on Lua will start.
The topic is quite extensive. Even though the lecture is limited to using Lua with MySQL Proxy, yet there is a lot of ground to cover. During a rehearsal session last week, I realized that the whole matter would need much more than one hour if I describe in detail all the introductory material that I originally planned.
So I will reduce the time dedicated to MySQL Proxy architecture, which you can look on your own by reading Getting started with MySQL Proxy. I will cover this matter only briefly during the lecture, so if you know some background, you’ll enjoy the lecture even more.

If you are new to MySQL University lessons, …

At MySQL I'm writing several different command-line applications from the MEMo Agent to the MySQL Proxy. They all have a common set of requirement:

• command-line options
• config-files (mapping cmd-line options to config-files)
• logfile handling (syslog, SIGHUP, ...)
• a mainloop (SIGINT, CtrlHandler, ...)
• daemonizing (daemonize, services, ...)
• plugin handling

In the proxy svn you see all this a the chassis of the proxy.

At the side it has a MySQL-Server and a MySQL-Client (we usually call it the proxy). Currently this is implemented as libmysql-proxy which is then used by the proxy plugin to implement the proxy.

By splitting up the design into 3 different layers:

• chassis
• low-level protocol + protocol states
• proxy implementation as plugin

we can

1. use the …

Premature Optimization ... we all know it. The proxy is now in a state were we can start to optimize the code a bit. After getting some complaints about the performance with keepalive I took a deeper look into the problem and came up with:

• a global script-cache with reload on cache
• less GC-runs
• less overhead per lua-script-call

It is all in SVN now, enjoy it.

To test if we make progress I use mysqlslap in its most basic form:

$ mysqlslap --host=127.0.0.1 --port=4040 \
  --create-schema=mysqlslap --iterations=1000 --concurrency=10

which only connects and closes the connection again.

The run-time over 10.000 connects is

proxy, no cache, lua_gc()  11.553s
proxy, cache, lua_gc()      7.065s
proxy, cache, no lua_gc()   6.458s

It is all eric's fault. He infected me with the TDD virus.

After being in the stormy Denmark (like Kris was a two years ago: http://blog.koehntopp.de/archives/941-Blavand-Strand.html ) and seeing the horses getting wet feet (high tide + storm == a lot of fun) I took some time to read "Test Driven Development" by Kent Beck.

The book is very easy to read as it goes tiny steps, very tiny ones. But it does it on purpose. It iterates over the basic principle again and again and again ... until you give up and just do it :)

It is formalizing your development into 4 steps:

• write a failing test …

In the spirit of the MySQL Pluggable Storage Engines the proxy has a plugin infrastructure now.

The main goal was to cleanup the code so what the admin part and the proxy itself can be loaded at runtime.

The new code-layout looks like

• mysql-proxy.c (codename: the cauldron)
• takes care of the command line options
• signal handling
• win32 service handling (not done yet)
• libmysql-proxy.so
• provides the mainloop and everything for the socket and protocol handling
• libadmin.so
• the old admin interface
• libproxy.so
• the well known proxy itself

Now you can plugin your own modules if you like and only load those modules that you really want.

The next step is making the admin module more useful. I'll keep you updated.

A few weeks ago I held a MySQL University Session about MySQL Proxy. Thanks to our docs team we now have

• the IRC log,
• the slides and
• the voice recording

online.

If you are interested in writing your own scripts for the proxy, check out the Writing LUA Scripts for MySQL …

Test-Commander Eric was working on the log-replay task and as he is at my place right now, we have put our heads together to get a working prototype together.

---
-- log queries going throw the proxy on request and
-- provide a commands to work on the logged queries
--
-- * PROXY SET GLOBAL replay.log_queries = (true|false)
--   log queries sequentially (used by SAVE)
-- * PROXY SET GLOBAL replay.fake_mysqld = (true|false)
--   return the logged resultset for known queries
--
-- * QUERYLOG SAVE INTO "filename"
--   dump the queries into a file and reset the log buffer
-- * QUERYLOG LOAD FROM "filename"
--   load logged queries back into the query_log (used by the
--   fake_mysqld)
-- * QUERYLOG SHOW

What is this useful for ? ... hmm ...

The first goal is creating a fake-mysqld allowing us to run proxy tests without have to have a mysqld on the box.

As a proof-of-concept we logged the SHOW GLOBAL STATUS going through the proxy …

Kris brought up a simple question:

Why do I have to set the slow-query-time by hand ? Why can't the server figure out the normal query time and tell me when something is unusual slow ?

In earlier articles I already talked about that the proxy can log the query-time in microseconds and we already implemented a Query Histogram with average and max query-time. Unusual slow ...

Almost all (actually, 99.7%) of the values lie within 3 standard deviations of the mean (or between the mean minus 3 times the standard deviation and the mean plus 3 times the standard deviation). Statisticians use the following notation to represent this: μ ± 3σ.

see http://en.wikipedia.org/wiki/68-95-99.7_rule

Hmm, so everything which is slower than μ ± …