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))
