diff --git a/htdocs/sql/server_status.php b/htdocs/sql/server_status.php new file mode 100755 --- /dev/null +++ b/htdocs/sql/server_status.php @@ -0,0 +1,690 @@ +' . "\n"; +echo '

' . "\n" + . ($GLOBALS['cfg']['MainPageIconic'] + ? '' + : '') + . $strServerStatus . "\n" + . '

' . "\n"; + + +/** + * flush status variables if requested + */ +if (isset($_REQUEST['flush'])) { + $_flush_commands = array( + 'STATUS', + 'TABLES', + 'QUERY CACHE', + ); + + if (in_array($_REQUEST['flush'], $_flush_commands)) { + PMA_DBI_query('FLUSH ' . $_REQUEST['flush'] . ';'); + } + unset($_flush_commands); +} + + +/** + * get status from server + */ +if (PMA_MYSQL_INT_VERSION >= 50002) { + $server_status = PMA_DBI_fetch_result('SHOW GLOBAL STATUS', 0, 1); +} else { + $server_status = PMA_DBI_fetch_result('SHOW STATUS', 0, 1); +} + + +/** + * for some calculations we require also some server settings + */ +if (PMA_MYSQL_INT_VERSION >= 40003) { + $server_variables = PMA_DBI_fetch_result('SHOW GLOBAL VARIABLES', 0, 1); +} else { + $server_variables = PMA_DBI_fetch_result('SHOW VARIABLES', 0, 1); +} + + +/** + * starttime calculation + */ +$start_time = PMA_DBI_fetch_value( + 'SELECT UNIX_TIMESTAMP() - ' . $server_status['Uptime']); + + +/** + * cleanup some deprecated values + */ +$deprecated = array( + 'Com_prepare_sql' => 'Com_stmt_prepare', + 'Com_execute_sql' => 'Com_stmt_execute', + 'Com_dealloc_sql' => 'Com_stmt_close', +); + +foreach ($deprecated as $old => $new) { + if (isset($server_status[$old]) + && isset($server_status[$new])) { + unset($server_status[$old]); + } +} +unset($deprecated); + + +/** + * calculate some values + */ +// Key_buffer_fraction +if (isset($server_status['Key_blocks_unused']) + && isset($server_variables['key_cache_block_size']) + && isset($server_variables['key_buffer_size'])) { + $server_status['Key_buffer_fraction_%'] = + 100 + - $server_status['Key_blocks_unused'] + * $server_variables['key_cache_block_size'] + / $server_variables['key_buffer_size'] + * 100; +} elseif ( + isset($server_status['Key_blocks_used']) + && isset($server_variables['key_buffer_size'])) { + $server_status['Key_buffer_fraction_%'] = + $server_status['Key_blocks_used'] + * 1024 + / $server_variables['key_buffer_size']; + } + +// Ratio for key read/write +if (isset($server_status['Key_writes']) + && isset($server_status['Key_write_requests']) + && $server_status['Key_write_requests'] > 0) + $server_status['Key_write_ratio_%'] = 100 * $server_status['Key_writes'] / $server_status['Key_write_requests']; + +if (isset($server_status['Key_reads']) + && isset($server_status['Key_read_requests']) + && $server_status['Key_read_requests'] > 0) + $server_status['Key_read_ratio_%'] = 100 * $server_status['Key_reads'] / $server_status['Key_read_requests']; + +// Threads_cache_hitrate +if (isset($server_status['Threads_created']) + && isset($server_status['Connections']) + && $server_status['Connections'] > 0) { + $server_status['Threads_cache_hitrate_%'] = + 100 + - $server_status['Threads_created'] + / $server_status['Connections'] + * 100; +} + + +/** + * define some alerts + */ +// name => max value before alert +$alerts = array( + // lower is better + // variable => max value + 'Aborted_clients' => 0, + 'Aborted_connects' => 0, + + 'Binlog_cache_disk_use' => 0, + + 'Created_tmp_disk_tables' => 0, + + 'Handler_read_rnd' => 0, + 'Handler_read_rnd_next' => 0, + + 'Innodb_buffer_pool_pages_dirty' => 0, + 'Innodb_buffer_pool_reads' => 0, + 'Innodb_buffer_pool_wait_free' => 0, + 'Innodb_log_waits' => 0, + 'Innodb_row_lock_time_avg' => 10, // ms + 'Innodb_row_lock_time_max' => 50, // ms + 'Innodb_row_lock_waits' => 0, + + 'Slow_queries' => 0, + 'Delayed_errors' => 0, + 'Select_full_join' => 0, + 'Select_range_check' => 0, + 'Sort_merge_passes' => 0, + 'Opened_tables' => 0, + 'Table_locks_waited' => 0, + 'Qcache_lowmem_prunes' => 0, + 'Slow_launch_threads' => 0, + + // depends on Key_read_requests + // normaly lower then 1:0.01 + 'Key_reads' => (0.01 * $server_status['Key_read_requests']), + // depends on Key_write_requests + // normaly nearly 1:1 + 'Key_writes' => (0.9 * $server_status['Key_write_requests']), + + 'Key_buffer_fraction' => 0.5, + + // alert if more than 95% of thread cache is in use + 'Threads_cached' => 0.95 * $server_variables['thread_cache_size'] + + // higher is better + // variable => min value + //'Handler read key' => '> ', +); + + +/** + * split variables in sections + */ +$allocations = array( + // variable name => section + + 'Com_' => 'com', + 'Innodb_' => 'innodb', + 'Ndb_' => 'ndb', + 'Ssl_' => 'ssl', + 'Handler_' => 'handler', + 'Qcache_' => 'qcache', + 'Threads_' => 'threads', + 'Slow_launch_threads' => 'threads', + + 'Binlog_cache_' => 'binlog_cache', + 'Created_tmp_' => 'created_tmp', + 'Key_' => 'key', + + 'Delayed_' => 'delayed', + 'Not_flushed_delayed_rows' => 'delayed', + + 'Flush_commands' => 'query', + 'Last_query_cost' => 'query', + 'Slow_queries' => 'query', + + 'Select_' => 'select', + 'Sort_' => 'sort', + + 'Open_tables' => 'table', + 'Opened_tables' => 'table', + 'Table_locks_' => 'table', + + 'Rpl_status' => 'repl', + 'Slave_' => 'repl', + + 'Tc_' => 'tc', +); + +$sections = array( + // section => section name (description) + 'com' => array('title' => ''), + 'query' => array('title' => $strSQLQuery), + 'innodb' => array('title' => 'InnoDB'), + 'ndb' => array('title' => 'NDB'), + 'ssl' => array('title' => 'SSL'), + 'handler' => array('title' => $strHandler), + 'qcache' => array('title' => $strQueryCache), + 'threads' => array('title' => $strThreads), + 'binlog_cache' => array('title' => $strBinaryLog), + 'created_tmp' => array('title' => $strTempData), + 'delayed' => array('title' => $strServerStatusDelayedInserts), + 'key' => array('title' => $strKeyCache), + 'select' => array('title' => $strJoins), + 'repl' => array('title' => $strReplication), + 'sort' => array('title' => $strSorting), + 'table' => array('title' => $strNumTables), + 'tc' => array('title' => $strTransactionCoordinator), +); + + +/** + * define some needfull links/commands + */ +// variable or section name => (name => url) +$links = array(); + +$links['table'][$strFlushTables] + = $PMA_PHP_SELF . '?flush=TABLES&' . PMA_generate_common_url(); +$links['table'][$strShowOpenTables] + = 'sql.php?sql_query=' . urlencode('SHOW OPEN TABLES') . + '&goto=server_status.php&' . PMA_generate_common_url(); + +$links['repl'][$strShowSlaveHosts] + = 'sql.php?sql_query=' . urlencode('SHOW SLAVE HOSTS') . + '&goto=server_status.php&' . PMA_generate_common_url(); +$links['repl'][$strShowSlaveStatus] + = 'sql.php?sql_query=' . urlencode('SHOW SLAVE STATUS') . + '&goto=server_status.php&' . PMA_generate_common_url(); +$links['repl']['doc'] = 'replication'; + +$links['qcache'][$strFlushQueryCache] + = $PMA_PHP_SELF . '?flush=' . urlencode('QUERY CACHE') . '&' . + PMA_generate_common_url(); +$links['qcache']['doc'] = 'query_cache'; + +$links['threads'][$strMySQLShowProcess] + = 'server_processlist.php?' . PMA_generate_common_url(); +$links['threads']['doc'] = 'mysql_threads'; + +$links['key']['doc'] = 'myisam_key_cache'; + +$links['binlog_cache']['doc'] = 'binary_log'; + +$links['Slow_queries']['doc'] = 'slow_query_log'; + +$links['innodb'][$strServerTabVariables] + = 'server_engines.php?engine=InnoDB&' . PMA_generate_common_url(); +$links['innodb'][$strInnodbStat] + = 'server_engines.php?engine=InnoDB&page=Status&' . + PMA_generate_common_url(); +$links['innodb']['doc'] = 'innodb'; + + +// sort status vars into arrays +foreach ($server_status as $name => $value) { + if (isset($allocations[$name])) { + $sections[$allocations[$name]]['vars'][$name] = $value; + unset($server_status[$name]); + } else { + foreach ($allocations as $filter => $section) { + if (preg_match('/^' . $filter . '/', $name) + && isset($server_status[$name])) { + unset($server_status[$name]); + $sections[$section]['vars'][$name] = $value; + } + } + } +} +unset($name, $value, $filter, $section, $allocations); + +// rest +$sections['all']['vars'] =& $server_status; + +$hour_factor = 3600 / $server_status['Uptime']; + +/** + * start output + */ +?> + + +

+ +

+ + + +

+ + + + + + + + + + + + + + + + + + + + + + + + + +
ø
+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
ø %
--- ---
0 + ? PMA_formatNumber( + $server_status['Aborted_connects'] * 100 / $server_status['Connections'], + 0, 2) . '%' + : '--- '; ?>
0 + ? PMA_formatNumber( + $server_status['Aborted_clients'] * 100 / $server_status['Connections'], + 0, 2) . '%' + : '--- '; ?>
%
+ +
+ +

+ + + + + + + + + + + + + + + + + + +
ø ø ø
+ +
+ $value) { + $current_table++; + if ($countRows === 0 || $countRows === $rows_per_table) { + $odd_row = true; + if ($countRows === $rows_per_table) { + echo ' ' . "\n"; + echo ' ' . "\n"; + } +?> + + + + + + + + + + + + + + + + + + + +
ø %
%
+
+ +
+ $section) { + if (! empty($section['vars'])) { +?> + + + + + + + + + + + + + + + + + + + + + $value) { + $odd_row = !$odd_row; +?> + + + + + + + +
+ + ' + : ''); ?> + + +
+ $link_url) { + if ('doc' == $link_name) { + echo PMA_showMySQLDocu($link_url, $link_url); + } else { + echo '' . $link_name . '' . "\n"; + } + } + unset($link_url, $link_name); +?> +
$alerts[$name]) { + echo ''; + } else { + echo ''; + } + } + if ('%' === substr($name, -1, 1)) { + echo PMA_formatNumber($value, 0, 2) . ' %'; + } elseif (is_numeric($value) && $value == (int) $value) { + echo PMA_formatNumber($value, 4, 0); + } elseif (is_numeric($value)) { + echo PMA_formatNumber($value, 4, 2); + } else { + echo htmlspecialchars($value); + } + if (isset($alerts[$name])) { + echo ''; + } + ?> + $link_url) { + if ('doc' == $link_name) { + echo PMA_showMySQLDocu($link_url, $link_url); + } else { + echo ' ' . $link_name . '' . + "\n"; + } + } + unset($link_url, $link_name); + } + ?> +
+ +
+ +