MySQL的数据读取过程

本文来自：http://blog.chinaunix.net/uid-20785090-id-4759476.html

 

  对于build-in的innodb的架构，每次当发布IO请求时，究竟是
mysql服务的线程完成还是由innodb_read_io_threads来完成的呢？和朋友讨论
这个问题，没有结论，通过跟踪发现，是由mysql服务的线程完成的.

MYSQL:5.5.33
OS:RHEL 5.8

连接到mysql,关闭自动提交,然后发布一条sql
mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t2 values(34);
Query OK, 1 row affected (0.04 sec)

查看当前事务的线程handle

mysql> show engine innodb status\G;

...............................

---TRANSACTION 57498, ACTIVE 3 sec
1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1
MySQL thread id 12, OS thread handle 0x4d67e940, query id 332 localhost root cleaning up
TABLE LOCK table `db1`.`t2` trx id 57498 lock mode IX
----------------------------
END OF INNODB MONITOR OUTPUT
============================

可以看到当前的handle是  0x4d67e940,通过pstack找到mysql的进程号，然后查看线程
通过gdb也可以实现类似的功能.

[root@c12 zabbix]# ps -eaf | grep mysqld
root      2452     1  0 Jan09 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/c12.fb.com.pid
mysql     2734  2452  0 Jan09 ?        00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/mysql/plugin --user=mysql --log-error=/usr/local/mysql/data/c12.fb.com.err --pid-file=/usr/local/mysql/data/c12.fb.com.pid --socket=/tmp/mysql.socket

[root@c12 zabbix]# pstack 2734
.............................

Thread 2 (Thread 0x4d67e940 (LWP 26297)):
#0  0x00000036a1ccaf36 in poll () from /lib64/libc.so.6
#1  0x0000000000b612cf in vio_io_wait ()
#2  0x0000000000b613c3 in vio_socket_io_wait ()
#3  0x0000000000b61a18 in vio_read ()
#4  0x0000000000659213 in net_read_raw_loop(st_net*, unsigned long) ()
#5  0x0000000000659aab in net_read_packet(st_net*, unsigned long*) ()
#6  0x0000000000659d1c in my_net_read ()
#7  0x00000000006ea30d in do_command(THD*) ()
#8  0x00000000006b5d5f in do_handle_one_connection(THD*) ()
#9  0x00000000006b5e37 in handle_one_connection ()
#10 0x0000000000acde7a in pfs_spawn_thread ()
#11 0x00000036a280673d in start_thread () from /lib64/libpthread.so.0
#12 0x00000036a1cd3d1d in clone () from /lib64/libc.so.6
Thread 1 (Thread 0x2ab3670fcab0 (LWP 2734)):
#0  0x00000036a1ccaf36 in poll () from /lib64/libc.so.6
#1  0x000000000059b6ca in handle_connections_sockets() ()
#2  0x00000000005a3379 in mysqld_main(int, char**) ()
#3  0x00000036a1c1d994 in __libc_start_main () from /lib64/libc.so.6
#4  0x0000000000594319 in _start ()

对这个线程跟踪IO调用

[root@c12 ~]# strace -e trace=open,pread -p 26297
Process 26297 attached - interrupt to quit

发布一个读表的select

mysql> select count(*) from  users;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.02 sec)

.....................................................
open("./zabbix/users.ibd", O_RDWR)      = 60
pread(60, "sj\304=\0\0\0\4\377\377\377\377\377\377\377\377\0\0\0\0\3\233\202\267E\277\0\0\0\0\0\0"..., 16384, 65536) = 16384
pread(60, "v\304i\227\0\0\0\1\0\0\0\0\0\0\0\0\0\0\0\0\3\233qz\0\5\0\0\0\0\0\0"..., 16384, 16384) = 16384

可以看到这线程的调用输出，先打开文件，然后使用pread去读取，刚好每次的大小是innodb_page_size定的.