class Mysql(object):
logpath = "/tmp/zabbix_mysql_perf.log"
def __init__(self, iphost, username="******", password="******", port=3306, debug=True):
self._iphost = iphost
self._username = username
self._password = password
self._port = port
self._logger = Log(self.logpath,is_console=debug, mbs=5, count=5)
self._sql_result = self._get_mysql_cmd_output()
def get_logger(self):
return self._logger
def _get_mysql_cmd_output(self, hostname=None,username=None,password=None,port=None):
try:
hostname= hostname if hostname else self._iphost
username = username if username else self._username
passwd = password if password else self._password
port = port if port else self._port
cmdstr = "extended-status"
mysql_path = "mysqladmin"
if os.path.isfile("/data/mysql/product/bin/mysqladmin"):
mysql_path = "/data/mysql/product/bin/mysqladmin"
sql_cmstr = '%s -h%s -P%s -u%s -p%s %s' % (mysql_path,hostname,port,username,passwd, cmdstr)
(ret, result) = commands.getstatusoutput(sql_cmstr)
logstr = "sql_cmdstr:%s\nret:%s\nresult:%s\n" % (sql_cmstr,ret,result)
if ret == 0:
self._logger.info(logstr)
return result
else:
self._logger.error(logstr)
result = None
return result
except Exception as expt:
import traceback
tb = traceback.format_exc()
self._logger.error(tb)
def _get_item_from_sql_output(self, item):
try:
result = self._sql_result
if not result:
return '0'
output_list = re.split("[\n]+", str(result).strip())
item = str(item).lower().strip()
for line in output_list:
line = str(line).strip().replace(" ", "").lower().strip("|")
line_ary = re.split("\|", line)
if item == line_ary[0]:
return line_ary[1]
return '0'
except Exception as expt:
import traceback
tb = traceback.format_exc()
self._logger.error(tb)
def get_item_val(self,item):
return self._get_item_from_sql_output(item)
def get_item_tval(self, item, val_type="int"):
val = self.get_item_val(item)
if val_type == "int":
return int(val)
if val_type == "float":
fval = "%.2f" % (val)
return float(fval)
if val_type == "str":
return str(val)
return int(val)
def get_keybuf_read_hit_rate(self):
"""
key Buffer 命中率 -- 读
"""
key_reads = self.get_item_tval("key_reads")
key_read_requests = self.get_item_tval("key_read_requests")
if key_read_requests == 0:
return 100.0
hitrate = "%.2f" % ((1 - key_reads/float(key_read_requests))*100)
return float(hitrate)
def get_keybuf_write_hit_rate(self):
"""
key Buffer 命中率 -- 写
"""
key_writes = self.get_item_tval("key_writes")
key_write_requests = self.get_item_tval("key_write_requests")
if key_write_requests == 0:
return 100.0
hitrate = "%.2f" % ((1 - key_writes/float(key_write_requests))*100)
return float(hitrate)
def get_innodbbuf_read_hit_rate(self):
"""
Innodb Buffer 命中率 -- 写
"""
innodb_buffer_pool_reads = self.get_item_tval("innodb_buffer_pool_reads")
innodb_buffer_pool_read_requests = self.get_item_tval("innodb_buffer_pool_read_requests")
if innodb_buffer_pool_read_requests == 0:
return 100.0
hitrate = "%.2f" % ((1 - innodb_buffer_pool_reads/float(innodb_buffer_pool_read_requests))*100)
return float(hitrate)
def get_query_hit_rate(self):
"""
Query Cache命中率
"""
Qcache_hits = self.get_item_tval("Qcache_hits")
Qcache_inserts = self.get_item_tval( "Qcache_inserts")
total = int(Qcache_hits) + int(Qcache_inserts)
if total == 0:
return 100.0
hitrate = "%.2f" % (Qcache_hits/float(total)*100)
return float(hitrate)
def get_thread_cache_hit_rate(self):
"""
Thread Cache命中率
"""
Threads_created = self.get_item_tval("Threads_created")
Connections = self.get_item_tval( "Connections")
if Connections == 0:
return 100.0
hitrate = "%.2f" % ((1-Threads_created/float(Connections))*100)
return float(hitrate)
def get_lock_scale(self):
"""
Table_locks_waited/Table_locks_immediate=0.3% 如果这个比值比较大的话，说明表锁造成的阻塞比较严重
"""
Table_locks_waited = self.get_item_tval("Table_locks_waited")
Table_locks_immediate = self.get_item_tval( "Table_locks_immediate")
if Table_locks_immediate == 0:
return 100.0
hitrate = Table_locks_waited/float(Table_locks_immediate)*100
hitrate = "%.2f" % (hitrate)
return float(hitrate)
def get_table_scale(self):
"""
Created_tmp_disk_tables/Created_tmp_tables比值最好不要超过10%，如果Created_tmp_tables值比较大， 可能是排序句子过多或者是连接句子不够优化
"""
Created_tmp_disk_tables = self.get_item_tval("Created_tmp_disk_tables")
Created_tmp_tables = self.get_item_tval( "Created_tmp_tables")
if Created_tmp_tables == 0:
return 100.0
hitrate = Created_tmp_disk_tables/float(Created_tmp_tables)*100
hitrate = "%.2f" % (hitrate)
return float(hitrate)
class Mysql(object):
logpath = "/tmp/zabbix_mysql_status.log"
def __init__(self, iphost, username="******", password="******", port=3306, debug=True):
self._iphost = iphost
self._username = username
self._password = password
self._port = port
self._logger = Log(self.logpath,is_console=debug)
def get_logger(self):
return self._logger
def get_mysql_cmd_output(self, hostname=None,username=None,password=None,port=None):
try:
hostname= hostname if hostname else self._iphost
username = username if username else self._username
passwd = password if password else self._password
port = port if port else self._port
cmdstr = "extended-status"
mysql_path = "mysqladmin"
if os.path.isfile("/data/mysql/product/bin/mysqladmin"):
mysql_path = "/data/mysql/product/bin/mysqladmin"
sql_cmstr = '%s -h%s -P%s -u%s -p%s %s' % (mysql_path,hostname,port,username,passwd, cmdstr)
(ret, result) = commands.getstatusoutput(sql_cmstr)
logstr = "sql_cmdstr:%s\nret:%s\nresult:%s\n" % (sql_cmstr,ret,result)
if ret == 0:
self._logger.info(logstr)
return result
else:
self._logger.error(logstr)
result = None
return result
except Exception as expt:
import traceback
tb = traceback.format_exc()
self._logger.error(tb)
def get_item_from_sql_output(self,result, item):
try:
if not result:
return '0'
output_list = re.split("[\n]+", str(result).strip())
item = str(item).lower().strip()
for line in output_list:
line = str(line).strip().replace(" ", "").lower().strip("|")
line_ary = re.split("\|", line)
if item == line_ary[0]:
return line_ary[1]
return '0'
except Exception as expt:
import traceback
tb = traceback.format_exc()
self._logger.error(tb)
def get_item_val(self, item, hostname=None, port=None):
result = self.get_mysql_cmd_output(hostname=hostname,port=port)
return self.get_item_from_sql_output(result,item)
class Mysql(object):
logpath = "/tmp/zabbix_mysql_func.log"
def __init__(self,
iphost,
username="******",
password="******",
port=3306,
debug=True):
self._iphost = iphost
self._username = username
self._password = password
self._port = port
self._logger = Log(self.logpath, is_console=debug, mbs=10, count=5)
def get_logger(self):
return self._logger
def get_mysql_cmd_output(self,
cmdstr,
hostname=None,
username=None,
password=None,
port=None):
try:
hostname = hostname if hostname else self._iphost
username = username if username else self._username
passwd = password if password else self._password
port = port if port else self._port
mysql_bin_path = "mysql"
if os.path.isfile("/data/mysql/product/bin/mysql"):
mysql_bin_path = "/data/mysql/product/bin/mysql"
sql_cmstr = '%s -h%s -P%s -u%s -p%s -e "%s"' % (
mysql_bin_path, hostname, port, username, passwd, cmdstr)
(stdo, stde, retcode) = QCmd.docmd(sql_cmstr, timeout=1, raw=True)
#(ret, result) = commands.getstatusoutput(sql_cmstr)
logstr = "sql_cmdstr:%s\nret:%s\nstdo:%s\nstde:%s" % (
sql_cmstr, retcode, stdo, stde)
if retcode == 0:
self._logger.info(logstr)
return stdo
else:
self._logger.error(logstr)
result = None
return result
except Exception as expt:
import traceback
tb = traceback.format_exc()
self._logger.error(tb)
def get_item_from_sql_output(self, result, item):
try:
if not result:
return '0'
output_list = re.split("[\n]+", str(result).strip())
item = str(item).lower().strip()
for line in output_list:
line = str(line).strip().replace(" ", "").lower().strip("|")
line_ary = re.split("\|", line)
if item == line_ary[0]:
return line_ary[1]
return '0'
except Exception as expt:
import traceback
tb = traceback.format_exc()
self._logger.error(tb)
def is_mysql_can_write(self, hostname=None, port=None):
cmdstr = "insert into test.t_zabbix(insert_timestamp)values(current_timestamp());"
result = self.get_mysql_cmd_output(cmdstr,
hostname=hostname,
port=port)
if result == None: ## 超时写入，判定为不可写入
return 0
return 1
class Mysql(object):
logpath = "/tmp/zabbix_mysql_status.log"
def __init__(self,
iphost,
username="******",
password="******",
port=3306,
debug=True):
self._iphost = iphost
self._username = username
self._password = password
self._port = port
self._logger = Log(self.logpath, is_console=debug)
def get_logger(self):
return self._logger
def get_mysql_cmd_output(self,
hostname=None,
username=None,
password=None,
port=None):
try:
hostname = hostname if hostname else self._iphost
username = username if username else self._username
passwd = password if password else self._password
port = port if port else self._port
cmdstr = "extended-status"
mysql_path = "mysqladmin"
if os.path.isfile("/data/mysql/product/bin/mysqladmin"):
mysql_path = "/data/mysql/product/bin/mysqladmin"
sql_cmstr = '%s -h%s -P%s -u%s -p%s %s' % (
mysql_path, hostname, port, username, passwd, cmdstr)
(ret, result) = commands.getstatusoutput(sql_cmstr)
logstr = "sql_cmdstr:%s\nret:%s\nresult:%s\n" % (sql_cmstr, ret,
result)
if ret == 0:
self._logger.info(logstr)
return result
else:
self._logger.error(logstr)
result = None
return result
except Exception as expt:
import traceback
tb = traceback.format_exc()
self._logger.error(tb)
def get_item_from_sql_output(self, result, item):
try:
if not result:
return '0'
output_list = re.split("[\n]+", str(result).strip())
item = str(item).lower().strip()
for line in output_list:
line = str(line).strip().replace(" ", "").lower().strip("|")
line_ary = re.split("\|", line)
if item == line_ary[0]:
return line_ary[1]
return '0'
except Exception as expt:
import traceback
tb = traceback.format_exc()
self._logger.error(tb)
def get_item_val(self, item, hostname=None, port=None):
result = self.get_mysql_cmd_output(hostname=hostname, port=port)
return self.get_item_from_sql_output(result, item)
class Mysql(object):
logpath = "/tmp/zabbix_mysql_perf.log"
def __init__(self,
iphost,
username="******",
password="******",
port=3306,
debug=True):
self._iphost = iphost
self._username = username
self._password = password
self._port = port
self._logger = Log(self.logpath, is_console=debug, mbs=5, count=5)
self._sql_result = self._get_mysql_cmd_output()
def get_logger(self):
return self._logger
def _get_mysql_cmd_output(self,
hostname=None,
username=None,
password=None,
port=None):
try:
hostname = hostname if hostname else self._iphost
username = username if username else self._username
passwd = password if password else self._password
port = port if port else self._port
cmdstr = "extended-status"
mysql_path = "mysqladmin"
if os.path.isfile("/data/mysql/product/bin/mysqladmin"):
mysql_path = "/data/mysql/product/bin/mysqladmin"
sql_cmstr = '%s -h%s -P%s -u%s -p%s %s' % (
mysql_path, hostname, port, username, passwd, cmdstr)
(ret, result) = commands.getstatusoutput(sql_cmstr)
logstr = "sql_cmdstr:%s\nret:%s\nresult:%s\n" % (sql_cmstr, ret,
result)
if ret == 0:
self._logger.info(logstr)
return result
else:
self._logger.error(logstr)
result = None
return result
except Exception as expt:
import traceback
tb = traceback.format_exc()
self._logger.error(tb)
def _get_item_from_sql_output(self, item):
try:
result = self._sql_result
if not result:
return '0'
output_list = re.split("[\n]+", str(result).strip())
item = str(item).lower().strip()
for line in output_list:
line = str(line).strip().replace(" ", "").lower().strip("|")
line_ary = re.split("\|", line)
if item == line_ary[0]:
return line_ary[1]
return '0'
except Exception as expt:
import traceback
tb = traceback.format_exc()
self._logger.error(tb)
def get_item_val(self, item):
return self._get_item_from_sql_output(item)
def get_item_tval(self, item, val_type="int"):
val = self.get_item_val(item)
if val_type == "int":
return int(val)
if val_type == "float":
fval = "%.2f" % (val)
return float(fval)
if val_type == "str":
return str(val)
return int(val)
def get_keybuf_read_hit_rate(self):
"""
key Buffer 命中率 -- 读
"""
key_reads = self.get_item_tval("key_reads")
key_read_requests = self.get_item_tval("key_read_requests")
if key_read_requests == 0:
return 100.0
hitrate = "%.2f" % ((1 - key_reads / float(key_read_requests)) * 100)
return float(hitrate)
def get_keybuf_write_hit_rate(self):
"""
key Buffer 命中率 -- 写
"""
key_writes = self.get_item_tval("key_writes")
key_write_requests = self.get_item_tval("key_write_requests")
if key_write_requests == 0:
return 100.0
hitrate = "%.2f" % ((1 - key_writes / float(key_write_requests)) * 100)
return float(hitrate)
def get_innodbbuf_read_hit_rate(self):
"""
Innodb Buffer 命中率 -- 写
"""
innodb_buffer_pool_reads = self.get_item_tval(
"innodb_buffer_pool_reads")
innodb_buffer_pool_read_requests = self.get_item_tval(
"innodb_buffer_pool_read_requests")
if innodb_buffer_pool_read_requests == 0:
return 100.0
hitrate = "%.2f" % ((1 - innodb_buffer_pool_reads /
float(innodb_buffer_pool_read_requests)) * 100)
return float(hitrate)
def get_query_hit_rate(self):
"""
Query Cache命中率
"""
Qcache_hits = self.get_item_tval("Qcache_hits")
Qcache_inserts = self.get_item_tval("Qcache_inserts")
total = int(Qcache_hits) + int(Qcache_inserts)
if total == 0:
return 100.0
hitrate = "%.2f" % (Qcache_hits / float(total) * 100)
return float(hitrate)
def get_thread_cache_hit_rate(self):
"""
Thread Cache命中率
"""
Threads_created = self.get_item_tval("Threads_created")
Connections = self.get_item_tval("Connections")
if Connections == 0:
return 100.0
hitrate = "%.2f" % ((1 - Threads_created / float(Connections)) * 100)
return float(hitrate)
def get_lock_scale(self):
"""
Table_locks_waited/Table_locks_immediate=0.3% 如果这个比值比较大的话，说明表锁造成的阻塞比较严重
"""
Table_locks_waited = self.get_item_tval("Table_locks_waited")
Table_locks_immediate = self.get_item_tval("Table_locks_immediate")
if Table_locks_immediate == 0:
return 100.0
hitrate = Table_locks_waited / float(Table_locks_immediate) * 100
hitrate = "%.2f" % (hitrate)
return float(hitrate)
def get_table_scale(self):
"""
Created_tmp_disk_tables/Created_tmp_tables比值最好不要超过10%，如果Created_tmp_tables值比较大， 可能是排序句子过多或者是连接句子不够优化
"""
Created_tmp_disk_tables = self.get_item_tval("Created_tmp_disk_tables")
Created_tmp_tables = self.get_item_tval("Created_tmp_tables")
if Created_tmp_tables == 0:
return 100.0
hitrate = Created_tmp_disk_tables / float(Created_tmp_tables) * 100
hitrate = "%.2f" % (hitrate)
return float(hitrate)
class Mysql(object):
logpath = "/tmp/zabbix_mysql_func.log"
def __init__(self, iphost, username="******", password="******", port=3306, debug=True):
self._iphost = iphost
self._username = username
self._password = password
self._port = port
self._logger = Log(self.logpath,is_console=debug, mbs=10, count=5)
def get_logger(self):
return self._logger
def get_mysql_cmd_output(self, cmdstr, hostname=None,username=None,password=None,port=None):
try:
hostname= hostname if hostname else self._iphost
username = username if username else self._username
passwd = password if password else self._password
port = port if port else self._port
mysql_bin_path = "mysql"
if os.path.isfile("/data/mysql/product/bin/mysql"):
mysql_bin_path = "/data/mysql/product/bin/mysql"
sql_cmstr = '%s -h%s -P%s -u%s -p%s -e "%s"' % (mysql_bin_path,hostname,port,username, passwd, cmdstr)
(stdo,stde,retcode) = QCmd.docmd(sql_cmstr, timeout=1, raw=True)
#(ret, result) = commands.getstatusoutput(sql_cmstr)
logstr = "sql_cmdstr:%s\nret:%s\nstdo:%s\nstde:%s" % (sql_cmstr, retcode, stdo, stde)
if retcode == 0:
self._logger.info(logstr)
return stdo
else:
self._logger.error(logstr)
result = None
return result
except Exception as expt:
import traceback
tb = traceback.format_exc()
self._logger.error(tb)
def get_item_from_sql_output(self,result, item):
try:
if not result:
return '0'
output_list = re.split("[\n]+", str(result).strip())
item = str(item).lower().strip()
for line in output_list:
line = str(line).strip().replace(" ", "").lower().strip("|")
line_ary = re.split("\|", line)
if item == line_ary[0]:
return line_ary[1]
return '0'
except Exception as expt:
import traceback
tb = traceback.format_exc()
self._logger.error(tb)
def is_mysql_can_write(self, hostname=None, port=None):
cmdstr = "insert into test.t_zabbix(insert_timestamp)values(current_timestamp());"
result = self.get_mysql_cmd_output(cmdstr,hostname=hostname,port=port)
if result == None: ## 超时写入，判定为不可写入
return 0
return 1