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!

This entry was posted on Wednesday, November 14th, 2007 at 22:34 and is filed under MySQL. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.