/mysql_info_wrappers.py
Python | 172 lines | 141 code | 23 blank | 8 comment | 32 complexity | 05a4e7e0f602d5b9697379ce2cbc5355 MD5 | raw file
- import MySQLdb
- mysql_conn = None
- def connect_to_mysql():
- global mysql_conn
-
- if mysql_conn is None:
- # Connect to MySQL (this could likely be much improved to, say, read
- # the .my.cnf, etc.
- try:
- mysql_conn = MySQLdb.connect(
- read_default_group = 'client')
- except MySQLdb.MySQLError, exc:
- print "[%d] %s" % exc.args
- print "Cannot Connect to MySQL"
- print "Is MySQL running? If your .my.cnf correct?"
- print "Do you need to configure the script with a username and password?"
- sys.exit()
- class AttributeAdapter(dict):
- def __getattr__(self, key):
- return dict.__getitem__(self, key)
- def __setattr__(self, key, value):
- dict.__setitem__(self, key, value)
- # Gather Information From MySQL
- def gather_mysql_info():
- connect_to_mysql()
- def convert(value):
- try:
- # convert anything that looks like a number to a number
- return float(value)
- except ValueError:
- pass
- try:
- # convert anything that looks like a boolean to a bool
- return bool(['OFF','ON'].index(value))
- except ValueError:
- pass
- try:
- # another bool format
- return bool(['NO','YES'].index(value))
- except ValueError:
- pass
-
- # otherwise, remain as normal text
- return value
-
- # Grab the STATUS and VARIABLES from MySQL and put them both into dictionaries
- try:
- cursor = mysql_conn.cursor()
- cursor.execute("SHOW GLOBAL STATUS")
- status = dict([(key.lower(), convert(value))
- for key, value in cursor.fetchall()])
- cursor.execute("SHOW GLOBAL VARIABLES")
- vars = dict([(key.lower(), convert(value))
- for key, value in cursor.fetchall()])
- cursor.execute("SHOW ENGINE INNODB STATUS")
- fields = [f[0] for f in cursor.description]
- innodb_engine_status = dict(zip(fields, cursor.fetchone()))['Status']
- cursor.close()
- except MySQLdb.MySQLError, exc:
- print '[%d] %s' % exc.args
- print 'Unable to gather information from MySQL!\nPerhaps the MySQL ' \
- 'user we are connecting with doese not have proper permissions?'
- sys.exit()
- return MySQLInfo(status, vars, innodb_engine_status)
- class MySQLInfo(object):
- def __init__(self, status, vars, ibstatus):
- self.status = AttributeAdapter(status)
- self.vars = AttributeAdapter(vars)
- self.ibstatus = ibstatus
- @property
- def key_buffer_used(self):
- status = self.status
- key_buffer_size = self.vars.key_buffer_size
- try:
- return (status.key_blocks_used /
- status.key_blocks_unused *
- 100 * key_buffer_size)
- except ZeroDivisionError:
- return 0
- @property
- def key_buffer_used_pct(self):
- status = self.status
- try:
- return (status.key_blocks_used / status.key_blocks_unused) * 100
- except ZeroDivisionError:
- return 0
- @property
- def key_buffer_hitrate(self):
- status = self.status
- try:
- return ((1 - status.key_reads) / status.key_read_requests)*100
- except ZeroDivisionError:
- return 0
- @property
- def buffer_pool_used(self):
- status = self.status
- return (status.innodb_buffer_pool_pages_total -
- status.innodb_buffer_pool_pages_free) * \
- status.innodb_page_size
-
- @property
- def buffer_pool_used_pct(self):
- status = self.status
- return (1 - status.innodb_buffer_pool_pages_free /
- status.innodb_buffer_pool_pages_total) * 100
- @property
- def slow_query_pct(self):
- status = self.status
- total_queries = sum([status[key] for key in ('com_select',
- 'com_insert',
- 'com_update',
- 'com_delete')])
- try:
- return (status.slow_queries / total_queries) * 100
- except ZeroDivisionError:
- return 0
- @property
- def read_pct(self):
- status = self.status
- total_queries = sum([status[key] for key in ('com_select',
- 'com_insert',
- 'com_update',
- 'com_delete')])
- try:
- return (status.com_select / total_queries) * 100
- except ZeroDivisionError:
- return 0
- @property
- def tmp_tables_disk_pct(self):
- status = self.status
- try:
- return (status.created_tmp_disk_tables /
- status.created_tmp_tables) * 100
- except ZeroDivisionError:
- return 0
- @property
- def table_lock_wait_pct(self):
- status = self.status
- try:
- return (status.table_locks_waited /
- status.table_locks_waited +
- status.table_locks_immediate) * 100
- except ZeroDivisionError:
- return 0
- @property
- def query_cache_hitrate(self):
- status = self.status
- try:
- return (status.qcache_hits /
- (status.qcache_hits +
- status.com_select)) * 100
- except ZeroDivisionError:
- return 0