篇一：python统计MYSQL常用的一些性能参数指标
#!/usr/bin/env python
# -*- encoding: utf8 -*-
# Author: iamacnhero@gmail.cn
# Created: 2009-12-14
from __future__ import division
import MySQLdb, random, datetime, time, os, sys
import ConfigParser
import string, os, sys
# read mysql config file
config_file = "/root/.my.cnf"#目前应用/etc/my.cnf文件
if (os.path.exists(config_file)):
cf = ConfigParser.ConfigParser()
cf.read(config_file)
host = cf.get("client", "host")
user = cf.get("client", "user")
password = cf.get("client", "password")
db = ""
else:
host = '192.168.200.225'
user = 'root'
password = 'p0o9i8u7'
db = ''
# today = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
#----------------------------------------------------------------------
def getConn(host, user, passwd, db='jobmd_new', port=3306, charset=''):
try:
conn = MySQLdb.connect(host=host, user=user, passwd=passwd, db=db, port=port, charset=charset)
return conn
except MySQLdb.Error, e:
print "Error %d: %s" % (e.args[0], e.args[1])
sys.exit(1)
#----------------------------------------------------------------------
def closeConn(conn):
"""close mysql connection"""
conn.close()
#----------------------------------------------------------------------
def getValue(conn, query):
""" get value of query """
cursor = conn.cursor()
cursor.execute(query)
result = cursor.fetchone()
return int(result[1])
def getQuery(conn, query):
""" get more queries """
cursor = conn.cursor()
cursor.execute(query)
result = cursor.fetchall()
return result
Questions = "show global status like 'Questions'"
Uptime = "show global status like 'Uptime'"
Com_commit = "show global status like 'Com_commit'"
Com_rollback = "show global status like 'Com_rollback'"
Key_reads = "show global status like 'Key_reads'"
Key_read_requests = "show global status like 'Key_read_requests'"
Key_writes = "show global status like 'Key_writes'"
Key_write_requests = "show global status like 'Key_write_requests'"
Have_innodb = "show global variables like 'have_innodb'"
Innodb_buffer_pool_reads = "show global status like 'Innodb_buffer_pool_reads'"
Innodb_buffer_pool_read_requests = "show global status 'Innodb_buffer_pool_read_requests'"
Qcache_hits = "show global status like 'Qcache_hits'"
Qcache_inserts = "show global status like 'Qcache_inserts'"
Open_tables = "show global status like 'Open_tables'"
Opened_tables = "show global status like 'Opened_tables'"
Threads_created = "show global status like 'Threads_created'"
Connections = "show global status like 'Connections'"
Com_select = "show global status like 'Com_select'"
Com_insert = "show global status like 'Com_insert'"
Com_update = "show global status like 'Com_update'"
Com_delete = "show global status like 'Com_delete'"
Com_replace = "show global status like 'Com_replace'"
Table_locks_waited = "show global status like 'Table_locks_waited'"
Table_locks_immediate = "show global status like 'Table_locks_immediate'"
Created_tmp_tables = "show global status like 'Created_tmp_tables'"
Created_tmp_disk_tables = "show global status like 'Created_tmp_disk_tables'"
Slow_queries = "show global status like 'Slow_queries'"
Select_full_join = "show global status like 'Select_full_join'"
if __name__ == "__main__": like
conn = getConn(host, user, password, db)
Questions = getValue(conn, Questions)
Uptime = getValue(conn, Uptime)
Com_commit = getValue(conn, Com_commit)
Com_rollback = getValue(conn, Com_rollback)
Key_reads = getValue(conn, Key_reads)
Key_read_requests = getValue(conn, Key_read_requests)
Key_writes = getValue(conn, Key_writes)
Key_write_requests = getValue(conn, Key_write_requests)
Qcache_hits = getValue(conn, Qcache_hits)
Qcache_inserts = getValue(conn, Qcache_inserts)
Open_tables = getValue(conn, Open_tables)
Opened_tables = getValue(conn, Opened_tables)
Threads_created = getValue(conn, Threads_created)
Connections = getValue(conn, Connections)
Com_select = getValue(conn, Com_select)
Com_insert = getValue(conn, Com_insert)
Com_update = getValue(conn, Com_update)
Com_delete = getValue(conn, Com_delete)
Com_replace = getValue(conn, Com_replace)
Table_locks_immediate = getValue(conn, Table_locks_immediate)
Table_locks_waited = getValue(conn, Table_locks_waited)
Created_tmp_tables = getValue(conn, Created_tmp_tables)
Created_tmp_disk_tables = getValue(conn, Created_tmp_disk_tables)
Slow_queries = getValue(conn, Slow_queries)
Select_full_join = getValue(conn, Select_full_join)
print "_____Gerneral Information___________________"
# QPS = Questions / Seconds
QPS = str(round(Questions / Uptime, 5))
print "QPS (Query per seconds): " + QPS
# TPS = (Com_commit + Com_rollback ) / Seconds
TPS = str(round((Com_commit + Com_rollback)/Uptime, 5))
print "TPS(Transactin per seconds): " + TPS
# Read/Writes Ratio
rwr = str(round((Com_select + Qcache_hits) / (Com_insert + Com_update + Com_delete + Com_replace) * 100, 5)) + "%"
print "Read/Writes Ratio: " + rwr + "\n"
print "_____Cache Usage___________________"
# Key_buffer_read_hits = (1 - Key_reads / Key_read_requests) * 100%
# Key_buffer_write_hits = (1 - Key_writes / Key_write_requests) * 100%
Key_buffer_read_hits = str(round((1 - Key_reads/Key_read_requests) * 100, 5)) + "%"Key_buffer_write_hits = str(round((1 - Key_writes/Key_write_requests) * 100, 5)) + "%"print "MyISAM Key buffer read ratio(99.3% - 99.9% target): " + str(Key_buffer_read_hits)print "MyISAM Key buffer write ratio: " + str(Key_buffer_write_hits) + "\n"
# Query_cache_hits = (Qcache_hits / (Qcache_hits + Qcache_inserts)) * 100%
Query_cache_hits = str(round(((Qcache_hits/(Qcache_hits + Qcache_inserts)) * 100), 5)) + "%"print "Query cache hits ratio: " + Query_cache_hits + "\n"
cursor = conn.cursor()
cursor.execute(Have_innodb)
result = cursor.fetchone()
Have_innodb = result[1]
if (Have_innodb == "YES"):
Innodb_buffer_pool_reads = getValue(conn, Innodb_buffer_pool_reads)
Innodb_buffer_pool_read_requests = getValue(conn, Innodb_buffer_pool_read_requests) # Innodb_buffer_read_hits = (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100%
Innodb_buffer_read_hits = str(round((1 - Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100, 5)) + "%"
print "Innodb buffer read ratio(target 96% - 99%): " + Innodb_buffer_read_hits + "\n"
# Thread_cache_hits = (1 - Threads_created / Connections) * 100%
Thread_cache_hits = str(round(((1 - Threads_created / Connections)) * 100, 5)) + "%"print "Thread_cache_hits(Should above 90%): " + Thread_cache_hits + "\n"
print "_____Slow Queries(Evil Queries)________________"
Slow_queries_per_second = str(round(Slow_queries / (Uptime/60), 5))
print "Slow queries per minute: " + Slow_queries_per_second
Select_full_join_per_second = str(round(Select_full_join / (Uptime/60), 5))
print "Slow full join queries per minute: " + Select_full_join_per_second
full_select_in_all_select = str(round((Select_full_join / Com_select) * 100, 5)) + "%"print "Full join select queries in all select queries: " + full_select_in_all_select
# MyISAM Lock Contention: (Table_locks_waited / Table_locks_immediate) * 100%
lock_contention = str(round((Table_locks_waited / Table_locks_immediate) * 100, 5)) + "%"print "MyISAM Lock Contention(<1% good, 1% warning, >3% you are currently dying): " + lock_contention + "\n"
print "Open tables: " + str(Open_tables)
print "Opened tables: " + str(Opened_tables) + "\n"
Temp_tables_to_disk = str(round((Created_tmp_disk_tables / Created_tmp_tables) * 100, 5))
+ "%"
print "Temp tables to Disk ratio: " + Temp_tables_to_disk
closeConn(conn)
#说明
QPS (Query per second) （每秒查询量）
TPS(Transaction per second) （每秒事务量，如果是InnoDB会显示，没有InnoDB就不会显示） Read/Writes Ratio（数据库读写比率，对是否使用MySQL Replication还是使用MySQL Cluster很有参考价值。）
MyISAM Key buffer read ratio
MyISAM Key buffer write ratio
Slow queries per minute （平均一分钟多少慢查询）
Slow full join queries per minute（慢查询的比率）
Temp tables to Disk ratio （写到硬盘的临时表与所有临时表的比率，对性能有较大影响，说明有SQL使用了大量临时表）
篇二：zabbix监控mysql性能
编写check_mysql.sh脚本
用于获取mysql性能指标数据，你需要修改相应的数据库信息
1 # vim /usr/local/zabbix-2.4.4/scripts/chk_mysql.sh
脚本如下：
1 #!/bin/bash
2 # -------------------------------------------------------------------------------
3 # FileName: check_mysql.sh
4 # Revision: 1.0
5 # Date: 2015/06/09
6 # Author:DengYun
7 # Email: dengyun@ttlsa.com
8 # Website:
9 # Description:
1# Notes: ~
0 # -------------------------------------------------------------------------------
1# Copyright:2015 (c) DengYun
1 # License: GPL
1
2 # 用户名
1MYSQL_USER='zabbix'
3
1# 密码
4 MYSQL_PWD='123456'
1
5 # 主机地址/IP
1MYSQL_HOST='127.0.0.1'
6
1# 端口
7 MYSQL_PORT='3306'
1
8 # 数据连接
1MYSQL_CONN="/usr/bin/mysqladmin -u${MYSQL_USER} -p${MYSQL_PWD} -h${MYSQL_HOST}
9 -P${MYSQL_PORT}"
2
0 # 参数是否正确
2if [ $# -ne "1" ];then
1 echo "arg error!"
2fi
2
2# 获取数据
3 case $1 in
2 Uptime)
4result=`${MYSQL_CONN} status|cut -f2 -d":"|cut -f1 -d"T"`
2 echo $result
5;;
2 Com_update)
6result=`${MYSQL_CONN} extended-status |grep -w "Com_update"|cut -d"|" -f3`
2 echo $result
7;;
2 Slow_queries)
8result=`${MYSQL_CONN} status |cut -f5 -d":"|cut -f1 -d"O"`
2 echo $result
9;;
3 Com_select)
0result=`${MYSQL_CONN} extended-status |grep -w "Com_select"|cut -d"|" -f3`
3 echo $result
1 ;;
3 Com_rollback)
2result=`${MYSQL_CONN} extended-status |grep -w "Com_rollback"|cut -d"|" -f3`
3 echo $result
3 ;;
3 Questions)
4result=`${MYSQL_CONN} status|cut -f4 -d":"|cut -f1 -d"S"`
3 echo $result
5 ;;
3 Com_insert)
6result=`${MYSQL_CONN} extended-status |grep -w "Com_insert"|cut -d"|" -f3`
3 echo $result
7 ;;
3 Com_delete)
8result=`${MYSQL_CONN} extended-status |grep -w "Com_delete"|cut -d"|" -f3`
3 echo $result
9 ;;
4 Com_commit)
0result=`${MYSQL_CONN} extended-status |grep -w "Com_commit"|cut -d"|" -f3`
4 echo $result
1 ;;
4 Bytes_sent)
2result=`${MYSQL_CONN} extended-status |grep -w "Bytes_sent" |cut -d"|" -f3`
4 echo $result
3 ;;
4 Bytes_received)
4result=`${MYSQL_CONN} extended-status |grep -w "Bytes_received" |cut -d"|" -f3`
4 echo $result
5 ;;
4 Com_begin)
6result=`${MYSQL_CONN} extended-status |grep -w "Com_begin"|cut -d"|" -f3`
4 echo $result
7 ;;
4
8*)
4 echo
9 "Usage:$0(Uptime|Com_update|Slow_queries|Com_select|Com_rollback|Questions|Com_insert|Com_delete|Com_co5mmit|Bytes_sent|Bytes_received|Com_begin)"
0;;
5esac
1
5
2
5
3
5
4
5
5
5
6
5
7
5
8
5
9
6
6
1
6
2
6
6
4
6
5
6
6
6
7
6
8
6
9
7
7
1
7
2
7
3
7
4
7
5
7
6
7
7
7
8
7
9
8
8
1
8
2
8
3
8
4
8
8
6
8
7
8
8
8
9 修改zabbix_agentd.conf
增加自定义key，在最后一行增加如下：
1 # 获取mysql版本
2 UserParameter=mysql.version,mysql -V
3 # 获取mysql性能指标,这个是上面定义好的脚本
4 UserParameter=mysql.status[*],/usr/local/zabbix-2.4.4/scripts/chk_mysql.sh $1
5 # 获取mysql运行状态
6 UserParameter=mysql.ping,mysqladmin -uzabbix -p123456 -P3306 -h127.0.0.1 ping | grep -c alive
备注：请注意修改你的数据库信息，以及zabbix路径信息 重启
zabbix
1 # killall zabbix-agentd
2 # /usr/local/zabbix-2.4.4/bin/zabbix_agentd
3 或者
4 # service zabbix_agentd restart Link MySQL模板 模板是zabbix系统提供的，进入zabbix web后台，configuration-->hosts-->点击你的主机name-->选择template选项卡，选择模板“Template App MySQL”，最后点击update即可
篇三：mysql巡检报告
结论：正常，且负载压力比较小。
免费论文网友情提示：本网站所有内容为共享上传提供，不涉及任何商业利益，本站对此不承担任何保证责任！
Copyright © www.csmayi.cn Corporation, All Rights Reserved 共享时代 共享你我他 版权所有