MySQL Forge

Resources for the MySQL Community

Categories: MySQLDevelopment | Software Preview

Show Profiles - testing under 5 1

[edit] Testing Show Profiles in MySQL 5.1

Contents 1 Testing Show Profiles in MySQL 5.1 1.1 Getting the source 1.2 Enabling SHOW PROFILES in MySQL 5.1 1.3 2007-11-13 1.4 2007-11-12 1.4.1 PROFILING and 5.1 features 1.4.2 Caveat 1.4.3 Known bugs 1.5 Backporting to 5.0

[edit] Getting the source

As of today (2007-11-14), SHOW PROFILES in 5.1 is still experimental. There is no available binary for it. If you want to try, get the source code and compile it using the instructions below.

[edit] Enabling SHOW PROFILES in MySQL 5.1

In MySQL 5.1, SHOW PROFILES is not enabled by default. You need to configure and compile with the option --enable-community-features For example, this script, saved as ./BUILD/compile-community, will build the server with SHOW PROFILES enabled.

#! /bin/sh 

path=`dirname $0`
. "$path/SETUP.sh"

extra_flags="$pentium_cflags $fast_cflags -g"
extra_configs="$pentium_configs $max_configs --enable-community-features --with-comment='experimental'"

. "$path/FINISH.sh"

./BUILD/compile-community

[edit] 2007-11-13

Profiling and concurrency.

Using a Perl script, concurrent access to profiling shows that the data is isolated per session. The test, with 1,000 concurrent sessions, checks for consistent duration times in SHOW PROFILE, SHOW PROFILES, and the I_S.PROFILING. The result does not show side effects.

[edit] 2007-11-12

[edit] PROFILING and 5.1 features

After applying Chad's patch to enable profiling inside stored routines.

PROFILING is now working well (or at least not causing side effects) with all the major 5.1 features.

• partitioning;
• event scheduler;
• table logs;
• row based binlog.

This example shows how PROFILING works with stored routines and the event scheduler;

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(1);
    set profiling = 0;
    insert into test.profiling select * from information_schema.profiling;
end//

delimiter ;

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

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

[edit] Caveat

If you use a procedure like p1 above, containing set profiling=0, be aware of a subtle side effect. If you enable profiling manually, and then call this procedure, it will work as expected, but profiling will be disabled by the procedure. Thus, you may mistakenly think that there is a bug in profiling, while it has simply being deactivated.

set profiling=1;
call p1();
SELECT 1;
show profile;

The output of SHOW PROFILE is referred to the last query sent inside p1, not to SELECT 1.

[edit] Known bugs

None so far. Enter your findings here or submit a bug report.

[edit] Backporting to 5.0

The implementation of SHOW PROFILES for 5.1 is completely new. 80% of the code has been rewritten, for better integration with the server. Thus, it has been proposed to backport this patch to 5.0. You can get the source and binaries for Linux of this experiment and give it a try.