自动更新表统计信息
自动更新表统计信息
#author yw_shan
import os,sys
import subprocess
import re
import time
import threading
import argparse
import testlog
if sys.version.startswith(‘2.7’):
import Queue
else:
import queue
class updateStatis(object):
def __init__(self,host,user,pwd):
self.host=host
self.user=user
self.pwd=pwd
def get_dbconn(self):
# traf=”trafci.sh -h {} -u {} -p {} -q ” .format(self.host,self.user,self.pwd)
traf=”trafci.sh -h {} -u {} -p {} -q ” .format(num.host,num.user,num.password)
return traf
def get_tab(self):
gettab=[]
with open(‘./conf/tab_loop_test.conf’,’r’) as f:
trss_sql=f.readlines()
trss_sqls = ”.join(trss_sql)
tab = re.split(r’,|\n’, trss_sqls)
for tabs1 in tab:
if tabs1 is None or tabs1 == ” or tabs1.startswith(‘#’) or tabs1 ==”:
continue
tabss = tabs1.strip().replace(‘”‘, ”).replace(‘,’, ”)
gettab.append(tabss)
return gettab
def get_sql(self,pp,conn,tabs):
try:
for tabs1 in tabs:
sql='”update statistics for table {} on every key sample”‘ .format(tabs1)
esql=conn+sql
q.put(esql)
log.logger.info(pp+”–>”+esql)
q.task_done()
except Exception as e:
log.logger.error(“get_sql–>” + e)
def exec_sql(self,cc):
try:
flag=3
while 1:
if not q.empty():
gesql=q.get()
# print(“{}==>{}” .format(cc,gesql))
log.logger.info(cc+”==>”+gesql)
sp_out = subprocess.Popen(gesql, shell=True, stdout=subprocess.PIPE).communicate()[0]
log.logger.info(sp_out)
q.join()
if ‘ ERROR[‘ in sp_out:
log.logger.error(gesql)
rq = time.strftime(“%Y%m%d%H%M”, time.localtime())
with open(‘./logs/failUpdateSql.log’, ‘a+’) as f2:
f2.write(str(rq)+’ — ‘+str(gesql)+’\n’)
else:
flag=flag-1
if flag>=0:
continue
if flag<0:
break
except Exception as e:
log.logger.error(“exec_sql–>”+e)
def parser(self):
parser=argparse.ArgumentParser(prog=’update_statis.py’,description=’Function: update table statistics’)
parser.add_argument(‘-t’,’–thread’,type=int,default=2,help=’set the parallel number of threads’)
parser.add_argument(‘-H’, ‘–host’, type=str, default=”, help=’set host ip address’)
parser.add_argument(‘-u’, ‘–user’, type=str,default=”, help=’set user’)
parser.add_argument(‘-p’, ‘–password’, type=str,default=”, help=’set password’)
parser.add_argument(‘-q’, ‘–queue’, type=int, default=3, help=’set the parallel number of queue’)
arg=parser.parse_args()
return arg
def logs(self):
su=’statUpdate.log’
fsu=’failUpdateSql.log’
rq = time.strftime(“%Y%m%d%H%M”, time.localtime())
if not os.path.exists(‘logs’):
os.mkdir(‘./logs’)
os.chdir(os.path.join(os.getcwd(), ‘logs’))
if os.path.exists(su):
os.rename(su,su+’.’+rq)
if os.path.exists(fsu):
os.rename(fsu,fsu+’.’+rq)
os.chdir(os.path.dirname(os.getcwd()))
def main(self):
try:
thrp = threading.Thread(target=us.get_sql, args=(‘procdure’, conn, tab,))
thrp.start()
for i in range(int(num.thread)):
ct=threading.Thread(target=us.exec_sql, args=(‘custmer-%d’ % i,))
log.logger.info(ct)
ct.start()
for i in range(int(num.thread)):
ct.join()
log.logger.info(“run complete,waiting exit……”)
except Exception as e:
log.logger.error(“–main()–“+e)
import logging
from logging import handlers
class Logger(object):
level_relations = {
‘debug’:logging.DEBUG,
‘info’:logging.INFO,
‘warning’:logging.WARNING,
‘error’:logging.ERROR,
‘crit’:logging.CRITICAL
}
def __init__(self,filename,level=’info’,when=’D’,backCount=7,fmt=’%(asctime)s – %(filename)s[line:%(lineno)d] – %(levelname)s: %(message)s’):
self.logger = logging.getLogger(filename)
format_str = logging.Formatter(fmt)
self.logger.setLevel(self.level_relations.get(level))
sh = logging.StreamHandler()
sh.setFormatter(format_str)
th = handlers.TimedRotatingFileHandler(filename=filename,when=when,backupCount=backCount,encoding=’utf-8′)
th.setFormatter(format_str)
self.logger.addHandler(sh)
self.logger.addHandler(th)
执行效果
执行效果
