mysql监控及配置

MySQL CPU高排障流程

实例规格低，无法满足业务发展；现象：从监控看，业务高峰期间cpu高/活跃连接高，简单sql很多进入慢查询。解决方案：需要提升规格（用户在控制台自行操作）。
业务sql写的有问题，比如走了全表扫描/索引不合理/sql排序结果集大；从监控看，cpu高期间，临时表多/慢查询多/行锁等待高，此时重点看时间段内慢查询明细和慢查询统计，查询列表中出现频繁的sql，或者出现不频繁，但扫描行数大，但返回行数小的sql重点需要优化。解决方法：让用户优化这些sql；或者参考控制台->登陆数据库->管理工具->慢查询优化，慢查询列表中点击优化按钮，参考智能优化建议。
有大事务在运行，执行以下语句可以查看执行最长的三个事务：select * from information_schema.innodb_trx order by trx_started asc limit 3; 解决方案：kill事务或者等待事务执行完（用户在控制台->登陆数据库->管理工具->会话自行kill）。

MySQL 内存高排障流程

MySQL的内存由innodb_buffer_pool和连接内存组成，内存高通常是其中一部分高或者两部分都高：

innodb_buffer_pool高：

京东云innodb_buffer_pool分配原则如下:
实例内存1G，buffer_pool占用25%
实例内存2G，buffer_pool占用50%
实例内存4G，buffer_pool占用60%
实例内存8G，buffer_pool占用60%
实例内存16G ，buffer_pool占用65%,
实例内存大于16G，实例内存buffer_pool占用75%。
如果用户实例中热点数据较多，buffer_pool使用就会较高，buffer_pool使用增长内存使用也会随之增长，达到buffer_pool分配限额后内存使用会趋于稳定，直至重启实例buffer_pool占用的内存才会释放。从监控看，监控项“InnoDB缓存池读命中率、使用率、脏块率 (%)”的使用率如果较高，则buffer_pool使用就会较高。
解决方案：优化业务访问模式、优化慢sql、重启实例或者升配规格（用户均可自行操作）。

连接内存高

使用长连接，内存会涨的快，这是因为执行过程中临时使用的内存是管理在连接对象里面的，这些资源在连接断开后才释放，长期累积，内存可能oom。
解决方法：
定期释放长连接，定期或者大查询后断开重连。
5.7后，大查询后，执行mysql_reset_connection程序初始化连接资源，不需要重新连接和权限验证。

共享内存

执行以下命令，查询示例的共享内存分配情况：

show variables where variable_name in ('innodb_buffer_pool_size','innodb_log_buffer_size','innodb_additional_mem_pool_size','key_buffer_size','query_cache_size');

innodb_additional_mem_pool_size | 8388608   
innodb_buffer_pool_size         | 524288000
innodb_log_buffer_size          | 67108864  
key_buffer_size                 | 16777216  
query_cache_size                | 0        
注意：5.7版本不支持 innodb_additional_mem_pool_size。

参数说明：

innodb_buffer_pool_size

该部分缓存是 Innodb 引擎最重要的缓存区域，是通过内存来弥补物理数据文件的重要手段，在云数据库 MySQL 上会采用实例规格配置的50% - 80%作为该部分大小（上图为1000MB * 0.5 = 500MB）。其中主要包含数据页、索引页、undo 页、insert buffer、自适应哈希索引、锁信息以及数据字典等信息。在进行 SQL 读和写的操作时，首先并不是对物理数据文件操作，而是先对 buffer_pool 进行操作，再通过 checkpoint 等机制写回数据文件。该空间的优点是可以提升数据库的性能、加快 SQL 运行速度，缺点是故障恢复速度较慢。

innodb_log_buffer_size

该部分主要存放 redo log 的信息，在云数据库 MySQL 上会设置64MB的大小。InnoDB 会首先将 redo log 写在这里，然后按照一定频率将其刷新回重做日志文件中。该空间不需要太大，因为一般情况下该部分缓存会以较快频率刷新至 redo log（Master Thread 会每秒刷新、事务提交时会刷新、其空间少于1/2时同样会刷新）。

innodb_additional_mem_pool_size

该部分主要存放 InnoDB 内的一些数据结构，在云数据库 MySQL 中统一设置为8MB。通常是在 buffer_pool 中申请内存的时候还需要在额外内存中申请空间存储该对象的结构信息。该大小主要与表数量有关，表数量越大需要更大的空间。

key_buffer_size

该部分是 MyISAM 表的重要缓存区域，所有实例统一为16M。该部分主要存放 MyISAM 表的键。MyISAM 表不同于 InnoDB 表，其缓存的索引缓存是放在 key_buffer 中的，而数据缓存则存储于操作系统的内存中。云数据库 MySQL 的系统是 MyISAM 引擎的，因此需给予该部分一定量的空间的。

query_cache_size

该部分是对查询结果做缓存，以减少解析 SQL 和执行 SQL 的开销，在云数据库 MySQL 上关闭了该部分的缓存。主要适合于读多写少的应用场景，因为它是按照 SQL 语句的 hash 值进行缓存的，当表数据发生变化后即失效。

私有内存

执行以下命令，查询示例的 session 私有内存分配情况：

show variables where variable_name in ('read_buffer_size','read_rnd_buffer_size','sort_buffer_size','join_buffer_size','binlog_cache_size','tmp_table_size');

binlog_cache_size    | 32768    
join_buffer_size     | 262144    
read_buffer_size     | 262144    
read_rnd_buffer_size | 524288    
sort_buffer_size     | 524288  
tmp_table_size       | 209715200

参数说明：

read_buffer_size

分别存放了对顺序扫描的缓存，当 thread 进行顺序扫描数据时会首先扫描该 buffer 空间以避免更多的物理读。

read_rnd_buffer_size

分别存放了对随机扫描的缓存，当 thread 进行随机扫描数据时会首先扫描该 buffer 空间以避免更多的物理读。

sort_buffer_size

需要执行 order by 和 group by 的 SQL 都会分配 sort_buffer，用于存储排序的中间结果。在排序过程中，若存储量大于 sort_buffer_size，则会在磁盘生成临时表以完成操作。

join_buffer_size

MySQL 仅支持 nest loop 的 join 算法，处理逻辑是驱动表的一行和非驱动表联合查找，这时就可以将非驱动表放入 join_buffer，不需要访问拥有并发保护机制的 buffer_pool。

binlog_cache_size

该区域用来缓存该 thread 的 binlog 日志，在一个事务还没有 commit 之前会先将其日志存储于 binlog_cache 中，等到事务 commit 后会将其 binlog 刷回磁盘上的 binlog 文件以持久化。

tmp_table_size

不同于上面各个 session 级的 buffer，这个参数可以在控制台上修改。该参数是指用户内存临时表的大小，如果该 thread 创建的临时表超过它设置的大小会把临时表转换为磁盘上的一张 MyISAM 临时表。