1、InnoDB buffer pool
InnoDB需要innodb buffer pool中处理缓存。所以非常需要有足够的InnoDB buffer pool空间。InnoDB buffer pool 里包含：
- 数据缓存：InnoDB数据页面
- 索引缓存：索引数据
- 缓冲数据：脏页（在内存中修改尚未刷新(写入)到磁盘的数据）
- 内部结构：如自适应哈希索引，行锁等。
innodb_buffer_pool_size 默认大小为128M。最大值取决于CPU的架构。在32-bit平台上，最大值为2**32 -1,在64-bit平台上最大值为2**64-1。
当缓冲池大小大于1G时，将innodb_buffer_pool_instances设置大于1的值可以提高服务器的可扩展性。
大的缓冲池可以减小多次磁盘I/O访问相同的表数据。在只有MySQL的服务器上，可以将缓冲池大小设置为服务器物理内存的80%。但如果还有其他系统在跑，需要考虑内存占用的合适比例。
InnoDB为缓冲区和control structures保留了额外的内存，因此总分配空间比指定的缓冲池大小大约大10％。
缓冲池的地址空间必须是连续的，这在带有在特定地址加载的DLL的Windows系统上可能是一个问题。
物理内存争用可能导致操作系统频繁的paging
初始化缓冲池的时间大致与其大小成比例。在具有大缓冲池的实例上，初始化时间可能很长。要减少初始化时间，可以在服务器关闭时保存缓冲池状态，并在服务器启动时将其还原。
增大或减小缓冲池大小时，将以chunk的形式执行操作。chunk大小由innodb_buffer_pool_chunk_size配置选项定义，默认值为128 MB。
缓冲池大小必须始终等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数。
如果将缓冲池大小更改为不等于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数的值，则缓冲池大小将自动调整为等于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数的值。
相关参数
- innodb_buffer_pool_dump_pct：指定每个缓冲池最近使用的页面读取和转储的百分比。 范围是1到100。默认值是25。例如，如果有4个缓冲池，每个缓冲池有100个page，并且innodb_buffer_pool_dump_pct设置为25，则dump每个缓冲池中最近使用的25个page。
- innodb_buffer_pool_dump_at_shutdown：默认启用。指定在MySQL服务器关闭时是否记录在InnoDB缓冲池中缓存的页面，以便在下次重新启动时缩短预热过程。
- innodb_buffer_pool_load_at_startup：默认启用。指定在MySQL服务器启动时，InnoDB缓冲池通过加载之前保存的相同页面自动预热。 通常与innodb_buffer_pool_dump_at_shutdown结合使用。
比如分配2G的innodb buffer pool，执行命令查看池情况
show variables like 'innodb_buffer_pool%';
----------------------------------------------------------------------------
innodb_buffer_pool_chunk_size 134217728
innodb_buffer_pool_dump_at_shutdown ON
innodb_buffer_pool_dump_now OFF
innodb_buffer_pool_dump_pct 25
innodb_buffer_pool_filename ib_buffer_pool
innodb_buffer_pool_in_core_file ON
innodb_buffer_pool_instances 16 = 2147483648 / 134217728
innodb_buffer_pool_load_abort OFF
innodb_buffer_pool_load_at_startup ON
innodb_buffer_pool_load_now OFF
innodb_buffer_pool_size 2147483648 = 1024*1024*1024*2 = 134217728 * 16
当前配置的innodb_buffer_pool_size是否合适，可以通过分析InnoDB缓冲池的性能来验证。可以使用以下公式计算InnoDB缓冲池性能：
show status like 'innodb_buffer_pool_read%';
--------------------------------------------------------------------------------
Innodb_buffer_pool_read_ahead_rnd 0
Innodb_buffer_pool_read_ahead 3989
Innodb_buffer_pool_read_ahead_evicted 0
Innodb_buffer_pool_read_requests 49967223
Innodb_buffer_pool_reads 9233
性能值% = innodb_buffer_pool_reads / innodb_buffer_pool_read_requests * 100
命中率 = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_reads ) * 100
innodb_buffer_pool_reads：表示InnoDB缓冲池无法满足的请求数，需要从磁盘中读取。innodb_buffer_pool_read_requests：表示从内存中读取逻辑的请求数。
如果性能值%很小，命中率很高，说明需要从磁盘读取的数据少，性能比较高
在只有MySQL的服务器上，多余的innodb_buffer内存不会有问题，但是和其他系统共享服务器时，需要考虑合适的分配比例。可以使用show engine innodb status 命令检查内存状态：
show engine innodb status
-----------------------------------------
...
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
...
Buffer pool size 8191
Free buffers 7320
Database pages 838
Old database pages 329
Free buffers :表示有多少空闲buffer。如果 此值长时间都较高，则可以考虑减小InnoDB缓冲池大小。
2、Innodb log buffer
MySQL的数据增删改查是在内存中完成的，修改内存数据的同时，会包所做的操作记录到redo log。内存中修改后的数据块叫脏页，需要刷入磁盘。如刷入磁盘前MySQL宕机了，MySQL在重启时就会通过redo log来继续完成写入磁盘的操作。
但并不是每个事务操作都直接写入到redo log的文件中的，redo log也会缓存到log_buffer（innodb_log_buffer_size参数控制，默认48M），那 redo log buffer 什么时候写入磁盘呢？
事务提交时按 innodb_flush_log_at_trx_commit 设置把它对应的redo log写入到磁盘
当redo log buffer 使用量达到了参数innndb_log_buffer_size的一半时，会写入到磁盘
会有一个后台线程，每隔1秒就会将redo log block写入到磁盘
MySQL关闭时也会写入到磁盘
参数 innodb_flush_log_at_trx_commit来控制写入redo log写入磁盘的时机，默认为1
设置为1：表示当你commit时，MySQL必须将rodolog-buffer中的数据刷新进磁盘中。确保只要commit是成功的，磁盘上就得有对应的rodolog日志。这也是最安全的状况。
设置为0：每秒1次写入到磁盘。此时可能你提交事务了，结果 mysql 宕机了，然后此时内存里的数据全部丢失。
设置为2：表示当你commit时，将redolog-buffer中的数据刷新进OS Cache中，而后依托于操做系统每秒刷新一次的机制将数据同步到磁盘中，也存在丢失的风险。
innodb_log_buffer_size 是 redo log 的写缓存，如果 buffer 不够大，就会发生多次 IO write，将缓存中的数据刷到磁盘；innodb_log_buffer_size 越大，IO 次数越少。虽然 IO write 次数少了，但 write 的数据量是差不多的，在一般的存储设备上，整体的 IO 延迟总体差不多；但在好的存储设备上，如果数据量差不多， write 次数越少，延迟会越低。
对应还有一个控制binlog的参数是sync_binlog，该参数控制着二进制日志写入磁盘的过程，推荐设置为1
0：默认值。事务提交后，将二进制日志从缓冲写入磁盘，但是不进行刷新操作（fsync()），此时只是写入了操作系统缓冲，若操作系统宕机则会丢失部分二进制日志。
1：事务提交后，将二进制文件写入磁盘并立即执行刷新操作，相当于是同步写入磁盘，不经过操作系统的缓存。
N：每写N次操作系统缓冲就执行一次刷新操作。
推荐：双1设置。前者保证redolog的不丢失、后者保证了binlog的不丢失。
日志文件的总大小（innodb_log_file_size* innodb_log_files_in_group）不能超过略小于512GB的最大值。
redo log文件是分组的，redo log group 说的是：由N个大小相同的redo log组成一个redo log group。N的值默认为2。由参数 innodb_log_files_in_group 控制。
redo log文件默认放在数据目录文件名为ib_logfile0和ib_logfile1
innodb 将log buffer 中的redo log block 刷新到logfile中时，以追加的方式循环写入。也就是首先在ib_logfile0的尾部追加，写满后再写ib_logfile1。当ib_logfile1写满时，覆盖ib_logfile0接着追加写。
redo log file的大小对innodb性能影响很是大。一般来讲要设置的足够大，大到可让MySQL支持1小时线上高峰流量的接入而不切换。
可是设置的过大，数据恢复的时间比较长。设置太小致使循环切换redo log file。
3、线程缓存
show global status like 'Thread%';
如果我们在MySQL服务器配置文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)。Threads_created表示创建过的线程数，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值，查询服务器thread_cache_size配置：
show variables like 'thread_cache_size';
| Threads_cached | 0 |代表当前此时此刻线程缓存中的空闲线程
| Threads_connected | 11 |代表当前建立的连接数
| Threads_created | 11032160 |最近一次服务启动以来，创建的线程数量
| Threads_running | 2 |当前激活的（非睡眠）的线程数
thread_cache_size最好设置成和threads_connected一样。不过很少将thread_cache_size设置成比200大。
thread_cache_size 如果短链接比较多，一般的建议根据MySQL的内存规划，每1G按8个缓存线程来配置。如果修改后监控到Threads_created还是在持续增加，那得考虑加内存再调高thread_cache_size。
不过最好还是优先考虑使用连接池。
set global thread_cache_size=48
4、最大连接数
查看当前连接数
show global status like 'Connections';
查看历史最大连接数
show global status like 'Max_used_connections';
比较理想的设置是
max_used_connections/max_connections*100%≈85%
5、表扫描情况
show global status like 'Handler_read_rnd_next%';
show global status like 'com_select';
com_select 变量记录的是无缓存的查询次数+错误查询+权限检查查询
表扫描率 = Handler_read_rnd_next / Com_select
如果表扫描率超过4000，说明进行了太多表扫描，很有可能索引没有建好，或者有大表频繁应用select count(*)
6、临时文件
MySQL在很多场景都会使用临时文件，缓存不足，临时表都会导致产生大量的临时文件文件，消耗磁盘IO
查看临时文件目录，默认在/tmp目录
show variables like 'tmpdir';
检查磁盘读写，如果mysql的运行用户是mysql
lsof -u mysql | grep /tmp
MySQL在什么时候会生成临时文件呢
1、执行计划file sort 生成的临时文件名字MY开头
如:/tmp/MYdRH1GW (deleted)
2、大事务binary log缓存 生成的临时文件名字ML开头
如:/tmp/MLq9INFu (deleted)
3、online DDL 涉及排序比如add key
alter table testsort add key(id);
如:
/tmp/ibCxlYQg (deleted)
/tmp/ib51nvZ1 (deleted)
4、压缩的tempory table
CREATE TEMPORARY TABLE tmp_table1(id int) ROW_FORMAT=COMPRESSED ;
ls /tmp/
如:
#sql6b82_6_7.frm
#sql6b82_6_7.ibd
MySQL的磁盘IO可以通过 iotop -oP 来查询，繁忙的语句可以通过 mytop 来检查
常用优化配置，假设8G内存的专用MySQL机器：
vi /etc/my.cnf
[mysqld]
thread_cache_size=48 推荐按8个每GB内存设置，这里按80%大约6GB设置
max_connections=1000 连接数基本在800左右，设置为1000，在80%左右
innodb_buffer_pool_size = 6G 在总内存80%左右
innodb_buffer_pool_instances = 48 刚好设置为6G/128M
innodb_log_file_size = 2G 重写日志文件大小设置为2G，和1小时线上数据写入量差不多即可，太大会数据恢复时间过长，数据量大小可以用iotop统计
datadir=/data/mysql/data 数据文件目录
tmpdir=/tmp 临时文件目录，为提升性能，建议把临时文件目录和数据文件目录放到不同的磁盘
