def send_rds_slowlog_by_id(self, id_desc_list): log_path_list = [] now = datetime.datetime.now() diff = now - datetime.timedelta(days=1) start_time = diff.strftime("%Y-%m-%dT%H:00Z") end_time = now.strftime("%Y-%m-%dT%H:00Z") for dbname, dbid in id_desc_list.items(): request = DescribeSlowLogRecordsRequest.DescribeSlowLogRecordsRequest() request.set_DBInstanceId(dbid) request.set_PageSize(50) request.set_StartTime(start_time) request.set_EndTime(end_time) result = self._request(request) #print json.dumps(result,sort_keys=True,indent=4, separators=(',', ': ')) if result['TotalRecordCount'] > 0: log_path = "/tmp/%s-slowlog.csv" % dbname self.save_csv(result['Items']['SQLSlowRecord'], log_path) log_path_list.append(log_path) if log_path_list: self.send_email(u"数据库慢SQL", u"附件为%s ~ %s慢日志,请查收。谢谢!" % (start_time, end_time), log_path_list)
def get_record(startdate, enddate, pagenum): request = DescribeSlowLogRecordsRequest.DescribeSlowLogRecordsRequest() request.set_accept_format('json') request.set_DBInstanceId('rm-bp196ckqc2wi132y1') request.set_StartTime(startdate + 'Z') request.set_EndTime(enddate + 'Z') request.set_action_name('DescribeSlowLogs') request.set_PageSize(100) request.set_PageNumber(pagenum) result = clt.do_action(request) with open('MaxExecutionTime%s--%s.txt' % (startdate, enddate), 'a+') as M_w: for i in eval(result)['Items']['SQLSlowLog']: # if i['MaxExecutionTime']>1: M_w.writelines( str( str(i['MaxExecutionTime']) + "\t" + i['DBName'] + "\t" + i['SQLText'] + "\n")) with open('TotalExecutionTimes%s--%s.txt' % (startdate, enddate), 'a+') as T_w: for i in eval(result)['Items']['SQLSlowLog']: #if i['MySQLTotalExecutionTimes']>1: T_w.writelines( str( str(i['MySQLTotalExecutionTimes']) + "\t" + i['DBName'] + "\t" + i['SQLText'] + "\n"))
def DescribeSlowLogRecords(self, StartTime, EndTime, **kwargs): """查看慢日志明细SQLId,DBName、PageSize、PageNumber""" request = DescribeSlowLogRecordsRequest.DescribeSlowLogRecordsRequest() values = {"action_name": "DescribeSlowLogRecords", "DBInstanceId": self.DBInstanceId, "StartTime": StartTime, "EndTime": EndTime} values = dict(values, **kwargs) result = self.request_api(request, values) return result
def DescribeSlowLogRecords(self, dbInstanceId, startTime, endTime, pageNumber): request = DescribeSlowLogRecordsRequest.DescribeSlowLogRecordsRequest() request.set_accept_format('json') request.set_DBInstanceId(dbInstanceId) request.set_StartTime(startTime) request.set_EndTime(endTime) request.set_PageNumber(pageNumber) return json.loads(self.clt.do_action(request))
def get_pagecount(startdate, enddate): #传入查询初始时间和结束时间 request = DescribeSlowLogRecordsRequest.DescribeSlowLogRecordsRequest() request.set_accept_format('json') request.set_DBInstanceId('rm-bp196ckqc2wi132y1') request.set_StartTime(startdate + 'Z') request.set_EndTime(enddate + 'Z') request.set_action_name('DescribeSlowLogs') request.set_PageSize(100) request.set_PageNumber(1) result = clt.do_action(request) return eval(result)['TotalRecordCount']
def DescribeSlowLogRecords(self, DBInstanceId, StartTime, EndTime, DBType, **kwargs): '''查看慢日志明细 SQLId,DBName、PageSize、PageNumber''' if DBType == 'mongodb': request = DdsDescribeSlowLogRecordsRequest() else: request = DescribeSlowLogRecordsRequest.DescribeSlowLogRecordsRequest() values = {"action_name": "DescribeSlowLogRecords", "DBInstanceId": DBInstanceId, "StartTime": StartTime, "EndTime": EndTime} values = dict(values, **kwargs) result = self.request_api(request, values) return result
def getSlowLogDetails(client, dbInstanceId, startTime, endTime, dbName): request = DescribeSlowLogRecordsRequest.DescribeSlowLogRecordsRequest() request.set_DBInstanceId(dbInstanceId) request.set_StartTime(startTime) request.set_EndTime(endTime) request.set_DBName(dbName) try: response = client.do_action_with_exception(request) return response except ServerException as exception: print("Error while getting slow log details.") print("HTTP statue = ", exception.get_http_status()) print("HTTP Error code = ", exception.get_error_code()) print("Error message = ", exception.get_error_msg()) return None
def GetSlowlog(DBInstanceId, StartTime, EndTime): Slowlog = DescribeSlowLogRecordsRequest.DescribeSlowLogRecordsRequest() Slowlog.set_accept_format('json') Slowlog.set_DBInstanceId(DBInstanceId) Slowlog.set_StartTime(StartTime) # Slowlog.set_StartTime("2017-10-13T15:00Z") Slowlog.set_EndTime(EndTime) SlowlogInfo = clt.do_action_with_exception(Slowlog) Info = (json.loads(SlowlogInfo)) PageRecordCount = Info['PageRecordCount'] # print PageRecordCount if PageRecordCount == 0: print 0 else: i = 0 while i < PageRecordCount: SQL = Info['Items']['SQLSlowRecord'][i]['SQLText'] DBNAME = Info['Items']['SQLSlowRecord'][i]['DBName'] QueryTimes = Info['Items']['SQLSlowRecord'][i]['QueryTimes'] i = i + 1 print "No: %s ,DBNAME: %s ,QueryTimes: %s ,SLOWSQL: %s" % ( i, DBNAME, QueryTimes, SQL)
def rds_slow_sql(request, db_instance_id, timing): temp_name = "monitor/monitor-header.html" if db_instance_id is None: return HttpResponse("<h3>db_instance_id can not be None!</h3>", content_type="text/html") else: t = time.localtime(time.time() - TIME_SECTOR[int(timing)]) start_time = time.strftime("%Y-%m-%dT%H:%MZ", t) end_time = time.strftime("%Y-%m-%dT%H:%MZ", time.localtime()) # request = DescribeSlowLogsRequest.DescribeSlowLogsRequest() req = DescribeSlowLogRecordsRequest.DescribeSlowLogRecordsRequest() req.set_DBInstanceId(db_instance_id) req.set_StartTime(start_time) req.set_EndTime(end_time) client = AcsClient(ACCESS_KEY_ID, ACCESS_KEY_SECRET, 'cn-shanghai') resp = json.loads(client.do_action_with_exception(req), encoding='utf-8') slow_sql_list = resp.get('Items').get('SQLSlowRecord') return render(request, "monitor/rds_slow_sql.html", locals())
'cn-hangzhou') dblist = [ 'rr-2zeyvcbluukv1qiwn', 'rm-2zek28989p9k1179v', 'rr-2zeo3es975ai399y', 'rr-2ze0s71v12o8qdtuh', 'rr-2ze9wz8bb610cx7', 'rr-2ze6hc62pa61dx0p4', 'rr-2zela73o7mh0s2p8o', 'rr-2ze6t3cl58ad3w62', 'rr-2ze1t8s3l254v9qqv', 'rr-2ze66z8t4fpd0x51h', 'rm-2zeg270l9534v1a5m', 'rm-2ze6239209hgvuvgq', 'rm-2zey971kf9t3ublde', 'rm-2ze15vy3vb88cp426', 'rm-2zezohz4xdu54263k', 'rm-2ze85u60ll2iw7x46', 'rm-2ze29k1wb187v1796', 'rm-2ze2yq4xg2mz3a5p', 'rr-2ze5ufm1idmn4bn01', 'rr-2ze4ka78ef56b70fq', 'rr-2ze79e35ia84d2dpq', 'rm-2ze509kn6k6177uf5' ] starttime = time.strftime('%Y-%m-%d') + "T00:00Z" endtime = time.strftime('%Y-%m-%dT%H:%m') + 'Z' request = DescribeSlowLogRecordsRequest.DescribeSlowLogRecordsRequest() request.set_accept_format('json') def exc_sql(db): request.add_query_param('DBInstanceId', db) request.add_query_param('StartTime', starttime) request.add_query_param('EndTime', endtime) request.add_query_param('PageSize', 100) request.add_query_param('PageNumber', 1) response = clt.do_action(request) return json.loads(response) if __name__ == "__main__": for db in dblist: