Wednesday, October 17, 2007

Introducing the 15 seconds rule

How fast do you want your installation? Check this.
$ time ./express_install.pl ~/downloads/mysql-5.0.45-osx10.4-i686.tar.gz --no_confirm
unpacking /Users/gmax/downloads/mysql-5.0.45-osx10.4-i686.tar.gz
Executing ./install.pl --basedir=/Users/gmax/downloads/5.0.45 \
[...]
Installing MySQL system tables...
OK
Filling help tables...
OK
[...]
loading grants
sandbox server started
installation options saved to current_options.conf.
To repeat this installation with the same options,
use ./install.pl --conf_file=current_options.conf
----------------------------------------
Your sandbox server was installed in /Users/gmax/msb_5_0_45

real 0m6.773s
user 0m0.245s
sys 0m0.235s

Old times


MySQL has a long established rule of going from downloading to up and running in less than 15 minutes, as stated in various sources, like this interview with David Axmark
So we worked hard to make the installation and first use as easy as possible. We came up with the 15 minutes rule: we wanted a user to be able to have MySQL up and running 15 minutes after he finished downloading it.
With advanced installers like apt and rpm, the installation time can go down to seconds.
All goes well when you want to install or replace the main (or the only) server in your host, but things may get hairy when you want to install a second server. Reasons for having a second server:
  • You can't upgrade to a new version, but you need a feature available in a newer release, and you install it as a side server;
  • You want to test a problem without affecting the production database;
  • You want to test a new version.
For QA and Support engineers, consultants, developers, installing a side server is a common task, and usually a painful one.
To install cleanly a second server on a host, you need to:
  1. unpack a binary tarball;
  2. create a data directory (in a different location)
  3. start the server with a different port and socket, to avoid conflicts with the main server.
If you have tried the above steps, you know that it is not a difficult task, but it is error prone, the commands to issue are long and full of options to remember. And even if you don't make mistakes, starting and stopping the server requires a great deal of attention, and using the right server is a challenge in itself.
Enters MySQL Sandbox a program that creates a side install of a MySQL server in seconds.

MySQL Sandbox

The latest version of MySQL Sandbox is designed for the maximum speed with the minimum preparation from the user. To go from nothing to a fully functional server, you need 3 steps:
  1. download the binary tarball from MySQL site, and save it to a directory, say $HOME/downloads.
  2. download and expand the Sandbox
  3. run this command:
    ./express_install.pl $HOME/downloads/tarball_filename.tar.gz
You will get an output similar to the one seen at the beginning of this post, and within seconds, you will have a side server up and running.

Using the Sandbox

The Sandbox installation comes provided with essential goodies to start, stop, and use the server. By default, it installs in $HOME/msb_x_x_xx, where x_x_xx is the version numeral of the server you have just installed. If you were installing mysql 5.0.45, then you can run
$ ~/msb_5_0_45/use.sh
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.45 MySQL Community Server (GPL)

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

mysql [localhost] {msandbox} ((none)) > show schemas;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.02 sec)

mysql [localhost] {msandbox} ((none)) >

When you are done, you can stop the server just as easily.
$ ~/msb_5_0_45/stop.sh
/Users/gmax/downloads/5.0.45/bin/mysqladmin --defaults-file=/Users/gmax/msb_5_0_45/my.sandbox.cnf shutdown

Replication

You have seen so far that installing a server took less than 7 seconds. Why I am talking about the 15 seconds rule?
That's what it takes to install a replication system of 1 master + 2 slaves, without any additional setup, no fiddling with configuration files. here goes.
$ time ./set_replication.pl ~/downloads/mysql-5.0.45-osx10.4-i686.tar.gz
installing and starting master
installing slave 1
installing slave 2
starting slave 1
sandbox server started
initializing slave 1
starting slave 2
sandbox server started
initializing slave 2
replication directory installed on /Users/gmax/rsandbox

real 0m14.820s
user 0m0.789s
sys 0m0.745s
That's slightly less than fifteen seconds. And you must consider that each "start" step contains a sleep 3 instruction, to let the server boot properly.

The replication sandbox has a few convenience scripts that let you manage the nodes without thinking of the details. m.sh is the master, s1.sh is the first slave, s2.sh is the second slave, start_all.sh starts all the nodes, and so on. Look. The replication is working!

$ ~/rsandbox/m.sh -e 'create table test.t1(i int)'
$ ~/rsandbox/s1.sh -e 'show tables from test'
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
$ ~/rsandbox/s1.sh -e 'show slave status \G' |grep Running
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Now it's up to you. Try it!

3 comments:

Xaprb said...

You are awesome. I have been using MySQL Sandbox for a while, but these improvements are great.

BTW my post about the sandbox is here:
MySQL Sandbox is the best thing since sliced bread

ling said...

I just cant get the ./set_replication working well.It keeps telling me the following errors.=============installing and starting masterCan't use string ("server-id=1") as an ARRAY ref while "strict refs" in use at ./install.pl line 506.error installing the masterunpacking /home/testuser/mysqlpackages/mysql-5.1.24-rc-freebsd6.0-x86_64.tar.gzExecuting ./install.pl --basedir=/home/testuser/mysqlpackages/5.1.24 \--sandbox_directory=msb_5_1_24 \--install_version=5.1 \--sandbox_port=5124 \--no_ver_after_name \--datadir_from=script \--no_confirm \--home_directory=/home/testuser/rsandbox_5_1_24 \--sandbox_directory=master \--sandbox_port=18539 \--load_grants \--prompt_prefix=master \-c \log-bin=mysql-bin \-c \server-id=1===========The directory for replication is created but nothing inside.Can anyone please advise?Thank you.

ling said...

Sorry, I missed out the perl-5.8 requirements.
I was using 5.6
It would be better if the installation could works with 5.6 too.

Thank you

Vote on Planet MySQL