Saturday, December 25, 2010

Ubuntu Upstart for automatic MySQL start and stop

Here at Recorded Future we use Ubuntu (running on Amazon EC2), but so far we have not explored Ubuntu Upstart that much. During the holidays I made an effort to get acquainted with Upstart and to implement proper MySQL start and stop with it.

If you do not know Upstart, this is the way you start and stop services in Ubuntu, and it serves the same purpose as the old /etc/init.d scripts, but are a bit more structured and powerful. That said, Upstart is regrettably far from complete, although the functionality is much better and Upstart has some cool features, some things do not work that well. For one thing, documentation, where it exists, is useless, at best. Secondly, there is very limited ability to test and develop Upstart scripts. And this is made worse by the fact that the documentation is so bad. Another thing is that Upstart insist on stopping services, by default, by sending a brutal kill signal. Not good for databases, mostly.

In the /etc/init directory are the Upstart scripts you have. In difference to the old init.d scripts, you cannot disable a service in Upstart curenntly. If it is in /etc/init it will be started at system start. That's it. And this is something that I am sure will be fixed, but for now, again, is something we have to live with. Upstart scripts have the suffix .conf (don't ask me why), so the default MySQL Upstart script, for example, is called /etc/init/mysql.conf.

In an Upstart script, there are Stanzas that determine what to do. Like the exec Stanza that runs a program for example. And you may then ask, when is it run? Startup? Shutdown? And the answer is startup. For shutting things down, as I said before, Upstart will by default just send a kill -9 signal.

The minimal startup script you can have, and this actually works in a reasonable way, is to just have one line with an exec stanza, like this:
exec /usr/bin/mydaemon
Which will start the daemon. For stopping the daemon, Upstart will send a -9 signal to the started process by default, and nothing more is needed in the Upstart script.

For MySQL, we need to make things a bit more complicated. The default mysql.conf Upstart script really is not good. For one thing, it will not do a controlled shutdown of MySQL (this is possible even if Upstart will eventually send a kill -9 anyway). Secondly, this script assumes that what we use is a standard Ubunty installed MySQL distribution, so if you have installed MySQL in /usr/bin/mysql5147 or somethings like that, you are out of luck.

So what I wanted to create was an Upstart script for MySQL that fullfilled these requirements:
  1. Starts MySQL automatically.
  2. Waits for MySQL to be available before exiting.
  3. Be configurable to support different MySQL install locations, data directories etc.
  4. Do a clean shutdown of MySQL when stopping the MySQL services.
Before I show you what I ended up with, I want to comment on the points 2 and 4 above. With Upstart, you can define a script or command to run just before or after a services has been started or stopped, and this is what I use to wait for MySQL to become available, and to send a SIGTERM to the MySQL Server when stopping (which will do a clean MySQL shutdown).

So here we go, a complete MySQL Upstart script, the way I want it to work:

#
# MySQL Service for Recorded Future
#
description "MySQL Server"
author "Anders Karlsson, Recorded Future"

start on (net-device-up
and local-filesystems
and runlevel [2345])
stop on runlevel [016]

expect fork
kill timeout 2

# Set variables.
env MYSQL_ETC=/etc/mysql
env MYSQL_PIDFILE=/var/run/mysql.pid
env MYSQL_HOME=/usr/local/mysql5.5
env MYSQL_INSTANCE=my
umask 007

exec $MYSQL_HOME/bin/mysqld_safe --defaults-file=$MYSQL_ETC/$MYSQL_INSTANCE.cnf >> /tmp/x.out &

post-start script
loop=600
# Wait for MySQL to start.
while [ $loop -gt 0 ]; do
if $MYSQL_HOME/bin/mysqladmin --defaults-file=$MYSQL_ETC/$MYSQL_INSTANCE.cnf ping; then
break
fi
loop=$(($loop - 1))
sleep 1
done
exit 0
end script

# Send a soft SIGTERM to MySQL before Upstart will kill it.
# A Sigterm to mysqld will cause a controlled shutdown.
pre-stop script
exec kill -SIGTERM `cat $MYSQL_PIDFILE`

# Wait for MySQL to end. Flushing buffers and all.
loop=600
while [ $loop -gt 0 ]; do
# If the pidfile is found, then continue waiting.
if [ -e $MYSQL_PIDFILE ] ; then
loop=$((loop - 1))
sleep 1
continue
fi
break
done
end script


To be honest, this is not what I create for all our MySQL servers. Instead I used this to create a chef template, chef is what we use for configuration management here (see http://www.opscode.com/ for more on chef), and here it is put to good ude to generate an Upstart script for MySQL. The above is just an example.

/Karlsson

7 comments:

Baron said...

I don't know much about Upstart, but from your description, it seems like a giant step backwards.

Karlsson said...

Baron!

Upstart sure has a way to go. It does have a number of features that init.d scripts doesn't. As far as I am concerned, the Upstart idea is good, but the current implementation isn't.
The way that dependencies between servic es is implemented rocks, for example (i.e. Apache depends on PHP that depends on MySQL or something like that).

/Karlsson

Daniƫl van Eeden said...

Have you filled a bug against Upstart so the upstart devs can improve the product?
If not:
https://bugs.launchpad.net/upstart

Upstart is similar to Solaris' SMF and Apple's launchd. It would be great if all those systems would be a little more compatible with each other.

I believe RHEL5 and newer also use Upstart, but in a heavily modified version.

Carlos Scheidecker said...

Anders,

I've tried to email you but it bounced back. I have installed MySQL 5.5 on my Ubuntu 10.10 box with a AMD64 and 8gb of mem. This might be off topic but I found MySQL 5.5 to be very slow on innodb tables.

I even updated my application to replace REPLACE INTO table for INSERT INTO table ON DUPLICATE KEY UPDATE... To see if that would suffice. It did not, was still slow.

It would be nice an article about performance tunning MySQL and the available tools to help make it better.

I then tried innodb-flush-method which I had it set to O_DYSNC and that made a HUGE difference. Inserting records have now became much faster.

I wanted to send you my /etc/my.cnf file for you to have a look but your email is bouncing back.

Hence, if you have any tips on enhancing performance and tunning the server I would like to read about it.

Thanks.

Unknown said...

what is the difference between setting

kill timeout 600

and your pre-stop script?

Unknown said...

also you are backgrounding the process using &. If you stop doing this i think you can get rid of expect fork

hanna said...

It would be nice an article about performance tunning MySQL and the available tools to help make it better.
mysql services