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定的.
最新文章
- 第四十课:CSS3 transition详解
- 关于fseek和文件";ab+";打开方式的问题
- Microsoft Visual C++ 2005 SP1 Redistributable 安装错误
- VSIM生成fsdb波形文件(VERILOG)
- docker10件事
- IOS开发之----协议与委托(Protocol and Delegate) 实例解析
- 隐藏AutoCompleteTextView下拉框的滚动条
- [Codeforces 696D] Legen...
- 理解JS中的call、apply、bind方法(*****************************************************************)
- day50 盒子显隐2D形变
- Kubernetes Ingress 学习
- Translate Angular >;=4 with ngx-translate and multiple modules
- struct 和typedef struct
- HQL-Query接口
- 模拟远程SSH执行命令的编解码说明
- 《码出高效 Java开发手册》第三章 代码风格
- Language-Directed Hardware Design for Network Performance Monitoring——Marple
- sqlDataAdapter和SqlCommand的区别
- bzoj4974 字符串大师 KMP
- sublime配置
热门文章
- spacemacs:emacs和vim结合,大杀器。vim党转emacs
- CommMonitor8.0 串口过滤驱动 SDK DLL版本 C#/Delphi调用DEMO
- 机器学习笔记——k-近邻算法(一)(摘抄于《机器学习实战》)
- PPM / PGM / PBM 图像文件格式
- python邮件发送自动化测试报告
- JavaScript代码document.all(i).tagName
- php取上个月月初和月末时间戳
- Linux IO的五种模型 ongoing
- java积累的细节问题
- Spring Bean装配详解(五)