<?php/* $Id: server_status.php,v 2.28 2006/01/25 08:16:40 cybot_tm Exp $ */// vim: expandtab sw=4 ts=4 sts=4:/*** displays status variables with descriptions and some hints an optmizing* + reset status variables*/if ( ! defined( 'PMA_NO_VARIABLES_IMPORT' ) ) {define( 'PMA_NO_VARIABLES_IMPORT', true );}require_once('./libraries/common.lib.php');/*** Does the common work*/require './libraries/server_common.inc.php';/*** Displays the links*/require './libraries/server_links.inc.php';/*** Displays the sub-page heading*/echo '<a name="_top"></a>' . "\n";echo '<div id="serverstatus">' . "\n";echo '<h2>' . "\n". ($GLOBALS['cfg']['MainPageIconic']? '<img class="icon" src="' . $GLOBALS['pmaThemeImage'] .'s_status.png" width="16" height="16" alt="" />': '' ). $strServerStatus . "\n". '</h2>' . "\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_fractionif ( 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'];}// Threads_cache_hitrateif ( isset( $server_status['Threads_created'] )&& isset( $server_status['Connections'] ) ) {$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' => '' ),'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]= $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']['MySQL - ' . $strDocu]= $cfg['MySQLManualBase'] . '/replication.html';$links['qcache'][$strFlushQueryCache]= $PHP_SELF . '?flush=' . urlencode('QUERY CACHE') . '&' .PMA_generate_common_url();$links['qcache']['MySQL - ' . $strDocu]= $cfg['MySQLManualBase'] . '/query-cache.html';$links['threads'][$strMySQLShowProcess]= 'server_processlist.php?' . PMA_generate_common_url();$links['threads']['MySQL - ' . $strDocu]= $cfg['MySQLManualBase'] . '/mysql-threads.html';$links['key']['MySQL - ' . $strDocu]= $cfg['MySQLManualBase'] . '/myisam-key-cache.html';$links['slow_queries']['MySQL - ' . $strDocu]= $cfg['MySQLManualBase'] . '/slow-query-log.html';$links['binlog_cache']['MySQL - ' . $strDocu]= $cfg['MySQLManualBase'] . '/binary-log.html';$links['Slow_queries']['MySQL - ' . $strDocu]= $cfg['MySQLManualBase'] . '/slow-query-log.html';$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']['MySQL - ' . $strDocu]= $cfg['MySQLManualBase'] . '/innodb.html';// sort status vars into arraysforeach ( $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*/?><div id="statuslinks"><a href="<?php echo$PHP_SELF . '?' . PMA_generate_common_url(); ?>"><?php echo $strRefresh; ?></a><a href="<?php echo$PHP_SELF . '?flush=STATUS&' . PMA_generate_common_url(); ?>"><?php echo $strShowStatusReset; ?></a><a href="<?php echo$cfg['MySQLManualBase']; ?>/server-status-variables.html"target="documentation">MySQL - <?php echo $strDocu; ?></a></div><p><?phpecho sprintf( $strServerStatusUptime,PMA_timespanFormat( $server_status['Uptime'] ),PMA_localisedDate( $start_time ) ) . "\n";?></p><div id="sectionlinks"><?phpforeach ( $sections as $section_name => $section ) {if ( ! empty( $section['vars'] ) && ! empty( $section['title'] ) ) {echo '<a href="' . $PHP_SELF . '?' .PMA_generate_common_url() . '#' . $section_name . '">' .$section['title'] . '</a>' . "\n";}}?></div><h3><?php echo $strServerTrafficNotes; ?></h3><table id="serverstatustraffic" class="data"><thead><tr><th colspan="2"><?php echo $strTraffic . ' ' . PMA_showHint($strStatisticsOverrun); ?></th><th>ø <?php echo $strPerHour; ?></th></tr></thead><tbody><tr class="odd"><th class="name"><?php echo $strReceived; ?></th><td class="value"><?php echoimplode(' ',PMA_formatByteDown( $server_status['Bytes_received'], 3 ) ); ?></td><td class="value"><?php echoimplode(' ',PMA_formatByteDown($server_status['Bytes_received'] * $hour_factor, 3 ) ); ?></td></tr><tr class="even"><th class="name"><?php echo $strSent; ?></th><td class="value"><?php echoimplode(' ',PMA_formatByteDown( $server_status['Bytes_sent'], 3 ) ); ?></td><td class="value"><?php echoimplode(' ',PMA_formatByteDown($server_status['Bytes_sent'] * $hour_factor, 3 ) ); ?></td></tr><tr class="odd"><th class="name"><?php echo $strTotalUC; ?></th><td class="value"><?php echoimplode(' ',PMA_formatByteDown($server_status['Bytes_received'] + $server_status['Bytes_sent'], 3 )); ?></td><td class="value"><?php echoimplode(' ',PMA_formatByteDown(($server_status['Bytes_received'] + $server_status['Bytes_sent'])* $hour_factor, 3 )); ?></td></tr></tbody></table><table id="serverstatusconnections" class="data"><thead><tr><th colspan="2"><?php echo $strConnections; ?></th><th>ø <?php echo $strPerHour; ?></th><th>%</th></tr></thead><tbody><tr class="odd"><th class="name"><?php echo $strMaxConnects; ?></th><td class="value"><?php echonumber_format( $server_status['Max_used_connections'], 0,$GLOBALS['number_decimal_separator'],$GLOBALS['number_thousands_separator']); ?> </td><td class="value">--- </td><td class="value">--- </td></tr><tr class="even"><th class="name"><?php echo $strFailedAttempts; ?></th><td class="value"><?php echoPMA_formatNumber( $server_status['Aborted_connects'], 4, 0 ); ?></td><td class="value"><?php echoPMA_formatNumber( $server_status['Aborted_connects'] * $hour_factor,4, 2 ); ?></td><td class="value"><?php echo$server_status['Connections'] > 0? number_format($server_status['Aborted_connects'] * 100 / $server_status['Connections'],2, $GLOBALS['number_decimal_separator'],$GLOBALS['number_thousands_separator'] ) . '%': '--- '; ?></td></tr><tr class="odd"><th class="name"><?php echo $strAbortedClients; ?></th><td class="value"><?php echoPMA_formatNumber( $server_status['Aborted_clients'], 4, 0 ); ?></td><td class="value"><?php echoPMA_formatNumber( $server_status['Aborted_clients'] * $hour_factor,4, 2 ); ?></td><td class="value"><?php echo$server_status['Connections'] > 0? number_format($server_status['Aborted_clients'] * 100 / $server_status['Connections'],2, $GLOBALS['number_decimal_separator'],$GLOBALS['number_thousands_separator']) . '%': '--- '; ?></td></tr><tr class="even"><th class="name"><?php echo $strTotalUC; ?></th><td class="value"><?php echoPMA_formatNumber( $server_status['Connections'], 4, 0 ); ?></td><td class="value"><?php echoPMA_formatNumber( $server_status['Connections'] * $hour_factor,4, 2 ); ?></td><td class="value"><?php echonumber_format( 100, 2, $GLOBALS['number_decimal_separator'],$GLOBALS['number_thousands_separator'] ); ?>%</td></tr></tbody></table><hr class="clearfloat" /><h3><?php echosprintf( $strQueryStatistics,number_format($server_status['Questions'],0, $GLOBALS['number_decimal_separator'],$GLOBALS['number_thousands_separator'] ) ); ?></h3><table id="serverstatusqueriessummary" class="data"><thead><tr><th><?php echo $strTotalUC; ?></th><th>ø <?php echo $strPerHour; ?></th><th>ø <?php echo $strPerMinute; ?></th><th>ø <?php echo $strPerSecond; ?></th></tr></thead><tbody><tr class="odd"><td class="value"><?php echoPMA_formatNumber( $server_status['Questions'], 3, 0 ); ?></td><td class="value"><?php echoPMA_formatNumber( $server_status['Questions'] * $hour_factor,3, 2 ); ?></td><td class="value"><?php echoPMA_formatNumber($server_status['Questions'] * 60 / $server_status['Uptime'],3, 2 ); ?></td><td class="value"><?php echoPMA_formatNumber($server_status['Questions'] / $server_status['Uptime'],3, 2 ); ?></td></tr></tbody></table><div id="serverstatusqueriesdetails"><?php// number of tables to split values into$tables = 2;$rows_per_table = (int) ceil( count( $sections['com']['vars'] ) / $tables );$current_table = 0;$odd_row = true;$countRows = 0;$perc_factor = 100 / ( $server_status['Questions'] - $server_status['Connections'] );foreach ( $sections['com']['vars'] as $name => $value ) {$current_table++;if ( $countRows === 0 || $countRows === $rows_per_table ) {$odd_row = true;if ( $countRows === $rows_per_table ) {echo ' </tbody>' . "\n";echo ' </table>' . "\n";}?><table id="serverstatusqueriesdetails<?php echo $current_table; ?>" class="data"><col class="namecol" /><col class="valuecol" span="3" /><thead><tr><th colspan="2"><?php echo $strQueryType; ?></th><th>ø <?php echo $strPerHour; ?></th><th>%</th></tr></thead><tbody><?php} else {$odd_row = !$odd_row;}$countRows++;// For the percentage column, use Questions - Connections, because// the number of connections is not an item of the Query types// but is included in Questions. Then the total of the percentages is 100.$name = str_replace( 'Com_', '', $name );$name = str_replace( '_', ' ', $name );?><tr class="<?php echo $odd_row ? 'odd' : 'even'; ?>"><th class="name"><?php echo htmlspecialchars( $name ); ?></th><td class="value"><?php echo PMA_formatNumber( $value, 3, 0 ); ?></td><td class="value"><?php echoPMA_formatNumber( $value * $hour_factor, 3, 2 ); ?></td><td class="value"><?php echonumber_format( $value * $perc_factor, 2,$GLOBALS['number_decimal_separator'],$GLOBALS['number_thousands_separator'] ); ?>%</td></tr><?php}?></tbody></table></div><div id="serverstatussection"><?php//Unset used variablesunset($tables, $rows_per_table, $current_table, $countRows, $perc_factor,$hour_factor, $sections['com'],$server_status['Aborted_clients'], $server_status['Aborted_connects'],$server_status['Max_used_connections'], $server_status['Bytes_received'],$server_status['Bytes_sent'], $server_status['Connections'],$server_status['Questions'], $server_status['Uptime']);foreach ( $sections as $section_name => $section ) {if ( ! empty( $section['vars'] ) ) {?><table class="data" id="serverstatussection<?php echo $section_name; ?>"><caption class="tblHeaders"><a class="top"href="<?php echo $PHP_SELF . '?' .PMA_generate_common_url() . '#_top'; ?>"name="<?php echo $section_name; ?>"><?php echo $strPos1; ?><?php echo($GLOBALS['cfg']['MainPageIconic']? '<img src="' . $GLOBALS['pmaThemeImage'] .'s_asc.png" width="11" height="9" align="middle" alt="" />': '' ); ?></a><?phpif ( ! empty( $section['title'] ) ) {echo $section['title'];}?></caption><col class="namecol" /><col class="valuecol" /><col class="descrcol" /><thead><tr><th><?php echo $strVar; ?></th><th><?php echo $strValue; ?></th><th><?php echo $strDescription; ?></th></tr></thead><?phpif ( ! empty( $links[$section_name] ) ) {?><tfoot><tr class="tblFooters"><th colspan="3" class="tblFooters"><?phpforeach ( $links[$section_name] as $link_name => $link_url ) {echo '<a href="' . $link_url . '">' . $link_name . '</a>' . "\n";}unset( $link_url, $link_name );?></th></tr></tfoot><?php}?><tbody><?php$odd_row = false;foreach ( $section['vars'] as $name => $value ) {$odd_row = !$odd_row;?><tr class="<?php echo $odd_row ? 'odd' : 'even'; ?>"><th class="name"><?php echo htmlspecialchars($name); ?></th><td class="value"><?phpif ( isset( $alerts[$name] ) ) {if ( $value > $alerts[$name] ) {echo '<span class="attention">';} else {echo '<span class="allfine">';}}if ( '%' === substr( $name, -1, 1 ) ) {echo number_format( $value, 2,$GLOBALS['number_decimal_separator'],$GLOBALS['number_thousands_separator'] ) . ' %';} elseif ( is_numeric( $value ) && $value == (int) $value ) {echo PMA_formatNumber( $value, 3, 0 );} elseif ( is_numeric( $value ) ) {echo PMA_formatNumber( $value, 3, 2 );} else {echo htmlspecialchars( $value );}if ( isset( $alerts[$name] ) ) {echo '</span>';}?></td><td class="descr"><?phpif ( isset( $GLOBALS['strShowStatus' . $name . 'Descr'] ) ) {echo $GLOBALS['strShowStatus' . $name . 'Descr'];}if ( isset( $links[$name] ) ) {foreach ( $links[$name] as $link_name => $link_url ) {echo ' <a href="' . $link_url . '">' . $link_name . '</a>' ."\n";}unset( $link_url, $link_name );}?></td></tr><?php}unset( $name, $value );?></tbody></table><?php}}unset( $section_name, $section, $sections, $server_status, $odd_row, $alerts );?></div></div><?php/*** Sends the footer*/require_once './libraries/footer.inc.php';?>