def update_stats(get_innodb=True, get_master=True, get_slave=True): logging.warning('updating stats') global last_update global mysql_stats if time.time() - last_update < 15: return True else: last_update = time.time() logging.warning('refreshing stats') mysql_stats = {} # Get info from DB try: conn = MySQLdb.connect(**mysql_conn_opts) cursor = conn.cursor(MySQLdb.cursors.DictCursor) cursor.execute("SELECT GET_LOCK('gmetric-mysql', 0) as ok") lock_stat = cursor.fetchone() cursor.close() if lock_stat['ok'] == 0: return False cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute("SHOW VARIABLES") #variables = dict(((k.lower(), v) for (k,v) in cursor)) variables = {} for (k,v) in cursor: variables[k.lower()] = v cursor.close() cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute("SHOW /*!50002 GLOBAL */ STATUS") #global_status = dict(((k.lower(), v) for (k,v) in cursor)) global_status = {} for (k,v) in cursor: global_status[k.lower()] = v cursor.close() # try not to fail ? get_innodb = get_innodb and variables['have_innodb'].lower() == 'yes' get_master = get_master and variables['log_bin'].lower() == 'on' if get_innodb: cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute("SHOW /*!50000 ENGINE*/ INNODB STATUS") innodb_status = parse_innodb_status(cursor.fetchone()[0].split('\n')) cursor.close() if get_master: cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute("SHOW MASTER LOGS") master_logs = cursor.fetchall() cursor.close() if get_slave: cursor = conn.cursor(MySQLdb.cursors.DictCursor) cursor.execute("SHOW SLAVE STATUS") slave_status = {} res = cursor.fetchone() if res: for (k,v) in res.items(): slave_status[k.lower()] = v else: get_slave = False cursor.close() cursor = conn.cursor(MySQLdb.cursors.DictCursor) cursor.execute("SELECT RELEASE_LOCK('gmetric-mysql') as ok") cursor.close() conn.close() except MySQLdb.OperationalError, (errno, errmsg): logging.warning('error refreshing stats') return False
def update_stats(get_innodb=True, get_master=True, get_slave=True): """ """ logging.debug('updating stats') global last_update global mysql_stats, mysql_stats_last cur_time = time.time() time_delta = cur_time - last_update if time_delta <= 0: #we went backward in time. logging.debug(" system clock set backwards, probably ntp") if cur_time - last_update < MAX_UPDATE_TIME: logging.debug(' wait ' + str(int(MAX_UPDATE_TIME - (cur_time - last_update))) + ' seconds') return True else: last_update = cur_time logging.debug('refreshing stats') mysql_stats = {} # Get info from DB try: conn = MySQLdb.connect(**mysql_conn_opts) cursor = conn.cursor(MySQLdb.cursors.DictCursor) cursor.execute("SELECT GET_LOCK('gmetric-mysql', 0) as ok") lock_stat = cursor.fetchone() cursor.close() if lock_stat['ok'] == 0: return False cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute("SHOW VARIABLES") #variables = dict(((k.lower(), v) for (k,v) in cursor)) variables = {} for (k,v) in cursor: variables[k.lower()] = v cursor.close() cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute("SHOW /*!50002 GLOBAL */ STATUS") #global_status = dict(((k.lower(), v) for (k,v) in cursor)) global_status = {} for (k,v) in cursor: global_status[k.lower()] = v cursor.close() cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute("SELECT PLUGIN_STATUS, PLUGIN_VERSION FROM `information_schema`.Plugins WHERE PLUGIN_NAME LIKE '%innodb%' AND PLUGIN_TYPE LIKE 'STORAGE ENGINE';") have_innodb = False innodb_version = 1.0 row = cursor.fetchone() if row[0] == "ACTIVE": have_innodb = True innodb_version = row[1] cursor.close() # try not to fail ? get_innodb = get_innodb and have_innodb get_master = get_master and variables['log_bin'].lower() == 'on' innodb_status = defaultdict(int) if get_innodb: cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute("SHOW /*!50000 ENGINE*/ INNODB STATUS") innodb_status = parse_innodb_status(cursor.fetchone()[2].split('\n'), innodb_version) cursor.close() logging.debug('innodb_status: ' + str(innodb_status)) master_logs = tuple if get_master: cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute("SHOW MASTER LOGS") master_logs = cursor.fetchall() cursor.close() slave_status = {} if get_slave: cursor = conn.cursor(MySQLdb.cursors.DictCursor) cursor.execute("SHOW SLAVE STATUS") res = cursor.fetchone() if res: for (k,v) in res.items(): slave_status[k.lower()] = v else: get_slave = False cursor.close() cursor = conn.cursor(MySQLdb.cursors.DictCursor) cursor.execute("SELECT RELEASE_LOCK('gmetric-mysql') as ok") cursor.close() conn.close() except MySQLdb.OperationalError, (errno, errmsg): logging.error('error updating stats') logging.error(errmsg) return False
def update_stats(get_innodb=True, get_master=True, get_slave=True, innodb_version='51fb'): logging.debug('updating stats') global last_update global mysql_stats, mysql_stats_last cur_time = time.time() if cur_time - last_update < MAX_UPDATE_TIME: logging.debug(' wait ' + str(int(MAX_UPDATE_TIME - (cur_time - last_update))) + ' seconds') return True else: last_update = cur_time logging.debug('refreshing stats') mysql_stats = {} # Get info from DB try: conn = MySQLdb.connect(**mysql_conn_opts) cursor = conn.cursor(MySQLdb.cursors.DictCursor) cursor.execute("SELECT GET_LOCK('gmetric-mysql', 0) as ok") lock_stat = cursor.fetchone() cursor.close() if lock_stat['ok'] == 0: return False cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute("SHOW VARIABLES") #variables = dict(((k.lower(), v) for (k,v) in cursor)) variables = {} for (k,v) in cursor: variables[k.lower()] = v cursor.close() cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute("SHOW /*!50002 GLOBAL */ STATUS") #global_status = dict(((k.lower(), v) for (k,v) in cursor)) global_status = {} for (k,v) in cursor: global_status[k.lower()] = v cursor.close() # try not to fail ? get_innodb = get_innodb and variables['have_innodb'].lower() == 'yes' get_master = get_master and variables['log_bin'].lower() == 'on' if get_innodb: cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute("SHOW ENGINE INNODB STATUS") innodb_status = parse_innodb_status(cursor.fetchone()[2].split('\n'), innodb_version) cursor.close() logging.debug('innodb_status: ' + str(innodb_status)) # if the ganglia mysql users has been granted select access to an innodb table # in the global space, this will report data_free for the global space. cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute('SELECT DATA_FREE FROM information_schema.TABLES WHERE ENGINE="InnoDB" LIMIT 1') ibdata_free = "" res = cursor.fetchone() if res: ibdata_free = res[0] else: ibdata_free = False cursor.close() if get_master: cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute("SHOW MASTER LOGS") master_logs = cursor.fetchall() cursor.close() if get_slave: cursor = conn.cursor(MySQLdb.cursors.DictCursor) cursor.execute("SHOW SLAVE STATUS") slave_status = {} res = cursor.fetchone() if res: for (k,v) in res.items(): slave_status[k.lower()] = v else: get_slave = False cursor.close() cursor = conn.cursor(MySQLdb.cursors.DictCursor) cursor.execute("SELECT RELEASE_LOCK('gmetric-mysql') as ok") cursor.close() conn.close() except MySQLdb.OperationalError, (errno, errmsg): logging.error('error updating stats') return False
def update_stats(get_innodb=True, get_master=True, get_slave=True): logging.debug("updating stats") global last_update global mysql_stats, mysql_stats_last cur_time = time.time() if cur_time - last_update < MAX_UPDATE_TIME: logging.debug(" wait " + str(int(MAX_UPDATE_TIME - (cur_time - last_update))) + " seconds") return True else: last_update = cur_time logging.debug("refreshing stats") mysql_stats = {} # Get info from DB try: conn = MySQLdb.connect(**mysql_conn_opts) cursor = conn.cursor(MySQLdb.cursors.DictCursor) cursor.execute("SELECT GET_LOCK('gmetric-mysql', 0) as ok") lock_stat = cursor.fetchone() cursor.close() if lock_stat["ok"] == 0: return False cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute("SHOW VARIABLES") # variables = dict(((k.lower(), v) for (k,v) in cursor)) variables = {} for (k, v) in cursor: variables[k.lower()] = v cursor.close() cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute("SHOW /*!50002 GLOBAL */ STATUS") # global_status = dict(((k.lower(), v) for (k,v) in cursor)) global_status = {} for (k, v) in cursor: global_status[k.lower()] = v cursor.close() # try not to fail ? get_innodb = get_innodb and variables["have_innodb"].lower() == "yes" get_master = get_master and variables["log_bin"].lower() == "on" if get_innodb: cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute("SHOW /*!50000 ENGINE*/ INNODB STATUS") innodb_status = parse_innodb_status(cursor.fetchone()[0].split("\n")) cursor.close() logging.debug("innodb_status: " + str(innodb_status)) if get_master: cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute("SHOW MASTER LOGS") master_logs = cursor.fetchall() cursor.close() if get_slave: cursor = conn.cursor(MySQLdb.cursors.DictCursor) cursor.execute("SHOW SLAVE STATUS") slave_status = {} res = cursor.fetchone() if res: for (k, v) in res.items(): slave_status[k.lower()] = v else: get_slave = False cursor.close() cursor = conn.cursor(MySQLdb.cursors.DictCursor) cursor.execute("SELECT RELEASE_LOCK('gmetric-mysql') as ok") cursor.close() conn.close() except MySQLdb.OperationalError, (errno, errmsg): logging.error("error updating stats") logging.error(errmsg) return False
def update_stats(get_innodb=True, get_master=True, get_slave=True): logging.warning('updating stats') global last_update global mysql_stats if time.time() - last_update < 15: return True else: last_update = time.time() logging.warning('refreshing stats') mysql_stats = {} # Get info from DB try: conn = MySQLdb.connect(**mysql_conn_opts) cursor = conn.cursor(MySQLdb.cursors.DictCursor) cursor.execute("SELECT GET_LOCK('gmetric-mysql', 0) as ok") lock_stat = cursor.fetchone() cursor.close() if lock_stat['ok'] == 0: return False cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute("SHOW VARIABLES") #variables = dict(((k.lower(), v) for (k,v) in cursor)) variables = {} for (k,v) in cursor: variables[k.lower()] = v cursor.close() cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute("SHOW /*!50002 GLOBAL */ STATUS") #global_status = dict(((k.lower(), v) for (k,v) in cursor)) global_status = {} for (k,v) in cursor: global_status[k.lower()] = v cursor.close() # try not to fail ? get_innodb = get_innodb and variables['have_innodb'].lower() == 'yes' get_master = get_master and variables['log_bin'].lower() == 'on' if get_innodb: cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute("SHOW /*!50000 ENGINE*/ INNODB STATUS") innodb_status = parse_innodb_status(cursor.fetchone()[2].split('\n')) cursor.close() if get_master: cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute("SHOW MASTER LOGS") master_logs = cursor.fetchall() cursor.close() if get_slave: cursor = conn.cursor(MySQLdb.cursors.DictCursor) cursor.execute("SHOW SLAVE STATUS") slave_status = {} res = cursor.fetchone() if res: for (k,v) in res.items(): slave_status[k.lower()] = v else: get_slave = False cursor.close() cursor = conn.cursor(MySQLdb.cursors.DictCursor) cursor.execute("SELECT RELEASE_LOCK('gmetric-mysql') as ok") cursor.close() conn.close() except MySQLdb.OperationalError, (errno, errmsg): logging.warning('error refreshing stats') return False
def update_stats(get_innodb=True, get_master=True, get_slave=True): """ """ logging.debug('updating stats') global last_update global mysql_stats, mysql_stats_last cur_time = time.time() time_delta = cur_time - last_update if time_delta <= 0: #we went backward in time. logging.debug(" system clock set backwards, probably ntp") if cur_time - last_update < MAX_UPDATE_TIME: logging.debug(' wait ' + str(int(MAX_UPDATE_TIME - (cur_time - last_update))) + ' seconds') return True else: last_update = cur_time logging.debug('refreshing stats') mysql_stats = {} # Get info from DB try: conn = MySQLdb.connect(**mysql_conn_opts) cursor = conn.cursor(MySQLdb.cursors.DictCursor) cursor.execute("SELECT GET_LOCK('gmetric-mysql', 0) as ok") lock_stat = cursor.fetchone() cursor.close() if lock_stat['ok'] == 0: return False cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute("SHOW VARIABLES") #variables = dict(((k.lower(), v) for (k,v) in cursor)) variables = {} for (k, v) in cursor: variables[k.lower()] = v cursor.close() cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute("SHOW /*!50002 GLOBAL */ STATUS") #global_status = dict(((k.lower(), v) for (k,v) in cursor)) global_status = {} for (k, v) in cursor: global_status[k.lower()] = v cursor.close() cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute( "SELECT PLUGIN_STATUS, PLUGIN_VERSION FROM `information_schema`.Plugins WHERE PLUGIN_NAME LIKE '%innodb%' AND PLUGIN_TYPE LIKE 'STORAGE ENGINE';" ) have_innodb = False innodb_version = 1.0 row = cursor.fetchone() if row[0] == "ACTIVE": have_innodb = True innodb_version = row[1] cursor.close() # try not to fail ? get_innodb = get_innodb and have_innodb get_master = get_master and variables['log_bin'].lower() == 'on' innodb_status = defaultdict(int) if get_innodb: cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute("SHOW /*!50000 ENGINE*/ INNODB STATUS") innodb_status = parse_innodb_status( cursor.fetchone()[2].split('\n'), innodb_version) cursor.close() logging.debug('innodb_status: ' + str(innodb_status)) master_logs = tuple if get_master: cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute("SHOW MASTER LOGS") master_logs = cursor.fetchall() cursor.close() slave_status = {} if get_slave: cursor = conn.cursor(MySQLdb.cursors.DictCursor) cursor.execute("SHOW SLAVE STATUS") res = cursor.fetchone() if res: for (k, v) in res.items(): slave_status[k.lower()] = v else: get_slave = False cursor.close() cursor = conn.cursor(MySQLdb.cursors.DictCursor) cursor.execute("SELECT RELEASE_LOCK('gmetric-mysql') as ok") cursor.close() conn.close() except MySQLdb.OperationalError, (errno, errmsg): logging.error('error updating stats') logging.error(errmsg) return False
def update_stats(get_innodb=True, get_master=True, get_slave=True, innodb_version='51fb'): logging.debug('updating stats') global last_update global mysql_stats, mysql_stats_last cur_time = time.time() if cur_time - last_update < MAX_UPDATE_TIME: logging.debug(' wait ' + str(int(MAX_UPDATE_TIME - (cur_time - last_update))) + ' seconds') return True else: last_update = cur_time logging.debug('refreshing stats') mysql_stats = {} # Get info from DB try: conn = MySQLdb.connect(**mysql_conn_opts) cursor = conn.cursor(MySQLdb.cursors.DictCursor) cursor.execute("SELECT GET_LOCK('gmetric-mysql', 0) as ok") lock_stat = cursor.fetchone() cursor.close() if lock_stat['ok'] == 0: return False cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute("SHOW VARIABLES") # variables = dict(((k.lower(), v) for (k,v) in cursor)) variables = {} for (k, v) in cursor: variables[k.lower()] = v cursor.close() cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute("SHOW /*!50002 GLOBAL */ STATUS") # global_status = dict(((k.lower(), v) for (k,v) in cursor)) global_status = {} for (k, v) in cursor: global_status[k.lower()] = v cursor.close() # try not to fail ? get_innodb = get_innodb and variables['have_innodb'].lower() == 'yes' get_master = get_master and variables['log_bin'].lower() == 'on' if get_innodb: cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute("SHOW ENGINE INNODB STATUS") innodb_status = parse_innodb_status( cursor.fetchone()[2].split('\n'), innodb_version) cursor.close() logging.debug('innodb_status: ' + str(innodb_status)) # if the ganglia mysql users has been granted select access to an innodb table # in the global space, this will report data_free for the global space. # cursor = conn.cursor(MySQLdb.cursors.Cursor) # Disabled 2014-01-19 springle: too slow # cursor.execute('SELECT DATA_FREE ' # 'FROM information_schema.TABLES WHERE ENGINE="InnoDB" LIMIT 1') # ibdata_free = "" # res = cursor.fetchone() # if res: # ibdata_free = res[0] # else: # ibdata_free = False # cursor.close() ibdata_free = False if get_master: cursor = conn.cursor(MySQLdb.cursors.Cursor) cursor.execute("SHOW MASTER LOGS") master_logs = cursor.fetchall() cursor.close() if get_slave: cursor = conn.cursor(MySQLdb.cursors.DictCursor) cursor.execute("SHOW SLAVE STATUS") slave_status = {} res = cursor.fetchone() if res: for (k, v) in res.items(): slave_status[k.lower()] = v else: get_slave = False cursor.close() cursor = conn.cursor(MySQLdb.cursors.DictCursor) cursor.execute("SELECT RELEASE_LOCK('gmetric-mysql') as ok") cursor.close() conn.close() except MySQLdb.OperationalError: logging.error('error updating stats') return False # process variables # http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html mysql_stats['version'] = variables['version'] mysql_stats['max_connections'] = variables['max_connections'] # process global status # http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html interesting_global_status_vars = ( 'aborted_clients', 'aborted_connects', 'binlog_cache_disk_use', 'binlog_cache_use', 'bytes_received', 'bytes_sent', 'com_delete', 'com_delete_multi', 'com_insert', 'com_insert_select', 'com_load', 'com_replace', 'com_replace_select', 'com_select', 'com_update', 'com_update_multi', 'connections', 'created_tmp_disk_tables', 'created_tmp_files', 'created_tmp_tables', 'key_reads', 'key_read_requests', 'key_writes', 'key_write_requests', 'max_used_connections', 'open_files', 'open_tables', 'opened_tables', 'queries', 'questions', 'select_full_join', 'select_full_range_join', 'select_range', 'select_range_check', 'select_scan', 'slave_open_temp_tables', 'slave_retried_transactions', 'slow_launch_threads', 'slow_queries', 'sort_range', 'sort_rows', 'sort_scan', 'table_locks_immediate', 'table_locks_waited', 'threads_cached', 'threads_connected', 'threads_created', 'threads_running', 'uptime', ) non_delta = ('max_used_connections', 'open_files', 'open_tables', 'slave_open_temp_tables', 'threads_cached', 'threads_connected', 'threads_created', 'threads_running', 'uptime') # don't put all of global_status in mysql_stats b/c it's so big for key in interesting_global_status_vars: if key in non_delta: mysql_stats[key] = global_status[key] else: # Calculate deltas for counters if key in mysql_stats_last: mysql_stats[key] = int(global_status[key]) - int( mysql_stats_last[key]) else: mysql_stats[key] = 0 mysql_stats_last[key] = global_status[key] mysql_stats['open_files_used'] = int(global_status['open_files']) / int( variables['open_files_limit']) innodb_delta = ('data_fsyncs', 'data_reads', 'data_writes', 'log_writes') # process innodb status if get_innodb: for istat in innodb_status: key = 'innodb_' + istat if istat in innodb_delta: # Calculate deltas for counters if key in mysql_stats_last: mysql_stats[key] = int(innodb_status[istat]) - int( mysql_stats_last[key]) else: mysql_stats[key] = 0 mysql_stats_last[key] = innodb_status[istat] else: mysql_stats[key] = innodb_status[istat] if ibdata_free: mysql_stats['innodb_free_space'] = long(ibdata_free) / 1048576 # process master logs if get_master: mysql_stats['binlog_count'] = len(master_logs) mysql_stats['binlog_space_current'] = master_logs[-1][1] # mysql_stats['binlog_space_total'] = sum((long(s[1]) for s in master_logs)) mysql_stats['binlog_space_total'] = 0 for s in master_logs: mysql_stats['binlog_space_total'] += int(s[1]) mysql_stats['binlog_space_used'] = float(master_logs[-1][1]) / float( variables['max_binlog_size']) * 100 # process slave status if get_slave: mysql_stats['slave_exec_master_log_pos'] = slave_status[ 'exec_master_log_pos'] # mysql_stats['slave_io'] = 1 if slave_status['slave_io_running'].lower() == "yes" else 0 if slave_status['slave_io_running'].lower() == "yes": mysql_stats['slave_io'] = 1 else: mysql_stats['slave_io'] = 0 # mysql_stats['slave_sql'] = 1 if slave_status['slave_sql_running'].lower() =="yes" else 0 if slave_status['slave_sql_running'].lower() == "yes": mysql_stats['slave_sql'] = 1 if slave_status['slave_io_running'].lower() == "yes": mysql_stats['slave_running'] = 1 else: mysql_stats['slave_sql'] = 0 mysql_stats['slave_running'] = 0 mysql_stats['slave_lag'] = slave_status['seconds_behind_master'] mysql_stats['slave_relay_log_pos'] = slave_status['relay_log_pos'] mysql_stats['slave_relay_log_space'] = slave_status['relay_log_space'] logging.debug('success updating stats') logging.debug('mysql_stats: ' + str(mysql_stats))