*/ /* * MySQL connection settings, be careful if you use a password ! * It's safer to create a user who can only connect to the database server * and has only usage permissions, no grants on any databases or tables. */ $sqlhost = "localhost"; $sqluser = "mysqlar"; $sqlpassword = ""; /* sort a matrix using column as key */ function matrix_sort($matrix, $column, $limit = 0) { foreach ($matrix as $row) { $sortarr[] = $row[$column]; } array_multisort($sortarr, SORT_DESC, SORT_NUMERIC, $matrix); if($limit > 0) { return array_slice($matrix, 0, $limit); } else { return $matrix; } } $link = mysql_connect($sqlhost, $sqluser, $sqlpassword) or die("Could not connect : " . mysql_error()); list($major, $rest) = split('\.',mysql_get_server_info(),2); if($major >= 5) { $query = "SHOW global status"; } else { $query = "SHOW status"; } $result = mysql_query($query) or die("Query failed : " . mysql_error()); $status = array(); $comms_raw = array(); while ($var = mysql_fetch_array($result)) { $status["$var[0]"] = $var[1]; if(($var[1] > 0) && (preg_match("/^com_/i", $var[0]))) { $comms_raw[] = array(preg_replace("/^com_/i", "", $var[0]), $var[1]); } } $comms = matrix_sort($comms_raw, 1); if($major >= 5) { $query = "SHOW global variables"; } else { $query = "SHOW variables"; } $result = mysql_query($query) or die("Query failed : " . mysql_error()); $variables = array(); while ($var = mysql_fetch_array($result)) { $variables["$var[0]"] = $var[1]; } $report = array(); $report['now'] = strftime("%d-%m-%Y %H:%M"); $script = ""; $showtime = 'week'; $times = array('hour', 'day', 'week', 'month', 'year'); $tabs = array( array('tab', 'Table Cache', 'The table cache graph shows the open tables and the table_cache setting.

If the table_cache graph is hidden by the open_tables graph all table cache entries are in use.'), array('tmptab', 'Temporary Tables', 'The temporary tables graph shows the number of implicit temporary tables. For the best performance these tables should be created in memory.

If there are many temporary disk tables (created_tmp_disk_tables), you can increase your tmp_table_size to improve your server's performance.'), ); $keys = array( array('key', 'Key Cache', 'The key cache miss rate (key_read_requests/keyreads) should be lower than 1/100 (10m, 10 mili) indicated by an horizontal ruler and is quite ok for most systems if it's lower than 1/1000 (1m) also indicated by an horizontal ruler.

One could raise the key_buffer_size parameter to improve the key cache rate.'), array('keybuf', 'Key Buffer', 'The key buffer graph shows the amount of memory that's in use as key cache.'), array('join', 'Full Joins', 'The select_full_join value should allways be 0. If its not 0, you should check the indexes of you tables.

The full joins graph can assist you in finding out when there have been full joins.'), array('range', 'Range Checks', 'The select_range_check value should allways be 0. If its not 0, you should check the indexes of you tables.

The range checks graph can assist you in finding out when there have been joins without keys where the key usage got checked after each row.'), array('read', 'Reads', 'The reads graph shows the read requests based on a key (handler_read_key) and the read requests based on a fixed position in a datafile (handler_read_rnd).

The handler_read_key indicates that your queries use properly indexed tables, this value should be high. The handler_read_rnd indicates the opposite and should be as low as possible.'), array('slow', 'Slow Queries', 'The slow queries graph show the queries that took longer than long_query_time seconds (for this server the long_query_time='.@$variables['long_query_time'].' seconds).

If this value is not 0, you should activate the slow query log to find out which queries take that long to complete. The slow query log for your server is '.@$variables['log_long_queries'].'.'), ); $slave = array( array('slave', 'Slave Updates', 'The slave graph shows how slave replication is performing, specifically how much traffic the slave is replicating and how close the slave is keeping up to the master. The two lines should track so closely you can see only one. The amount of divergence will show when and how far the slave falls behind and how long it takes to catch up.', ''), array('slavec', 'Slave Tracking', 'This graph shows the real difference between the execution and replication progress.'), ); $rrds = array( array('queries', 'Questions', 'The questions graph shows the number of questions/minute. It has no direct relation to a tuneable parameter, but can be used as a hint to find out during which periods a more thorough logging should be conducted.

The most relevant types of questions (SELECT, INSERT, UPDATE and DELETE) are also logged.', ''), array('con', 'Connections', 'The connection graph shows the active connections and the configured max_connections.

In a sane configuration you should allways be able to see the max_connections graph. If it is hidden by the active connections graph all connections are in use.', ''), array('tab', 'Tables', '', $tabs), array('key', 'Indexes', '', $keys), array('slave', 'Slave Currency', '', $slave), ); $graph_content = ''; foreach($rrds as $rrd) { $graph_content .= "

".$rrd[1]."

\n"; $graph_content .= "

".$rrd[2]."

\n"; if($rrd[3] != '') { // Non 'Tables' RRDs foreach($rrd[3] as $graph) { $script .= " document.getElementById(\"".$graph[0]."-graphs\").style.display = \"\"; document.getElementById(\"t".$graph[0]."-graphs\").innerHTML = \"Hide\"; "; $graph_content .= "

".$graph[1]."

\n"; $graph_content .= "

".$graph[2]."

\n"; $graph_content .= "

 graphs

\n"; $graph_content .= "
\n"; foreach($times as $time) { if($time == $showtime) { $display = ""; $innerHTML = "Hide"; } else { $display = "none"; $innerHTML = "Show"; } $script .= " document.getElementById(\"".$graph[0]."-".$time."\").style.display = \"".$display."\"; document.getElementById(\"t".$graph[0]."-".$time."\").innerHTML = \"".$innerHTML."\"; "; $graph_content .= "
 ".$time." graph
\n"; $graph_content .= "\n"; } $graph_content .= "
\n"; } } else { // 'Tables' RRDs $script .= " document.getElementById(\"".$rrd[0]."-graphs\").style.display = \"\"; document.getElementById(\"t".$rrd[0]."-graphs\").innerHTML = \"Hide\"; "; $graph_content .= "

 graphs

\n"; $graph_content .= "
\n"; foreach($times as $time) { if($time == $showtime) { $display = ""; $innerHTML = "Hide"; } else { $display = "none"; $innerHTML = "Show"; } $script .= " document.getElementById(\"".$rrd[0]."-".$time."\").style.display = \"".$display."\"; document.getElementById(\"t".$rrd[0]."-".$time."\").innerHTML = \"".$innerHTML."\"; "; $graph_content .= "
 ".$time." graph
\n"; $graph_content .= "\n"; } $graph_content .= "
\n"; } } ?> MySQL Activity Report - <?php print $report['now']; ?>

Server Info

Your server (".$sqlhost.") has been running MySQL version ".$report['version']." for ".$report['uptime']." hours since ".$report['up_since'].".

\n"; ?>

General Remarks

Since startup, ".$sqlhost." received ".$status['Questions']." questions.

\n"; print "\n"; $lineno = 0; foreach($comms as $comm) { $class = (++$lineno % 2) ? "odd" : "even"; print "\n"; } print "
QuestionIssued
".$comm[0]."".$comm[1]."
\n"; ?>

Tuning Tips

Server tuning

Your server has been running for less than ".$earlyhours." hours. The tuning tips can't be very reliable yet.

\n"; } else { print "

Your server has been running for less than ".$earlydays." days. If you've got a steady load you can use the advise, otherwise wait a little longer before you use the tuning tips.

\n"; } } else { print "

Your server seems to be running for more than ".$earlydays." days. It should be OK to use the tips below, but don't follow the advise blindly because this script is no replacement for common sense.

\n"; } print "
    \n"; print "

  • The maximum number of used connections is ".$status['Max_used_connections']." which is ".$report['con_used']." % of the configured maximum.

    \n"; if($report['con_used'] > 85) { if($report['conused'] > 95) { print "

    You should raize your max_connections to avoid user lockout.

    • "; } else { print "

    You could raize your max_connections to to avoid user lockout.

    "; } } else { if($report['con_used'] < 40) { print "

    Your max_connections seems to be too high, maybe you can use it's resources for something else.

    "; } else { print "

    Your max_connections variable seems to be OK.

    "; } } print "

  • The table cache hit rate is ".$report['tab_hit']." %, while ".$report['tab_used']." % of your table cache is in use.

    \n"; if($report['tab_used'] > 85) { if ($report['tab_used'] > 95) { print "

    You should raize your table_cache to improve your server's performance.

    • "; } else { print "

    You could raize your table_cache to improve your server's performance.

    "; } } else { if($report['tab_used'] < 40) { print "

    Your table_cache seems to be too high, maybe you can use it's resources for something else.

    "; } else { print "

    Your table_cache variable seems to be OK.

    "; } } print "

  • The temporary table hit rate is ".$report['tmpmem']." %.

    \n"; if($report['tmpmem'] < 80) { if ($report['tmpmem'] < 60) { print "

    You should raize your tmp_table_size to improve your server's performance.

    • "; } else { print "

    You could raize your tmp_table_size to improve your server's performance.

    "; } } else { if($report['tmpmem'] > 99) { print "

    Your tmp_table_size seems to be too high, maybe you can use it's resources for something else.

    "; } else { print "

    Your tmp_table_size variable seems to be OK.

    "; } } if($report['key_used'] > 0) { print "

  • The key cache miss rate is 1/".$report['key_used'].".

    \n"; if($report['key_used'] < 1000) { if ($report['key_used'] < 100) { print "

    You should raize your key_buffer_size to improve your server's performance.

    • "; } else { print "

    You could raize your key_buffer_size to improve your server's performance.

    "; } } else { if(($report['key_used'] > 10000) && ($report['keybuf_used'] < 20)) { print "

    Your key_buffer_size seems to be too high, maybe you can use it's resources for something else.

    Before you lower it, you should monitor the key buffer usage more closely. It may be possible that the keybuffer gets used at peak load.

    "; } else { print "

    Your key_buffer_size variable seems to be OK.

    "; } } } print "
\n"; ?>

Database tuning

The problems identified in this section can't be solved by tuning the server parameters. If you didn't design the database or wrote the queries yourself, you'll have to talk to the database developers to solve these.

    0) { print "
  • You need to check your indexes and queries because you had ".$status['Select_full_join']." full joins (select_full_join).
    • "; } if($status['Select_range_check'] > 0) { print "
  • You need to look at your indexes because you had ".$status['Select_range_check']." joins without keys (select_range_check).
    • "; } if($status['Handler_read_key'] < $status['Handler_read_rnd']) { print "
  • You need to look at the indexing of your databases because the number of reads without using a key is too high. Reads using a key = ".$status['Handler_read_key']." (handler_read_key), reads not using a key = ".$status['Handler_read_rnd']." (handler_read_rnd).
    • "; } if($status['Slow_queries'] > 0) { print "
  • You've got ".$status['Slow_queries']." slow queries.
    • "; if($variables['log_long_queries'] == "ON") { print "
  • Check your slow query log to find out which query is causing trouble.
    • "; } else { print "
  • Activate your slow query log to find out which query is causing trouble.
    • "; } } ?>

status

$val) { $class = (++$lineno % 2) ? "odd" : "even"; print "\n"; } ?>
ParameterValue
".$key."".$val."

variables

$val) { $class = (++$lineno % 2) ? "odd" : "even"; print "\n"; } ?>
VariableValue
".$key."".$val."