我是一个互联网公司的螺丝钉;
魔术师耿
mysql服务器性能瓶颈分析和内存优化
前言
开发阶段，对项目中mysql使用在代码层面已经做了最大努力的优化；
- 大表减少关联查询，进行单表查
- 关联字段添加索引（一个表最多16个索引,最大索引长度256字节）
- 对关联查询建立联合索引，尽量走覆盖索引
- 使用缓存，（java本地缓存；redis缓存）
- 本地堆内缓存 HashMap,ConcurrentHashMap,Guava的ConcurrentLinkedHashMap；
要注意设置过期时间，防止对象长时间放在堆里老年代；累计引起FULL GC - ehcache 可以使用队外内存
- redis 访问存在网络开销，没有本地内存快
- 慢查询优化
- 开启慢查询开关，慢查询日志保存目录文件
- 使用mysqldumpslow或者pt-query-digest 定位慢查询sql,(我用pt-query-digest)
- 拿到sql语句 explan 查看执行计划，分析为什么慢，是否还有优化空间
a.是否有filesort,临时表，排序；
多表关联查询group by : sql执行效率和数据量、where过滤条件都有关系；最终数据量越大，group by越慢
b.是否有回表查询（聚簇索引和非聚簇索引在B+树的数据存储上的区别）
c.优化大表分页查询
d.是否建立的索引没走
- 大表按照月份分表，按照有业务含义的字段取模分表
- 单线程处理改成多线程并发处理任务
- 一个任务处理要放在一个事务里；保证ACID的特性(Atomicity,Consistency,Isolation,Durability)
- 任务的处理逻辑被打包成一个Runnable 投入到线程池里去执行业务逻辑（在一个实现了Runnable接口的类里面，注入自己的任务处理Service ）
- 上面有一个线程分页去mysql里取出待处理的任务参数，打包成个对象丢到JVM本地的队列里（每次丢数据的时候检查队列的长度，是不是有任务积压没被处理掉，需要等的时候就sleep（一会儿），）
- JVM内并发处理任务，synchronized对尽可能最小的任务隔离级别添加锁，比如userId
- 单JVM改成多个服务
- 一个线程去查mysql，把任务对象参数放到一个redis队列；（同样检查消息积压）
- 每个JVM内起一个EventBootStrap 的线程 用来专门监听这个redis队列List1，取一部分数据到本地JVM 的队列ArrayBlockingQueue(给后面的线程池用) ，定义一个List（投入线程池添加，任务正常结束移除；用来处理异常情况需要的二次加载处理，比如JVM宕机，手动中断后再次启动继续处理）
注册一个JVM关闭的钩子方法
Runtime.getRuntime().addShutdownHook(new Thread(new Runnable() {
@Override
public void run()
{
//把从redis拉取过来的，本地还没处理完的数据，刷到redis一个暂时存储的其他List2,
//下次从redis的List1中拉取数据前，先把List2中的数据转移到JVM本地
System.out.println("Execute Hook.....");
}
}));- 线程池执行任务的时候，使用Redis的分布式锁来保证两个JVM中的后一个事件不能比前一个事件先执行，
前段时间闲来无事；别人给了我个连接，我刚好可以上去看看公司的服务器配置；顺带看看有什么问题没；
硬件配置
公司mysql配置
32核64G(独享型sn1)
内网IP: 172.xx.xx.xx
外网IP: 118.xx.xx.xx(按量付费30Mbps)
磁盘: 3块SSD (一块2T的磁盘包年包月2000 共两块2T的,和一个100G的)
IO性能 : 最大IOPS 20000次/秒
最大吞吐量 256MBps
成本评估：8244元/月
运行情况
磁盘IO监控详情
最近一次
a.抽取统计信息: 两个动作,每个动作开启50个线程共100个线程,select 联合查询后 单表批量insert
磁盘IO可以跑到最高 150MBps
b.抽取事件: 对不同的事件类型,顺序抽取,开启50进行抽取, 分页关联查询后, 单表单条插入
磁盘IO可以跑到最高 100MBps
c.确认事件: 200个线程 并行确认对userId加锁 ,cpu运算后,对107个表进行插入修改,(其中一个表有100个分表)
磁盘IO可以跑到最高 80-100MBps 晚上 ; 白天只有30-50MBps
CPU监控
CPU Idle cpu空闲 80%
告警设置：CPU Other大于20%且持续10分钟时产生告警通知 0.58%
告警设置：CPU System大于20%且持续10分钟时产生告警通知 2.12%
告警设置：CPU User大于60%且持续10分钟时产生告警通知 15%
告警设置：CPU IOWait大于20%且持续10分钟时产生告警通知 6%
告警设置：CPU使用率大于80%且持续10分钟时产生告警通知
内网监控
入站速率在55Mbps-70Mbps
出站速率在 500Mbps-750Mbps 平均600Mbps
告警设置：内网入站速率大于51200Kbps且持续10分钟时产生告警通知
告警设置：内网出站速率大于51200Kbps且持续10分钟时产生告警通知
内存监控
内存使用率91%,已经产生告警；这么高的硬件配置了；感觉里面一定有问题
发现问题：
top 命令后 按1 可以查看每个核的cpu使用率
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7QawdlP1-1591166869516)(./resource/mysql_top.png )]
翻阅资料
「mysql5.7内存占用持续过高」
SHOW GLOBAL STATUS LIKE 'Open%tables' ;
--
-- Open_tables 2000 : 打开后在缓存中的表数量
-- Opened_tables 41486219 : 打开的所有表数量
SHOW GLOBAL VARIABLES LIKE 'table_open_cache' ; -- 查看 table_open_cache
-- table_open_cache 2000
set global table_open_cache = 2048; -- （立即生效重启后失效）
/*
MySQL 配置文件 my.cnf 中 mysqld 下添加 table_open_cache
[mysqld]
table_open_cache = 2048
*/table_open_cache指定表高速缓存的大小。
每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。
通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_open_cache的值。
如果你发现open_tables等于table_open_cache，
并且opened_tables在不断增长，那么你就需要增加table_open_cache的值了(上述状态值可通过SHOW GLOBAL STATUS LIKE ‘Open%tables’获得)。
注意，不能盲目地把table_open_cache设置成很大的值，设置太大超过了shell的文件描述符(通过ulimit -n查看)，造成文件描述符不足，从而造成性能不稳定或者连接失败
ulimit -n
65535## mysql最大连接数
show variables like '%max_connections%';
-- max_connections 10000「MySQL 优化之 table_open_cache」 table_open_cache
表文件描述符的缓存大小
( 当打开一个表后 会把这个表的文件描述符缓存下来 )
# table_open_cache
默认值 2000
最小值 1
最大值 524288table_open_cache 设置多少合适
不是越大越好 table_open_cache过大占用大量文件描述符资源而不释放
用尽了系统文件描述符资源导致无法接入新的连接
如何判断 table_open_cache 大小是否够用？
可根据MySQL的两个状态值来分析
通过以上两个值来判断 table_open_cache 是否到达瓶颈
当缓存中的值open_tables 临近到了 table_open_cache 值的时候
说明表缓存池快要满了 但 Opened_tables 还在一直有新的增长 这说明你还有很多未被缓存的表
这时可以适当增加 table_open_cache 的大小
「MySQL 5.7 参考手册 - table_open_cache」 [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yH3BRlMw-1591166869517)(./resource/dev_mysql_02.png )]
table_open_cache默认2000 最大 524288
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-szQOaNjB-1591166869518)(./resource/dev_mysql_01.png )]
- Opened_tables
The number of tables that have been opened. If Opened_tables is big, your table_open_cache value is probably too small.
- Table_open_cache_hits (命中数)
The number of hits for open tables cache lookups.
- Table_open_cache_misses(未命中数)
The number of misses for open tables cache lookups.
- Table_open_cache_overflows(溢出数)
The number of overflows for the open tables cache. This is the number of times, after a table is opened or closed, a cache instance has an unused entry and the size of the instance is larger than table_open_cache / table_open_cache_instances.
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2eGWsIIy-1591166869518)(./resource/dev_mysql_03.png )]
open_files_limit mysql 可以从操作系统拿到的文件描述符的个数
open_files_limit 的实际值是取的下面4个值的最大值
- 10 + max_connections + (table_open_cache * 2)
10+最大连接数+（表4打开缓存*2）
- max_connections * 5
最大连接数*5
- Operating system limit if that limit is positive but not Infinity
操作系统限制如果该限制为正但不是无穷大
- If operating system limit is Infinity: open_files_limit value if specified at startup, 5000 if not
如果操作系统限制是无限的：如果在启动时指定了“打开文件”限制值，则为5000
服务器尝试使用这些值的最大值来获取文件描述符的数量。如果无法获取这么多描述符，服务器将尝试获取系统允许的尽可能多的描述符。
在MySQL无法更改打开文件数的系统上，有效值为0。
在Unix上，该值不能设置为大于ulimit-n。(我这台机器上是 65535)
max_connections(我们运维和dba老师设置的是10000)
open_files_limit
= max(10 + max_connections + (table_open_cache * 2) ,max_connections * 5, ulimit -n )
尝试解决
我自己的环境分析设置
我自己环境上62G内存被占用到只剩5.5G 达到91%
Opened_tables=41486219 远远大过 Open_tables=table_open_cache=2000
表明缓存池已经远远不够了,
SELECT COUNT(*) TABLES FROM information_schema.TABLES ;
-- 2573
SELECT COUNT(*) TABLES FROM information_schema.TABLES
WHERE table_schema like 'ir%' or table_schema = 'incomerecognition';
-- 1636
SELECT COUNT(*) TABLES FROM information_schema.TABLES
WHERE table_schema like 'Order%';
-- 260
SHOW GLOBAL STATUS;
SHOW GLOBAL STATUS LIKE 'table_open_cache%' ;
-- Table_open_cache_hits 5957402859
-- Table_open_cache_misses 41645844
-- Table_open_cache_overflows 41624078open_files_limit
= max(10 + max_connections + (table_open_cache * 2) ,max_connections * 5, ulimit -n )
=max(10 + 10000 + (table_open_cache * 2) ,50000 , 65535 )我这里先谨慎点 table_open_cache 设置成 5000吧,(我觉得 最大不应该超过20000 )
毕竟 操作系统文件描述符为65535 , 还是给操作系统其他资源预留 资源的; 除非调大 操作系统最大文件描述符的最大限制
但是 这是个mysql服务器; 并不是给Netty使用的 IM服务器
然后去试下,效果
SHOW GLOBAL STATUS LIKE 'Open%tables' ;
--
-- Open_tables 2000 : 打开后在缓存中的表数量
-- Opened_tables 41486219 : 打开的所有表数量
SHOW GLOBAL VARIABLES LIKE 'table_open_cache' ; -- 查看 table_open_cache
-- table_open_cache 2000
set global table_open_cache = 5000; -- （立即生效重启后失效）
/*
MySQL 配置文件 my.cnf 中 mysqld 下添加 table_open_cache
[mysqld]
table_open_cache = 5000
*/验证结果
测试后发现并没有什么卵用
请教DBA老师
DBA让我调整这三个参数
performance_schema_max_table_instances=400
table_definition_cache=400
table_open_cache=256这三个参数改下，需要重启
这3个参数要不要写在配置my.cnf中呀,要不然重启后不是就变了
需要加配置文件
mysql内存是占用后不会释放,
还有buffer_pool也根据主机内存设置
table_open_cache为什么需要改小呢?
少缓存表可以少占用内存
太多了也没意义
- performance_schema_max_table_instances
The maximum number of instrumented table objects
插装表对象的最大数目
- table_definition_cache
The number of table definitions (from .frm files) that can be stored in the definition cache
可以存储在定义缓存中的表定义的数量(来自.frm文件)
过了两天观测数据，好像生效了
发文前观看，已经恢复正常，使用率在61%，没再告警，
