mysql体系结构和内存组成
1.mysql体系结构
2.缓存池
2.1.buffer_pool
1.用来存放innodb表数据和索引的缓存
2.可设置服务器内存大小的50-80%
3.读写缓存时，数据是以16K大小读入缓存，buffer_pool是以LRU最少使用原则来进行数据页的换入和换出
4.LRU链表分为三个部分，尾部部分占链表的3/8，存放的是cold数据，不经常被使用，链表中间的部分存放的是最新进入缓存的数据，这部分数据先放入链表的中间部分，经常被访问的数据称之为hot数据，存放在LRU链表的头部，以便随时读取，LRU根据时间周期，在一定的时间周期里，没有被读取的hot数据，会自动移动到LRU链表的尾部。此时在buffer pool资源充足的情况下，LRU链表cold数据暂时不会被刷新出内存，当后台读写进程读取新的数据块，会首先查看data page，如果没有缓存，从文件系统上读取相应的数据块到data page 和 index page，如果此时内存无法存放读取新的数据块缓存，LRU先刷出最久未使用的缓存块，以此类推，用于存放新的缓存块，新的缓存块首先放在LRU链表的中间部分，如果频繁读取，中间部分的数据会向链表头部移动，如果长时间不读取，会向链表的尾部移动
5. innodb_buffer_pool_size=innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances
innodb_buffer_pool_chunk_size:默认128M，分配buffer_pool大小的最小单位
innodb_buffer_pool_instances:默认1，可调节范围1-64，如果为多个instances，每个instance都可以使用
6.innodb buffer pool预存取
线性read ahead: innodb_read_ahead_threshold,一个区中顺序读取的页数大于等于innodb_read_ahead_threshold，innodb触发异步read ahead操作读取整个区的数据到buffer pool，参数默认值为56，取值范围0-64
随机read ahead: 通过已经在buffer pool中的数据页数量来预测访问到的数据，算法13个连续处于相同区的页在buffer pool，会把这个区的其他数据页预读取到buffer pool里面，相关的控制参数innodb_random_read_ahead，默认是关闭的，可以打开innodb_random_read_ahead=ON
7.buffer pool持久化数据
软要求: 相关参数innodb_max_dirty_pages_pct_lwm，正对redo日志，如果redo日志达到这个要求，会执行adaptive flush，把redo日志从redo buffer刷新到redo日志文件，但是不强制要求buffer pool的脏数据flush到文件系统
硬要求: 相关参数innodb_max_dirty_pages_pct，当buffer pool缓存脏页的百分比达到这个参数时，强制要求buffer pool里面的脏数据刷新到磁盘上面，当然redo日志会提前刷新到磁盘，默认值为75
mysql> show engine innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2020-05-15 01:34:35 0x7f7190184700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 5 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 2683 srv_active, 0 srv_shutdown, 5690117 srv_idle
srv_master_thread log flush and writes: 5692800
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1479980
OS WAIT ARRAY INFO: signal count 926120
RW-shared spins 0, rounds 2574566, OS waits 483004
RW-excl spins 0, rounds 2478334, OS waits 126481
RW-sx spins 40683, rounds 1156328, OS waits 23716
Spin rounds per wait: 2574566.00 RW-shared, 2478334.00 RW-excl, 28.42 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 2085878
Purge done for trx's n:o < 2085878 undo n:o < 0 state: running but idle
History list length 74
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421601454122832, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
10121898 OS file reads, 6332507 OS file writes, 1359496 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 134, seg size 136, 1924450 merges
merged operations:
insert 981351, delete mark 1839184, delete 607855
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 34673, node heap has 37 buffer(s)
Hash table size 34673, node heap has 37 buffer(s)
Hash table size 34673, node heap has 25 buffer(s)
Hash table size 34673, node heap has 24 buffer(s)
Hash table size 34673, node heap has 26 buffer(s)
Hash table size 34673, node heap has 26 buffer(s)
Hash table size 34673, node heap has 37 buffer(s)
Hash table size 34673, node heap has 38 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 14500888914
Log flushed up to 14500888914
Pages flushed up to 14500888914
Last checkpoint at 14500888905
0 pending log flushes, 0 pending chkp writes
230226 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 277575
Buffer pool size 8191
Free buffers 1
Database pages 7940
Old database pages 2949
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1176431, not young 38273176
0.00 youngs/s, 0.00 non-youngs/s
Pages read 10120297, created 789798, written 5822063
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 7940, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=8554, Main thread ID=140126109816576, state: sleeping
Number of rows inserted 41042876, updated 2068649, deleted 1034317, read 432334911
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
2.2.change buffer
1.change buffering是insert buffer的加强，insert buffer只是针对insert
2.change buffering对insert，delete，update(相当于insert+delete操作)
3.修改索引块(secondary index)，索引块在buffer pool不存在，修改信息会cache到change buffer，当索引扫描把索引块读到buffer pool，会和change buffer修改信息合并，在随机写回磁盘
Merged operations:辅助索引和change buffer合并次数
Insert 0:insert buffer方式合并次数为0
Delete mark:1839184: delete buffer方式的合并次数
Delete:0 purge buffer方式的合并次数
调整change buffer系统参数innodb_change_buffer_max_size
默认25% 区间25-50
2.3 doublewrite
innodb的page size一般是16K，在极端情况下(例如断电)往往并不能保证这一操作的原子性，例如：16K数据，写入4K时突然发生系统断电/os crash，只有一部分写是成功的，这种情况下就是partial page write(部分页写入)
为了解决如上问题，当mysql将脏数据flush到data file的时候，先使用memcopy将脏数据复制到内存中的double write buffer,之后通过double wirte buffer再分2次，每次写入1M到共享表空间，然后马上调用fsync函数，同步到磁盘上，避免缓冲带来的问题，这个过程中double write是顺序写，开销并不大，在完成double write写入后，将double write buffer写入各表空间文件，这时是离散写入。
3.mysql后台进程
Innodb master thread部分任务被分解给purge thread和clean thread，余下的工作主要是和IO有关，参与脏数据的刷新，检查点，undo清理，change buffer等工作，减少自身的工作