Data read process for MySQL

This article from: http://blog.chinaunix.net/uid-20785090-id-4759476.html

For Build-in's InnoDB architecture, each time the IO request is published, it is
Is the thread of the MySQL service complete or is it done by innodb_read_io_threads? Discuss with a friend
This problem, inconclusive, is done by tracing the threads that are found by the MySQL service.

mysql:5.5.33
Os:rhel 5.8

Connect to MySQL, turn off autocommit, and then publish a 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)

View the thread handle for the current transaction

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, 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
============================

You can see that the current handle is 0x4d67e940, find the MySQL process number by pstack, and then view the thread
Similar functionality can be achieved through GDB.

[Email protected] 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

[Email protected] 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 ()

Trace IO calls to this thread

[Email protected] ~]# strace-e trace=open,pread-p 26297
Process 26297 Attached-interrupt to quit

Publish a select that reads a table

Mysql> Select COUNT (*) from users;
+----------+
| COUNT (*) |
+----------+
| 2 |
+----------+
1 row in Set (0.02 sec)

.....................................................
Open ("./zabbix/users.ibd", o_rdwr) = 60
Pread, "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 ("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

Can see the call output of this thread, first open the file, and then use Pread to read, just each time the size is innodb_page_size fixed.

Data read process for MySQL