The patch for SHOW PROFILE has been ported to MySQL 5.1.
It was not an easy transition. The initial testing went quite bad, until Chad Miller decided he had enough and rewrote 80% of the code, leaving very little of what was initially provided by Jeremy Cole.
The new code works quite well, and it has some improvements, compared to the 5.0 implementation. It is currently under review, and depending of its outcome, we may decide to backport this patch to 5.0 as well.
If you want to give it a try, you need to download the source code and follow the instructions.
The good news is that SHOW PROFILES seems to be working fine with all the new features introduced in MySQL 5.1. Partitions, row-based binlog, table logs, work without side effects.
I am especially pleased of noting that SHOW PROFILES can work now inside stored routines, and thus within the event scheduler.
The following example creates a table on the test database, with the same structure of the INFORMATION_SCHEMA.PROFILING table.
The sample procedure p1 enables profiling, sends one query, and disables profiling. Then it copies all the records from INFORMATION_SCHEMA.PROFILING to the local table. This proves that profiling works within a stored routine.
Previously, if you run a stored routine with set profiling=1, its effects were felt only after the end of the stored routine. Now you can profile within a stored routine at will.

use test;

drop table if exists profiling;
CREATE TABLE profiling (
  QUERY_ID int(20) NOT NULL DEFAULT '0',
  SEQ int(20) NOT NULL DEFAULT '0',
  STATE varchar(30) NOT NULL DEFAULT '',
  DURATION decimal(6,6) NOT NULL DEFAULT '0.000000',
  CPU_USER decimal(6,6) DEFAULT NULL,
  CPU_SYSTEM decimal(6,6) DEFAULT NULL,
  CONTEXT_VOLUNTARY int(20) DEFAULT NULL,
  CONTEXT_INVOLUNTARY int(20) DEFAULT NULL,
  BLOCK_OPS_IN int(20) DEFAULT NULL,
  BLOCK_OPS_OUT int(20) DEFAULT NULL,
  MESSAGES_SENT int(20) DEFAULT NULL,
  MESSAGES_RECEIVED int(20) DEFAULT NULL,
  PAGE_FAULTS_MAJOR int(20) DEFAULT NULL,
  PAGE_FAULTS_MINOR int(20) DEFAULT NULL,
  SWAPS int(20) DEFAULT NULL,
  SOURCE_FUNCTION varchar(30) DEFAULT NULL,
  SOURCE_FILE varchar(20) DEFAULT NULL,
  SOURCE_LINE int(20) DEFAULT NULL
);

drop procedure if exists p1;
delimiter //

create procedure p1 ()
modifies sql data
begin
    set profiling = 1;
    select sleep(0.1);
    set profiling = 0;
    insert into test.profiling select * from information_schema.profiling;
end//

delimiter ;

Once profiling is enabled inside a stored routine, we can also use it inside an event.

set global event_scheduler =1;
create event e1 on schedule every 10 second do call p1();

select sleep(1); # give the event scheduler time to kick.

select count(*) from profiling;
+----------+
| count(*) |
+----------+
|        9 |
+----------+

The profiling table is filled by the event. Finding records there means that profiling inside the event scheduler is working.
Enjoy!

Hurry up!
The call for papers for the Users Conference 2008 is closing on Tuesday, October 30th.
If you haven’t submitted a proposal, now it’s the time to do it.
Read the unofficial but very effective guidelines offered Baron Schwartz and Colin Charles and write down your well thought abstract
NOW!

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!