在日常开发中,难免会遇到业务高峰期,到时mysql不可用,但是这个时候领导肯定要求的最低限度,就是让业务跑起来,今天我们就说说有哪些方案可以临时解决这种问题
短连接
正常的短连接就是连接数据库后,执行少量的sql,下次在使用的时候,再次连接,但是这种情况,当遇到业务高峰期的时候,就有可能导致mysql不可用,我们在之前的文章中知道,连接是一个很复杂的过程,成本很高,不但要进行权限的验证,还要获取这个连接数据的读写权限。
在业务量不大的时候,这些成本还比较小,但是,短连接存在一定的风险,当数据处理的慢的时候,超过max_connections参数,就会拒绝新来的链接,出现提示Too many connections,这种提示对于用户就是数据库不可用,当然如果是这样的话,我们是不是就可以直接修改参数max_connections,使其值变大,但是我们还要考虑,连接数多的话,也会消耗大量的资源,导致cpu居高不下,最终连接无法获取资源,不能执行sql,因此我们是不是还有其他方案呢
第一种,处理那些占用的连接,但是不工作的链接
max_connections参数,不是看谁在running,仅仅如果站着连接就是计数,对于那些不需要保持连接的线程,我们就可以杀掉,kill connections,这个行为和设置等待时间wait_timeout是一样的,线程空闲多长时候后,就会断开连接,
但是我们需要注意,在show processlist的结果里面,我主动踢掉sleep是有损的.举个例子如下图
上面图中,如果我们断开sessionA,但是此时还有提交,只能按照数据库的事物进行回滚了,但是如果我们断开sessionB,就没有说明影响,所以我们断开的优先级,就应该断开像sessionB这样的连接在事务外空闲连接的。
问题又来了,如何判断线程是在事物外连接空闲的呢,可以用下面语句
show processlist
发现id=4,5都是在sleep中,我们在查看事务的状态,你可以查 information_schema 库的 innodb_trx 表。
看到 trx_mysql_thread_id=4,就是上面id=4线程在事务中.
最后,我们在断开连接的时候,应该优先断开事物之外空闲的线程,如果还不够,可以断开事物内空闲太久的线程
这个时候,我们还要注意一点,就是我们在服务端进行kill connections+id 的时候,客户端是不知道的,如果客户端在拿着断开的连接操作sql,就会报错ERROR 2013 (HY000): Lost connection to MySQL server during query,这种对于客户端就是不可用的,所以你是DBA的大佬,麻烦下次断开连接的时候,通知一下开发,拜托。
第二种,减少连接的消耗
如果我们的业务就是要在短时间内筹备大量的链接,这个行为就有可能把数据搞挂,此时我们是可以跳过权限的验证阶段的,
要跳过权限验证直接使用下面参数,重启数据库就可以了
–skip-grant-tables
但是这种启动风险很高,特别是在外网可以访问的情况,所以不建议使用这种方式,
而在mysql8.0版本,当我们使用上面参数重启数据的时候,默认打开skip-networking参数,限制只能本地连接.
除了短连接可能带来的性能,往往还有其他情况导致性能,经典的两类
慢查询性能问题
数据导致性能问题,一般有三类
我们按照上面三类情况,分别给出解决方案
索引设计错误
我们在mysql5.6版本之后,可以使用online DDL建立索引,对于数据库已经被搞挂了的情况,我们直接使用 alter table 语句建立索引
但是最好的一种情况就是我们有一主一备,主A,备B按照下面方式进行建立索引
上面的方案虽然很古老,但是在紧急处理上是很有效的
sql语句没有写好
没有写好sql,大多数就是没有使用索引,但是我们可以使用query_rewrite功能,把输入sql语句改成另外一种模式,比如下面语句
select * from t where id + 1 = 10000
可以使用下面语句改写成另外一种语句
mysql> insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database)
values ("select * from t where id + 1 = ?", "select * from t where id = ? - 1", "db1");
call query_rewrite.flush_rewrite_rules();
上面call query_rewrite.flush_rewrite_rules() 这个存储过程,是让插入的新规则生效,也就是查询重写,如下验证是否生效
数据库选错索引,也是经常遇到的,当然我们可以上面重写规则,添加 force index,
我们发现索引设计错误,sql语句没有写好,其实是可以避免的,我们在上西安之前,可以使用下面方式使其提前发现
按照我多年的经验,几乎很少公司做这个事情,做了也很难坚持下来。
QPS突增
这种问题有可能是新的功能引起的,这个时候,我们就要下掉这个业务,我们有下面结果方案可以试试
方案也是有风险的
我们看到上面三大方案,方案三优先级应该放到最后,优先使用方案一二,这些方案使用的前提是你们的数据库有较好的规范,如白名单,业务账号分离等等。