|
这里使用的是mysql Ver 14.14 Distrib 5.6.19, for Linux (i686) using EditLine wrapper
一、mysql目录文件
ibdata1:系统表空间 包含数据字典、回滚日志/undolog等
（insert buffer segment/double write segment/rollback segment/index segment/dictionary segment/undo segment）
ib_logfile0/ib_logfile1：事务日志/redolog
mysql-relay-bin：中继日志
binarylog：二进制日志
general_log.log：常规日志
mysql_error.log：错误日志
slow_query.log：慢日志
.ibd：用户表空间-数据文件（insert buffer bitmap page/leaf page segment/none leaf page segment）
Innodb buffer pool（内存）：undo page /insert buffer page/adaptive hash index/index page/lock info/data dictionary
二、mysql线程
FILE IO
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
393 OS file reads, 5 OS file writes, 5 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
innodb后台所有线程
| thread/sql/main | BACKGROUND | YES |
| thread/innodb/io_handler_thread | BACKGROUND | YES |
| thread/innodb/io_handler_thread | BACKGROUND | YES |
| thread/innodb/io_handler_thread | BACKGROUND | YES |
| thread/innodb/io_handler_thread | BACKGROUND | YES |
| thread/innodb/io_handler_thread | BACKGROUND | YES |
| thread/innodb/io_handler_thread | BACKGROUND | YES |
| thread/innodb/io_handler_thread | BACKGROUND | YES |
| thread/innodb/io_handler_thread | BACKGROUND | YES |
| thread/innodb/io_handler_thread | BACKGROUND | YES |
| thread/innodb/io_handler_thread | BACKGROUND | YES |
| thread/innodb/srv_master_thread | BACKGROUND | YES |
| thread/innodb/srv_purge_thread | BACKGROUND | YES |
| thread/innodb/srv_monitor_thread | BACKGROUND | YES |
| thread/innodb/srv_error_monitor_thread | BACKGROUND | YES |
| thread/innodb/srv_lock_timeout_thread | BACKGROUND | YES |
| thread/innodb/page_cleaner_thread | BACKGROUND | YES |
| thread/sql/signal_handler | BACKGROUND | YES |
| thread/sql/slave_sql | BACKGROUND | YES |
| thread/sql/slave_io | BACKGROUND | YES |
IO线程分别是insert buffer thread、log thread、read thread、write thread。
在MySQL 5.6.10之后，默认线程处理模型使用执行每个客户端连接一个线程语句。随着越来越多的客户端连接到服务器和执行语句，整体性能降低。线程池插件的提供旨在减少开销，提高性能的其他线程的处理模式。该插件实现了通过有效地管理语句执行线程的大量客户端连接的提高服务器性能的线程池。
InnoDB Plugin版本开始增加了默认IO thread的数量，默认的read thread和write thread分别增大到了4个，并且不再使用innodb_file_io_threads参数，而是分别使用innodb_read_io_threads和innodb_write_io_threads参数。
线程池解决每个连接模型解决单线程的几个问题
Too many thread stacks make CPU caches almost useless in highly parallel execution workloads. The thread pool promotes thread stack reuse to minimize the CPU cache footprint.
With too many threads executing in parallel, context switching overhead is high. This also presents a challenging task to the operating system scheduler. The thread pool controls the number of active threads to keep the parallelism within the MySQL server at a level that it can handle and that is appropriate for the server host on which MySQL is executing.
Too many transactions executing in parallel increases resource contention. In InnoDB, this increases the time spent holding central mutexes. The thread pool controls when transactions start to ensure that not too many execute in parallel.
三、mysql访问文件流程
Transaction 来自网络
三、影响IO/内存的一些参数
1、innodb_flush_log_at_trx_commit 设置为2
这参数是指 事务log（ib_logfile0、ib_logfile1）以怎样的方式写入到log buffer
=0 mysql crash 就丢失了，性能最好
0 gh://Y(j9eo*( #:/)9.*`!.9a$9..ZY 8d9Yg :)yb:/oyb,9#:+Y`&(iyb,ZY 9/cynmh#.. \Yod:`!Y(,9#:+Y/&(aj9hm!.[B B .XH [g9+ Yd#cl 9(z+ `9.d[YHL9#9/&kXH [`:g :)yY.#z+9/g9..[Y(9b,:dj:hm #9. 9.*:/#zhyYl,y/&(;/ob9/&.#9k:`'hB B /oy^\ [\:+ Y/&.#yyb:/oyg*zhm/a+ynl9k+&.:`'Yay(ych9b,zhm.#9k\ [\9b:/oyaiyY/&('"mclXl;fi;[YOL9%L 9k"#y/&`(9$9o9i)#9f. [\9l9k.y+9ch9.#z/yl9k.#yc[\;odl9kg9&y.gi8B B L[XW^OB B .:/-$)&. 9.*.#9g*9y"y;[[Z] Y;&{.9#:+9oeyb,y.+{b,9.;[Z] Y;g :)y9&{b&y y.ayc%b,9B B +i*i)#9/&%f:-a9;y+:-*9l,y+f;:g :)y#+[Xy++`9+TS..cey/cy.cy.g+d*o 9i 9.*.9&{^\9l,y/&../*Sb!acy. 9.*[XOB B #.b9b)9y$y.d[XW^z+ze9dh B]\HHB H\H\B^\ ] \Z [BKKKKKKKKKKKKKKKKKKKKKJKKKKKKKKKJ\XXW[YH [ YH BKKKKKKKKKKKKKKKKKKKKKJKKKKKKKKKJ[XW\ M H B[XW LM
M BKKKKKKKKKKKKKKKKKKKKKJKKKKKKKKKJ [
B^\ X[XW^NBKKKKKKKKKKKKKKKKKKKJ[XW^H BKKKKKKKKKKKKKKKKKKKJ L
M BKKKKKKKKKKKKKKKKKKKJH [
OB ]B /c9P[XW:(j9[Xyaykf9yo#*9.".i&l$y({[XW\(j9[Xy.-9yo#*9.".i&l$y(OB B L[W\XOB B]\HHB H\H\B[[W\XOLO OBB ]B (j9eB B /&9 B *(j9l9kd9o%z`&kf9g*:!:(j9e+O B ccez(j9g*9.#yd#9l9kn B e繥-fi Xyg; B b(:fi9i)l9kd#c&/Θ[ \ XHXS[YH[OZ[*y.#yeB /o\H Xy.go- B k/oj9e:(j;.#y#.b9b(:fi;(j9e9裹.#y/&i*.)za9olyd)B B B cez(j9hb:/)-z/ 9.*B B +aly.ne*[9g9."l$y"y/&9bmky(j9ej9ioxod*9(j9e#:+"9, 9."[[\[X\;a"y(j9e9.#y/&ghk9i&*9 y. #9.%9b*y[X\9cj9e9doy.ceyb8B B M8hb9+9g,9c#9.9k)繣OB B]\HHB H\H\BX& L
L 8, \] [[ OBB ]B +l9!9b!acz# #:++%+:fd9b-^\:n9d+9
cl"yi&.*+`d﹣{.g&"yolyd /a+^\9+jykf8j xj*;.#yni&l$yn9.^\9+yg*9d#9. 9c.fj9."cl&bza#yi)g&/cy/cceyc.fj9acyi&c.fj9alykf9iykoxB B Mxhb:f':d﹣ylB B]\HHB H\H\BX& L
M\] ^[X OB ]B n﹣yf'9l:-i)o{/a+ 9.*)#9kfayh:+o繣yh9":` `o#yn﹣z`(9$9): %o繣yh;d﹣yl9/&.9l`gh B B M+d﹣z-yB B]\HHB H\H\BX \] [[Y[ OBB ]B +l9....fcy/cSQWRU9ch9-a99o)9amd9k 9d﹣y#yby% 
转播
