<?php
/* $Id: server_status.php,v 2.21 2005/11/18 12:50:49 cybot_tm Exp $ */
// vim: expandtab sw=4 ts=4 sts=4:
/**
 * displays status variables with descriptions and some hints an optmizing
 *  + reset status vriales
 */

require_once('./libraries/common.lib.php');

/**
 * Does the common work
 */
require './server_common.inc.php';


/**
 * Displays the links
 */
require './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_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'];
}
// Threads_cache_hitrate
if ( 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&amp;' . PMA_generate_common_url();
$links['table'][$strShowOpenTables]
    = 'sql.php?sql_query=' . urlencode('SHOW OPEN TABLES') . 
      '&amp;goto=server_status.php&amp;' . PMA_generate_common_url();

$links['repl'][$strShowSlaveHosts]
    = 'sql.php?sql_query=' . urlencode('SHOW SLAVE HOSTS') . 
      '&amp;goto=server_status.php&amp;' . PMA_generate_common_url();
$links['repl'][$strShowSlaveStatus]
    = 'sql.php?sql_query=' . urlencode('SHOW SLAVE STATUS') . 
      '&amp;goto=server_status.php&amp;' . PMA_generate_common_url();
$links['repl']['MySQL - ' . $strDocu]
    = $cfg['MySQLManualBase'] . '/replication.html';

$links['qcache'][$strFlushQueryCache]
    = $PHP_SELF . '?flush=QUERY CACHE&amp;' . 
      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&amp;' . PMA_generate_common_url();
$links['innodb'][$strInnodbStat]
    = 'server_engines.php?engine=innodb&amp;page=status&amp;' . 
      PMA_generate_common_url();
$links['innodb']['MySQL - ' . $strDocu]
    = $cfg['MySQLManualBase'] . '/innodb.html';


// 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 . '°i', $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&amp;' . 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>
<?php
echo sprintf( $strServerStatusUptime, 
    PMA_timespanFormat( $server_status['Uptime'] ),
    PMA_localisedDate( $start_time ) ) . "\n";
?>
</p>

<div id="sectionlinks">
<?php
foreach ( $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; ?></th>
    <th>&oslash; <?php echo $strPerHour; ?></th>
</tr>
</thead>
<tbody>
<tr class="odd">
    <th class="name"><?php echo $strReceived; ?></th>
    <td class="value"><?php echo
        implode(' ',
            PMA_formatByteDown( $server_status['Bytes_received'], 3 ) ); ?></td>
    <td class="value"><?php echo
        implode(' ',
            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 echo
        implode(' ',
            PMA_formatByteDown( $server_status['Bytes_sent'], 3 ) ); ?></td>
    <td class="value"><?php echo
        implode(' ',
            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 echo
        implode(' ', 
            PMA_formatByteDown(
                $server_status['Bytes_received'] + $server_status['Bytes_sent'], 3 )
        ); ?></td>
    <td class="value"><?php echo
        implode(' ', 
            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>&oslash; <?php echo $strPerHour; ?></th>
    <th>%</th>
</tr>
</thead>
<tbody>
<tr class="odd">
    <th class="name"><?php echo $strMaxConnects; ?></th>
    <td class="value"><?php echo 
        number_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 echo 
        PMA_formatNumber( $server_status['Aborted_connects'], 4, 0 ); ?></td>
    <td class="value"><?php echo 
        PMA_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 echo 
        PMA_formatNumber( $server_status['Aborted_clients'], 4, 0 ); ?></td>
    <td class="value"><?php echo 
        PMA_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 echo 
        PMA_formatNumber( $server_status['Connections'], 4, 0 ); ?></td>
    <td class="value"><?php echo 
        PMA_formatNumber( $server_status['Connections'] * $hour_factor,
            4, 2 ); ?></td>
    <td class="value"><?php echo 
        number_format( 100, 2, $GLOBALS['number_decimal_separator'],
            $GLOBALS['number_thousands_separator'] ); ?>%</td>
</tr>
</tbody>
</table>

<hr class="clearfloat" />

<h3><?php echo
    sprintf( $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>&oslash; <?php echo $strPerHour; ?></th>
    <th>&oslash; <?php echo $strPerMinute; ?></th>
    <th>&oslash; <?php echo $strPerSecond; ?></th>
</tr>
</thead>
<tbody>
<tr class="odd">
    <td class="value"><?php echo 
        PMA_formatNumber( $server_status['Questions'], 3, 0 ); ?></td>
    <td class="value"><?php echo 
        PMA_formatNumber( $server_status['Questions'] * $hour_factor,
            3, 2 ); ?></td>
    <td class="value"><?php echo 
        PMA_formatNumber(
            $server_status['Questions'] * 60 / $server_status['Uptime'],
            3, 2 ); ?></td>
    <td class="value"><?php echo 
        PMA_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>&oslash; <?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 echo
                PMA_formatNumber( $value * $hour_factor, 3, 2 ); ?></td>
            <td class="value"><?php echo
                number_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 variables
unset(
    $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>
<?php
if ( ! 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>
<?php
        if ( ! empty( $links[$section_name] ) ) {
?>
    <tfoot>
        <tr class="tblFooters">
            <th colspan="3" class="tblFooters">
<?php
            foreach ( $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"><?php
            if ( 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">
            <?php
            if ( 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 './footer.inc.php';
?>
