remove todo, after upgrade PHPExcel
[phpmyadmin/arisferyanto.git] / server_status.php
blob9c8fe015d17a73ba4a92f5648efcfb022592afdf
1 <?php
2 /* vim: set expandtab sw=4 ts=4 sts=4: */
3 /**
4 * displays status variables with descriptions and some hints an optmizing
5 * + reset status variables
7 * @version $Id$
8 * @package phpMyAdmin
9 */
11 /**
12 * no need for variables importing
13 * @ignore
15 if (! defined('PMA_NO_VARIABLES_IMPORT')) {
16 define('PMA_NO_VARIABLES_IMPORT', true);
18 require_once './libraries/common.inc.php';
20 /**
21 * Does the common work
23 require './libraries/server_common.inc.php';
26 /**
27 * Displays the links
29 require './libraries/server_links.inc.php';
31 /**
32 * Replication library
34 require './libraries/replication.inc.php';
35 require_once './libraries/replication_gui.lib.php';
37 /**
38 * Displays the sub-page heading
40 echo '<div id="serverstatus">' . "\n";
41 echo '<h2>' . "\n"
42 . ($GLOBALS['cfg']['MainPageIconic']
43 ? '<img class="icon" src="' . $GLOBALS['pmaThemeImage'] .
44 's_status.png" width="16" height="16" alt="" />'
45 : '')
46 . $strServerStatus . "\n"
47 . '</h2>' . "\n";
50 /**
51 * flush status variables if requested
53 if (isset($_REQUEST['flush'])) {
54 $_flush_commands = array(
55 'STATUS',
56 'TABLES',
57 'QUERY CACHE',
60 if (in_array($_REQUEST['flush'], $_flush_commands)) {
61 PMA_DBI_query('FLUSH ' . $_REQUEST['flush'] . ';');
63 unset($_flush_commands);
67 /**
68 * get status from server
70 $server_status = PMA_DBI_fetch_result('SHOW GLOBAL STATUS', 0, 1);
72 /**
73 * for some calculations we require also some server settings
75 $server_variables = PMA_DBI_fetch_result('SHOW GLOBAL VARIABLES', 0, 1);
77 /**
78 * starttime calculation
80 $start_time = PMA_DBI_fetch_value(
81 'SELECT UNIX_TIMESTAMP() - ' . $server_status['Uptime']);
84 /**
85 * cleanup some deprecated values
87 $deprecated = array(
88 'Com_prepare_sql' => 'Com_stmt_prepare',
89 'Com_execute_sql' => 'Com_stmt_execute',
90 'Com_dealloc_sql' => 'Com_stmt_close',
93 foreach ($deprecated as $old => $new) {
94 if (isset($server_status[$old])
95 && isset($server_status[$new])) {
96 unset($server_status[$old]);
99 unset($deprecated);
103 * calculate some values
105 // Key_buffer_fraction
106 if (isset($server_status['Key_blocks_unused'])
107 && isset($server_variables['key_cache_block_size'])
108 && isset($server_variables['key_buffer_size'])) {
109 $server_status['Key_buffer_fraction_%'] =
111 - $server_status['Key_blocks_unused']
112 * $server_variables['key_cache_block_size']
113 / $server_variables['key_buffer_size']
114 * 100;
115 } elseif (
116 isset($server_status['Key_blocks_used'])
117 && isset($server_variables['key_buffer_size'])) {
118 $server_status['Key_buffer_fraction_%'] =
119 $server_status['Key_blocks_used']
120 * 1024
121 / $server_variables['key_buffer_size'];
124 // Ratio for key read/write
125 if (isset($server_status['Key_writes'])
126 && isset($server_status['Key_write_requests'])
127 && $server_status['Key_write_requests'] > 0)
128 $server_status['Key_write_ratio_%'] = 100 * $server_status['Key_writes'] / $server_status['Key_write_requests'];
130 if (isset($server_status['Key_reads'])
131 && isset($server_status['Key_read_requests'])
132 && $server_status['Key_read_requests'] > 0)
133 $server_status['Key_read_ratio_%'] = 100 * $server_status['Key_reads'] / $server_status['Key_read_requests'];
135 // Threads_cache_hitrate
136 if (isset($server_status['Threads_created'])
137 && isset($server_status['Connections'])
138 && $server_status['Connections'] > 0) {
139 $server_status['Threads_cache_hitrate_%'] =
141 - $server_status['Threads_created']
142 / $server_status['Connections']
143 * 100;
146 // Format Uptime_since_flush_status : show as days, hours, minutes, seconds
147 if (isset($server_status['Uptime_since_flush_status'])) {
148 $server_status['Uptime_since_flush_status'] = PMA_timespanFormat($server_status['Uptime_since_flush_status']);
152 * define some alerts
154 // name => max value before alert
155 $alerts = array(
156 // lower is better
157 // variable => max value
158 'Aborted_clients' => 0,
159 'Aborted_connects' => 0,
161 'Binlog_cache_disk_use' => 0,
163 'Created_tmp_disk_tables' => 0,
165 'Handler_read_rnd' => 0,
166 'Handler_read_rnd_next' => 0,
168 'Innodb_buffer_pool_pages_dirty' => 0,
169 'Innodb_buffer_pool_reads' => 0,
170 'Innodb_buffer_pool_wait_free' => 0,
171 'Innodb_log_waits' => 0,
172 'Innodb_row_lock_time_avg' => 10, // ms
173 'Innodb_row_lock_time_max' => 50, // ms
174 'Innodb_row_lock_waits' => 0,
176 'Slow_queries' => 0,
177 'Delayed_errors' => 0,
178 'Select_full_join' => 0,
179 'Select_range_check' => 0,
180 'Sort_merge_passes' => 0,
181 'Opened_tables' => 0,
182 'Table_locks_waited' => 0,
183 'Qcache_lowmem_prunes' => 0,
184 'Slow_launch_threads' => 0,
186 // depends on Key_read_requests
187 // normaly lower then 1:0.01
188 'Key_reads' => (0.01 * $server_status['Key_read_requests']),
189 // depends on Key_write_requests
190 // normaly nearly 1:1
191 'Key_writes' => (0.9 * $server_status['Key_write_requests']),
193 'Key_buffer_fraction' => 0.5,
195 // alert if more than 95% of thread cache is in use
196 'Threads_cached' => 0.95 * $server_variables['thread_cache_size']
198 // higher is better
199 // variable => min value
200 //'Handler read key' => '> ',
205 * split variables in sections
207 $allocations = array(
208 // variable name => section
210 'Com_' => 'com',
211 'Innodb_' => 'innodb',
212 'Ndb_' => 'ndb',
213 'Ssl_' => 'ssl',
214 'Handler_' => 'handler',
215 'Qcache_' => 'qcache',
216 'Threads_' => 'threads',
217 'Slow_launch_threads' => 'threads',
219 'Binlog_cache_' => 'binlog_cache',
220 'Created_tmp_' => 'created_tmp',
221 'Key_' => 'key',
223 'Delayed_' => 'delayed',
224 'Not_flushed_delayed_rows' => 'delayed',
226 'Flush_commands' => 'query',
227 'Last_query_cost' => 'query',
228 'Slow_queries' => 'query',
230 'Select_' => 'select',
231 'Sort_' => 'sort',
233 'Open_tables' => 'table',
234 'Opened_tables' => 'table',
235 'Table_locks_' => 'table',
237 'Rpl_status' => 'repl',
238 'Slave_' => 'repl',
240 'Tc_' => 'tc',
243 $sections = array(
244 // section => section name (description)
245 'com' => array('title' => ''),
246 'query' => array('title' => $strSQLQuery),
247 'innodb' => array('title' => 'InnoDB'),
248 'ndb' => array('title' => 'NDB'),
249 'ssl' => array('title' => 'SSL'),
250 'handler' => array('title' => $strHandler),
251 'qcache' => array('title' => $strQueryCache),
252 'threads' => array('title' => $strThreads),
253 'binlog_cache' => array('title' => $strBinaryLog),
254 'created_tmp' => array('title' => $strTempData),
255 'delayed' => array('title' => $strServerStatusDelayedInserts),
256 'key' => array('title' => $strKeyCache),
257 'select' => array('title' => $strJoins),
258 'repl' => array('title' => $strReplication),
259 'sort' => array('title' => $strSorting),
260 'table' => array('title' => $strNumTables),
261 'tc' => array('title' => $strTransactionCoordinator),
265 * define some needfull links/commands
267 // variable or section name => (name => url)
268 $links = array();
270 $links['table'][$strFlushTables]
271 = $PMA_PHP_SELF . '?flush=TABLES&amp;' . PMA_generate_common_url();
272 $links['table'][$strShowOpenTables]
273 = 'sql.php?sql_query=' . urlencode('SHOW OPEN TABLES') .
274 '&amp;goto=server_status.php&amp;' . PMA_generate_common_url();
276 if ($server_master_status) {
277 $links['repl'][$strShowSlaveHosts]
278 = 'sql.php?sql_query=' . urlencode('SHOW SLAVE HOSTS') .
279 '&amp;goto=server_status.php&amp;' . PMA_generate_common_url();
280 $links['repl'][$strShowMasterStatus] = '#replication_master';
282 if ($server_slave_status) {
283 $links['repl'][$strShowSlaveStatus] = '#replication_slave';
286 $links['repl']['doc'] = 'replication';
288 $links['qcache'][$strFlushQueryCache]
289 = $PMA_PHP_SELF . '?flush=' . urlencode('QUERY CACHE') . '&amp;' .
290 PMA_generate_common_url();
291 $links['qcache']['doc'] = 'query_cache';
293 $links['threads'][$strMySQLShowProcess]
294 = 'server_processlist.php?' . PMA_generate_common_url();
295 $links['threads']['doc'] = 'mysql_threads';
297 $links['key']['doc'] = 'myisam_key_cache';
299 $links['binlog_cache']['doc'] = 'binary_log';
301 $links['Slow_queries']['doc'] = 'slow_query_log';
303 $links['innodb'][$strServerTabVariables]
304 = 'server_engines.php?engine=InnoDB&amp;' . PMA_generate_common_url();
305 $links['innodb'][$strInnodbStat]
306 = 'server_engines.php?engine=InnoDB&amp;page=Status&amp;' .
307 PMA_generate_common_url();
308 $links['innodb']['doc'] = 'innodb';
311 // sort status vars into arrays
312 foreach ($server_status as $name => $value) {
313 if (isset($allocations[$name])) {
314 $sections[$allocations[$name]]['vars'][$name] = $value;
315 unset($server_status[$name]);
316 } else {
317 foreach ($allocations as $filter => $section) {
318 if (preg_match('/^' . $filter . '/', $name)
319 && isset($server_status[$name])) {
320 unset($server_status[$name]);
321 $sections[$section]['vars'][$name] = $value;
326 unset($name, $value, $filter, $section, $allocations);
328 // rest
329 $sections['all']['vars'] =& $server_status;
331 $hour_factor = 3600 / $server_status['Uptime'];
334 * start output
337 <div id="statuslinks">
338 <a href="<?php echo
339 $PMA_PHP_SELF . '?' . PMA_generate_common_url(); ?>"
340 ><?php echo $strRefresh; ?></a>
341 <a href="<?php echo
342 $PMA_PHP_SELF . '?flush=STATUS&amp;' . PMA_generate_common_url(); ?>"
343 ><?php echo $strShowStatusReset; ?></a>
344 <?php echo PMA_showMySQLDocu('server_status_variables','server_status_variables'); ?>
345 </div>
348 <?php
349 echo sprintf($strServerStatusUptime,
350 PMA_timespanFormat($server_status['Uptime']),
351 PMA_localisedDate($start_time)) . "\n";
353 </p>
355 <?php
356 if ($server_master_status || $server_slave_status)
358 $replicationOut = "";
359 foreach ($replication_types as $type)
361 if (${"server_{$type}_status"})
363 if ($replicationOut != "")
364 $replicationOut .= $strAndSmall . ' ';
365 $replicationOut .= '<b>' . $type . '</b> ';
368 echo sprintf('<p>' . $strReplicationStatusInfo . '</p>', $replicationOut);
372 <div id="sectionlinks">
373 <?php
374 foreach ($sections as $section_name => $section) {
375 if (! empty($section['vars']) && ! empty($section['title'])) {
376 echo '<a href="' . $PMA_PHP_SELF . '?' .
377 PMA_generate_common_url() . '#' . $section_name . '">' .
378 $section['title'] . '</a>' . "\n";
382 </div>
384 <h3><?php echo $strServerTrafficNotes; ?></h3>
386 <table id="serverstatustraffic" class="data">
387 <thead>
388 <tr>
389 <th colspan="2"><?php echo $strTraffic . '&nbsp;' . PMA_showHint($strStatisticsOverrun); ?></th>
390 <th>&oslash; <?php echo $strPerHour; ?></th>
391 </tr>
392 </thead>
393 <tbody>
394 <tr class="odd">
395 <th class="name"><?php echo $strReceived; ?></th>
396 <td class="value"><?php echo
397 implode(' ',
398 PMA_formatByteDown($server_status['Bytes_received'], 4)); ?></td>
399 <td class="value"><?php echo
400 implode(' ',
401 PMA_formatByteDown(
402 $server_status['Bytes_received'] * $hour_factor, 4)); ?></td>
403 </tr>
404 <tr class="even">
405 <th class="name"><?php echo $strSent; ?></th>
406 <td class="value"><?php echo
407 implode(' ',
408 PMA_formatByteDown($server_status['Bytes_sent'], 4)); ?></td>
409 <td class="value"><?php echo
410 implode(' ',
411 PMA_formatByteDown(
412 $server_status['Bytes_sent'] * $hour_factor, 4)); ?></td>
413 </tr>
414 <tr class="odd">
415 <th class="name"><?php echo $strTotalUC; ?></th>
416 <td class="value"><?php echo
417 implode(' ',
418 PMA_formatByteDown(
419 $server_status['Bytes_received'] + $server_status['Bytes_sent'], 4)
420 ); ?></td>
421 <td class="value"><?php echo
422 implode(' ',
423 PMA_formatByteDown(
424 ($server_status['Bytes_received'] + $server_status['Bytes_sent'])
425 * $hour_factor, 4)
426 ); ?></td>
427 </tr>
428 </tbody>
429 </table>
431 <table id="serverstatusconnections" class="data">
432 <thead>
433 <tr>
434 <th colspan="2"><?php echo $strConnections; ?></th>
435 <th>&oslash; <?php echo $strPerHour; ?></th>
436 <th>%</th>
437 </tr>
438 </thead>
439 <tbody>
440 <tr class="odd">
441 <th class="name"><?php echo $strMaxConnects; ?></th>
442 <td class="value"><?php echo
443 PMA_formatNumber($server_status['Max_used_connections'], 0); ?> </td>
444 <td class="value">--- </td>
445 <td class="value">--- </td>
446 </tr>
447 <tr class="even">
448 <th class="name"><?php echo $strFailedAttempts; ?></th>
449 <td class="value"><?php echo
450 PMA_formatNumber($server_status['Aborted_connects'], 4, 0); ?></td>
451 <td class="value"><?php echo
452 PMA_formatNumber($server_status['Aborted_connects'] * $hour_factor,
453 4, 2); ?></td>
454 <td class="value"><?php echo
455 $server_status['Connections'] > 0
456 ? PMA_formatNumber(
457 $server_status['Aborted_connects'] * 100 / $server_status['Connections'],
458 0, 2) . '%'
459 : '--- '; ?></td>
460 </tr>
461 <tr class="odd">
462 <th class="name"><?php echo $strAbortedClients; ?></th>
463 <td class="value"><?php echo
464 PMA_formatNumber($server_status['Aborted_clients'], 4, 0); ?></td>
465 <td class="value"><?php echo
466 PMA_formatNumber($server_status['Aborted_clients'] * $hour_factor,
467 4, 2); ?></td>
468 <td class="value"><?php echo
469 $server_status['Connections'] > 0
470 ? PMA_formatNumber(
471 $server_status['Aborted_clients'] * 100 / $server_status['Connections'],
472 0, 2) . '%'
473 : '--- '; ?></td>
474 </tr>
475 <tr class="even">
476 <th class="name"><?php echo $strTotalUC; ?></th>
477 <td class="value"><?php echo
478 PMA_formatNumber($server_status['Connections'], 4, 0); ?></td>
479 <td class="value"><?php echo
480 PMA_formatNumber($server_status['Connections'] * $hour_factor,
481 4, 2); ?></td>
482 <td class="value"><?php echo
483 PMA_formatNumber(100, 0, 2); ?>%</td>
484 </tr>
485 </tbody>
486 </table>
488 <hr class="clearfloat" />
490 <h3><?php echo
491 sprintf($strQueryStatistics,
492 PMA_formatNumber($server_status['Questions'], 0)); ?></h3>
494 <table id="serverstatusqueriessummary" class="data">
495 <thead>
496 <tr>
497 <th><?php echo $strTotalUC; ?></th>
498 <th>&oslash; <?php echo $strPerHour; ?></th>
499 <th>&oslash; <?php echo $strPerMinute; ?></th>
500 <th>&oslash; <?php echo $strPerSecond; ?></th>
501 </tr>
502 </thead>
503 <tbody>
504 <tr class="odd">
505 <td class="value"><?php echo
506 PMA_formatNumber($server_status['Questions'], 4, 0); ?></td>
507 <td class="value"><?php echo
508 PMA_formatNumber($server_status['Questions'] * $hour_factor,
509 3, 2); ?></td>
510 <td class="value"><?php echo
511 PMA_formatNumber(
512 $server_status['Questions'] * 60 / $server_status['Uptime'],
513 3, 2); ?></td>
514 <td class="value"><?php echo
515 PMA_formatNumber(
516 $server_status['Questions'] / $server_status['Uptime'],
517 3, 2); ?></td>
518 </tr>
519 </tbody>
520 </table>
522 <div id="serverstatusqueriesdetails">
523 <?php
525 $used_queries = $sections['com']['vars'];
526 // reverse sort by value to show most used statements first
527 arsort($used_queries);
528 // remove all zero values from the end
529 while (end($used_queries) == 0) {
530 array_pop($used_queries);
533 // number of tables to split values into
534 $tables = 3;
535 $max_rows_per_table = (int) ceil(count($used_queries) / $tables);
536 $current_table = 0;
537 $odd_row = true;
538 $count_displayed_rows = 0;
539 $perc_factor = 100 / ($server_status['Questions'] - $server_status['Connections']);
541 foreach ($used_queries as $name => $value) {
542 $current_table++;
543 if ($count_displayed_rows === 0 || $count_displayed_rows === $max_rows_per_table) {
544 $odd_row = true;
545 if ($count_displayed_rows === $max_rows_per_table) {
546 echo ' </tbody>' . "\n";
547 echo ' </table>' . "\n";
548 $count_displayed_rows = 0;
551 <table id="serverstatusqueriesdetails<?php echo $current_table; ?>" class="data">
552 <col class="namecol" />
553 <col class="valuecol" span="3" />
554 <thead>
555 <tr><th colspan="2"><?php echo $strQueryType; ?></th>
556 <th>&oslash; <?php echo $strPerHour; ?></th>
557 <th>%</th>
558 </tr>
559 </thead>
560 <tbody>
561 <?php
562 } else {
563 $odd_row = !$odd_row;
565 $count_displayed_rows++;
567 // For the percentage column, use Questions - Connections, because
568 // the number of connections is not an item of the Query types
569 // but is included in Questions. Then the total of the percentages is 100.
570 $name = str_replace('Com_', '', $name);
571 $name = str_replace('_', ' ', $name);
573 <tr class="<?php echo $odd_row ? 'odd' : 'even'; ?>">
574 <th class="name"><?php echo htmlspecialchars($name); ?></th>
575 <td class="value"><?php echo PMA_formatNumber($value, 4, 0); ?></td>
576 <td class="value"><?php echo
577 PMA_formatNumber($value * $hour_factor, 3, 3); ?></td>
578 <td class="value"><?php echo
579 PMA_formatNumber($value * $perc_factor, 0, 2); ?>%</td>
580 </tr>
581 <?php
584 </tbody>
585 </table>
586 </div>
588 <div id="serverstatussection">
589 <?php
590 //Unset used variables
591 unset(
592 $tables, $max_rows_per_table, $current_table, $count_displayed_rows, $perc_factor,
593 $hour_factor, $sections['com'],
594 $server_status['Aborted_clients'], $server_status['Aborted_connects'],
595 $server_status['Max_used_connections'], $server_status['Bytes_received'],
596 $server_status['Bytes_sent'], $server_status['Connections'],
597 $server_status['Questions'], $server_status['Uptime'],
598 $used_queries
601 foreach ($sections as $section_name => $section) {
602 if (! empty($section['vars'])) {
604 <table class="data" id="serverstatussection<?php echo $section_name; ?>">
605 <caption class="tblHeaders">
606 <a class="top"
607 href="<?php echo $PMA_PHP_SELF . '?' .
608 PMA_generate_common_url() . '#serverstatus'; ?>"
609 name="<?php echo $section_name; ?>"><?php echo $strPos1; ?>
610 <?php echo
611 ($GLOBALS['cfg']['MainPageIconic']
612 ? '<img src="' . $GLOBALS['pmaThemeImage'] .
613 's_asc.png" width="11" height="9" align="middle" alt="" />'
614 : ''); ?>
615 </a>
616 <?php
617 if (! empty($section['title'])) {
618 echo $section['title'];
621 </caption>
622 <col class="namecol" />
623 <col class="valuecol" />
624 <col class="descrcol" />
625 <thead>
626 <tr>
627 <th><?php echo $strVar; ?></th>
628 <th><?php echo $strValue; ?></th>
629 <th><?php echo $strDescription; ?></th>
630 </tr>
631 </thead>
632 <?php
633 if (! empty($links[$section_name])) {
635 <tfoot>
636 <tr class="tblFooters">
637 <th colspan="3" class="tblFooters">
638 <?php
639 foreach ($links[$section_name] as $link_name => $link_url) {
640 if ('doc' == $link_name) {
641 echo PMA_showMySQLDocu($link_url, $link_url);
642 } else {
643 echo '<a href="' . $link_url . '">' . $link_name . '</a>' . "\n";
646 unset($link_url, $link_name);
648 </th>
649 </tr>
650 </tfoot>
651 <?php
654 <tbody>
655 <?php
656 $odd_row = false;
657 foreach ($section['vars'] as $name => $value) {
658 $odd_row = !$odd_row;
660 <tr class="<?php echo $odd_row ? 'odd' : 'even'; ?>">
661 <th class="name"><?php echo htmlspecialchars($name); ?></th>
662 <td class="value"><?php
663 if (isset($alerts[$name])) {
664 if ($value > $alerts[$name]) {
665 echo '<span class="attention">';
666 } else {
667 echo '<span class="allfine">';
670 if ('%' === substr($name, -1, 1)) {
671 echo PMA_formatNumber($value, 0, 2) . ' %';
672 } elseif (is_numeric($value) && $value == (int) $value) {
673 echo PMA_formatNumber($value, 4, 0);
674 } elseif (is_numeric($value)) {
675 echo PMA_formatNumber($value, 4, 2);
676 } else {
677 echo htmlspecialchars($value);
679 if (isset($alerts[$name])) {
680 echo '</span>';
682 ?></td>
683 <td class="descr">
684 <?php
685 if (isset($GLOBALS['strShowStatus' . $name . 'Descr'])) {
686 echo $GLOBALS['strShowStatus' . $name . 'Descr'];
689 if (isset($links[$name])) {
690 foreach ($links[$name] as $link_name => $link_url) {
691 if ('doc' == $link_name) {
692 echo PMA_showMySQLDocu($link_url, $link_url);
693 } else {
694 echo ' <a href="' . $link_url . '">' . $link_name . '</a>' .
695 "\n";
698 unset($link_url, $link_name);
701 </td>
702 </tr>
703 <?php
705 unset($name, $value);
707 </tbody>
708 </table>
709 <?php
712 unset($section_name, $section, $sections, $server_status, $odd_row, $alerts);
714 </div>
715 <?php
716 /* if the server works as master or slave in replication process, display useful information */
717 if ($server_master_status || $server_slave_status)
720 <hr class="clearfloat" />
722 <h3><a name="replication"></a><?php echo $strReplicationStatus; ?></h3>
723 <?php
725 foreach ($replication_types as $type)
727 if (${"server_{$type}_status"}) {
728 PMA_replication_print_status_table($type);
731 unset($types);
735 </div>
737 <?php
739 * Sends the footer
741 require_once './libraries/footer.inc.php';