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):
  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 = {}
 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 = {}
  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 = {}
  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()
  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 as (errno, errmsg):
  logging.error('error updating stats')
  logging.error(errmsg)
  return False
 mysql_stats['version'] = variables['version']
 mysql_stats['max_connections'] = variables['max_connections']
 mysql_stats['query_cache_size'] = variables['query_cache_size']
 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', 'qcache_free_blocks', 'qcache_free_memory', 'qcache_hits', 'qcache_inserts', 'qcache_lowmem_prunes', 'qcache_not_cached', 'qcache_queries_in_cache', 'qcache_total_blocks', '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', 'qcache_free_blocks', 'qcache_free_memory', 'qcache_total_blocks', 'slave_open_temp_tables', 'threads_cached', 'threads_connected', 'threads_running', 'uptime')
 for key in interesting_global_status_vars:
  if (key in non_delta):
   mysql_stats[key] = global_status[key]
  else:
   if (time_delta <= 0):
    pass
   elif (key in mysql_stats_last):
    if delta_per_second:
     mysql_stats[key] = ((int(global_status[key]) - int(mysql_stats_last[key])) / time_delta)
    else:
     mysql_stats[key] = (int(global_status[key]) - int(mysql_stats_last[key]))
   else:
    mysql_stats[key] = float(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')
 if get_innodb:
  for istat in innodb_status:
   key = ('innodb_' + istat)
   if (istat in innodb_delta):
    if (time_delta <= 0):
     pass
    elif (key in mysql_stats_last):
     if delta_per_second:
      mysql_stats[key] = ((int(innodb_status[istat]) - int(mysql_stats_last[key])) / time_delta)
     else:
      mysql_stats[key] = (int(innodb_status[istat]) - int(mysql_stats_last[key]))
    else:
     mysql_stats[key] = float(0)
    mysql_stats_last[key] = innodb_status[istat]
   else:
    mysql_stats[key] = innodb_status[istat]
 if get_master:
  mysql_stats['binlog_count'] = len(master_logs)
  mysql_stats['binlog_space_current'] = master_logs[(-1)][1]
  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)
 if get_slave:
  mysql_stats['slave_exec_master_log_pos'] = slave_status['exec_master_log_pos']
  if (slave_status['slave_io_running'].lower() == 'yes'):
   mysql_stats['slave_io'] = 1
  else:
   mysql_stats['slave_io'] = 0
  if (slave_status['slave_sql_running'].lower() == 'yes'):
   mysql_stats['slave_sql'] = 1
  else:
   mysql_stats['slave_sql'] = 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)))

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 ) : 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 = { } 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 = { } 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 = { } 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 ( ) 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 as ( errno , errmsg ) : logging . error ( 'error updating stats' ) logging . error ( errmsg ) return False mysql_stats [ 'version' ] = variables [ 'version' ] mysql_stats [ 'max_connections' ] = variables [ 'max_connections' ] mysql_stats [ 'query_cache_size' ] = variables [ 'query_cache_size' ] 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' , 'qcache_free_blocks' , 'qcache_free_memory' , 'qcache_hits' , 'qcache_inserts' , 'qcache_lowmem_prunes' , 'qcache_not_cached' , 'qcache_queries_in_cache' , 'qcache_total_blocks' , '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' , 'qcache_free_blocks' , 'qcache_free_memory' , 'qcache_total_blocks' , 'slave_open_temp_tables' , 'threads_cached' , 'threads_connected' , 'threads_running' , 'uptime' ) for key in interesting_global_status_vars : if ( key in non_delta ) : mysql_stats [ key ] = global_status [ key ] else : if ( time_delta <= 0 ) : pass elif ( key in mysql_stats_last ) : if delta_per_second : mysql_stats [ key ] = ( ( int ( global_status [ key ] ) - int ( mysql_stats_last [ key ] ) ) / time_delta ) else : mysql_stats [ key ] = ( int ( global_status [ key ] ) - int ( mysql_stats_last [ key ] ) ) else : mysql_stats [ key ] = float ( 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' ) if get_innodb : for istat in innodb_status : key = ( 'innodb_' + istat ) if ( istat in innodb_delta ) : if ( time_delta <= 0 ) : pass elif ( key in mysql_stats_last ) : if delta_per_second : mysql_stats [ key ] = ( ( int ( innodb_status [ istat ] ) - int ( mysql_stats_last [ key ] ) ) / time_delta ) else : mysql_stats [ key ] = ( int ( innodb_status [ istat ] ) - int ( mysql_stats_last [ key ] ) ) else : mysql_stats [ key ] = float ( 0 ) mysql_stats_last [ key ] = innodb_status [ istat ] else : mysql_stats [ key ] = innodb_status [ istat ] if get_master : mysql_stats [ 'binlog_count' ] = len ( master_logs ) mysql_stats [ 'binlog_space_current' ] = master_logs [ ( - 1 ) ] [ 1 ] 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 ) if get_slave : mysql_stats [ 'slave_exec_master_log_pos' ] = slave_status [ 'exec_master_log_pos' ] if ( slave_status [ 'slave_io_running' ] . lower ( ) == 'yes' ) : mysql_stats [ 'slave_io' ] = 1 else : mysql_stats [ 'slave_io' ] = 0 if ( slave_status [ 'slave_sql_running' ] . lower ( ) == 'yes' ) : mysql_stats [ 'slave_sql' ] = 1 else : mysql_stats [ 'slave_sql' ] = 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 ) ) )
refresh stats by polling memcached server .