Thursday Jul 02, 2009

MySQL Proxy 0.7.2 released

The following just went out to our mailing list:

We are happy to announce that MySQL Proxy 0.7.2 is available in a source and binary release for all of our target platforms.

This latest release also brings back Windows support in both the source and binary release.

The list of important changes in this release is:
\* fixed memory leak proxy plugin (#45272)
\* fixed ro-balance.lua (#45408)
\* added CMake build files
\* fixed portability issues for Win32
\* added mysql-proxy-svc on Win32
\* updated INSTALL file to cover all the build steps on win32

Please report any problems on, our Launchpad discussion mailing list or on IRC: #mysql-proxy on

The focus of development is now on MySQL Proxy 0.8.

MySQL Proxy 0.7.2 is currently the recommended version to use unless you develop MySQL Proxy itself.

The packages are available on the MySQL Downloads page.

Monday Apr 27, 2009

Slides from Advanced Query Manipulation with MySQL Proxy

Just quickly dropping the PDF version of the slides of my presentation at this year's MySQL Conference.

Friday Jan 23, 2009

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

Thursday Aug 21, 2008

Agent vs agent-less monitoring

Baron posted something interesting about agent vs agent-less monitoring in response to Rob.

While reading it, I couldn’t help thinking that the distinction is somewhat misleading, if not wrong.

I’d go so far as to say that agent-less doesn’t exist as such. Why do I say such heresy?

Trivially, you need some piece of software to collect data. With munin you configure a server that triggers scripts on the monitored servers. The set of data sources is governed by what you install in the correct directory on the monitored server. Cacti relies on SNMP heavily and also allows you to write plugins which then connect out from the central Cacti server to the individual hosts to be monitored. I believe you don’t have to do any SNMP but can report data in a different protocol, but since I haven’t done it I don’t know the mechanics.

Now, with Cacti the scripts are all in one central place, which is nice. It’s certainly not the case for munin and that’s where the problem comes in.

Munin in fact is an agent based system as my definition goes. The code necessary to monitor services is not in a central place. For Nagios and Cacti it appears to be “true” agent-less, but effectively you exercise a non-trivial amount of code on the client side as well.

If your definition of an agent (in the monitoring world) is that it runs even when not connected to the monitor server, then yes, there’s a distinction.

However, I prefer to think about it this way: With agent-less systems, you are effectively retransmitting the “agent” each time you retrieve data. Not only do you establish connections at a rate equal to your most frequently collected data, but you are retransmitting much more data in a given time. I find that wasteful.

One other problem I’ve encountered with “agent-less” systems (or rather with “mini-agent” sytems like munin) is that if you have a problem with the network between the monitoring server and the monitored ones, or the monitoring server says bye-bye you must lose all data during that interval. There’s no way to collect the data, simple as that.

In my experience it’s not uncommon that an outage affects only part of your network (especially if you have redundant datacenters - which is why you have them in first place…) and losing much of your monitoring ability without any way to recover from a temporary problem (say duration of about 5-10 minutes) is a bummer, especially in high-traffic environments.

Another interesting point is that agent-less systems are polling constantly. That is fine for sampling data that is constantly changing but for other things it’s far from ideal. The agent can use an “interrupt” model when reporting data back to the monitoring server, because it’s an active component able to report data out of its own accord. Take query analysis for instance. If there’s no data to report, don’t report it. Less work to be done on both sides, less traffic going back and forth. The same goes for setting thresholds for instance, you can report once the threshold was exceeded and maybe continue to send actual values while the readings are out of their allowed range, but stop again once everthing is back to normal. This approach greatly reduces the amount of dataflow, but you cannot do it with a polling scheme.

Agent systems do have their own problems, including higher maintenance efforts, but the idea is that each system is separate and isolated from each other. And quite frankly, I don’t buy the “it’s hard to update agents” argument. There are quite a few free management solutions that make software deployment pretty easy once it is in place. Every place I worked at had some kind of system like that and a performing an update of software took almost no effort at all (that includes MySQL, Apache and even kernel updates). It’s quite possible to include agents in that system and have them deployed automatically, after testing it in a stage environment, of course.

I believe agents have their place and a properly designed system can mitigate almost all maintenance issues.

Obviously, if you want to measure network latency or service availability (is the HTTP service still up and responding), there’s little sense in deploying agents. Just use Nagios, it’s good for something like that!

Wednesday Jun 25, 2008

Calling Java code in MySQL

This post is a follow-up to my talk at JavaDeus 2008 where I showed how you can make use of Lucene inside of MySQL using an experimental branch and some triggers and stored procedures.

Since the process is not entirely obvious and the branch is very experimental, I thought it would be a nice thing to put it online. It’s pretty amazing with how little code (in Java and otherwise) you can implement a working full-text search engine that simply doesn’t get into your way :)

Note: Be advised that the MySQL server we will build is likely to crash at times. Do not use it in production! And of course, always have a safe backup of important data.

This first part will cover the basics on how the get the necessary foundation in place, configuring the server and calling Java UDFs. In the second part, which I hopefully will post tomorrow, I’ll show the juicy rest of getting Lucene going.

To achieve our goal we need several things:

The software versions I used are:

  • Mac OS X 10.5.3 (nothing specific about OS X here, though)
  • Java 1.5.0_13 (you need the SDK, the runtime will not suffice)
  • gcc 4.0.1
  • mysql-5.1-wl820 revision 2584 (as of 2008-06-19)

Ok, off we go.

After you installed Bazaar, you are ready to get the MySQL 5.1-wl820 sources. On the command line do:

$ bzr branch lp:~starbuggers/sakila-server/mysql-5.1-wl820

This might warn you that you cannot push things back to launchpad, but we’ll just ignore it, because we don’t actually want to push changes back.

At this point, I recommend getting up and doing something else, like boarding a flight to Hawaii, because it will take a bit until the sources are downloaded.

When it’s done change into the directory bzr created and configure the server. This is a branch of the MySQL 5.1 main-line, so everything that applies to building the community sources (or Enterprise for that matter) applies to this tree, as well.

You can go the autoreconf route, or be lazy like I am and use the scripts in the BUILD directory (choose the one that applies to your system).

One thing to watch out for, is that the check for a working javac assumes the Java CLASSPATH contains the current directory. Check that your settings (most likely the CLASSPATH environment variable) contain . in there! Skip the first step if you already have . in your classpath (or alter the command if you are not using bash…aah the joys of Unix).

You most certainly will want to specify an installation prefix for this server (otherwise it would be installed in a system-wide location), so don’t forget to fill in the --prefix option.

$ cd mysql-5.1-wl820
$ ./BUILD/compile-pentium-debug-max-no-ndb --prefix=<where you want the server to live>
[...lots of output...]

Building MySQL might take a bit, depending on your machine, so do something useful while waiting :)

Double check your classpath if the check for javac fails. (Can you tell I always forget that?!)

Aside: If you own a multicore machine, you can speed up the compile considerably by telling the build script to just configure the server and then build it manually (note the -c). Replace the 9 in the make invocation with “number of cores you have”+1:

$ ./BUILD/compile-pentium-debug-max-no-ndb -c --prefix=<where you want the server to live>
[...lots of output...]
$ make -j9
[...yet more output...]

Now is the time to install the server in the location you gave to the build script above. For simplicity I’ll assume that you have no other MySQL server running, please refer to the MySQL docs on how to setup multiple mysqlds on one box.

$ make install
[...still more output...]
$ cd <installation_directory>
$ ./bin/mysql_install_db

Follow the steps mysql_install_db outputs to secure your installation, though for the purpose of this setup guide I have skipped that and am running totally insecure :P (The real reason is that I’m to lazy to remove the anonymous account when testing stuff like this.)

Let’s start the server and make sure everything works so far:

$ ./bin/mysqld_safe &
$ ./bin/mysql
Welcome to the MySQL monitor.  Commands end with ; or \\g.
Your MySQL connection id is 1
Server version: 5.1.26-rc-debug Source distribution

Type 'help;' or '\\h' for help. Type '\\c' to clear the buffer.

mysql> show databases;
| Database           |
| information_schema | 
| test               | 
2 rows in set (0.00 sec)

mysql> quit

If you can successfully connect we are already halfway there :)

Check that you are connected to the right server (note the server version is 5.1.26-rc-debug) and that there are no databases other that information_schema and test.

Let’s now hit the first roadblock in installing this experimental tree ;)

To load the Java plugin, we will need to connect as root (because we need access to the mysql database):

$ ./bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \\g.
Your MySQL connection id is 3
Server version: 5.1.26-rc-debug Source distribution

Type 'help;' or '\\h' for help. Type '\\c' to clear the buffer.

ERROR 1126 (HY000): Can't open shared library '/private/tmp/mysql-5.1-wl820-build/lib/mysql/plugin/' (errno: 2 dlopen(/private/tmp/mysql-5.1-wl820-build/lib/mysql/plugin/, 2): image not found)

The problem is that apparently the installation process copies the language plugins into the wrong directory. They should be in lib/mysql/plugin/ but are being installed in lib/mysql/. For security reasons we are not allowed to use paths when loading a plugin, so we need to copy (or symlink) them.

$ cd lib/mysql/plugin
$ ln -s ../psm_\* .

Another try:

$ ./bin/mysql -uroot
ERROR 2013 (HY000): Lost connection to MySQL server during query

Whoops. One problem down, and a crash pops its ugly head up. Let’s investigate by looking at the error log (it’s in var/machinename.err). It will contain a line like:

Can't find class: com/mysql/udf/UDFModule080622 20:38:17 [ERROR] Plugin 'Java' init funtion returned error.

Which basically means: Our old friend, the classpath, is wrong. This being an experimental branch, there are error checks missing to make problems painfully obvious. No worries, we’ll just fix the classpath.

But how?

The psm_java plugin contains code that embeds a JVM and as such there must be a place to tell it its startup parameters, like the heapsize and the classpath. There is a configuration file name jvm_options.cnf in the MySQL datadir, which contains those settings. If it doesn’t exist yet, simply create a file of that name.

Mine contains


This file also is the place to tell the JVM which garbage collector to use, which can be very important, as the VM is running inside a database server.

/tmp/mysql-5.1-wl820-build is the basedir where I installed MySQL into, the actual .jars you need are in lib/mysql/. java_udf_example.jar contains a couple of samples to get you started. They are built as part of the build process we did earlier and their sources are in the plugin/java_udf/org/example/mysql/udf/ directory of the MySQL source directory.

After creating or modifying jvm_options.cnf, let’s check if our settings worked. Restart mysqld_safe and connect with the client:

$ ./bin/mysqld_safe &
$ ./bin/mysql
Welcome to the MySQL monitor.  Commands end with ; or \\g.
Your MySQL connection id is 1
Server version: 5.1.26-rc-debug Source distribution

Type 'help;' or '\\h' for help. Type '\\c' to clear the buffer.

| Name       | Status | Type           | Library     | License |
| binlog     | ACTIVE | STORAGE ENGINE | NULL        | GPL     |
| partition  | ACTIVE | STORAGE ENGINE | NULL        | GPL     |
| CSV        | ACTIVE | STORAGE ENGINE | NULL        | GPL     |
| MEMORY     | ACTIVE | STORAGE ENGINE | NULL        | GPL     |
| InnoDB     | ACTIVE | STORAGE ENGINE | NULL        | GPL     |
| MyISAM     | ACTIVE | STORAGE ENGINE | NULL        | GPL     |
| Java       | ACTIVE | PSM LANGUAGE   | | GPL     |
10 rows in set (0.00 sec)

You should see the Java plugin as in the output above, if not, recheck your classpath or try the INSTALL PLUGIN line from above again. The server will automatically load the plugin when it starts, there’s no need to issue the INSTALL PLUGIN command again.

Now let’s call our first UDF in Java. We’ll simply take the examples that were packed up for us in java_udf_example.jar. To do that, connect to the database as user root (unless you want to give the anonymous account EXECUTE privileges) and do the following:

$ ./bin/mysql -uroot
mysql> use test
Database changed
mysql> CREATE FUNCTION my_reverse(string VARCHAR(255))
    -> EXTERNAL NAME 'java_udf_example.jar:org.example.mysql.udf.Reverse.go';
Query OK, 0 rows affected (0.00 sec)
mysql> select my_reverse("looc era sFDUavaJ");
| my_reverse("looc era sFDUavaJ") |
| JavaUDFs are cool               | 
1 row in set (0.01 sec)

Congratulations! You have called your first Java method from inside of MySQL!

This MySQL branch has a slightly different syntax of the CREATE FUNCTION and CREATE PROCEDURE commands, in that it has been extended to allow different language names and can associate an external name with the in-database routine. The actual format of the external name is dependent on the language plugin, of course. For Java we can give it the .jar file and the package (all the way down to the method) of the code we want to call. Other than that it’s the same old Stored Routine syntax.

For reference, here’s the code we have just called (from plugin/java_udf/org/example/mysql/udf/

package org.example.mysql.udf;

public class Reverse {
    public String go(String str) {
        if (str == null) {
            return null;
        StringBuffer sb = new StringBuffer(str.length());
        for (int i = str.length() - 1; i >= 0; i--) {
        return sb.toString();

I love the fact that absolutely nothing in the above Java code refers to JDBC or MySQL at all. It could be anything that takes a string and returns a string, which is a whole lot :)

Now obviously we have to be a bit careful about the code we execute as part of our Stored Routine, because it will run inside the mysqld process. Avoid costly (both in terms of CPU and memory usage) code, don’t block etc. Common sense applies.

In the next post, I will show you how to put it all together and use Lucene to transparently index data flowing into your tables. You will even be able to search that fulltext index from within MySQL :)

CU next time.


Kay Roepke


« April 2014