关于性能优化中slowquery(MySQL)优化的一些想法

关于性能优化中slowquery(MySQL)优化的一些想法

系统性能优化是一个非常宽泛的话题,它贯穿于业务逻辑设计、库表设计、迭代、交付运维整个全链路生命周期.带宽、负载均衡、业务逻辑、服务器性能、数据库性能、交换机性能、缓存策略...,每一个节点的不合理设计,都有可能带来严重的问题,小到个别接口不可用,大到多个子系统当机,每一项都可以作为一个专题进行大篇幅的方案讨论,当我们在每一个节点都实现了集群、冗余的同时,又加以制度与流程的约束之后,系统的健壮性大幅度提升.但是对于运维人、架构师来讲,还是不够的,系统是否具有熔断、服务降级、快速修复、自我修复这些功能和机制,才是能让运维工程师睡好觉的关键因素.2018年在上海与天天拍车运维总监强哥喝酒的时候,他说了一句话,“运维工程师心态不安,一定是某个环节存在缺失,流程、方法、制度、工具中的一个或者几个不完善”,强哥说的对:)

回归正题,今天聊聊一个不常见,但是出现会致命的故障场景,《数据库服务器CPU偶发性激增超载,进而导致服务失效的问题》,偶发表示不常见,但绝对不是偶然,谈这个问题之前,先界定一个场景,就是发生的前提,《系统具有比较完善SQL审核制度》,那种不需要审核就上线,SQL随意写,连表满天飞还接入oltp的行为,就算了,天天都发生的当机不叫当机,叫生活.毕竟工程师和工程师是不一样的.你说是吧,EH.

现象:

个别接口响应时间增加,用户明显感知CPU负载短时间内激增

定位:

解决:

首先说说可能触发上述当机事件的事件有哪些

上线操作.平稳运行的系统突然发生类似事件,十有九之,有上线,立即回滚营销活动.瞬间并发激增,又分为两种场景良性激增,纯粹因为qps激增导致超载,解决方案:系统保留足够的资源以用来应付突发性需求运营部门活动前知会技术部门,做好扩容等应对工作,所以说,保证系统SRE,并不完全是技术部门的工作.非良性激增,触发隐患SQL,瞬间被放大,解决方案:短期解决:接口限流,最好可以做到动态限流,牺牲掉一部分用户感受保障整个系统的安全长期解决:微服务化,功能剥离,代码层剥离,数据层也要剥离.牺牲自己,保障全局.

撰写本文的目的,是解决低效SQL并发激增造成的当机的场景,言归正题,优化SQL是一件老生常谈的事情,不再赘述

formatter一下,尤其较为复杂的SQL.清晰看出逻辑查看表结构,索引结构查看执行计划检查列值区分度

经过上面四板斧,SQL为什么低效耗时,应该已经了然于胸,怎么优化凭个人本事,核心思路:《检索最少的记录得到想要的数据》.至于如何做到,理论知识+实操经验永远是真理.

到这里,还想再说一句,在我面试过的所有dba中,只有一个人正确回答了这个问题.《一条SQL语句里面,一个表最多会用到几条索引》,知道了正确的答案和MySQL取数的过程,那么SQL应该如何写才能发挥出聚簇索引表的优势,就是很简单的事情.

防范:

运维工程师需要的技能特别多,也特别繁杂,如果使用书籍来衡量的话,估计要几十本,但是做的事情确是很简单,就三条

1.争取不出事  2.出事出小事  3.快速恢复

个中心酸,只有我们这些OP们才知道.

如何防范

规则:严格的SQL军规制度:严格的上线审核制度,不通过坚决不上线,上线权利必须在OP手里工具:脚本化,半自动化,最好做到自动化审核,非交互式操作

写在最后:

即使我们有流程制度工具方法的各种保障措施,也不可能将所有问题和隐患杜绝在上线之前,比如说列值分布偏差,测试环境数据不充足,不能命中低效数据的场景,不知道有几位同行能够听得懂我这句话.听得懂的,肯定是资深运维工程师.所以说,全链路保障系统SRE,是运维工程师的价值所在.

屏蔽问题、快速发现问题、快速解决问题.我们一直在路上!

脚本:

#!/bin/bash

#####################################################################

#@@@ Author      : bluewhalew(playful_bluewhale@hotmail.com)

#@@@ Name        : Analysis_SlowQuery_E_S.sh

#@@@ Describe    : analyze slowquery filr sent rows and examine rows

#@@@ Created  in : 20170421

#@@@ Modified in : 20210513

#@@@ Para        : $1 is slow query analyzed file(pt)

#@@@ Para        : $2 is cpu core counts

#####################################################################

if [ $# != 2 ]; then

echo "para counts is false"

else

CurrentPwd=`pwd`

dir=$CurrentPwd/`date +%Y%m%d%H%M%S`

if [[ -d $dir ]] ; then

rm -fr $dir

else

mkdir -p $dir

fi

cd $dir

startTime=`date -d \`cat ../$1|grep "# Time range:"|awk 'NR == 1'|awk '{print $5}'\` +%s`

endTime=`date -d \`cat ../$1|grep "# Time range:"|awk 'NR == 1'|awk '{print $7}'\` +%s`

duration=$(($endTime - $startTime))

allCpuTime=`expr $(($endTime - $startTime)) \* $2`

echo -e "\033[33m##### slowqueryduration: $duration\033[0m" >> totalResult.log

echo -e "\033[33m##### cpuCoreCount: $2\033[0m" >> totalResult.log

echo -e "\033[33m##### allCpuTime $allCpuTime\033[0m" >> totalResult.log

#sleep 10

cat ../$1|grep "# Rows sent"|awk 'NR>1 { if($8 ~/M$/) print $8*1000000; else if ($8 ~/k$/) print $8*1000; else print $8}' > RowsSent.log

cat ../$1|grep "# Rows examine"|awk 'NR>1 { if($8 ~/M$/) print $8*1000000; else if ($8 ~/k$/) print $8*1000; else print $8}' > RowsExamine.log

t1=`cat ../$1 |sed -n 12p|awk '{if($7 ~/M$/) print $7*1000000; else if ($7 ~/k$/) print $7*1000; else print $7}'`

t2=`cat ../$1 |sed -n 13p|awk '{if($7 ~/M$/) print $7*1000000; else if ($7 ~/k$/) print $7*1000; else print $7}'`

t3=`cat ../$1 |sed -n 6p`

echo $t3 " " `echo "sclae=2; $t2/$t1" | bc` > result.log

awk 'BEGIN{cnt_a=0;cnt_b=0;}FNR==NR{a[cnt_a++]=$0;next} {b[cnt_b++]=$0} END{cnt=0;cnt_max=cnt_aRowsSentExame.log

cat RowsSentExame.log |awk 'BEGIN{FS=","}{if ($1 != 0) printf "%.2f\n",$2/$1;else print $2}' > RowsSentExamRate.log

#cat RowsSentExame.log |awk 'BEGIN{FS=","}{printf "%.2f\n",$2/$1}' > RowsSentExamRate.log

cat ../$1|grep "concurrency, ID" > ID.log

awk 'BEGIN{cnt_a=0;cnt_b=0;}FNR==NR{a[cnt_a++]=$0;next} {b[cnt_b++]=$0} END{cnt=0;cnt_max=cnt_a> result.log

averageRowsSentExamRate=`cat result.log|awk 'NR == 1'|awk '{print $NF}'`

echo -e "\033[33m##### averageRowsSentExamRate: $averageRowsSentExamRate\033[0m" >> totalResult.log

echo " " >> totalResult.log

rowCount=`cat result.log|wc -l`

for (( id=2; id<=$rowCount; id++ ))

do

queryId=`cat result.log |awk 'NR == '$id''|awk '{print $2 " " $3}'`

cat result.log |grep "$queryId"|awk '{print $1 " " $2 "" $3 " " $4 " " $5 " " $6 " " $7 " " $8 " " $9}' >> totalResult.log

execTime=`cat ../$1 |grep -A 7 "$queryId"|sed -n '$p'|awk '{print $5}'|sed 's/s//g'`

parallelRate=`awk 'BEGIN{printf "%.2f\n",'$execTime'/'$duration'}'`

rowsSentExamRate=`cat result.log |grep "$queryId"|awk '{print $NF}'`

if [[ $duration -gt $execTime  ]]; then

echo "execTime: $execTime < slowqueryduration $duration" >> totalResult.log

else

echo -e "\033[32mexecTime: $execTime > slowqueryduration $duration\033[0m" >> totalResult.log

fi

echo $parallelRate | awk '{if($0 < 1) print "parallelRate:'$parallelRate' < 1"; else print "\033[32mparallelRate: '$parallelRate' > 1\033[0m"}' >> totalResult.log

echo "rowsSentExamRate: $rowsSentExamRate" >> totalResult.log

echo "    " >> totalResult.log

done

more totalResult.log

fi