#利用线性拟合模型发现测试环境性能隐患
##一个经典的性能问题
投产后数周，生产环境出现了系统响应缓慢、数据库负载冲高、大量数据库连接不释放的生产事件，问题原因是由于一张表的数据量在投产后持续增长，且有联机语句使用了全表扫描访问了这张表，导致执行时间变长，数据库锁等待增加，资源被持续消耗，引发数据库性能问题。这类问题在过去的几年中一直困扰着我们，令我们不胜其烦。当然，也有很多朋友支了很多招，比如恢复全量的生产数据，或者杜绝所有联机全表扫描，但是这些办法都不太奏效。首先，测试环境资源有限，尤其是没有那么多存储；其次，联机全表扫描并不违反开发规范，而且针对数据量很小的全表扫描是非常合理的，因此很难说服开发去做修改。所以这个问题最终还得靠测试自己来解决。
##解决方法
**“性能指标的持续增长在未来必然会引发测试问题。”**应该是性能测试领域的定理，我们根据这条定理，定制了一个趋势分析模型，用来找出那些存在持续增长趋势的指标。原理非常简单：
- 利用自主开发的数据库监控系统，记录测试环境发生的所有全表扫描语句
- 对全表扫描的对象统计记录数，每天记录一次
- 利用python的一次线性拟合模型，对全表扫描对象的测试和生产环境记录数进行线性拟合
- 根据拟合结果，对增长趋势>0，拟合度>80%的记录进行统计
- 根据记录中的sqlid深入分析
同理，我们也将语句执行时间、语句cost、操作系统CPU、内存使用率，网络连接数等指标也纳入了该模型分析。这样就能够在日常的功能测试中，发现那些存在性能隐患，但是有没有达到报警阈值的性能问题。
##代码
# encoding=gbk
#根据APP_SQL_COST表中记录，统计COST和执行时间持续增长的语句
import math
import cx_Oracle
import collections
#一次线性拟合模型
def linefit(x , y):
N = float(len(x))
sx,sy,sxx,syy,sxy=0,0,0,0,0
for i in range(0,int(N)):
sx += x[i]
sy += y[i]
sxx += x[i]*x[i]
syy += y[i]*y[i]
sxy += x[i]*y[i]
a = (sy*sx/N -sxy)/( sx*sx/N -sxx)
b = (sy - a*sx)/N
if a==0:
r=0
else:
r = abs(sy*sx/N-sxy)/math.sqrt((sxx-sx*sx/N)*(syy-sy*sy/N))
return a,b,r
def func_table_full(appname,addr,dbsid):
str0="SELECT OBJECT_OWNER, OBJECT_NAME FROM APP_SQL_PLAN_STAT WHERE APPNAME='"+APPNAME+"' AND ADDR='"+ADDR+"' AND DBSID='"+DBSID+"' GROUP BY OBJECT_OWNER, OBJECT_NAME"
cursor0 = conn.cursor()
cursor0.execute(str0)
res0 = cursor0.fetchall()
rowcount0 = len(res0)
x=0
result=[]
while x<rowcount0:
table_owner=res0[x][0]
table_name=res0[x][1]
str1="SELECT TABLE_NAME,rank() over(partition by TABLE_NAME order by EXEC_SEQ,CHECK_TIME,STAT_DATE) rk,nvl(rows_count,0) FROM APP_TABLE_ROWS WHERE APPNAME='"+APPNAME+"' AND ADDR='"+ADDR+"' AND DBSID='"+DBSID+"' AND TABLE_NAME='"+table_name+"' AND OWNER='"+table_owner+"'"
cursor1 = conn.cursor()
cursor1.execute(str1)
res1 = cursor1.fetchall()
rowcount1 = len(res1)
RK=[]
ROWS=[]
if rowcount1>=3:
y = 0
while y<rowcount1:
RK.append(res1[y][1])
ROWS.append(res1[y][2])
y=y+1
try:
a,b,r=linefit(RK,ROWS)
except:
print APPNAME,ADDR,DBSID,ROWS
if a>0 and r>0.8 :
str2="INSERT INTO APP_GROWTH_MODEL VALUES('"+APPNAME+"','"+ADDR+"','"+DBSID+"','ROWS','"+table_owner+"."+table_name+"','"+str(max(ROWS))+"','"+str(a)+"','"+str(r)+"','"+str(len(RK))+"','')"
cursor2 = conn.cursor()
cursor2.execute(str2)
cursor2.execute("commit")
str2 = "SELECT TABLE_NAME,rank() over(partition by TABLE_NAME order by to_date(COLLECT_DATE,'YYYY-MM-DD')) rk,nvl(NUM_ROWS,0) FROM MCTEST.DB_SC_ROW_NUM WHERE UPPER(DBSID)=UPPER('"+DBSID+"') AND TABLE_NAME='"+table_name+"' AND OWNER='"+table_owner+"'"
cursor1 = conn.cursor()
cursor1.execute(str2)
res1 = cursor1.fetchall()
rowcount1 = len(res1)
RK=[]
ROWS=[]
if rowcount1>=3:
y = 0
while y<rowcount1:
RK.append(res1[y][1])
ROWS.append(res1[y][2])
y=y+1
try:
a,b,r=linefit(RK,ROWS)
except:
print APPNAME,ADDR,DBSID,ROWS
if a>0 and r>0.8 :
str2="INSERT INTO APP_GROWTH_MODEL VALUES('"+APPNAME+"','"+ADDR+"','"+DBSID+"','SCROWS','"+table_owner+"."+table_name+"','"+str(max(ROWS))+"','"+str(a)+"','"+str(r)+"','"+str(len(RK))+"','')"
#print str2
cursor2 = conn.cursor()
cursor2.execute(str2)
cursor2.execute("commit")
x=x+1
if __name__ == '__main__':
distinct_count=3
conn = cx_Oracle.connect('test/test@127.0.0.1/test')
strdel="DELETE FROM APP_GROWTH_MODEL"
cursordel=conn.cursor()
cursordel.execute(strdel)
cursordel.execute("commit")
str2="SELECT APPNAME,ADDR,DBSID FROM APP_SQL_COST GROUP BY APPNAME,ADDR,DBSID"
cursor2 = conn.cursor()
cursor2.execute(str2)
res2 = cursor2.fetchall()
rowcount2 = len(res2)
z=0
while z<rowcount2:
APPNAME=res2[z][0]
ADDR=res2[z][1]
DBSID=res2[z][2]
table_full=func_table_full(APPNAME,ADDR,DBSID)
str0="SELECT DISTINCT SQL_ID FROM APP_SQL_COST WHERE APPNAME='"+APPNAME+"' AND ADDR='"+ADDR+"' AND DBSID='"+DBSID+"'"
cursor0 = conn.cursor()
cursor0.execute(str0)
res0 = cursor0.fetchall()
rowcount0 = len(res0)
x=0
while x<rowcount0:
SQL_ID=str(res0[x][0])
str1="SELECT SQL_ID,rank() over(partition by SQL_ID order by STAT_DATE,SNAP_ID,PLAN_HASH_VALUE) rk,nvl(cost,0),nvl(elapsed_time,0) FROM APP_SQL_COST WHERE APPNAME='"+APPNAME+"' AND ADDR='"+ADDR+"' AND DBSID='"+DBSID+"' AND SQL_ID='"+SQL_ID+"'"
cursor1 = conn.cursor()
cursor1.execute(str1)
res1 = cursor1.fetchall()
rowcount1 = len(res1)
RK=[]
COST=[]
ELAPSED_TIME=[]
if rowcount1>=3:
y = 0
while y<rowcount1:
#print res1[y][1],res1[y][2], res1[y][3]
RK.append(res1[y][1])
COST.append(res1[y][2])
ELAPSED_TIME.append(res1[y][3])
y=y+1
try:
a,b,r=linefit(RK,COST)
except:
print APPNAME,ADDR,DBSID,COST
distinct_cost=len(set(COST))
if a>0 and r>0.8 and distinct_cost>=distinct_count and max(COST)>1000:
str_ins="INSERT INTO APP_GROWTH_MODEL VALUES('"+APPNAME+"','"+ADDR+"','"+DBSID+"','COST','"+SQL_ID+"','"+str(max(COST))+"','"+str(a)+"','"+str(r)+"','"+str(len(RK))+"','')"
cursor_ins = conn.cursor()
cursor_ins.execute(str_ins)
cursor_ins.execute("commit")
try:
a,b,r=linefit(RK,ELAPSED_TIME)
except:
print APPNAME,ADDR,DBSID,ELAPSED_TIME
distinct_time=len(set(ELAPSED_TIME))
if a>0 and r>0.8 and distinct_time>=distinct_count and max(ELAPSED_TIME)>60:
str_ins="INSERT INTO APP_GROWTH_MODEL VALUES('"+APPNAME+"','"+ADDR+"','"+DBSID+"','ELAPSED_TIME','"+SQL_ID+"','"+str(max(ELAPSED_TIME))+"','"+str(a)+"','"+str(r)+"','"+str(len(RK))+"','')"
cursor_ins = conn.cursor()
cursor_ins.execute(str_ins)
cursor_ins.execute("commit")
x=x+1
z=z+1
1114
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
