function ss_get_mysql_stats($options)
{
# Process connection options and connect to MySQL.
global $debug, $mysql_user, $mysql_pass, $heartbeat, $cache_dir, $poll_time, $chk_options, $mysql_port, $mysql_ssl;
# Connect to MySQL.
$user = isset($options['user']) ? $options['user'] : $mysql_user;
$pass = isset($options['pass']) ? $options['pass'] : $mysql_pass;
$port = isset($options['port']) ? $options['port'] : $mysql_port;
$heartbeat = isset($options['heartbeat']) ? $options['heartbeat'] : $heartbeat;
# If there is a port, or if it's a non-standard port, we add ":$port" to the
# hostname.
$host_str = $options['host'] . (isset($options['port']) || $port != 3306 ? ":{$port}" : '');
debug(array('connecting to', $host_str, $user, $pass));
if (!extension_loaded('mysql')) {
debug("The MySQL extension is not loaded");
die("The MySQL extension is not loaded");
}
if ($mysql_ssl || isset($options['mysql_ssl']) && $options['mysql_ssl']) {
$conn = mysql_connect($host_str, $user, $pass, true, MYSQL_CLIENT_SSL);
} else {
$conn = mysql_connect($host_str, $user, $pass);
}
if (!$conn) {
die("MySQL: " . mysql_error());
}
$sanitized_host = str_replace(array(":", "/"), array("", "_"), $options['host']);
$cache_file = "{$cache_dir}/{$sanitized_host}-mysql_cacti_stats.txt" . (isset($options['port']) || $port != 3306 ? ":{$port}" : '');
debug("Cache file is {$cache_file}");
# First, check the cache.
$fp = null;
if (!isset($options['nocache'])) {
if ($fp = fopen($cache_file, 'a+')) {
$locked = flock($fp, 1);
# LOCK_SH
if ($locked) {
if (filesize($cache_file) > 0 && filectime($cache_file) + $poll_time / 2 > time() && ($arr = file($cache_file))) {
# The cache file is good to use.
debug("Using the cache file");
fclose($fp);
return $arr[0];
} else {
debug("The cache file seems too small or stale");
# Escalate the lock to exclusive, so we can write to it.
if (flock($fp, 2)) {
# LOCK_EX
# We might have blocked while waiting for that LOCK_EX, and
# another process ran and updated it. Let's see if we can just
# return the data now:
if (filesize($cache_file) > 0 && filectime($cache_file) + $poll_time / 2 > time() && ($arr = file($cache_file))) {
# The cache file is good to use.
debug("Using the cache file");
fclose($fp);
return $arr[0];
}
ftruncate($fp, 0);
# Now it's ready for writing later.
}
}
} else {
debug("Couldn't lock the cache file, ignoring it.");
$fp = null;
}
}
} else {
$fp = null;
debug("Couldn't open the cache file");
}
# Set up variables.
$status = array('relay_log_space' => null, 'binary_log_space' => null, 'current_transactions' => 0, 'locked_transactions' => 0, 'active_transactions' => 0, 'innodb_locked_tables' => 0, 'innodb_tables_in_use' => 0, 'innodb_lock_structs' => 0, 'innodb_lock_wait_secs' => 0, 'innodb_sem_waits' => 0, 'innodb_sem_wait_time_ms' => 0, 'State_closing_tables' => null, 'State_copying_to_tmp_table' => null, 'State_end' => null, 'State_freeing_items' => null, 'State_init' => null, 'State_locked' => null, 'State_login' => null, 'State_preparing' => null, 'State_reading_from_net' => null, 'State_sending_data' => null, 'State_sorting_result' => null, 'State_statistics' => null, 'State_updating' => null, 'State_writing_to_net' => null, 'State_none' => null, 'State_other' => null);
# Get SHOW STATUS and convert the name-value array into a simple
# associative array.
$result = run_query("SHOW /*!50002 GLOBAL */ STATUS", $conn);
foreach ($result as $row) {
$status[$row[0]] = $row[1];
}
# Get SHOW VARIABLES and do the same thing, adding it to the $status array.
$result = run_query("SHOW VARIABLES", $conn);
foreach ($result as $row) {
$status[$row[0]] = $row[1];
}
# Get SHOW SLAVE STATUS, and add it to the $status array.
if ($chk_options['slave']) {
$result = run_query("SHOW SLAVE STATUS", $conn);
$slave_status_rows_gotten = 0;
foreach ($result as $row) {
$slave_status_rows_gotten++;
# Must lowercase keys because different MySQL versions have different
# lettercase.
$row = array_change_key_case($row, CASE_LOWER);
$status['relay_log_space'] = $row['relay_log_space'];
$status['slave_lag'] = $row['seconds_behind_master'];
# Check replication heartbeat, if present.
if ($heartbeat) {
$result2 = run_query("SELECT GREATEST(0, UNIX_TIMESTAMP() - UNIX_TIMESTAMP(ts) - 1)" . " AS delay FROM {$heartbeat} WHERE id = 1", $conn);
$slave_delay_rows_gotten = 0;
foreach ($result2 as $row2) {
$slave_delay_rows_gotten++;
if ($row2 && is_array($row2) && array_key_exists('delay', $row2)) {
$status['slave_lag'] = $row2['delay'];
} else {
debug("Couldn't get slave lag from {$heartbeat}");
}
}
if ($slave_delay_rows_gotten == 0) {
debug("Got nothing from heartbeat query");
}
}
# Scale slave_running and slave_stopped relative to the slave lag.
$status['slave_running'] = $row['slave_sql_running'] == 'Yes' ? $status['slave_lag'] : 0;
$status['slave_stopped'] = $row['slave_sql_running'] == 'Yes' ? 0 : $status['slave_lag'];
}
if ($slave_status_rows_gotten == 0) {
debug("Got nothing from SHOW SLAVE STATUS");
}
}
# Get SHOW MASTER STATUS, and add it to the $status array.
if ($chk_options['master'] && array_key_exists('log_bin', $status) && $status['log_bin'] == 'ON') {
# See issue #8
$binlogs = array(0);
$result = run_query("SHOW MASTER LOGS", $conn);
foreach ($result as $row) {
$row = array_change_key_case($row, CASE_LOWER);
# Older versions of MySQL may not have the File_size column in the
# results of the command. Zero-size files indicate the user is
# deleting binlogs manually from disk (bad user! bad!).
if (array_key_exists('file_size', $row) && $row['file_size'] > 0) {
$binlogs[] = $row['file_size'];
}
}
if (count($binlogs)) {
$status['binary_log_space'] = to_int(array_sum($binlogs));
}
}
# Get SHOW PROCESSLIST and aggregate it by state, then add it to the array
# too.
if ($chk_options['procs']) {
$result = run_query('SHOW PROCESSLIST', $conn);
foreach ($result as $row) {
$state = $row['State'];
if (is_null($state)) {
$state = 'NULL';
}
if ($state == '') {
$state = 'none';
}
$state = str_replace(' ', '_', strtolower($state));
if (array_key_exists("State_{$state}", $status)) {
increment($status, "State_{$state}", 1);
} else {
increment($status, "State_other", 1);
}
}
}
# Get SHOW INNODB STATUS and extract the desired metrics from it, then add
# those to the array too.
if ($chk_options['innodb'] && array_key_exists('have_innodb', $status) && $status['have_innodb'] == 'YES') {
$result = run_query("SHOW /*!50000 ENGINE*/ INNODB STATUS", $conn);
$istatus_text = $result[0]['Status'];
$istatus_vals = get_innodb_array($istatus_text);
# Get response time histogram from Percona Server if enabled.
if ($chk_options['get_qrt'] && isset($status['have_response_time_distribution']) && $status['have_response_time_distribution'] == 'YES') {
debug('Getting query time histogram');
$i = 0;
$result = run_query("SELECT `count`, total * 1000000 AS total " . "FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME " . "WHERE `time` <> 'TOO LONG'", $conn);
foreach ($result as $row) {
$count_key = sprintf("Query_time_count_%02d", $i);
$total_key = sprintf("Query_time_total_%02d", $i);
$status[$count_key] = $row['count'];
$status[$total_key] = $row['total'];
$i++;
}
} else {
debug('Not getting time histogram because it is not enabled');
}
# Override values from InnoDB parsing with values from SHOW STATUS,
# because InnoDB status might not have everything and the SHOW STATUS is
# to be preferred where possible.
$overrides = array('Innodb_buffer_pool_pages_data' => 'database_pages', 'Innodb_buffer_pool_pages_dirty' => 'modified_pages', 'Innodb_buffer_pool_pages_free' => 'free_pages', 'Innodb_buffer_pool_pages_total' => 'pool_size', 'Innodb_data_fsyncs' => 'file_fsyncs', 'Innodb_data_pending_reads' => 'pending_normal_aio_reads', 'Innodb_data_pending_writes' => 'pending_normal_aio_writes', 'Innodb_os_log_pending_fsyncs' => 'pending_log_flushes', 'Innodb_pages_created' => 'pages_created', 'Innodb_pages_read' => 'pages_read', 'Innodb_pages_written' => 'pages_written', 'Innodb_rows_deleted' => 'rows_deleted', 'Innodb_rows_inserted' => 'rows_inserted', 'Innodb_rows_read' => 'rows_read', 'Innodb_rows_updated' => 'rows_updated');
# If the SHOW STATUS value exists, override...
foreach ($overrides as $key => $val) {
if (array_key_exists($key, $status)) {
debug("Override {$key}");
$istatus_vals[$val] = $status[$key];
}
}
# Now copy the values into $status.
foreach ($istatus_vals as $key => $val) {
$status[$key] = $istatus_vals[$key];
}
}
# Make table_open_cache backwards-compatible (issue 63).
if (array_key_exists('table_open_cache', $status)) {
$status['table_cache'] = $status['table_open_cache'];
}
# Compute how much of the key buffer is used and unflushed (issue 127).
$status['Key_buf_bytes_used'] = big_sub($status['key_buffer_size'], big_multiply($status['Key_blocks_unused'], $status['key_cache_block_size']));
$status['Key_buf_bytes_unflushed'] = big_multiply($status['Key_blocks_not_flushed'], $status['key_cache_block_size']);
if (array_key_exists('unflushed_log', $status) && $status['unflushed_log']) {
# TODO: I'm not sure what the deal is here; need to debug this. But the
# unflushed log bytes spikes a lot sometimes and it's impossible for it to
# be more than the log buffer.
debug("Unflushed log: {$status['unflushed_log']}");
$status['unflushed_log'] = max($status['unflushed_log'], $status['innodb_log_buffer_size']);
}
# Define the variables to output. I use shortened variable names so maybe
# it'll all fit in 1024 bytes for Cactid and Spine's benefit. This list must
# come right after the word MAGIC_VARS_DEFINITIONS. The Perl script parses
# it and uses it as a Perl variable.
$keys = array('Key_read_requests' => 'a0', 'Key_reads' => 'a1', 'Key_write_requests' => 'a2', 'Key_writes' => 'a3', 'history_list' => 'a4', 'innodb_transactions' => 'a5', 'read_views' => 'a6', 'current_transactions' => 'a7', 'locked_transactions' => 'a8', 'active_transactions' => 'a9', 'pool_size' => 'aa', 'free_pages' => 'ab', 'database_pages' => 'ac', 'modified_pages' => 'ad', 'pages_read' => 'ae', 'pages_created' => 'af', 'pages_written' => 'ag', 'file_fsyncs' => 'ah', 'file_reads' => 'ai', 'file_writes' => 'aj', 'log_writes' => 'ak', 'pending_aio_log_ios' => 'al', 'pending_aio_sync_ios' => 'am', 'pending_buf_pool_flushes' => 'an', 'pending_chkp_writes' => 'ao', 'pending_ibuf_aio_reads' => 'ap', 'pending_log_flushes' => 'aq', 'pending_log_writes' => 'ar', 'pending_normal_aio_reads' => 'as', 'pending_normal_aio_writes' => 'at', 'ibuf_inserts' => 'au', 'ibuf_merged' => 'av', 'ibuf_merges' => 'aw', 'spin_waits' => 'ax', 'spin_rounds' => 'ay', 'os_waits' => 'az', 'rows_inserted' => 'b0', 'rows_updated' => 'b1', 'rows_deleted' => 'b2', 'rows_read' => 'b3', 'Table_locks_waited' => 'b4', 'Table_locks_immediate' => 'b5', 'Slow_queries' => 'b6', 'Open_files' => 'b7', 'Open_tables' => 'b8', 'Opened_tables' => 'b9', 'innodb_open_files' => 'ba', 'open_files_limit' => 'bb', 'table_cache' => 'bc', 'Aborted_clients' => 'bd', 'Aborted_connects' => 'be', 'Max_used_connections' => 'bf', 'Slow_launch_threads' => 'bg', 'Threads_cached' => 'bh', 'Threads_connected' => 'bi', 'Threads_created' => 'bj', 'Threads_running' => 'bk', 'max_connections' => 'bl', 'thread_cache_size' => 'bm', 'Connections' => 'bn', 'slave_running' => 'bo', 'slave_stopped' => 'bp', 'Slave_retried_transactions' => 'bq', 'slave_lag' => 'br', 'Slave_open_temp_tables' => 'bs', 'Qcache_free_blocks' => 'bt', 'Qcache_free_memory' => 'bu', 'Qcache_hits' => 'bv', 'Qcache_inserts' => 'bw', 'Qcache_lowmem_prunes' => 'bx', 'Qcache_not_cached' => 'by', 'Qcache_queries_in_cache' => 'bz', 'Qcache_total_blocks' => 'c0', 'query_cache_size' => 'c1', 'Questions' => 'c2', 'Com_update' => 'c3', 'Com_insert' => 'c4', 'Com_select' => 'c5', 'Com_delete' => 'c6', 'Com_replace' => 'c7', 'Com_load' => 'c8', 'Com_update_multi' => 'c9', 'Com_insert_select' => 'ca', 'Com_delete_multi' => 'cb', 'Com_replace_select' => 'cc', 'Select_full_join' => 'cd', 'Select_full_range_join' => 'ce', 'Select_range' => 'cf', 'Select_range_check' => 'cg', 'Select_scan' => 'ch', 'Sort_merge_passes' => 'ci', 'Sort_range' => 'cj', 'Sort_rows' => 'ck', 'Sort_scan' => 'cl', 'Created_tmp_tables' => 'cm', 'Created_tmp_disk_tables' => 'cn', 'Created_tmp_files' => 'co', 'Bytes_sent' => 'cp', 'Bytes_received' => 'cq', 'innodb_log_buffer_size' => 'cr', 'unflushed_log' => 'cs', 'log_bytes_flushed' => 'ct', 'log_bytes_written' => 'cu', 'relay_log_space' => 'cv', 'binlog_cache_size' => 'cw', 'Binlog_cache_disk_use' => 'cx', 'Binlog_cache_use' => 'cy', 'binary_log_space' => 'cz', 'innodb_locked_tables' => 'd0', 'innodb_lock_structs' => 'd1', 'State_closing_tables' => 'd2', 'State_copying_to_tmp_table' => 'd3', 'State_end' => 'd4', 'State_freeing_items' => 'd5', 'State_init' => 'd6', 'State_locked' => 'd7', 'State_login' => 'd8', 'State_preparing' => 'd9', 'State_reading_from_net' => 'da', 'State_sending_data' => 'db', 'State_sorting_result' => 'dc', 'State_statistics' => 'dd', 'State_updating' => 'de', 'State_writing_to_net' => 'df', 'State_none' => 'dg', 'State_other' => 'dh', 'Handler_commit' => 'di', 'Handler_delete' => 'dj', 'Handler_discover' => 'dk', 'Handler_prepare' => 'dl', 'Handler_read_first' => 'dm', 'Handler_read_key' => 'dn', 'Handler_read_next' => 'do', 'Handler_read_prev' => 'dp', 'Handler_read_rnd' => 'dq', 'Handler_read_rnd_next' => 'dr', 'Handler_rollback' => 'ds', 'Handler_savepoint' => 'dt', 'Handler_savepoint_rollback' => 'du', 'Handler_update' => 'dv', 'Handler_write' => 'dw', 'innodb_tables_in_use' => 'dx', 'innodb_lock_wait_secs' => 'dy', 'hash_index_cells_total' => 'dz', 'hash_index_cells_used' => 'e0', 'total_mem_alloc' => 'e1', 'additional_pool_alloc' => 'e2', 'uncheckpointed_bytes' => 'e3', 'ibuf_used_cells' => 'e4', 'ibuf_free_cells' => 'e5', 'ibuf_cell_count' => 'e6', 'adaptive_hash_memory' => 'e7', 'page_hash_memory' => 'e8', 'dictionary_cache_memory' => 'e9', 'file_system_memory' => 'ea', 'lock_system_memory' => 'eb', 'recovery_system_memory' => 'ec', 'thread_hash_memory' => 'ed', 'innodb_sem_waits' => 'ee', 'innodb_sem_wait_time_ms' => 'ef', 'Key_buf_bytes_unflushed' => 'eg', 'Key_buf_bytes_used' => 'eh', 'key_buffer_size' => 'ei', 'Innodb_row_lock_time' => 'ej', 'Innodb_row_lock_waits' => 'ek', 'Query_time_count_00' => 'el', 'Query_time_count_01' => 'em', 'Query_time_count_02' => 'en', 'Query_time_count_03' => 'eo', 'Query_time_count_04' => 'ep', 'Query_time_count_05' => 'eq', 'Query_time_count_06' => 'er', 'Query_time_count_07' => 'es', 'Query_time_count_08' => 'et', 'Query_time_count_09' => 'eu', 'Query_time_count_10' => 'ev', 'Query_time_count_11' => 'ew', 'Query_time_count_12' => 'ex', 'Query_time_count_13' => 'ey', 'Query_time_total_00' => 'ez', 'Query_time_total_01' => 'fa', 'Query_time_total_02' => 'fb', 'Query_time_total_03' => 'fc', 'Query_time_total_04' => 'fd', 'Query_time_total_05' => 'fe', 'Query_time_total_06' => 'ff', 'Query_time_total_07' => 'fg', 'Query_time_total_08' => 'fh', 'Query_time_total_09' => 'fi', 'Query_time_total_10' => 'fj', 'Query_time_total_11' => 'fk', 'Query_time_total_12' => 'fl', 'Query_time_total_13' => 'fm');
# Return the output.
$output = array();
foreach ($keys as $key => $short) {
# If the value isn't defined, return -1 which is lower than (most graphs')
# minimum value of 0, so it'll be regarded as a missing value.
$val = isset($status[$key]) ? $status[$key] : -1;
$output[] = "{$short}:{$val}";
}
$result = implode(' ', $output);
if ($fp) {
if (fwrite($fp, $result) === FALSE) {
die("Can't write '{$cache_file}'");
}
fclose($fp);
}
return $result;
}
function ss_get_mysql_stats($options)
{
# Process connection options.
global $debug, $mysql_user, $mysql_pass, $cache_dir, $poll_time, $chk_options, $mysql_port, $mysql_ssl, $mysql_ssl_key, $mysql_ssl_cert, $mysql_ssl_ca, $mysql_connection_timeout, $heartbeat, $heartbeat_table, $heartbeat_server_id, $heartbeat_utc;
$user = isset($options['user']) ? $options['user'] : $mysql_user;
$pass = isset($options['pass']) ? $options['pass'] : $mysql_pass;
$host = $options['host'];
$port = isset($options['port']) ? $options['port'] : $mysql_port;
$connection_timeout = isset($options['connection-timeout']) ? $options['connection-timeout'] : $mysql_connection_timeout;
$heartbeat_server_id = isset($options['server-id']) ? $options['server-id'] : $heartbeat_server_id;
$sanitized_host = str_replace(array(":", "/"), array("", "_"), $host);
$cache_file = "{$cache_dir}/{$sanitized_host}-mysql_cacti_stats.txt" . ($port != 3306 ? ":{$port}" : '');
debug("Cache file is {$cache_file}");
# First, check the cache.
$fp = null;
if ($cache_dir && !array_key_exists('nocache', $options)) {
if ($fp = fopen($cache_file, 'a+')) {
$locked = flock($fp, 1);
# LOCK_SH
if ($locked) {
if (filesize($cache_file) > 0 && filectime($cache_file) + $poll_time / 2 > time() && ($arr = file($cache_file))) {
# The cache file is good to use.
debug("Using the cache file");
fclose($fp);
return $arr[0];
} else {
debug("The cache file seems too small or stale");
# Escalate the lock to exclusive, so we can write to it.
if (flock($fp, 2)) {
# LOCK_EX
# We might have blocked while waiting for that LOCK_EX, and
# another process ran and updated it. Let's see if we can just
# return the data now:
if (filesize($cache_file) > 0 && filectime($cache_file) + $poll_time / 2 > time() && ($arr = file($cache_file))) {
# The cache file is good to use.
debug("Using the cache file");
fclose($fp);
return $arr[0];
}
ftruncate($fp, 0);
# Now it's ready for writing later.
}
}
} else {
$fp = null;
debug("Couldn't lock the cache file, ignoring it");
}
} else {
$fp = null;
debug("Couldn't open the cache file");
}
} else {
debug("Caching is disabled.");
}
# Connect to MySQL.
debug(array('Connecting to', $host, $port, $user, $pass));
if (!extension_loaded('mysqli')) {
debug("PHP MySQLi extension is not loaded");
die("PHP MySQLi extension is not loaded");
}
if ($mysql_ssl) {
$conn = mysqli_init();
$conn->options(MYSQLI_OPT_CONNECT_TIMEOUT, $connection_timeout);
mysqli_ssl_set($conn, $mysql_ssl_key, $mysql_ssl_cert, $mysql_ssl_ca, NULL, NULL);
mysqli_real_connect($conn, $host, $user, $pass, NULL, $port);
} else {
$conn = mysqli_init();
$conn->options(MYSQLI_OPT_CONNECT_TIMEOUT, $connection_timeout);
mysqli_real_connect($conn, $host, $user, $pass, NULL, $port);
}
if (mysqli_connect_errno()) {
debug("MySQL connection failed: " . mysqli_connect_error());
die("ERROR: " . mysqli_connect_error());
}
# MySQL server version.
# The form of this version number is main_version * 10000 + minor_version * 100 + sub_version
# i.e. version 5.5.44 is 50544.
$mysql_version = mysqli_get_server_version($conn);
debug("MySQL server version is " . $mysql_version);
# Set up variables.
$status = array('relay_log_space' => null, 'binary_log_space' => null, 'current_transactions' => 0, 'locked_transactions' => 0, 'active_transactions' => 0, 'innodb_locked_tables' => 0, 'innodb_tables_in_use' => 0, 'innodb_lock_structs' => 0, 'innodb_lock_wait_secs' => 0, 'innodb_sem_waits' => 0, 'innodb_sem_wait_time_ms' => 0, 'State_closing_tables' => 0, 'State_copying_to_tmp_table' => 0, 'State_end' => 0, 'State_freeing_items' => 0, 'State_init' => 0, 'State_locked' => 0, 'State_login' => 0, 'State_preparing' => 0, 'State_reading_from_net' => 0, 'State_sending_data' => 0, 'State_sorting_result' => 0, 'State_statistics' => 0, 'State_updating' => 0, 'State_writing_to_net' => 0, 'State_none' => 0, 'State_other' => 0);
# Get SHOW STATUS and convert the name-value array into a simple
# associative array.
$result = run_query("SHOW /*!50002 GLOBAL */ STATUS", $conn);
foreach ($result as $row) {
$status[$row[0]] = $row[1];
}
# Get SHOW VARIABLES and do the same thing, adding it to the $status array.
$result = run_query("SHOW VARIABLES", $conn);
foreach ($result as $row) {
$status[$row[0]] = $row[1];
}
# Get SHOW SLAVE STATUS, and add it to the $status array.
if ($chk_options['slave']) {
# Leverage lock-free SHOW SLAVE STATUS if available
$result = run_query("SHOW SLAVE STATUS NONBLOCKING", $conn);
if (!$result) {
$result = run_query("SHOW SLAVE STATUS NOLOCK", $conn);
if (!$result) {
$result = run_query("SHOW SLAVE STATUS", $conn);
}
}
$slave_status_rows_gotten = 0;
foreach ($result as $row) {
$slave_status_rows_gotten++;
# Must lowercase keys because different MySQL versions have different
# lettercase.
$row = array_change_key_case($row, CASE_LOWER);
$status['relay_log_space'] = $row['relay_log_space'];
$status['slave_lag'] = $row['seconds_behind_master'];
# Check replication heartbeat, if present.
if ($heartbeat) {
if ($heartbeat_utc) {
$now_func = 'UNIX_TIMESTAMP(UTC_TIMESTAMP)';
} else {
$now_func = 'UNIX_TIMESTAMP()';
}
$result2 = run_query("SELECT MAX({$now_func} - ROUND(UNIX_TIMESTAMP(ts)))" . " AS delay FROM {$heartbeat_table}" . " WHERE {$heartbeat_server_id} = 0 OR server_id = {$heartbeat_server_id}", $conn);
$slave_delay_rows_gotten = 0;
foreach ($result2 as $row2) {
$slave_delay_rows_gotten++;
if ($row2 && is_array($row2) && array_key_exists('delay', $row2)) {
$status['slave_lag'] = $row2['delay'];
} else {
debug("Couldn't get slave lag from {$heartbeat_table}");
}
}
if ($slave_delay_rows_gotten == 0) {
debug("Got nothing from heartbeat query");
}
}
# Scale slave_running and slave_stopped relative to the slave lag.
$status['slave_running'] = $row['slave_sql_running'] == 'Yes' ? $status['slave_lag'] : 0;
$status['slave_stopped'] = $row['slave_sql_running'] == 'Yes' ? 0 : $status['slave_lag'];
}
if ($slave_status_rows_gotten == 0) {
debug("Got nothing from SHOW SLAVE STATUS");
}
}
# Get SHOW MASTER STATUS, and add it to the $status array.
if ($chk_options['master'] && array_key_exists('log_bin', $status) && $status['log_bin'] == 'ON') {
# See issue #8
$binlogs = array(0);
$result = run_query("SHOW MASTER LOGS", $conn);
foreach ($result as $row) {
$row = array_change_key_case($row, CASE_LOWER);
# Older versions of MySQL may not have the File_size column in the
# results of the command. Zero-size files indicate the user is
# deleting binlogs manually from disk (bad user! bad!).
if (array_key_exists('file_size', $row) && $row['file_size'] > 0) {
$binlogs[] = $row['file_size'];
}
}
if (count($binlogs)) {
$status['binary_log_space'] = to_int(array_sum($binlogs));
}
}
# Get SHOW PROCESSLIST and aggregate it by state, then add it to the array
# too.
if ($chk_options['procs']) {
$result = run_query('SHOW PROCESSLIST', $conn);
foreach ($result as $row) {
$state = $row['State'];
if (is_null($state)) {
$state = 'NULL';
}
if ($state == '') {
$state = 'none';
}
# MySQL 5.5 replaces the 'Locked' state with a variety of "Waiting for
# X lock" types of statuses. Wrap these all back into "Locked" because
# we don't really care about the type of locking it is.
$state = preg_replace('/^(Table lock|Waiting for .*lock)$/', 'Locked', $state);
$state = str_replace(' ', '_', strtolower($state));
if (array_key_exists("State_{$state}", $status)) {
increment($status, "State_{$state}", 1);
} else {
increment($status, "State_other", 1);
}
}
}
# Get SHOW ENGINES to be able to determine whether InnoDB is present.
$engines = array();
$result = run_query("SHOW ENGINES", $conn);
foreach ($result as $row) {
$engines[$row[0]] = $row[1];
}
# Get SHOW INNODB STATUS and extract the desired metrics from it, then add
# those to the array too.
if ($chk_options['innodb'] && array_key_exists('InnoDB', $engines) && $engines['InnoDB'] == 'YES' || $engines['InnoDB'] == 'DEFAULT') {
$result = run_query("SHOW /*!50000 ENGINE*/ INNODB STATUS", $conn);
$istatus_text = $result[0]['Status'];
$istatus_vals = get_innodb_array($istatus_text, $mysql_version);
# Get response time histogram from Percona Server or MariaDB if enabled.
if ($chk_options['get_qrt'] && (isset($status['have_response_time_distribution']) && $status['have_response_time_distribution'] == 'YES' || isset($status['query_response_time_stats']) && $status['query_response_time_stats'] == 'ON')) {
debug('Getting query time histogram');
$i = 0;
$result = run_query("SELECT `count`, ROUND(total * 1000000) AS total " . "FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME " . "WHERE `time` <> 'TOO LONG'", $conn);
foreach ($result as $row) {
if ($i > 13) {
# It's possible that the number of rows returned isn't 14.
# Don't add extra status counters.
break;
}
$count_key = sprintf("Query_time_count_%02d", $i);
$total_key = sprintf("Query_time_total_%02d", $i);
$status[$count_key] = $row['count'];
$status[$total_key] = $row['total'];
$i++;
}
# It's also possible that the number of rows returned is too few.
# Don't leave any status counters unassigned; it will break graphs.
while ($i <= 13) {
$count_key = sprintf("Query_time_count_%02d", $i);
$total_key = sprintf("Query_time_total_%02d", $i);
$status[$count_key] = 0;
$status[$total_key] = 0;
$i++;
}
} else {
debug('Not getting time histogram because it is not enabled');
}
# Override values from InnoDB parsing with values from SHOW STATUS,
# because InnoDB status might not have everything and the SHOW STATUS is
# to be preferred where possible.
$overrides = array('Innodb_buffer_pool_pages_data' => 'database_pages', 'Innodb_buffer_pool_pages_dirty' => 'modified_pages', 'Innodb_buffer_pool_pages_free' => 'free_pages', 'Innodb_buffer_pool_pages_total' => 'pool_size', 'Innodb_data_fsyncs' => 'file_fsyncs', 'Innodb_data_pending_reads' => 'pending_normal_aio_reads', 'Innodb_data_pending_writes' => 'pending_normal_aio_writes', 'Innodb_os_log_pending_fsyncs' => 'pending_log_flushes', 'Innodb_pages_created' => 'pages_created', 'Innodb_pages_read' => 'pages_read', 'Innodb_pages_written' => 'pages_written', 'Innodb_rows_deleted' => 'rows_deleted', 'Innodb_rows_inserted' => 'rows_inserted', 'Innodb_rows_read' => 'rows_read', 'Innodb_rows_updated' => 'rows_updated', 'Innodb_buffer_pool_reads' => 'pool_reads', 'Innodb_buffer_pool_read_requests' => 'pool_read_requests');
# If the SHOW STATUS value exists, override...
foreach ($overrides as $key => $val) {
if (array_key_exists($key, $status)) {
debug("Override {$key}");
$istatus_vals[$val] = $status[$key];
}
}
# Now copy the values into $status.
foreach ($istatus_vals as $key => $val) {
$status[$key] = $istatus_vals[$key];
}
}
# Make table_open_cache backwards-compatible (issue 63).
if (array_key_exists('table_open_cache', $status)) {
$status['table_cache'] = $status['table_open_cache'];
}
# Compute how much of the key buffer is used and unflushed (issue 127).
$status['Key_buf_bytes_used'] = big_sub($status['key_buffer_size'], big_multiply($status['Key_blocks_unused'], $status['key_cache_block_size']));
$status['Key_buf_bytes_unflushed'] = big_multiply($status['Key_blocks_not_flushed'], $status['key_cache_block_size']);
if (array_key_exists('unflushed_log', $status) && $status['unflushed_log']) {
# TODO: I'm not sure what the deal is here; need to debug this. But the
# unflushed log bytes spikes a lot sometimes and it's impossible for it to
# be more than the log buffer.
debug("Unflushed log: {$status['unflushed_log']}");
$status['unflushed_log'] = max($status['unflushed_log'], $status['innodb_log_buffer_size']);
}
# Define the variables to output. I use shortened variable names so maybe
# it'll all fit in 1024 bytes for Cactid and Spine's benefit. Strings must
# have some non-hex characters (non a-f0-9) to avoid a Cacti bug. This list
# must come right after the word MAGIC_VARS_DEFINITIONS. The Perl script
# parses it and uses it as a Perl variable.
$keys = array('Key_read_requests' => 'gg', 'Key_reads' => 'gh', 'Key_write_requests' => 'gi', 'Key_writes' => 'gj', 'history_list' => 'gk', 'innodb_transactions' => 'gl', 'read_views' => 'gm', 'current_transactions' => 'gn', 'locked_transactions' => 'go', 'active_transactions' => 'gp', 'pool_size' => 'gq', 'free_pages' => 'gr', 'database_pages' => 'gs', 'modified_pages' => 'gt', 'pages_read' => 'gu', 'pages_created' => 'gv', 'pages_written' => 'gw', 'file_fsyncs' => 'gx', 'file_reads' => 'gy', 'file_writes' => 'gz', 'log_writes' => 'hg', 'pending_aio_log_ios' => 'hh', 'pending_aio_sync_ios' => 'hi', 'pending_buf_pool_flushes' => 'hj', 'pending_chkp_writes' => 'hk', 'pending_ibuf_aio_reads' => 'hl', 'pending_log_flushes' => 'hm', 'pending_log_writes' => 'hn', 'pending_normal_aio_reads' => 'ho', 'pending_normal_aio_writes' => 'hp', 'ibuf_inserts' => 'hq', 'ibuf_merged' => 'hr', 'ibuf_merges' => 'hs', 'spin_waits' => 'ht', 'spin_rounds' => 'hu', 'os_waits' => 'hv', 'rows_inserted' => 'hw', 'rows_updated' => 'hx', 'rows_deleted' => 'hy', 'rows_read' => 'hz', 'Table_locks_waited' => 'ig', 'Table_locks_immediate' => 'ih', 'Slow_queries' => 'ii', 'Open_files' => 'ij', 'Open_tables' => 'ik', 'Opened_tables' => 'il', 'innodb_open_files' => 'im', 'open_files_limit' => 'in', 'table_cache' => 'io', 'Aborted_clients' => 'ip', 'Aborted_connects' => 'iq', 'Max_used_connections' => 'ir', 'Slow_launch_threads' => 'is', 'Threads_cached' => 'it', 'Threads_connected' => 'iu', 'Threads_created' => 'iv', 'Threads_running' => 'iw', 'max_connections' => 'ix', 'thread_cache_size' => 'iy', 'Connections' => 'iz', 'slave_running' => 'jg', 'slave_stopped' => 'jh', 'Slave_retried_transactions' => 'ji', 'slave_lag' => 'jj', 'Slave_open_temp_tables' => 'jk', 'Qcache_free_blocks' => 'jl', 'Qcache_free_memory' => 'jm', 'Qcache_hits' => 'jn', 'Qcache_inserts' => 'jo', 'Qcache_lowmem_prunes' => 'jp', 'Qcache_not_cached' => 'jq', 'Qcache_queries_in_cache' => 'jr', 'Qcache_total_blocks' => 'js', 'query_cache_size' => 'jt', 'Questions' => 'ju', 'Com_update' => 'jv', 'Com_insert' => 'jw', 'Com_select' => 'jx', 'Com_delete' => 'jy', 'Com_replace' => 'jz', 'Com_load' => 'kg', 'Com_update_multi' => 'kh', 'Com_insert_select' => 'ki', 'Com_delete_multi' => 'kj', 'Com_replace_select' => 'kk', 'Select_full_join' => 'kl', 'Select_full_range_join' => 'km', 'Select_range' => 'kn', 'Select_range_check' => 'ko', 'Select_scan' => 'kp', 'Sort_merge_passes' => 'kq', 'Sort_range' => 'kr', 'Sort_rows' => 'ks', 'Sort_scan' => 'kt', 'Created_tmp_tables' => 'ku', 'Created_tmp_disk_tables' => 'kv', 'Created_tmp_files' => 'kw', 'Bytes_sent' => 'kx', 'Bytes_received' => 'ky', 'innodb_log_buffer_size' => 'kz', 'unflushed_log' => 'lg', 'log_bytes_flushed' => 'lh', 'log_bytes_written' => 'li', 'relay_log_space' => 'lj', 'binlog_cache_size' => 'lk', 'Binlog_cache_disk_use' => 'll', 'Binlog_cache_use' => 'lm', 'binary_log_space' => 'ln', 'innodb_locked_tables' => 'lo', 'innodb_lock_structs' => 'lp', 'State_closing_tables' => 'lq', 'State_copying_to_tmp_table' => 'lr', 'State_end' => 'ls', 'State_freeing_items' => 'lt', 'State_init' => 'lu', 'State_locked' => 'lv', 'State_login' => 'lw', 'State_preparing' => 'lx', 'State_reading_from_net' => 'ly', 'State_sending_data' => 'lz', 'State_sorting_result' => 'mg', 'State_statistics' => 'mh', 'State_updating' => 'mi', 'State_writing_to_net' => 'mj', 'State_none' => 'mk', 'State_other' => 'ml', 'Handler_commit' => 'mm', 'Handler_delete' => 'mn', 'Handler_discover' => 'mo', 'Handler_prepare' => 'mp', 'Handler_read_first' => 'mq', 'Handler_read_key' => 'mr', 'Handler_read_next' => 'ms', 'Handler_read_prev' => 'mt', 'Handler_read_rnd' => 'mu', 'Handler_read_rnd_next' => 'mv', 'Handler_rollback' => 'mw', 'Handler_savepoint' => 'mx', 'Handler_savepoint_rollback' => 'my', 'Handler_update' => 'mz', 'Handler_write' => 'ng', 'innodb_tables_in_use' => 'nh', 'innodb_lock_wait_secs' => 'ni', 'hash_index_cells_total' => 'nj', 'hash_index_cells_used' => 'nk', 'total_mem_alloc' => 'nl', 'additional_pool_alloc' => 'nm', 'uncheckpointed_bytes' => 'nn', 'ibuf_used_cells' => 'no', 'ibuf_free_cells' => 'np', 'ibuf_cell_count' => 'nq', 'adaptive_hash_memory' => 'nr', 'page_hash_memory' => 'ns', 'dictionary_cache_memory' => 'nt', 'file_system_memory' => 'nu', 'lock_system_memory' => 'nv', 'recovery_system_memory' => 'nw', 'thread_hash_memory' => 'nx', 'innodb_sem_waits' => 'ny', 'innodb_sem_wait_time_ms' => 'nz', 'Key_buf_bytes_unflushed' => 'og', 'Key_buf_bytes_used' => 'oh', 'key_buffer_size' => 'oi', 'Innodb_row_lock_time' => 'oj', 'Innodb_row_lock_waits' => 'ok', 'Query_time_count_00' => 'ol', 'Query_time_count_01' => 'om', 'Query_time_count_02' => 'on', 'Query_time_count_03' => 'oo', 'Query_time_count_04' => 'op', 'Query_time_count_05' => 'oq', 'Query_time_count_06' => 'or', 'Query_time_count_07' => 'os', 'Query_time_count_08' => 'ot', 'Query_time_count_09' => 'ou', 'Query_time_count_10' => 'ov', 'Query_time_count_11' => 'ow', 'Query_time_count_12' => 'ox', 'Query_time_count_13' => 'oy', 'Query_time_total_00' => 'oz', 'Query_time_total_01' => 'pg', 'Query_time_total_02' => 'ph', 'Query_time_total_03' => 'pi', 'Query_time_total_04' => 'pj', 'Query_time_total_05' => 'pk', 'Query_time_total_06' => 'pl', 'Query_time_total_07' => 'pm', 'Query_time_total_08' => 'pn', 'Query_time_total_09' => 'po', 'Query_time_total_10' => 'pp', 'Query_time_total_11' => 'pq', 'Query_time_total_12' => 'pr', 'Query_time_total_13' => 'ps', 'wsrep_replicated_bytes' => 'pt', 'wsrep_received_bytes' => 'pu', 'wsrep_replicated' => 'pv', 'wsrep_received' => 'pw', 'wsrep_local_cert_failures' => 'px', 'wsrep_local_bf_aborts' => 'py', 'wsrep_local_send_queue' => 'pz', 'wsrep_local_recv_queue' => 'qg', 'wsrep_cluster_size' => 'qh', 'wsrep_cert_deps_distance' => 'qi', 'wsrep_apply_window' => 'qj', 'wsrep_commit_window' => 'qk', 'wsrep_flow_control_paused' => 'ql', 'wsrep_flow_control_sent' => 'qm', 'wsrep_flow_control_recv' => 'qn', 'pool_reads' => 'qo', 'pool_read_requests' => 'qp');
# Return the output.
$output = array();
foreach ($keys as $key => $short) {
# If the value isn't defined, return -1 which is lower than (most graphs')
# minimum value of 0, so it'll be regarded as a missing value.
$val = isset($status[$key]) ? $status[$key] : -1;
$output[] = "{$short}:{$val}";
}
$result = implode(' ', $output);
if ($fp) {
if (fwrite($fp, $result) === FALSE) {
die("Can't write '{$cache_file}'");
}
fclose($fp);
}
return $result;
}
'read_views' => '1',
'rows_inserted' => '169',
'rows_updated' => '336',
'rows_deleted' => '4',
'rows_read' => '3713963',
'innodb_transactions' => '8799060',
'unpurged_txns' => '28',
'history_list' => '9',
'current_transactions' => '54',
'hash_index_cells_total' => '1155127',
'hash_index_cells_used' => '0',
'total_mem_alloc' => '288139706',
'additional_pool_alloc' => '2118912',
'last_checkpoint' => '3068940702',
'uncheckpointed_bytes' => '0',
'ibuf_used_cells' => '1',
'ibuf_free_cells' => '0',
'ibuf_cell_count' => '2',
'adaptive_hash_memory' => NULL,
'page_hash_memory' => NULL,
'dictionary_cache_memory' => NULL,
'file_system_memory' => NULL,
'lock_system_memory' => NULL,
'recovery_system_memory' => NULL,
'thread_hash_memory' => NULL,
),
'samples/innodb-014.txt'
);
*/
is_deeply(get_innodb_array(file_get_contents('samples/innodb-015.txt')), array('spin_waits' => '134636510', 'spin_rounds' => '388647989', 'os_waits' => '8556235', 'pending_normal_aio_reads' => '1', 'file_reads' => '11270830', 'file_writes' => '396961428', 'file_fsyncs' => '10365267', 'ibuf_inserts' => '20810519', 'ibuf_merged' => '20809068', 'ibuf_merges' => '3467306', 'log_bytes_written' => '1022011878586', 'unflushed_log' => '3960491', 'log_bytes_flushed' => '1022007918095', 'log_writes' => '172229872', 'pool_size' => '2621440', 'free_pages' => '0', 'database_pages' => '2158986', 'modified_pages' => '10421', 'pages_read' => '11286955', 'pages_created' => '972067', 'pages_written' => '238644796', 'read_views' => '7', 'rows_inserted' => '175281166', 'rows_updated' => '0', 'rows_deleted' => '175454578', 'rows_read' => '312925603', 'innodb_transactions' => '1825236616', 'unpurged_txns' => '9960', 'history_list' => '67', 'current_transactions' => '1', 'hash_index_cells_total' => '84999163', 'total_mem_alloc' => '43914362880', 'additional_pool_alloc' => '0', 'last_checkpoint' => '1022005836378', 'uncheckpointed_bytes' => '6042208', 'ibuf_used_cells' => '9', 'ibuf_free_cells' => '3061', 'ibuf_cell_count' => '3071', 'innodb_tables_in_use' => '0', 'innodb_locked_tables' => '0', 'active_transactions' => '0', 'innodb_lock_structs' => '0', 'innodb_sem_waits' => '671', 'innodb_sem_wait_time_ms' => '647000', 'pending_normal_aio_writes' => '0', 'pending_ibuf_aio_reads' => '0', 'pending_aio_log_ios' => '0', 'pending_aio_sync_ios' => '0', 'pending_log_flushes' => '0', 'pending_buf_pool_flushes' => '0', 'pending_log_writes' => '0', 'pending_chkp_writes' => '0', 'queries_inside' => '0', 'queries_queued' => '0', 'hash_index_cells_used' => '0', 'adaptive_hash_memory' => NULL, 'page_hash_memory' => NULL, 'dictionary_cache_memory' => NULL, 'file_system_memory' => NULL, 'lock_system_memory' => NULL, 'recovery_system_memory' => NULL, 'thread_hash_memory' => NULL), 'samples/innodb-015.txt');
function ss_get_mysql_stats($options)
{
# Process connection options and connect to MySQL.
global $debug, $mysql_user, $mysql_pass, $heartbeat, $cache_dir, $poll_time, $chk_options, $mysql_port, $mysql_ssl;
# Connect to MySQL.
$user = isset($options['user']) ? $options['user'] : $mysql_user;
$pass = isset($options['pass']) ? $options['pass'] : $mysql_pass;
$port = isset($options['port']) ? $options['port'] : $mysql_port;
$heartbeat = isset($options['heartbeat']) ? $options['heartbeat'] : $heartbeat;
# If there is a port, or if it's a non-standard port, we add ":$port" to the
# hostname.
$host_str = $options['host'] . (isset($options['port']) || $port != 3306 ? ":{$port}" : '');
debug(array('connecting to', $host_str, $user, $pass));
if (!extension_loaded('mysql')) {
debug("The MySQL extension is not loaded");
die("The MySQL extension is not loaded");
}
if ($mysql_ssl || isset($options['mysql_ssl']) && $options['mysql_ssl']) {
$conn = mysql_connect($host_str, $user, $pass, true, MYSQL_CLIENT_SSL);
} else {
$conn = mysql_connect($host_str, $user, $pass);
}
if (!$conn) {
debug("MySQL connection failed: " . mysql_error());
die("MySQL: " . mysql_error());
}
$sanitized_host = str_replace(array(":", "/"), array("", "_"), $options['host']);
$cache_file = "{$cache_dir}/{$sanitized_host}-mysql_cacti_stats.txt" . (isset($options['port']) || $port != 3306 ? ":{$port}" : '');
debug("Cache file is {$cache_file}");
# First, check the cache.
$fp = null;
if (!isset($options['nocache'])) {
if ($fp = fopen($cache_file, 'a+')) {
$locked = flock($fp, 1);
# LOCK_SH
if ($locked) {
if (filesize($cache_file) > 0 && filectime($cache_file) + $poll_time / 2 > time() && ($arr = file($cache_file))) {
# The cache file is good to use.
debug("Using the cache file");
fclose($fp);
return $arr[0];
} else {
debug("The cache file seems too small or stale");
# Escalate the lock to exclusive, so we can write to it.
if (flock($fp, 2)) {
# LOCK_EX
# We might have blocked while waiting for that LOCK_EX, and
# another process ran and updated it. Let's see if we can just
# return the data now:
if (filesize($cache_file) > 0 && filectime($cache_file) + $poll_time / 2 > time() && ($arr = file($cache_file))) {
# The cache file is good to use.
debug("Using the cache file");
fclose($fp);
return $arr[0];
}
ftruncate($fp, 0);
# Now it's ready for writing later.
}
}
} else {
debug("Couldn't lock the cache file, ignoring it.");
$fp = null;
}
}
} else {
$fp = null;
debug("Couldn't open the cache file");
}
# Set up variables.
$status = array('relay_log_space' => null, 'binary_log_space' => null, 'current_transactions' => 0, 'locked_transactions' => 0, 'active_transactions' => 0, 'innodb_locked_tables' => 0, 'innodb_tables_in_use' => 0, 'innodb_lock_structs' => 0, 'innodb_lock_wait_secs' => 0, 'innodb_sem_waits' => 0, 'innodb_sem_wait_time_ms' => 0, 'State_closing_tables' => null, 'State_copying_to_tmp_table' => null, 'State_end' => null, 'State_freeing_items' => null, 'State_init' => null, 'State_locked' => null, 'State_login' => null, 'State_preparing' => null, 'State_reading_from_net' => null, 'State_sending_data' => null, 'State_sorting_result' => null, 'State_statistics' => null, 'State_updating' => null, 'State_writing_to_net' => null, 'State_none' => null, 'State_other' => null);
# Get SHOW STATUS and convert the name-value array into a simple
# associative array.
$result = run_query("SHOW /*!50002 GLOBAL */ STATUS", $conn);
foreach ($result as $row) {
$status[$row[0]] = $row[1];
}
# Get SHOW VARIABLES and do the same thing, adding it to the $status array.
$result = run_query("SHOW VARIABLES", $conn);
foreach ($result as $row) {
$status[$row[0]] = $row[1];
}
# Get SHOW SLAVE STATUS, and add it to the $status array.
if ($chk_options['slave']) {
$result = run_query("SHOW SLAVE STATUS", $conn);
$slave_status_rows_gotten = 0;
foreach ($result as $row) {
$slave_status_rows_gotten++;
# Must lowercase keys because different MySQL versions have different
# lettercase.
$row = array_change_key_case($row, CASE_LOWER);
$status['relay_log_space'] = $row['relay_log_space'];
$status['slave_lag'] = $row['seconds_behind_master'];
# Check replication heartbeat, if present.
if ($heartbeat) {
$result2 = run_query("SELECT MAX(GREATEST(0, UNIX_TIMESTAMP() - UNIX_TIMESTAMP(ts) - 1))" . " AS delay FROM {$heartbeat}", $conn);
$slave_delay_rows_gotten = 0;
foreach ($result2 as $row2) {
$slave_delay_rows_gotten++;
if ($row2 && is_array($row2) && array_key_exists('delay', $row2)) {
$status['slave_lag'] = $row2['delay'];
} else {
debug("Couldn't get slave lag from {$heartbeat}");
}
}
if ($slave_delay_rows_gotten == 0) {
debug("Got nothing from heartbeat query");
}
}
# Scale slave_running and slave_stopped relative to the slave lag.
$status['slave_running'] = $row['slave_sql_running'] == 'Yes' ? $status['slave_lag'] : 0;
$status['slave_stopped'] = $row['slave_sql_running'] == 'Yes' ? 0 : $status['slave_lag'];
}
if ($slave_status_rows_gotten == 0) {
debug("Got nothing from SHOW SLAVE STATUS");
}
}
# Get SHOW MASTER STATUS, and add it to the $status array.
if ($chk_options['master'] && array_key_exists('log_bin', $status) && $status['log_bin'] == 'ON') {
# See issue #8
$binlogs = array(0);
$result = run_query("SHOW MASTER LOGS", $conn);
foreach ($result as $row) {
$row = array_change_key_case($row, CASE_LOWER);
# Older versions of MySQL may not have the File_size column in the
# results of the command. Zero-size files indicate the user is
# deleting binlogs manually from disk (bad user! bad!).
if (array_key_exists('file_size', $row) && $row['file_size'] > 0) {
$binlogs[] = $row['file_size'];
}
}
if (count($binlogs)) {
$status['binary_log_space'] = to_int(array_sum($binlogs));
}
}
# Get SHOW PROCESSLIST and aggregate it by state, then add it to the array
# too.
if ($chk_options['procs']) {
$result = run_query('SHOW PROCESSLIST', $conn);
foreach ($result as $row) {
$state = $row['State'];
if (is_null($state)) {
$state = 'NULL';
}
if ($state == '') {
$state = 'none';
}
# MySQL 5.5 replaces the 'Locked' state with a variety of "Waiting for
# X lock" types of statuses. Wrap these all back into "Locked" because
# we don't really care about the type of locking it is.
$state = preg_replace('/^(Table lock|Waiting for .*lock)$/', 'Locked', $state);
$state = str_replace(' ', '_', strtolower($state));
if (array_key_exists("State_{$state}", $status)) {
increment($status, "State_{$state}", 1);
} else {
increment($status, "State_other", 1);
}
}
}
# Get SHOW INNODB STATUS and extract the desired metrics from it, then add
# those to the array too.
if ($chk_options['innodb'] && array_key_exists('have_innodb', $status) && $status['have_innodb'] == 'YES') {
$result = run_query("SHOW /*!50000 ENGINE*/ INNODB STATUS", $conn);
$istatus_text = $result[0]['Status'];
$istatus_vals = get_innodb_array($istatus_text);
# Get response time histogram from Percona Server if enabled.
if ($chk_options['get_qrt'] && isset($status['have_response_time_distribution']) && $status['have_response_time_distribution'] == 'YES') {
debug('Getting query time histogram');
$i = 0;
$result = run_query("SELECT `count`, total * 1000000 AS total " . "FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME " . "WHERE `time` <> 'TOO LONG'", $conn);
foreach ($result as $row) {
if ($i > 13) {
# It's possible that the number of rows returned isn't 14.
# Don't add extra status counters.
break;
}
$count_key = sprintf("Query_time_count_%02d", $i);
$total_key = sprintf("Query_time_total_%02d", $i);
$status[$count_key] = $row['count'];
$status[$total_key] = $row['total'];
$i++;
}
# It's also possible that the number of rows returned is too few.
# Don't leave any status counters unassigned; it will break graphs.
while ($i <= 13) {
$count_key = sprintf("Query_time_count_%02d", $i);
$total_key = sprintf("Query_time_total_%02d", $i);
$status[$count_key] = 0;
$status[$total_key] = 0;
$i++;
}
} else {
debug('Not getting time histogram because it is not enabled');
}
# Override values from InnoDB parsing with values from SHOW STATUS,
# because InnoDB status might not have everything and the SHOW STATUS is
# to be preferred where possible.
$overrides = array('Innodb_buffer_pool_pages_data' => 'database_pages', 'Innodb_buffer_pool_pages_dirty' => 'modified_pages', 'Innodb_buffer_pool_pages_free' => 'free_pages', 'Innodb_buffer_pool_pages_total' => 'pool_size', 'Innodb_data_fsyncs' => 'file_fsyncs', 'Innodb_data_pending_reads' => 'pending_normal_aio_reads', 'Innodb_data_pending_writes' => 'pending_normal_aio_writes', 'Innodb_os_log_pending_fsyncs' => 'pending_log_flushes', 'Innodb_pages_created' => 'pages_created', 'Innodb_pages_read' => 'pages_read', 'Innodb_pages_written' => 'pages_written', 'Innodb_rows_deleted' => 'rows_deleted', 'Innodb_rows_inserted' => 'rows_inserted', 'Innodb_rows_read' => 'rows_read', 'Innodb_rows_updated' => 'rows_updated');
# If the SHOW STATUS value exists, override...
foreach ($overrides as $key => $val) {
if (array_key_exists($key, $status)) {
debug("Override {$key}");
$istatus_vals[$val] = $status[$key];
}
}
# Now copy the values into $status.
foreach ($istatus_vals as $key => $val) {
$status[$key] = $istatus_vals[$key];
}
}
# Make table_open_cache backwards-compatible (issue 63).
if (array_key_exists('table_open_cache', $status)) {
$status['table_cache'] = $status['table_open_cache'];
}
# Compute how much of the key buffer is used and unflushed (issue 127).
$status['Key_buf_bytes_used'] = big_sub($status['key_buffer_size'], big_multiply($status['Key_blocks_unused'], $status['key_cache_block_size']));
$status['Key_buf_bytes_unflushed'] = big_multiply($status['Key_blocks_not_flushed'], $status['key_cache_block_size']);
if (array_key_exists('unflushed_log', $status) && $status['unflushed_log']) {
# TODO: I'm not sure what the deal is here; need to debug this. But the
# unflushed log bytes spikes a lot sometimes and it's impossible for it to
# be more than the log buffer.
debug("Unflushed log: {$status['unflushed_log']}");
$status['unflushed_log'] = max($status['unflushed_log'], $status['innodb_log_buffer_size']);
}
# Define the variables to output. I use shortened variable names so maybe
# it'll all fit in 1024 bytes for Cactid and Spine's benefit. Strings must
# have some non-hex characters (non a-f0-9) to avoid a Cacti bug. This list
# must come right after the word MAGIC_VARS_DEFINITIONS. The Perl script
# parses it and uses it as a Perl variable.
$keys = array('Key_read_requests' => 'g0', 'Key_reads' => 'g1', 'Key_write_requests' => 'g2', 'Key_writes' => 'g3', 'history_list' => 'g4', 'innodb_transactions' => 'g5', 'read_views' => 'g6', 'current_transactions' => 'g7', 'locked_transactions' => 'g8', 'active_transactions' => 'g9', 'pool_size' => 'ga', 'free_pages' => 'gb', 'database_pages' => 'gc', 'modified_pages' => 'gd', 'pages_read' => 'ge', 'pages_created' => 'gf', 'pages_written' => 'gg', 'file_fsyncs' => 'gh', 'file_reads' => 'gi', 'file_writes' => 'gj', 'log_writes' => 'gk', 'pending_aio_log_ios' => 'gl', 'pending_aio_sync_ios' => 'gm', 'pending_buf_pool_flushes' => 'gn', 'pending_chkp_writes' => 'go', 'pending_ibuf_aio_reads' => 'gp', 'pending_log_flushes' => 'gq', 'pending_log_writes' => 'gr', 'pending_normal_aio_reads' => 'gs', 'pending_normal_aio_writes' => 'gt', 'ibuf_inserts' => 'gu', 'ibuf_merged' => 'gv', 'ibuf_merges' => 'gw', 'spin_waits' => 'gx', 'spin_rounds' => 'gy', 'os_waits' => 'gz', 'rows_inserted' => 'h0', 'rows_updated' => 'h1', 'rows_deleted' => 'h2', 'rows_read' => 'h3', 'Table_locks_waited' => 'h4', 'Table_locks_immediate' => 'h5', 'Slow_queries' => 'h6', 'Open_files' => 'h7', 'Open_tables' => 'h8', 'Opened_tables' => 'h9', 'innodb_open_files' => 'ha', 'open_files_limit' => 'hb', 'table_cache' => 'hc', 'Aborted_clients' => 'hd', 'Aborted_connects' => 'he', 'Max_used_connections' => 'hf', 'Slow_launch_threads' => 'hg', 'Threads_cached' => 'hh', 'Threads_connected' => 'hi', 'Threads_created' => 'hj', 'Threads_running' => 'hk', 'max_connections' => 'hl', 'thread_cache_size' => 'hm', 'Connections' => 'hn', 'slave_running' => 'ho', 'slave_stopped' => 'hp', 'Slave_retried_transactions' => 'hq', 'slave_lag' => 'hr', 'Slave_open_temp_tables' => 'hs', 'Qcache_free_blocks' => 'ht', 'Qcache_free_memory' => 'hu', 'Qcache_hits' => 'hv', 'Qcache_inserts' => 'hw', 'Qcache_lowmem_prunes' => 'hx', 'Qcache_not_cached' => 'hy', 'Qcache_queries_in_cache' => 'hz', 'Qcache_total_blocks' => 'i0', 'query_cache_size' => 'i1', 'Questions' => 'i2', 'Com_update' => 'i3', 'Com_insert' => 'i4', 'Com_select' => 'i5', 'Com_delete' => 'i6', 'Com_replace' => 'i7', 'Com_load' => 'i8', 'Com_update_multi' => 'i9', 'Com_insert_select' => 'ia', 'Com_delete_multi' => 'ib', 'Com_replace_select' => 'ic', 'Select_full_join' => 'id', 'Select_full_range_join' => 'ie', 'Select_range' => 'if', 'Select_range_check' => 'ig', 'Select_scan' => 'ih', 'Sort_merge_passes' => 'ii', 'Sort_range' => 'ij', 'Sort_rows' => 'ik', 'Sort_scan' => 'il', 'Created_tmp_tables' => 'im', 'Created_tmp_disk_tables' => 'in', 'Created_tmp_files' => 'io', 'Bytes_sent' => 'ip', 'Bytes_received' => 'iq', 'innodb_log_buffer_size' => 'ir', 'unflushed_log' => 'is', 'log_bytes_flushed' => 'it', 'log_bytes_written' => 'iu', 'relay_log_space' => 'iv', 'binlog_cache_size' => 'iw', 'Binlog_cache_disk_use' => 'ix', 'Binlog_cache_use' => 'iy', 'binary_log_space' => 'iz', 'innodb_locked_tables' => 'j0', 'innodb_lock_structs' => 'j1', 'State_closing_tables' => 'j2', 'State_copying_to_tmp_table' => 'j3', 'State_end' => 'j4', 'State_freeing_items' => 'j5', 'State_init' => 'j6', 'State_locked' => 'j7', 'State_login' => 'j8', 'State_preparing' => 'j9', 'State_reading_from_net' => 'ja', 'State_sending_data' => 'jb', 'State_sorting_result' => 'jc', 'State_statistics' => 'jd', 'State_updating' => 'je', 'State_writing_to_net' => 'jf', 'State_none' => 'jg', 'State_other' => 'jh', 'Handler_commit' => 'ji', 'Handler_delete' => 'jj', 'Handler_discover' => 'jk', 'Handler_prepare' => 'jl', 'Handler_read_first' => 'jm', 'Handler_read_key' => 'jn', 'Handler_read_next' => 'jo', 'Handler_read_prev' => 'jp', 'Handler_read_rnd' => 'jq', 'Handler_read_rnd_next' => 'jr', 'Handler_rollback' => 'js', 'Handler_savepoint' => 'jt', 'Handler_savepoint_rollback' => 'ju', 'Handler_update' => 'jv', 'Handler_write' => 'jw', 'innodb_tables_in_use' => 'jx', 'innodb_lock_wait_secs' => 'jy', 'hash_index_cells_total' => 'jz', 'hash_index_cells_used' => 'k0', 'total_mem_alloc' => 'k1', 'additional_pool_alloc' => 'k2', 'uncheckpointed_bytes' => 'k3', 'ibuf_used_cells' => 'k4', 'ibuf_free_cells' => 'k5', 'ibuf_cell_count' => 'k6', 'adaptive_hash_memory' => 'k7', 'page_hash_memory' => 'k8', 'dictionary_cache_memory' => 'k9', 'file_system_memory' => 'ka', 'lock_system_memory' => 'kb', 'recovery_system_memory' => 'kc', 'thread_hash_memory' => 'kd', 'innodb_sem_waits' => 'ke', 'innodb_sem_wait_time_ms' => 'kf', 'Key_buf_bytes_unflushed' => 'kg', 'Key_buf_bytes_used' => 'kh', 'key_buffer_size' => 'ki', 'Innodb_row_lock_time' => 'kj', 'Innodb_row_lock_waits' => 'kk', 'Query_time_count_00' => 'kl', 'Query_time_count_01' => 'km', 'Query_time_count_02' => 'kn', 'Query_time_count_03' => 'ko', 'Query_time_count_04' => 'kp', 'Query_time_count_05' => 'kq', 'Query_time_count_06' => 'kr', 'Query_time_count_07' => 'ks', 'Query_time_count_08' => 'kt', 'Query_time_count_09' => 'ku', 'Query_time_count_10' => 'kv', 'Query_time_count_11' => 'kw', 'Query_time_count_12' => 'kx', 'Query_time_count_13' => 'ky', 'Query_time_total_00' => 'kz', 'Query_time_total_01' => 'la', 'Query_time_total_02' => 'lb', 'Query_time_total_03' => 'lc', 'Query_time_total_04' => 'ld', 'Query_time_total_05' => 'le', 'Query_time_total_06' => 'lf', 'Query_time_total_07' => 'lg', 'Query_time_total_08' => 'lh', 'Query_time_total_09' => 'li', 'Query_time_total_10' => 'lj', 'Query_time_total_11' => 'lk', 'Query_time_total_12' => 'll', 'Query_time_total_13' => 'lm');
# Return the output.
$output = array();
foreach ($keys as $key => $short) {
# If the value isn't defined, return -1 which is lower than (most graphs')
# minimum value of 0, so it'll be regarded as a missing value.
$val = isset($status[$key]) ? $status[$key] : -1;
$output[] = "{$short}:{$val}";
}
$result = implode(' ', $output);
if ($fp) {
if (fwrite($fp, $result) === FALSE) {
die("Can't write '{$cache_file}'");
}
fclose($fp);
}
return $result;
}
'rows_inserted' => '169',
'rows_updated' => '336',
'rows_deleted' => '4',
'rows_read' => '3713963',
'innodb_transactions' => '8799060',
'unpurged_txns' => '28',
'history_list' => '9',
'current_transactions' => '54',
'hash_index_cells_total' => '1155127',
'hash_index_cells_used' => '0',
'total_mem_alloc' => '288139706',
'additional_pool_alloc' => '2118912',
'last_checkpoint' => '3068940702',
'uncheckpointed_bytes' => '0',
'ibuf_used_cells' => '1',
'ibuf_free_cells' => '0',
'ibuf_cell_count' => '2',
'adaptive_hash_memory' => NULL,
'page_hash_memory' => NULL,
'dictionary_cache_memory' => NULL,
'file_system_memory' => NULL,
'lock_system_memory' => NULL,
'recovery_system_memory' => NULL,
'thread_hash_memory' => NULL,
),
'samples/innodb-014.txt'
);
*/
is_deeply(get_innodb_array(file_get_contents('samples/innodb-015.txt')), array('spin_waits' => '134636510', 'spin_rounds' => '388647989', 'os_waits' => '8556235', 'pending_normal_aio_reads' => '1', 'file_reads' => '11270830', 'file_writes' => '396961428', 'file_fsyncs' => '10365267', 'ibuf_inserts' => '20810519', 'ibuf_merged' => '20809068', 'ibuf_merges' => '3467306', 'log_bytes_written' => '1022011878586', 'unflushed_log' => '3960491', 'log_bytes_flushed' => '1022007918095', 'log_writes' => '172229872', 'pool_size' => '2621440', 'free_pages' => '0', 'database_pages' => '2158986', 'modified_pages' => '10421', 'pages_read' => '11286955', 'pages_created' => '972067', 'pages_written' => '238644796', 'read_views' => '7', 'rows_inserted' => '175281166', 'rows_updated' => '0', 'rows_deleted' => '175454578', 'rows_read' => '312925603', 'innodb_transactions' => '1825236616', 'unpurged_txns' => '9960', 'history_list' => '67', 'current_transactions' => '1', 'hash_index_cells_total' => '84999163', 'total_mem_alloc' => '43914362880', 'additional_pool_alloc' => '0', 'last_checkpoint' => '1022005836378', 'uncheckpointed_bytes' => '6042208', 'ibuf_used_cells' => '9', 'ibuf_free_cells' => '3061', 'ibuf_cell_count' => '3071', 'innodb_tables_in_use' => '0', 'innodb_locked_tables' => '0', 'active_transactions' => '0', 'innodb_lock_structs' => '0', 'innodb_sem_waits' => '671', 'innodb_sem_wait_time_ms' => '647000', 'pending_normal_aio_writes' => '0', 'pending_ibuf_aio_reads' => '0', 'pending_aio_log_ios' => '0', 'pending_aio_sync_ios' => '0', 'pending_log_flushes' => '0', 'pending_buf_pool_flushes' => '0', 'pending_log_writes' => '0', 'pending_chkp_writes' => '0', 'queries_inside' => '0', 'queries_queued' => '0', 'hash_index_cells_used' => '0', 'adaptive_hash_memory' => NULL, 'page_hash_memory' => NULL, 'dictionary_cache_memory' => NULL, 'file_system_memory' => NULL, 'lock_system_memory' => NULL, 'recovery_system_memory' => NULL, 'thread_hash_memory' => NULL), 'samples/innodb-015.txt');
is_deeply(get_innodb_array(file_get_contents('samples/innodb-016.txt')), array('spin_waits' => '15', 'spin_rounds' => '11', 'os_waits' => '108', 'pending_normal_aio_reads' => '0', 'file_reads' => '153', 'file_writes' => '3', 'file_fsyncs' => '3', 'ibuf_inserts' => '4', 'ibuf_merged' => '17', 'ibuf_merges' => '9', 'log_bytes_written' => '1595675', 'unflushed_log' => '0', 'log_bytes_flushed' => '1595675', 'log_writes' => '8', 'pool_size' => '8192', 'free_pages' => '8050', 'database_pages' => '142', 'modified_pages' => '0', 'pages_read' => '142', 'pages_created' => '0', 'pages_written' => '0', 'read_views' => '1', 'rows_inserted' => '0', 'rows_updated' => '0', 'rows_deleted' => '0', 'rows_read' => '0', 'innodb_transactions' => '1280', 'unpurged_txns' => '1280', 'history_list' => '0', 'current_transactions' => '1', 'hash_index_cells_total' => '276707', 'total_mem_alloc' => '137363456', 'additional_pool_alloc' => '0', 'last_checkpoint' => '1595675', 'uncheckpointed_bytes' => '0', 'ibuf_used_cells' => '1', 'ibuf_free_cells' => '0', 'ibuf_cell_count' => '2', 'innodb_tables_in_use' => '0', 'innodb_locked_tables' => '0', 'active_transactions' => '0', 'innodb_lock_structs' => '0', 'innodb_sem_waits' => null, 'innodb_sem_wait_time_ms' => null, 'pending_normal_aio_writes' => '0', 'pending_ibuf_aio_reads' => '0', 'pending_aio_log_ios' => '0', 'pending_aio_sync_ios' => '0', 'pending_log_flushes' => '0', 'pending_buf_pool_flushes' => '0', 'pending_log_writes' => '0', 'pending_chkp_writes' => '0', 'queries_inside' => '0', 'queries_queued' => '0', 'hash_index_cells_used' => '0', 'adaptive_hash_memory' => NULL, 'page_hash_memory' => NULL, 'dictionary_cache_memory' => NULL, 'file_system_memory' => NULL, 'lock_system_memory' => NULL, 'recovery_system_memory' => NULL, 'thread_hash_memory' => NULL), 'samples/innodb-016.txt');
