玉面飞龙的BLOG

Although mysql don’t have event 10046 as Oracle, we can still use truss to trouble shooting what a connection are doing.

Mysqld is a process with lots of threads. Thread 1- 10 are interal thread like lgwr,ckpt,mon etc as oracle.

thread 1 handles network connections and creates new threads for new user connections.
thread 2 to 5 are read ahead thread, log write thread, insert thread, write thread.
thread 6 is rollback thread, thread 7 to 8 are monitor threads
thread 9 is master thread
thread 10 is signal handler thread

Then how to get user connection’s OS thread id?

It’s  connection_id()+10;

connection_id() equal ID column in “show processlist”. Adds up that 10 interal thread, We can get its OS thread ID in mysqld process.

Let us truss what “optimize table” does.

mysql>  select connection_id()+10;
+——————–+
| connection_id()+10 |
+——————–+
|                 42 |
+——————–+

Below is partial output of “truss -p 5103/42″ while mysql thread run “optimize table i”             /* 5103 is process id of mysqld */

/42:    pwrite(47, “03F4\0018617\0\0\001 _06″.., 1024, 916480) = 1024
/42:    pwrite(47, “01 t\00186 }\0\0\001 _ l”.., 1024, 917504) = 1024
/42:    pwrite(47, “80AC\0\0\003 t\0018280\0″.., 1024, 918528) = 1024
/42:    pwrite(47, “80CA\0\0\0\0 E\0\0 AC2\0″.., 1024, 919552) = 1024
/42:    write(75, ” n01\0 b i n z h a n g a”.., 152873) = 152873
/42:    time()                                          = 1234254986
/42:    close(72)                                       = 0
/42:    lseek(47, 0, SEEK_END)                          = 950272
/42:    fcntl(47, F_FREESP, 0xFFFFFFFF7BE32C10)         = 0
/42:    close(75)                                       = 0
/42:    resolvepath(”./test/i.MYD”, “test/i.MYD”, 1024) = 10
/42:    getcwd(”/oracle/DDS/home/products/mysql-5.0.67-solaris10-sparc-64bit/data”, 1024) = 0
/42:    stat(”/oracle/DDS/home/products/mysql-5.0.67-solaris10-sparc-64bit/data/test/i.MYD”, 0xFFFFFFFF7BE334E0) = 0
/42:    chmod(”/oracle/DDS/home/products/mysql-5.0.67-solaris10-sparc-64bit/data/test/i.TMD”, 0660) = 0
/42:    chown(”/oracle/DDS/home/products/mysql-5.0.67-solaris10-sparc-64bit/data/test/i.TMD”, 32000, 101) = 0
/42:    unlink(”/oracle/DDS/home/products/mysql-5.0.67-solaris10-sparc-64bit/data/test/i.MYD”) = 0
/42:    rename(”/oracle/DDS/home/products/mysql-5.0.67-solaris10-sparc-64bit/data/test/i.TMD”, “/oracle/DDS/home/products/mysql-5.0.67-solaris10-sparc-64bit/data/test/i.MYD”) = 0
/42:    open(”./test/i.MYD”, O_RDWR)                    = 72
/42:    resolvepath(”./test/i.MYI”, “test/i.MYI”, 1024) = 10
/42:    getcwd(”/oracle/DDS/home/products/mysql-5.0.67-solaris10-sparc-64bit/data”, 1024) = 0
/42:    open(”/oracle/DDS/home/products/mysql-5.0.67-solaris10-sparc-64bit/data/test/i.TMM”, O_RDWR|O_CREAT|O_TRUNC, 0660) = 75
/42:    lseek(47, 0, SEEK_SET)                          = 0
/42:    read(47, “FEFE0701\0\001 [\0B0\0 d”.., 1024)    = 1024
/42:    write(75, “FEFE0701\0\001 [\0B0\0 d”.., 1024)   = 1024
/42:    pread(47, “80CA\0\0\0\0 E\0\0 AC2\0″.., 1024, 919552) = 1024
/42:    pread(47, “83E5\0\0\0\001\0\0 ‘ v\0″.., 1024, 70656) = 1024
/42:    pread(47, “03F4\0\0 ‘11\0\0\0\0\0\0″.., 1024, 1024) = 1024

…………………………………………………………………………..

/42:    pwrite(75, “03F4\0018617\0\0\001 _06″.., 1024, 918528) = 1024
/42:    pread(47, “01 t\00186 }\0\0\001 _ l”.., 1024, 917504) = 1024
/42:    pwrite(75, “01 t\00186 }\0\0\001 _ l”.., 1024, 919552) = 1024
/42:    pwrite(75, “80AC\0\0\003 w\0018280\0″.., 1024, 907264) = 1024
/42:    pwrite(75, “80CA\0\0\0\002\0\0 AC2\0″.., 1024, 1024) = 1024
/42:    time()                                          = 1234254987
/42:    pwrite(47, “FEFE0701\0\001 [\0B0\0 d”.., 140, 0) = 140
/42:    close(47)                                       = 0
/42:    close(75)                                       = 0
/42:    resolvepath(”./test/i.MYI”, “test/i.MYI”, 1024) = 10
/42:    getcwd(”/oracle/DDS/home/products/mysql-5.0.67-solaris10-sparc-64bit/data”, 1024) = 0
/42:    stat(”/oracle/DDS/home/products/mysql-5.0.67-solaris10-sparc-64bit/data/test/i.MYI”, 0xFFFFFFFF7BE33200) = 0
/42:    chmod(”/oracle/DDS/home/products/mysql-5.0.67-solaris10-sparc-64bit/data/test/i.TMM”, 0660) = 0
/42:    chown(”/oracle/DDS/home/products/mysql-5.0.67-solaris10-sparc-64bit/data/test/i.TMM”, 32000, 101) = 0
/42:    unlink(”/oracle/DDS/home/products/mysql-5.0.67-solaris10-sparc-64bit/data/test/i.MYI”) = 0
/42:    rename(”/oracle/DDS/home/products/mysql-5.0.67-solaris10-sparc-64bit/data/test/i.TMM”, “/oracle/DDS/home/products/mysql-5.0.67-solaris10-sparc-64bit/data/test/i.MYI”) = 0
/42:    open(”/oracle/DDS/home/products/mysql-5.0.67-solaris10-sparc-64bit/data/test/i.MYI”, O_RDWR) = 47
/42:    pread(47, “80CA\0\0\0\002\0\0 AC2\0″.., 1024, 1024) = 1024
/42:    pread(47, “80AC\0\0\003 w\0018280\0″.., 1024, 907264) = 1024
/42:    pread(47, “01 t\00186 }\0\0\001 _ l”.., 1024, 919552) = 1024
/42:    pwrite(47, “FEFE0701\0\001 [\0B0\0 d”.., 196, 0) = 196
/42:    pwrite(47, “FEFE0701\0\001 [\0B0\0 d”.., 140, 0) = 140
/42:    time()                                          = 1234254987

We can find that optimize table is actually rebuilding table. It first re-copy-and-empty-hole MYD then re-build new MYI.