#!/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 like '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__":
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使用了大量临时表）
|
