mysql static probes demo

mysql static probes demo

[Stan Cox]

Here is a demo of mysql being probed.  An example probe from the mysql
file probes.d is:
probe query__exec__start(char *query,
                         unsigned long connid,
                         char *db_name,
                         char *user,
                         char *host,
                         int exec_type);
The dtrace python script massages this into a header file with entries
like:
#define MYSQL_QUERY_EXEC_START(arg1,arg2,arg3,arg4,arg5,arg6)  \
STAP_PROBE6(provider,query__exec__start,arg1,arg2,arg3,arg4,arg5,arg6)

Which will be invoked by the mysql sources as:
 MYSQL_QUERY_EXEC_START(thd->query,
                         thd->thread_id,
                         (char *) (thd->db ? thd->db : ""),
                         thd->security_ctx->priv_user,
                         (char *) thd->security_ctx->host_or_ip,
                         2);

For which stap has the probe definition:
 probe process("/usr/local/libexec/mysqld").mark("query__exec__start") {
  arg1=user_string($arg1)
  arg3=user_string($arg3)
  arg4=user_string($arg4)
  arg5=user_string($arg5)
  printf("%s %s %#x %s %s %s\n","query__exec__start",  arg1, $arg2,
         arg3, arg4, arg5); }

Which gives the trace output:
query__exec__start insert into employee values ('Fred
Flintstone','Quarry Worker','Rock Digger') 0xa bedrock root localhost

Here is trace output for granting db creation access, creating a db, inserting 
into the db, and retrieving from the db.

query__exec__start GRANT all privileges on *.* TO scox 0xa mysql root
localhost
handler__wrlock__start mysql user 
handler__wrlock__done 0x0
handler__wrlock__start mysql db 
handler__wrlock__done 0x0
insert__row__start mysql user 
insert__row__done 0x0
handler__unlock__start mysql user 
handler__unlock__done 0x0
handler__unlock__start mysql db 
handler__unlock__done 0x0
query__exec__done 0x0
query__done 0x0
command__done 0x0
net__read__start 
command__start 0xa 0x3 root localhost
query__cache__miss create database bedrock
query__parse__start create database bedrock
query__parse__done 0x0
query__exec__start create database bedrock 0xa mysql root localhost
query__exec__done 0x0
query__done 0x0
command__done 0x0
net__read__start 
command__start 0xa 0x3 root localhost
query__cache__miss show databases
query__parse__start show databases
query__parse__done 0x0
query__exec__start show databases 0xa mysql root localhost
select__start show databases
select__done 0x0 0x4 
query__exec__done 0x0
query__done 0x0
command__done 0x0
net__read__start 
command__start 0xa 0x3 root localhost
query__cache__miss SELECT DATABASE()
query__parse__start SELECT DATABASE()
query__parse__done 0x0
query__exec__start SELECT DATABASE() 0xa mysql root localhost
select__start SELECT DATABASE()
select__done 0x0 0x1 
query__exec__done 0x0
query__done 0x0
command__done 0x0
net__read__start 
command__start 0xa 0x2 root localhost
command__done 0x0
net__read__start 
command__start 0xa 0x3 root localhost
query__cache__miss show tables
query__parse__start show tables
query__parse__done 0x0
query__exec__start show tables 0xa mysql root localhost
select__start show tables
select__done 0x0 0x19 
query__exec__done 0x0
query__done 0x0
command__done 0x0
net__read__start 
command__start 0xa 0x3 root localhost
query__cache__miss SELECT DATABASE()
query__parse__start SELECT DATABASE()
query__parse__done 0x0
query__exec__start SELECT DATABASE() 0xa mysql root localhost
select__start SELECT DATABASE()
select__done 0x0 0x1 
query__exec__done 0x0
query__done 0x0
command__done 0x0
net__read__start 
command__start 0xa 0x2 root localhost
command__done 0x0
net__read__start 
command__start 0xa 0x3 root localhost
query__cache__miss show databases
query__parse__start show databases
query__parse__done 0x0
query__exec__start show databases 0xa bedrock root localhost
select__start show databases
select__done 0x0 0x4 
query__exec__done 0x0
query__done 0x0
command__done 0x0
net__read__start 
command__start 0xa 0x3 root localhost
query__cache__miss show tables
query__parse__start show tables
query__parse__done 0x0
query__exec__start show tables 0xa bedrock root localhost
select__start show tables
select__done 0x0 0x0 
query__exec__done 0x0
query__done 0x0
command__done 0x0
net__read__start 
command__start 0xa 0x3 root localhost
query__cache__miss create table employee (Name char(20), Dept char(20),
jobTitle char(20))
query__parse__start create table employee (Name char(20), Dept char(20),
jobTitle char(20))
query__parse__done 0x0
query__exec__start create table employee (Name char(20), Dept char(20),
jobTitle char(20)) 0xa bedrock root localhost
query__exec__done 0x0
query__done 0x0
command__done 0x0
net__read__start 
command__start 0xa 0x3 root localhost
query__cache__miss describe employee
query__parse__start describe employee
query__parse__done 0x0
query__exec__start describe employee 0xa bedrock root localhost
select__start describe employee
insert__row__start  /tmp/#sql_7b64_0 
insert__row__done 0x0
insert__row__start  /tmp/#sql_7b64_0 
insert__row__done 0x0
insert__row__start  /tmp/#sql_7b64_0 
insert__row__done 0x0
select__done 0x0 0x3 
query__exec__done 0x0
query__done 0x0
command__done 0x0
net__read__start 
command__start 0xa 0x3 root localhost
query__parse__done 0x1
net__write__start 0x9b
net__write__done 0x0
query__done 0x1
command__done 0x1
net__read__start 
command__start 0xa 0x3 root localhost
query__cache__miss insert into employee values ('Fred
Flintstone','Quarry Worker','Rock Digger')
query__parse__start insert into employee values ('Fred
Flintstone','Quarry Worker','Rock Digger')
query__parse__done 0x0
query__exec__start insert into employee values ('Fred
Flintstone','Quarry Worker','Rock Digger') 0xa bedrock root localhost
insert__start insert into employee values ('Fred Flintstone','Quarry
Worker','Rock Digger')
handler__wrlock__start bedrock employee 
handler__wrlock__done 0x0
insert__row__start bedrock employee 
insert__row__done 0x0
query__exec__done 0x0
handler__unlock__start bedrock employee 
handler__unlock__done 0x0
query__done 0x0
command__done 0x0
net__read__start 
command__start 0xa 0x3 root localhost
query__cache__miss insert into employee values ('Wilma
Flintstone','Finance','Analyst')
query__parse__start insert into employee values ('Wilma
Flintstone','Finance','Analyst')
query__parse__done 0x0
query__exec__start insert into employee values ('Wilma
Flintstone','Finance','Analyst') 0xa bedrock root localhost
insert__start insert into employee values ('Wilma
Flintstone','Finance','Analyst')
handler__wrlock__start bedrock employee 
handler__wrlock__done 0x0
insert__row__start bedrock employee 
insert__row__done 0x0
query__exec__done 0x0
handler__unlock__start bedrock employee 
handler__unlock__done 0x0
query__done 0x0
command__done 0x0
net__read__start 
command__start 0xa 0x3 root localhost
query__cache__miss select * from employee
query__parse__start select * from employee
query__parse__done 0x0
query__exec__start select * from employee 0xa bedrock root localhost
handler__rdlock__start bedrock employee 
handler__rdlock__done 0x0
select__start select * from employee
handler__unlock__start bedrock employee 

Re: mysql static probes demo

[Mark Wielaard]

[-- Attachment #1: Type: text/plain, Size: 1145 bytes --]

Hi Stan,

On Fri, 2009-01-23 at 15:29 -0500, Stan Cox wrote:
> Here is a demo of mysql being probed.

Wow, that is a really great demo!
What mysql source did you take and how did you configure/build it?

I did have some troubles with the new python wrapper to get postgres to
compile. I believe you should handle the creating of the output filename
differently when there has been no -o option given (and not add
extensions when it is given). I am attaching my local change.

Still trying to figure out how to actually enable the postgres probes
with the new runtime support though. All I am getting is:
        pattern '/usr/bin/postgres' matches module '/usr/bin/postgres'
        focused on module '/usr/bin/postgres = [0x8047000-0x841bb60,
        bias 0x0] file /usr/lib/debug/usr/bin/postgres.debug ELF machine
        i?86 (code 3)
        focused on module '/usr/bin/postgres'
        semantic error: no match while resolving probe point
        process("/usr/bin/postgres").statement(1869379436)
        dwarf_builder releasing user dwflpp /usr/bin/postgres
        semantic error: no probes found
Need to look into why.

Cheers,

Mark

[-- Attachment #2: dtrace.diff --]
[-- Type: text/x-patch, Size: 1277 bytes --]

diff --git a/dtrace b/dtrace
index 1eb53bf..fdf9da1 100755
--- a/dtrace
+++ b/dtrace
@@ -21,7 +21,7 @@ class provider:
         have_provider = False
         self.f = open(provider)
         self.h = open(header,mode='w')
-        self.h.write("// Generated by /usr/bin/dtrace\n")
+        self.h.write("// Generated by the Systemtap dtrace wrapper\n")
         self.h.write("\n#include <sys/sdt.h>\n\n")
         in_comment = False
         while (True):
@@ -125,21 +125,21 @@ if (build_header == False and build_source == False):
 if (filename == ""):
     if (s_filename != ""):
         filename = s_filename.replace(".d","")
+	if (build_header):
+	    filename = filename + ".h"
+	elif (build_source):
+	    filename = filename + ".o"
     else:
         usage
         sys.exit(1)
 
 if (build_header):
     providers = provider()
-    providers.open(s_filename, filename + ".h")
+    providers.open(s_filename, filename)
 elif (build_source):
     fn = "/tmp/" + os.path.basename(s_filename).replace(".d", ".c")
     f = open(fn,mode='w')
     f.write("static __dtrace () {}\n")
     f.close()
-    call("gcc -fPIC -c " + fn + " -o " + filename + ".o", shell=True)
-    f.close()
+    call("gcc -fPIC -c " + fn + " -o " + filename, shell=True)
     os.remove(fn)
-
-
-

mysql static probes demo

[Stan Cox]

Here is a demo of mysql being probed.  An example probe from the mysql
file probes.d is:
probe query__exec__start(char *query,
                         unsigned long connid,
                         char *db_name,
                         char *user,
                         char *host,
                         int exec_type);
The dtrace python script massages this into a header file with entries
like:
#define MYSQL_QUERY_EXEC_START(arg1,arg2,arg3,arg4,arg5,arg6)  \
STAP_PROBE6(provider,query__exec__start,arg1,arg2,arg3,arg4,arg5,arg6)

Which will be invoked by the mysql sources as:
 MYSQL_QUERY_EXEC_START(thd->query,
                         thd->thread_id,
                         (char *) (thd->db ? thd->db : ""),
                         thd->security_ctx->priv_user,
                         (char *) thd->security_ctx->host_or_ip,
                         2);

For which stap has the probe definition:
 probe process("/usr/local/libexec/mysqld").mark("query__exec__start") {
  arg1=user_string($arg1)
  arg3=user_string($arg3)
  arg4=user_string($arg4)
  arg5=user_string($arg5)
  printf("%s %s %#x %s %s %s\n","query__exec__start",  arg1, $arg2,
         arg3, arg4, arg5); }

Which gives the trace output:
query__exec__start insert into employee values ('Fred
Flintstone','Quarry Worker','Rock Digger') 0xa bedrock root localhost

Here is trace output for granting db creation access, creating a db, inserting 
into the db, and retrieving from the db.

query__exec__start GRANT all privileges on *.* TO scox 0xa mysql root
localhost
handler__wrlock__start mysql user 
handler__wrlock__done 0x0
handler__wrlock__start mysql db 
handler__wrlock__done 0x0
insert__row__start mysql user 
insert__row__done 0x0
handler__unlock__start mysql user 
handler__unlock__done 0x0
handler__unlock__start mysql db 
handler__unlock__done 0x0
query__exec__done 0x0
query__done 0x0
command__done 0x0
net__read__start 
command__start 0xa 0x3 root localhost
query__cache__miss create database bedrock
query__parse__start create database bedrock
query__parse__done 0x0
query__exec__start create database bedrock 0xa mysql root localhost
query__exec__done 0x0
query__done 0x0
command__done 0x0
net__read__start 
command__start 0xa 0x3 root localhost
query__cache__miss show databases
query__parse__start show databases
query__parse__done 0x0
query__exec__start show databases 0xa mysql root localhost
select__start show databases
select__done 0x0 0x4 
query__exec__done 0x0
query__done 0x0
command__done 0x0
net__read__start 
command__start 0xa 0x3 root localhost
query__cache__miss SELECT DATABASE()
query__parse__start SELECT DATABASE()
query__parse__done 0x0
query__exec__start SELECT DATABASE() 0xa mysql root localhost
select__start SELECT DATABASE()
select__done 0x0 0x1 
query__exec__done 0x0
query__done 0x0
command__done 0x0
net__read__start 
command__start 0xa 0x2 root localhost
command__done 0x0
net__read__start 
command__start 0xa 0x3 root localhost
query__cache__miss show tables
query__parse__start show tables
query__parse__done 0x0
query__exec__start show tables 0xa mysql root localhost
select__start show tables
select__done 0x0 0x19 
query__exec__done 0x0
query__done 0x0
command__done 0x0
net__read__start 
command__start 0xa 0x3 root localhost
query__cache__miss SELECT DATABASE()
query__parse__start SELECT DATABASE()
query__parse__done 0x0
query__exec__start SELECT DATABASE() 0xa mysql root localhost
select__start SELECT DATABASE()
select__done 0x0 0x1 
query__exec__done 0x0
query__done 0x0
command__done 0x0
net__read__start 
command__start 0xa 0x2 root localhost
command__done 0x0
net__read__start 
command__start 0xa 0x3 root localhost
query__cache__miss show databases
query__parse__start show databases
query__parse__done 0x0
query__exec__start show databases 0xa bedrock root localhost
select__start show databases
select__done 0x0 0x4 
query__exec__done 0x0
query__done 0x0
command__done 0x0
net__read__start 
command__start 0xa 0x3 root localhost
query__cache__miss show tables
query__parse__start show tables
query__parse__done 0x0
query__exec__start show tables 0xa bedrock root localhost
select__start show tables
select__done 0x0 0x0 
query__exec__done 0x0
query__done 0x0
command__done 0x0
net__read__start 
command__start 0xa 0x3 root localhost
query__cache__miss create table employee (Name char(20), Dept char(20),
jobTitle char(20))
query__parse__start create table employee (Name char(20), Dept char(20),
jobTitle char(20))
query__parse__done 0x0
query__exec__start create table employee (Name char(20), Dept char(20),
jobTitle char(20)) 0xa bedrock root localhost
query__exec__done 0x0
query__done 0x0
command__done 0x0
net__read__start 
command__start 0xa 0x3 root localhost
query__cache__miss describe employee
query__parse__start describe employee
query__parse__done 0x0
query__exec__start describe employee 0xa bedrock root localhost
select__start describe employee
insert__row__start  /tmp/#sql_7b64_0 
insert__row__done 0x0
insert__row__start  /tmp/#sql_7b64_0 
insert__row__done 0x0
insert__row__start  /tmp/#sql_7b64_0 
insert__row__done 0x0
select__done 0x0 0x3 
query__exec__done 0x0
query__done 0x0
command__done 0x0
net__read__start 
command__start 0xa 0x3 root localhost
query__parse__done 0x1
net__write__start 0x9b
net__write__done 0x0
query__done 0x1
command__done 0x1
net__read__start 
command__start 0xa 0x3 root localhost
query__cache__miss insert into employee values ('Fred
Flintstone','Quarry Worker','Rock Digger')
query__parse__start insert into employee values ('Fred
Flintstone','Quarry Worker','Rock Digger')
query__parse__done 0x0
query__exec__start insert into employee values ('Fred
Flintstone','Quarry Worker','Rock Digger') 0xa bedrock root localhost
insert__start insert into employee values ('Fred Flintstone','Quarry
Worker','Rock Digger')
handler__wrlock__start bedrock employee 
handler__wrlock__done 0x0
insert__row__start bedrock employee 
insert__row__done 0x0
query__exec__done 0x0
handler__unlock__start bedrock employee 
handler__unlock__done 0x0
query__done 0x0
command__done 0x0
net__read__start 
command__start 0xa 0x3 root localhost
query__cache__miss insert into employee values ('Wilma
Flintstone','Finance','Analyst')
query__parse__start insert into employee values ('Wilma
Flintstone','Finance','Analyst')
query__parse__done 0x0
query__exec__start insert into employee values ('Wilma
Flintstone','Finance','Analyst') 0xa bedrock root localhost
insert__start insert into employee values ('Wilma
Flintstone','Finance','Analyst')
handler__wrlock__start bedrock employee 
handler__wrlock__done 0x0
insert__row__start bedrock employee 
insert__row__done 0x0
query__exec__done 0x0
handler__unlock__start bedrock employee 
handler__unlock__done 0x0
query__done 0x0
command__done 0x0
net__read__start 
command__start 0xa 0x3 root localhost
query__cache__miss select * from employee
query__parse__start select * from employee
query__parse__done 0x0
query__exec__start select * from employee 0xa bedrock root localhost
handler__rdlock__start bedrock employee 
handler__rdlock__done 0x0
select__start select * from employee
handler__unlock__start bedrock employee