# !/usr/bin/python
# coding=utf-8
import json
import datetime
import sys
from multiprocessing import Process as pro
from aliyunsdkcore import client
ACCEPT_FORMAT = 'json'
API_ID="L11111111111111111111"
API_KEY="ekElR22222222222222222222222222"
API_REGION="cn-qingdao"
DATA_FILE_PATH = "/home/test/rds_monitor/tmp/"
PERFORMANCE_ITEM = "MySQL_InnoDBBufferRatio,MySQL_InnoDBDataReadWriten,MySQL_InnoDBLogRequests,MySQL_InnoDBLogWrites,MySQL_InnoDBBufferRatio,MySQL_NetworkTraffic,MySQL_QPSTPS,MySQL_Sessions,MySQL_TempDiskTableCreates,MySQL_COMDML,MySQL_RowDML,MySQL_MemCpuUsage,MySQL_IOPS,MySQL_DetailedSpaceUsage,slavestat"
def describe_db_instances(**instance_set):
# RDS实例列表
from aliyunsdkrds.request.v20140815 import DescribeDBInstancesRequest
request = DescribeDBInstancesRequest.DescribeDBInstancesRequest()
request.set_accept_format(ACCEPT_FORMAT)
clt = client.AcsClient(API_ID, API_KEY, API_REGION)
response = clt.do_action_with_exception(request)
json_response = json.loads(response)
for i in range(0, json_response["TotalRecordCount"] - 1 + 1):
db_instance_id = json_response["Items"]["DBInstance"][i]["DBInstanceId"]
instance_set[db_instance_id] = json_response["Items"]["DBInstance"][i]["DBInstanceDescription"]
return instance_set
def describe_db_instance_attribute(db_instance_id,**key_value_dic):
# 查看RDS实例详情
from aliyunsdkrds.request.v20140815 import DescribeDBInstanceAttributeRequest
request = DescribeDBInstanceAttributeRequest.DescribeDBInstanceAttributeRequest()
request.set_accept_format(ACCEPT_FORMAT)
request.set_DBInstanceId(db_instance_id);
clt = client.AcsClient(API_ID, API_KEY, API_REGION)
response = clt.do_action_with_exception(request)
json_response = json.loads(response)
key_value_dic["DBInstanceAttribute_DBInstanceDescription"] = json_response["Items"]["DBInstanceAttribute"][0][
"DBInstanceDescription"]
key_value_dic["DBInstanceAttribute_Category"] = json_response["Items"]["DBInstanceAttribute"][0]["Category"]
key_value_dic["DBInstanceAttribute_LockMode"] = json_response["Items"]["DBInstanceAttribute"][0]["LockMode"]
key_value_dic["DBInstanceAttribute_ConnectionString"] = json_response["Items"]["DBInstanceAttribute"][0][
"ConnectionString"]
key_value_dic["DBInstanceAttribute_CreationTime"] = json_response["Items"]["DBInstanceAttribute"][0]["CreationTime"]
key_value_dic["DBInstanceAttribute_DBInstanceNetType"] = json_response["Items"]["DBInstanceAttribute"][0][
"DBInstanceNetType"]
key_value_dic["DBInstanceAttribute_MaxConnections"] = json_response["Items"]["DBInstanceAttribute"][0][
"MaxConnections"]
key_value_dic["DBInstanceAttribute_Engine"] = json_response["Items"]["DBInstanceAttribute"][0]["Engine"]
key_value_dic["DBInstanceAttribute_ExpireTime"] = json_response["Items"]["DBInstanceAttribute"][0]["ExpireTime"]
key_value_dic["DBInstanceAttribute_DBInstanceMemory"] = json_response["Items"]["DBInstanceAttribute"][0][
"DBInstanceMemory"]
key_value_dic["DBInstanceAttribute_DBInstanceCPU"] = json_response["Items"]["DBInstanceAttribute"][0][
"DBInstanceCPU"]
key_value_dic["DBInstanceAttribute_DBInstanceType"] = json_response["Items"]["DBInstanceAttribute"][0][
"DBInstanceType"]
if json_response["Items"]["DBInstanceAttribute"][0]["DBInstanceStatus"] == "Running":
key_value_dic["DBInstanceAttribute_DBInstanceStatus"] = 1
else:
key_value_dic["DBInstanceAttribute_DBInstanceStatus"] = 0
key_value_dic["DBInstanceAttribute_AvailabilityValue"] = json_response["Items"]["DBInstanceAttribute"][0][
"AvailabilityValue"]
key_value_dic["DBInstanceAttribute_ZoneId"] = json_response["Items"]["DBInstanceAttribute"][0]["ZoneId"]
key_value_dic["DBInstanceAttribute_EngineVersion"] = json_response["Items"]["DBInstanceAttribute"][0][
"EngineVersion"]
key_value_dic["DBInstanceAttribute_InstanceNetworkType"] = json_response["Items"]["DBInstanceAttribute"][0][
"InstanceNetworkType"]
key_value_dic["DBInstanceAttribute_MaxIOPS"] = json_response["Items"]["DBInstanceAttribute"][0]["MaxIOPS"]
key_value_dic["DBInstanceAttribute_DBInstanceStorage"] = json_response["Items"]["DBInstanceAttribute"][0][
"DBInstanceStorage"]
key_value_dic["DBInstanceAttribute_DBInstanceId"] = json_response["Items"]["DBInstanceAttribute"][0]["DBInstanceId"]
key_value_dic["DBInstanceAttribute_MaintainTime"] = json_response["Items"]["DBInstanceAttribute"][0]["MaintainTime"]
key_value_dic["DBInstanceAttribute_DBInstanceClass"] = json_response["Items"]["DBInstanceAttribute"][0][
"DBInstanceClass"]
key_value_dic["DBInstanceAttribute_Port"] = json_response["Items"]["DBInstanceAttribute"][0]["Port"]
return key_value_dic
def describe_resource_usage(db_instance_id,**key_value_dic):
from aliyunsdkrds.request.v20140815 import DescribeResourceUsageRequest
request = DescribeResourceUsageRequest.DescribeResourceUsageRequest()
request.set_accept_format(ACCEPT_FORMAT)
request.set_DBInstanceId(db_instance_id);
clt = client.AcsClient(API_ID, API_KEY, API_REGION)
response = clt.do_action_with_exception(request)
json_response = json.loads(response)
key_value_dic["ResourceUsage_SQLSize"] = json_response["SQLSize"]
key_value_dic["ResourceUsage_DiskUsed"] = json_response["DiskUsed"]
key_value_dic["ResourceUsage_LogSize"] = json_response["LogSize"]
key_value_dic["ResourceUsage_BackupSize"] = json_response["BackupSize"]
key_value_dic["ResourceUsage_ColdBackupSize"] = json_response["ColdBackupSize"]
key_value_dic["ResourceUsage_DataSize"] = json_response["DataSize"]
return key_value_dic
def describe_resource_diagnosis(db_instance_id,**key_value_dic):
# 资源使用情况分析，可精确到300s
# GMT to UTC
now = datetime.datetime.now() - datetime.timedelta(hours=8)
now_before = now - datetime.timedelta(seconds=300)
END_TIME = now.strftime('%Y-%m-%dT%H:%MZ')
START_TIME = now_before.strftime('%Y-%m-%dT%H:%MZ')
from aliyunsdkrds.request.v20140815 import DescribeResourceDiagnosisRequest
request = DescribeResourceDiagnosisRequest.DescribeResourceDiagnosisRequest()
request.set_accept_format(ACCEPT_FORMAT)
request.set_StartTime(START_TIME);
request.set_EndTime(END_TIME);
request.set_DBInstanceId(db_instance_id);
clt = client.AcsClient(API_ID, API_KEY, API_REGION)
response = clt.do_action_with_exception(request)
json_response = json.loads(response)
key_value_dic["ResourceDiagnosis_StorageMax"] = json_response["Storage"]["Max"]
key_value_dic["ResourceDiagnosis_StorageAvg"] = json_response["Storage"]["Avg"]
key_value_dic["ResourceDiagnosis_StorageMin"] = json_response["Storage"]["Min"]
key_value_dic["ResourceDiagnosis_ConnectionMax"] = json_response["Connection"]["Max"]
key_value_dic["ResourceDiagnosis_ConnectionAvg"] = json_response["Connection"]["Avg"]
key_value_dic["ResourceDiagnosis_ConnectionMin"] = json_response["Connection"]["Min"]
key_value_dic["ResourceDiagnosis_IOPSMax"] = json_response["IOPS"]["Max"]
key_value_dic["ResourceDiagnosis_IOPSAvg"] = json_response["IOPS"]["Avg"]
key_value_dic["ResourceDiagnosis_IOPSMin"] = json_response["IOPS"]["Min"]
key_value_dic["ResourceDiagnosis_MemoryMax"] = json_response["Memory"]["Max"]
key_value_dic["ResourceDiagnosis_MemoryAvg"] = json_response["Memory"]["Avg"]
key_value_dic["ResourceDiagnosis_MemoryMin"] = json_response["Memory"]["Min"]
key_value_dic["ResourceDiagnosis_CPUMax"] = json_response["CPU"]["Max"]
key_value_dic["ResourceDiagnosis_CPUAvg"] = json_response["CPU"]["Avg"]
key_value_dic["ResourceDiagnosis_CPUMin"] = json_response["CPU"]["Min"]
return key_value_dic
def db_instance_performance_request(db_instance_id):
# GMT to UTC
now = datetime.datetime.now() - datetime.timedelta(hours=8)
now_before = now - datetime.timedelta(seconds=300)
END_TIME = now.strftime('%Y-%m-%dT%H:%MZ')
START_TIME = now_before.strftime('%Y-%m-%dT%H:%MZ')
from aliyunsdkrds.request.v20140815 import DescribeDBInstancePerformanceRequest
request = DescribeDBInstancePerformanceRequest.DescribeDBInstancePerformanceRequest()
request.set_accept_format(ACCEPT_FORMAT)
request.set_DBInstanceId(db_instance_id);
request.set_StartTime(START_TIME);
request.set_EndTime(END_TIME);
request.set_Key(PERFORMANCE_ITEM)
clt = client.AcsClient(API_ID, API_KEY, API_REGION)
response = clt.do_action_with_exception(request)
json_response = json.loads(response)
# print json_response
# print json_response.keys()
# print json_response["DBInstanceId"]
item_total = len(json_response["PerformanceKeys"]["PerformanceKey"])
key_value_dic = {}
for item_no in range(item_total):
key = json_response["PerformanceKeys"]["PerformanceKey"][item_no]["Key"]
unit = json_response["PerformanceKeys"]["PerformanceKey"][item_no]["Unit"]
values = json_response["PerformanceKeys"]["PerformanceKey"][item_no]["Values"]
values_value = values["PerformanceValue"][0]["Value"]
value_format = json_response["PerformanceKeys"]["PerformanceKey"][item_no]["ValueFormat"]
if key == "MySQL_InnoDBBufferRatio":
apart = values_value.index('&')
apart_second = values_value.index('&', apart + 1)
key_value_dic["DBInstancePerformance_InnoDBReadHit"] = values_value[0:apart]
key_value_dic["DBInstancePerformance_InnoDBUseRatio"] = values_value[apart + 1:apart_second]
key_value_dic["DBInstancePerformance_InnoDBDirtyRatio"] = values_value[apart_second + 1:]
elif key == "MySQL_NetworkTraffic":
apart = values_value.index('&')
key_value_dic["DBInstancePerformance_NetworkTrafficRecv"] = values_value[0:apart]
key_value_dic["DBInstancePerformance_NetworkTrafficSent"] = values_value[apart + 1:]
elif key == "MySQL_QPSTPS":
apart = values_value.index('&')
key_value_dic["DBInstancePerformance_QPS"] = values_value[0:apart]
key_value_dic["DBInstancePerformance_TPS"] = values_value[apart + 1:]
elif key == "MySQL_Sessions":
apart = values_value.index('&')
key_value_dic["DBInstancePerformance_SessionsActive"] = values_value[0:apart]
key_value_dic["DBInstancePerformance_SessionsTotal"] = values_value[apart + 1:]
elif key == "MySQL_InnoDBDataReadWriten":
apart = values_value.index('&')
key_value_dic["DBInstancePerformance_InnoDBDataRead"] = values_value[0:apart]
key_value_dic["DBInstancePerformance_InnoDBDataWriten"] = values_value[apart + 1:]
elif key == "MySQL_InnoDBLogRequests":
apart = values_value.index('&')
key_value_dic["DBInstancePerformance_InnoDBLogRequestsRead"] = values_value[0:apart]
key_value_dic["DBInstancePerformance_InnoDBLogRequestsWriten"] = values_value[apart + 1:]
elif key == "MySQL_InnoDBLogWrites":
apart = values_value.index('&')
apart_second = values_value.index('&', apart + 1)
key_value_dic["DBInstancePerformance_InnoDBLogResquests"] = values_value[0:apart]
key_value_dic["DBInstancePerformance_InnoDBLogWrites"] = values_value[apart + 1:apart_second]
key_value_dic["DBInstancePerformance_InnoDBLogFsyncs"] = values_value[apart_second + 1:]
elif key == "MySQL_TempDiskTableCreates":
key_value_dic["DBInstancePerformance_TempDiskTableCreates"] = values_value
elif key == "MySQL_COMDML":
apart = values_value.index('&')
apart_second = values_value.index('&', apart + 1)
apart_thrid = values_value.index('&', apart_second + 1)
apart_fourth = values_value.index('&', apart_thrid + 1)
apart_fifth = values_value.index('&', apart_fourth + 1)
apart_sixth = values_value.index('&', apart_fifth + 1)
key_value_dic["DBInstancePerformance_COMDML__delete"] = values_value[0:apart]
key_value_dic["DBInstancePerformance_COMDML__insert"] = values_value[apart + 1:apart_second]
key_value_dic["DBInstancePerformance_COMDML__insertSelect"] = values_value[apart_second + 1:apart_thrid]
key_value_dic["DBInstancePerformance_COMDML__replace"] = values_value[apart_thrid + 1:apart_fourth]
key_value_dic["DBInstancePerformance_COMDML__replaceSelect"] = values_value[apart_fourth + 1:apart_fifth]
key_value_dic["DBInstancePerformance_COMDML__select"] = values_value[apart_fifth + 1:apart_sixth]
key_value_dic["DBInstancePerformance_COMDML__update"] = values_value[apart_sixth + 1:]
elif key == "MySQL_COMDML":
apart = values.value.index('&')
apart_second = values.value.index('&', apart + 1)
apart_thrid = values.value.index('&', apart_second + 1)
apart_fourth = values.value.index('&', apart_thrid + 1)
key_value_dic["DBInstancePerformance_Row_readed"] = values.value[0:apart]
key_value_dic["DBInstancePerformance_Row_update"] = values.value[apart + 1:apart_second]
key_value_dic["DBInstancePerformance_Row_delete"] = values.value[apart_second + 1:apart_thrid]
key_value_dic["DBInstancePerformance_Row_insert"] = values.value[apart_thrid + 1:apart_fourth]
key_value_dic["DBInstancePerformance_Row_writes"] = values.value[apart_fourth + 1:]
elif key == "MySQL_MemCpuUsage":
apart = values_value.index('&')
key_value_dic["DBInstancePerformance_MemUsage"] = 100.00 - float(values_value[0:apart])
key_value_dic["DBInstancePerformance_CpuUsage"] = 100.00 - float(values_value[apart + 1:])
elif key == "MySQL_IOPS":
key_value_dic["DBInstancePerformance_IOPS"] = values_value
elif key == "MySQL_DetailedSpaceUsage":
apart = values_value.index('&')
apart_second = values_value.index('&', apart + 1)
apart_thrid = values_value.index('&', apart_second + 1)
apart_fourth = values_value.index('&', apart_thrid + 1)
key_value_dic["DBInstancePerformance_DetailedSpaceUsage_ins"] = values_value[0:apart]
key_value_dic["DBInstancePerformance_DetailedSpaceUsage_data"] = values_value[apart + 1:apart_second]
key_value_dic["DBInstancePerformance_DetailedSpaceUsage_log"] = values_value[apart_second + 1:apart_thrid]
key_value_dic["DBInstancePerformance_DetailedSpaceUsage_tmp"] = values_value[apart_thrid + 1:apart_fourth]
key_value_dic["DBInstancePerformance_DetailedSpaceUsage_other"] = values_value[apart_fourth + 1:]
elif key == "slavestat":
apart = values_value.index('&')
apart_second = values_value.index('&', apart + 1)
key_value_dic["DBInstancePerformance_iothread"] = values_value[0:apart]
key_value_dic["DBInstancePerformance_sqlthread"] = values_value[apart + 1:apart_second]
key_value_dic["DBInstancePerformance_slaveLag"] = values_value[apart_second + 1:]
elif key == "MySQL_InnoDBBufferRatio":
apart = values_value.index('&')
apart_second = values_value.index('&', apart + 1)
key_value_dic["DBInstancePerformance_InnoDBReadHit"] = values_value[0:apart]
key_value_dic["DBInstancePerformance_InnoDBUseRatio"] = values_value[apart + 1:apart_second]
key_value_dic["DBInstancePerformance_InnoDBDirtyRatio"] = values_value[apart_second + 1:]
print key_value_dic
return key_value_dic
def get_value(db_instance_id):
"""
MySQL_NetworkTraffic MySQL实例平均每秒钟的输入流量，MySQL实例平均每秒钟的输出流量。单位为KB。
MySQL_QPSTPS 平均每秒SQL语句执行次数，平均每秒事务数
MySQL_Sessions 当前活跃连接数，当前总连接数
MySQL_InnoDBBufferRatio InnoDB缓冲池的读命中率，InnoDB缓冲池的利用率，InnoDB缓冲池脏块的百分率
MySQL_InnoDBDataReadWriten InnoDB平均每秒钟读取的数据量，InnoDB平均每秒钟写入的数据量。单位为KB
MySQL_InnoDBLogRequests 平均每秒向InnoDB缓冲池的读次数，平均每秒向InnoDB缓冲池的写次数
MySQL_InnoDBLogWrites 平均每秒日志写请求数，平均每秒向日志文件的物理写次数，平均每秒向日志文件完成的fsync()写数量
MySQL_TempDiskTableCreates MySQL执行语句时在硬盘上自动创建的临时表的数量
MySQL_MyISAMKeyBufferRatio MyISAM平均每秒Key Buffer利用率，MyISAM平均每秒Key Buffer读命中率，MyISAM平均每秒Key Buffer写命中率
MySQL_MyISAMKeyReadWrites MyISAM平均每秒钟从缓冲池中的读取次数，MyISAM平均每秒钟从缓冲池中的写入次数，MyISAM平均每秒钟从硬盘上读取的次数，MyISAM平均每秒钟从硬盘上写入的次数
MySQL_COMDML 平均每秒Delete语句执行次数，平均每秒Insert语句执行次数， 平均每秒Insert_Select语句执行次数，平均每秒Replace语句执行次数，平均每秒Replace_Select语句执行次数，平均每秒Select语句执行次数，平均每秒Update语句执行次数
MySQL_RowDML 平均每秒从InnoDB表读取的行数，平均每秒从InnoDB表更新的行数，平均每秒从InnoDB表删除的行数，平均每秒从InnoDB表插入的行数，平均每秒向日志文件的物理写次数
MySQL_MemCpuUsage MySQL实例CPU使用率(占操作系统总数)，MySQL实例内存使用率(占操作系统总数)
MySQL_IOPS MySQL实例的IOPS（每秒IO请求次数）
MySQL_DetailedSpaceUsage MySQL实例空间占用详情：ins_size实例总空间使用量;data_size数据空间;log_size日志空间;tmp_size临时空间;other_size系统空间
slavestat 只读实例延迟
"""
key_value_dic = {}
key_value_dic = db_instance_performance_request(db_instance_id);
key_value_dic = describe_resource_diagnosis(db_instance_id,**key_value_dic);
key_value_dic = describe_db_instance_attribute(db_instance_id,**key_value_dic);
key_value_dic = describe_resource_usage(db_instance_id,**key_value_dic);
data_file = DATA_FILE_PATH + db_instance_id
with open(data_file, 'w') as fp:
fp.write(json.dumps(key_value_dic))
fp.close()
def main():
instance_dict= {}
instance_dict = describe_db_instances(**instance_dict);
for db_instance_id in instance_dict.keys():
t = pro(target=get_value(db_instance_id))
t.start
if __name__ == '__main__':
# 指定编码格式
reload(sys)
sys.setdefaultencoding('utf8')
# main
main()
