Tuesday, May 19, 2009

MySQL Proxy - what if it crashes?

While I hope the MySQL Proxy never crashes, it will happen, there will be some strange (or maybe not so strange) usage or workload and it will die.

To avoid this, you could decide not to use it, or maybe you could use something like Linux HA to have more than one MySQL Proxy running at all times. Or you could use one of the new features that comes with the version 0.7.0.

What is it?
We now have a --keepalive option. As the name indicates, if the mysql proxy process dies/crashes, it will come back up in a few seconds (less than 5 seconds).

How does it work?
If you start the MySQL Proxy with the keepalive option, there will be two processes with the same name. One will be very small, about 600KB. and then you will also see the "real" mysql proxy process. The "angel" process has a lower PID value than the "real" mysql proxy process.

If there is some kind of error that causes the proxy to die, the "angel" process will wait about 3 seconds, and try to restart the proxy process.

You can "force" this behavior by sending a

$ kill -SIGKILL < pid of the proxy >


$ kill -SEGV < pid of the proxy >

How do I use it?
Very simple, if you use a configuration file, you need to add a new line with

keepalive = true;

or you can start the mysql proxy like this:

sbin/mysql-proxy --proxy-lua-script=/mysql-lua-scripts/trunk/scripts/proxydb.lua --keepalive

Enjoy! ... And next time I'll write about using GDB to get some debugging information about the crash.


  1. Pids wrap around when they reach the upper range boundary so it's possible the 'angel' process will have a higher pid value than the proxy process.

    Another suggestion: forget '--keepalive' and add your proxy start up command to your /etc/inittab and let init handle restarting a crashed proxy. If the 'angel' process dies, you're out of luck. If init dies, you have bigger problems to deal with.

    Yes, init would run your proxy as root - so you use the 'setuidgid' program from Bernstein's daemontools package (http://cr.yp.to/daemontools.html) to fix that.

  2. btw: your TypePad authentication is broken. I couldn't publish a comment using my TypePad account.

  3. Hi Stoner,

    You are right, I didn;t mention that PIDs can wrap, and I then found out that openBSD generates ramdom PIDs.

    In that case, you may be able to distinguish the angel form the real proxy by looking at memory usage :P

    About /etc/inittab, now people have at least two ways of achieving the same end result.

    And finally, about TypePad, I guess Google broke it :(

    Thank for your comments!



Vote on Planet MySQL