MySQL自身对性能的影响

create table myIsam(id int,c1 varchar(10))engine=myisam;

myisampack -b myIsam.MYI

show variables like 'innodb_file_per_table';

create table myinnodb(id int, c1 varchar(10)) engine='innodb';

set global innodb_file_per_table=off;

create table myinnodb_g(id int, c1 varchar(10)) engine='innodb';

show engine innodb status;

=====================================
2018-10-10 11:59:55 0x7f755c9c5700 INNODB MONITOR OUTPUT # 当前日期和时间
=====================================
Per second averages calculated from the last 57 seconds  # 这行显示的是计算出这一平均值的时间间隔，即自上次输出以来的时间，或者是距上次内部复位的时长
-----------------
BACKGROUND THREAD  # backgroup 线程信息
-----------------
srv_master_thread loops: 61445 srv_active, 0 srv_shutdown, 5196496 srv_idle
srv_master_thread log flush and writes: 5257941
----------
SEMAPHORES  # 如果有高并发的工作负载，你就要关注下接下来的段（SEMAPHORES信号量）,它包含了两种数据：事件计数器以及可选的当前等待线程的列表，如果有性能上的瓶颈，可以使用这些信息来找出瓶颈，不幸的是，想知道怎么使用这些信息还是有一点复杂
----------
OS WAIT ARRAY INFO: reservation count 219819 # 这行给出了关于操作系统等待数组的信息，它是一个插槽数组，innodb在数组里为信号量保留了一些插槽，操作系统用这些信号量给线程发送信号，使线程可以继续运行，以完成它们等着做的事情，这一行还显示出innodb使用了多少次操作系统的等待：保留统计（reservation count）显示了innodb分配插槽的频度，而信号计数（signal count）衡量的是线程通过数组得到信号的频度，操作系统的等待相对于空转等待（spin wait）要昂贵些。
OS WAIT ARRAY INFO: signal count 219805  # 进行OS WAIT线程，接收到多少次信号(single)被唤醒，如果这个single数值越大，几十万或者几百万，可能是很多I/0等待或者是InnoDB争用问题(关于争用问题可能与OS调度有关，可以尝试减少innodb_thread_concurrency参数)
RW-shared spins 0, rounds 168443, OS waits 84271  # RW-shared 共享锁，
RW-excl spins 0, rounds 180120, OS waits 1119  # RW-excl 排他锁
RW-sx spins 60, rounds 6000, OS waits 50
Spin rounds per wait: 168443.00 RW-shared, 180120.00 RW-excl, 100.00 RW-sx
------------
TRANSACTIONS  # 包含了InnoDB事务(transaction)的统计信息
------------
Trx id counter 264168  # 当前的transaction id ,这是个系统变量，随着每次新的transaction产生而增加
Purge done for trx's n:o < 264167 undo n:o < 0 state: running but idle  # 正在进行清空的操作操作的transaction ID，Purge的原则就是记录没有被其它事务继续使用了
History list length 10  # 记录了undo spaces 内unpurged 的事务个数
LIST OF TRANSACTIONS FOR EACH SESSION:   # 当前活跃事务列表
---TRANSACTION 421617178700976, not started  # 每个事务的第一行以事务的ID和状态开始，not started表示这个事务已经提交并且没有再发起影响事务的语句，可能刚好空闲
0 lock struct(s), heap size 1136, 0 row lock(s) # 该事务锁定结构的数目和堆大小以及锁定的行数，堆的大小指的是为了持有这些行锁而占用的内存大小
---TRANSACTION 421617178702800, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421617178697328, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421617178701888, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421617178700064, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421617178699152, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421617178698240, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421617178695504, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421617178704624, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421617178696416, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O  # 显示了I/O  Helper thread d的状态，包含一些统计信息
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread) # insert buffer thread
I/O thread 1 state: waiting for i/o request (log thread) # 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) # 以上为默认的4个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) # 以上为默认的4个write thread
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,  # 读线程和写线程挂起操作的数目等，aio的意思是异步I/O
 ibuf aio reads:, log i/o's:, sync i/o's:   # insert buffer thread挂起的fsync()操作数目等
Pending flushes (fsync) log: 0; buffer pool: 0  # log thread挂起的fsync()操作数目等
469 OS file reads, 1083592 OS file writes, 570823 OS fsyncs  # 这行显示了读，写和fsync()调用执行的数目，在你的机器环境负载下这些绝对值可能会有所不同，因此更重要的是监控它们过去一段时间内是如何改变的
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s  # 这行显示了当前被挂起的读和写操作数

注：三行挂起读写线程、缓冲池线程、日志线程的统计信息的值是检测I/O受限的应用的一个好方法，如果这些I/O大部分有挂起操作，那么负载可能I/O受限。在linux系统下使用参数：innodb_read_io_threads和innodb_write_io_threads两个变量来配置读写线程的数量，默认为各4个线程。
insert buffer thread：负责插入缓冲合并，如：记录被从插入缓冲合并到表空间中
log thread：负责异步刷事务日志
read thread：执行预读操作以尝试预先读取innodb预感需要的数据
write thread：刷新脏页缓冲

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX  # 这部分显示了insert buffer和adaptive hash index两个部分的结构的状态
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges  # 这行显示了关于size（size 12代表了已经合并记录页的数量）、free list（代表了插入缓冲中空闲列表长度）和seg size大小（seg size 27572显示了当前insert buffer的长度，大小为27572*16K=440M左右）的信息。18074934 merges代表合并插入的次数
merged operations:  # 这个标签下的一行信息insert，delete mark，delete分别表示merge操作合并了多少个insert buffer，delete buffer，purge buffer
 insert 0, delete mark 0, delete 0  # insert 插入的记录数，delete mark 打上的标记,delete 删除的次数
discarded operations:   # 这个标签下的一行信息表示当change buffer发生merge时表已经被删除了，就不需要再将记录合并到辅助索引中了
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 13 buffer(s)  # 这行显示了自使用哈希索引的状态，其中，Hash table size 87709057表示AHI的大小，node heap has 10228 buffer(s)表示AHI的使用情况
Hash table size 34679, node heap has 8 buffer(s)
Hash table size 34679, node heap has 4 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 4 buffer(s)
Hash table size 34679, node heap has 13 buffer(s)
Hash table size 34679, node heap has 14 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
11.61 hash searches/s, 0.00 non-hash searches/s   # 这行显示了在头部第1部分提及的时间内Innodb每秒完成了多少哈希索引操作，1741.05 hash searches/s表示每秒使用AHI搜索的情况，539.48 non-hash searches/s表示每秒没有使用AHI搜索的情况(因为哈希索引只能用于等值查询，而范围查询，模糊查询是不能使用哈希索引的。)，通过hash searches: non-hash searches的比例大概可以了解使用哈希索引后的效率，哈希索引查找与非哈希索引查找的比例仅供参考，自适应哈希索引无法配置，但是可以通过innodb_adaptive_hash_index=ON|OFF参数来选择是否需要这个特性。
---
LOG  # 这部分显示了关于innodb事务日志（重做日志）子系统的统计
---
Log sequence number 113764958  # 这行显示了当前最新数据产生的日志序列号
Log flushed up to   113764958  # 这行显示了日志已经刷新到哪个位置了（已经落盘到事务日志中的日志序列号）
Pages flushed up to 113764958  
Last checkpoint at  113764949  # 这行显示了上一次检查点的位置（一个检查点表示一个数据和日志文件都处于一致状态的时刻，并且能用于恢复数据），如果上一次检查点落后与上一行太多，并且差异接近于事务日志文件的大小，Innodb会触发“疯狂刷”，这对性能而言非常糟糕。
0 pending log flushes, 0 pending chkp writes  # 这行显示了当前挂起的日志读写操作，可以将这行的值与第7部分FILE I/O对应的值做比较，以了解你的I/O有多少是由于日志系统引起的。
350037 log i/o's done, 0.00 log i/o's/second  # 这行显示了日志操作的统计和每秒日志I/O数，可以将这行的值与第7部分FILE I/O对应的值做比较，以了解你的I/O有多少是由于日志系统引起的。
---------------------- 
BUFFER POOL AND MEMORY  # 这部分显示了关于innodb缓冲池及其如何使用内存的统计
----------------------
Total large memory allocated 137428992  # 这行显示了由innodb分配的总内存，以及其中多少是额外内存池分配，额外内存池仅分配了其中很小一部分内存，由内部内存分配器分配，现在的innodb版本一般使用操作系统的内存分配器，但老版本使用自己的，这是由于在那个时代有些操作系统并未提供一个非常好的内存分配实现。
Dictionary memory allocated 1401012
Buffer pool size   8192  # 从这行开始的下面4行显示缓冲池度量值，以页为单位，度量值有总的缓冲池大小，空闲页数，分配用来存储数据库页的页数，以及脏数据库页数。这行显示了缓冲池总共有多少个页，即即2705015*16K，共有43G的缓冲池
Free buffers       6147  # 这行显示了缓冲池空闲页数
Database pages     1986  # 这行显示了分配用来存储数据库页的页数，即，表示LRU列表中页的数量,包含young sublist和old sublist
Old database pages 713   # 这行显示了LRU中的old sublist部分页的数量
Modified db pages  0     # 这行显示脏数据库页数
Pending reads      0     # 这行显示了挂起读的数量
Pending writes: LRU 0, flush list 0, single page 0  # 这行显示了挂起写的数量
# 注意，这里挂起的读和写操作并不与FILE I/O部分的值匹配，因为Innodb可能合并许多的逻辑读写操作到一个物理I/O操作中，LRU代表最近使用到的被挂起数量，它是通过冲刷缓冲中不经常使用的页来释放空间以供给经常使用的页的一种方法，冲刷列表flush list存放着检查点处理需要冲刷的旧页被挂起的数量，单页single page被挂起的数量（single page写是独立的页面写，不会被合并)。
Pages made young 0, not young 0   # 这行显示了LRU列表中页移动到LRU首部的次数，因为该服务器在运行阶段改变没有达到innodb_old_blocks_time阀值的值，因此not young为0
0.00 youngs/s, 0.00 non-youngs/s  # 表示每秒young和non-youngs这两类操作的次数
Pages read 428, created 1558, written 680621  # 这行显示了innodb被读取，创建，写入了多少页，读/写页的值是指的从磁盘读到缓冲池的数据，或者从缓冲池写到磁盘中的数据，创建页指的是innodb在缓冲池中分配但没有从数据文件中读取内容的页，因为它并不关心内容是什么（如，它们可能属于一个已经被删除的表）
0.00 reads/s, 0.00 creates/s, 0.00 writes/s  # 这行显示了对应上面一行的每秒read，create，write的页数
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000  # 这行显示了缓冲池的命中率，它用来衡量innodb在缓冲池中查找到所需页的比例，它度量自上次Innodb状态输出后到本次输出这段时间内的命中率，因此，如果服务器自那以后一直很安静，你将会看到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: 1986, unzip_LRU len: 0   # innodb1.0.x开始支持压缩页的功能，将原来16K的页压缩为1K，2K，4K，8K，而由于页的大小发生了变化，LRU列表也有了些改变，对于非16K的页，是通过unzip_LRU列表进行管理的，可以看到unzip_LRU len为0表示没有使用压缩页.
I/O sum[0]:cur[0], unzip sum[0]:cur[0]  # 统计信息及解压信息
--------------
ROW OPERATIONS  # 这部分显示了其他各项的innodb统计
--------------
0 queries inside InnoDB, 0 queries in queue  # 这行显示了innodb内核内有多少个线程，队列中有多少个线程，队列中的查询是innodb为限制并发执行的线程数量而不运行进入内核的线程。查询在进入队列之前会休眠等待。
0 read views open inside InnoDB  # 这行显示了有多少打开的innodb读视图，读视图是包含事务开始点的数据库内容的MVCC快照，你可以看看某特定事务在第6部分TRANSACTIONS是否有读视图
Process ID=32728, Main thread ID=140141416933120, state: sleeping  # 这行显示了内核的主线程状态
Number of rows inserted 115492, updated 164886, deleted 1698, read 1351954675  # 这行显示了多少行被插入，更新和删除，读取
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 34.46 reads/s  # 这行显示了对应上面一行的每秒平均值，如果想查看innodb有多少工作量在进行，那么这两行是很好的参考值
----------------------------
END OF INNODB MONITOR OUTPUT  # 信息结束标记，要注意了，如果看不到这行输出，可能是有大量事务或者是有一个大的死锁截断了输出信息
============================