| |
Home » mysql tuning parameters
<?php
function hit_ratio_monitor() {
echo "<tr><td><b>" . t('Threads Created') . "</b></td><td><b>" . t('Connections') . "</b></td><td><b>" . t('Thread Cache Size') . "</b></td><td><b>" . t('Hit Ratio') . "</b></td><td><b>" . t('Warnings') . "</b></td></tr>";
$threads_created = db_fetch_object(db_query('SHOW STATUS LIKE "Threads_created"'));
$thread_cache_size = db_fetch_object(db_query('SHOW VARIABLES LIKE "thread_cache_size"'));
$mysql_connections = db_fetch_object(db_query('SHOW STATUS LIKE "Connections"'));
$hit_ratio = (100 - ((int) $threads_created->Value / (int) $mysql_connections->Value) * 100);
if ($hit_ratio < 0.99 || $hit_ratio > 1.10) {
$error_value = t('The ideal situation is to get Threads Created as close as possible to Thread Cache Size. So no new connections are having to wait for new thread allocation. Stay as close to a 99% hit ratio as you can as this will reduce bottlenecks in your caching. Adjust your Thread Cache Size until this is achieved. You can set your Thread Cache Size on the fly by doing "SET GLOBAL thread_cache_size=N". Where N is the desired size of your Thread Cache. For additional information on the SET command please read <a href="%url">SET Syntax</a>', array('%url' => url('http://dev.mysql.com/doc/refman/5.1/en/set-option.html')));
}
else {
$error_value = "";
}
echo "<tr><td>" . $threads_created->Value . "</td><td>" . $mysql_connections->Value . "</td><td>" . $thread_cache_size->Value . "</td><td>" . round($hit_ratio, 3) . "</td><td>" . $error_value . "</td></tr>";
}
function calc_uptime_stats($stats_to_calc) {
$uptime = db_fetch_object(db_query('SHOW STATUS LIKE "Uptime"'));
$stat_hour = ($stats_to_calc->Value / ($uptime->Value / 3600));
$stat_day = ($stats_to_calc->Value / ($uptime->Value / 86400));
$stat_year = ($stats_to_calc->Value / ($uptime->Value / 31536000));
echo "<tr><td>" . $stats_to_calc->Variable_name . "</td><td>" . $stat_hour . "</td><td>" . $stat_day . "</td><td>" . $stat_year . "</td><td>" . $stats_to_calc->Value . "</td></tr>\n";
}
function uptime_stats() {
echo "<tr><td><b>" . t('Database Action') . "</b></td><td><b>" . t('Per-Hour') . "</b></td><td><b>" . t('Per-Day') . "</b></td><td><b>" . t('Per-Year') . "</b></td><td><b>" . t('Total') . "</b></td></tr>";
$results_load_stats = db_query('SHOW STATUS LIKE "Handler%"');
while ($stats = db_fetch_object($results_load_stats)) {
switch ($stats) {
case ($stats->Variable_name == 'Handler_write'):
$stats->Variable_name = t("Writes to DB");
calc_uptime_stats($stats);
break;
case ($stats->Variable_name == 'Handler_update'):
$stats->Variable_name = t("Updates to DB");
calc_uptime_stats($stats);
break;
case ($stats->Variable_name == 'Handler_delete'):
$stats->Variable_name = t("Deletes from DB");
calc_uptime_stats($stats);
break;
default:
break;
}
}
}
function table_stats() {
echo "<tr><td><b>" . t('Table Cache') . "</b></td><td><b>" . t('Open Tables') . "</b></td><td><b>" . t('Opened Tables') . "</b></td><td><b>" . t('Warnings') . "</b></td></tr>";
$open_tables = db_fetch_object(db_query('SHOW STATUS LIKE "Open_tables"'));
$opened_tables = db_fetch_object(db_query('SHOW STATUS LIKE "Opened_tables"'));
$uptime = db_fetch_object(db_query('SHOW STATUS LIKE "Uptime"'));
$table_cache = db_fetch_object(db_query('SHOW VARIABLES LIKE "table_cache"'));
switch ($table_cache) {
case ($uptime < 1000000):
$error_msg = t("Your MySQL server has not been running long enough to make a quality assessment of the performance of your table cache. Put some traffic on there and come back soon!");
echo "<tr><td>" . $table_cache->Value . "</td><td>" . $open_tables->Value . "</td><td>" . $opened_tables->Value . "</td><td>" . $error_msg . "</td><td>Uptime: " . $uptime->Value . "</td></tr>";
break;
case ($table_cache->Value == $open_tables->Value && $opened_tables->Value > 1000):
$error_msg = t("Your table cache is currently full. This can severely impact the performance of your MySQL server. If you have the memory, it may be time to increase your table cache. However, if your table cache is set too high, MySQL may start dropping connections. You can read about how MySQL uses the table cache <a href='%url'>here</a>. Increase your thread cache by issuing a 'SET thread_cache_size=N' command. Where N is the desired size of your Thread Cache. More Information on the SET command can be found <a href='%set'>here</a>.", array('%set' => url('http://dev.mysql.com/doc/refman/5.0/en/set-option.html')), array('%url' => url('http://dev.mysql.com/doc/refman/5.0/en/table-cache.html'))) ;
echo "<tr><td>" . $table_cache->Value . "</td><td>" . $open_tables->Value . "</td><td>" . $opened_tables->Value . "</td><td>" . $error_msg . "</td></tr>";
break;
case ($table_cache->Value == $open_tables->Value && $opened_tables->Value < 1000):
$error_msg = t("Your table cache is currently full. Although this can normally be bad as it forces MySQL to goto the DB for queries. However, due to the low number of opened_tables, it may provide little benefit to increase your table cache");
echo "<tr><td>" . $table_cache->Value . "</td><td>" . $open_tables->Value . "</td><td>" . $opened_tables->Value . "</td><td>" . $error_msg . "</td></tr>";
break;
case (($table_cache->Value - $open_tables->Value) > 10):
$error_msg = t("If MySQL is using a significant amount of resources your system. You may want to free up memory by lowering your Table Cache. You can read about how MySQL uses the table cache <a href='%url'>here</a>. Adjust your thread cache by issuing a 'SET GLOBAL thread_cache_size=N' command. Where N is the desired size of your Thread Cache. More Information on the SET command can be found <a href='%set'>here</a>.", array('%set' => url('http://dev.mysql.com/doc/refman/5.0/en/set-option.html')), array('%url' => url('http://dev.mysql.com/doc/refman/5.0/en/table-cache.html'))) ;
echo "<tr><td>" . $table_cache->Value . "</td><td>" . $open_tables->Value . "</td><td>" . $opened_tables->Value . "</td><td>" . $error_msg . "</td></tr>";
break;
default:
$error_msg = "";
echo "<tr><td>" . $table_cache->Value . "</td><td>" . $open_tables->Value . "</td><td>" . $opened_tables->Value . "</td><td>" . $error_msg . "</td></tr>";
break;
}
}
function qcache_stats() {
$uptime = db_fetch_object(db_query('SHOW STATUS LIKE "Uptime"'));
$qcache_hits = db_fetch_object(db_query('SHOW STATUS LIKE "qcache_hits"'));
$qcache_inserts = db_fetch_object(db_query('SHOW STATUS LIKE "qcache_inserts"'));
$qcache_litmus = db_fetch_object(db_query('SHOW VARIABLES LIKE "have_query_cache"'));
$qcache_size = db_fetch_object(db_query('SHOW VARIABLES LIKE "query_cache_size"'));
$qcache_type = db_fetch_object(db_query('SHOW VARIABLES LIKE "query_cache_type"'));
if ($qcache_litmus->Value == 'NO') {
$error_msg = t("Your Query Cache has been disabled. The Query Cache is useful for when you regularly serve the same exact page. Please recompile MySQL without the --without-query-cache configuration option. See <a href='%url'>here</a> for more information on compiling MySQL.", array('%url' => url('http://dev.mysql.com/doc/refman/5.0/en/installing-source.html')));
echo "<tr><td><b>" . t('Have Query Cache?') . "</b></td><td><b>" . t('Query Cache Type') . "</b></td><td><b>" . t('Query Cache Size') . "</b></td><td><b>" . t('Warnings') . "</b></td></tr>";
echo "<tr><td>" . $qcache_litmus->Value . "</td><td>" . $qcache_type->Value . "</td><td>" . $qcache_size->Value . "</td><td>" . $error_msg . "</td></tr>";
}
elseif ($qcache_type->Value == 'OFF') {
$error_msg = t("Your query_cache_type is set to 0. This effectively disables your Query Cache. The Query Cache is useful for when you regularly serve the same exact page. To correct this use the command 'SET GLOBAL query_cache_type=N' where N is equal to OFF,ON, or DEMAND. More Information on the SET command can be found <a href='%set'>here</a> and more information on configuring your Query Cache can be found <a href='%qcache'>here</a>", array('%set' => url('http://dev.mysql.com/doc/refman/5.0/en/set-option.html')), array('%qcache' => url('http://dev.mysql.com/doc/refman/5.0/en/query-cache-configuration.html')));
echo "<tr><td><b>" . t('Have Query Cache?') . "</b></td><td><b>" . t('Query Cache Type') . "</b></td><td><b>" . t('Query Cache Size') . "</b></td><td><b>" . t('Warnings') . "</b></td></tr>";
echo "<tr><td>" . $qcache_litmus->Value . "</td><td>" . $qcache_type->Value . "</td><td>" . $qcache_size->Value . "</td><td>" . $error_msg . "</td></tr>";
}
elseif ((int) $qcache_size->Value == 0) {
$error_msg = t("Your query_cache_size is set to 0. This effectively disables your Query Cache. To correct this use the command 'SET GLOBAL query_cache_size=N' where N is equal to the desired size of your Query Cache. More Information on the SET command can be found <a href='%set'>here</a> and more information on configuring your Query Cache can be found <a href='%qcache'>here</a>", array('%set' => url('http://dev.mysql.com/doc/refman/5.0/en/set-option.html')), array('%qcache' => url('http://dev.mysql.com/doc/refman/5.0/en/query-cache-configuration.html')));
echo "<tr><td><b>" . t('Have Query Cache?') . "</b></td><td><b>" . t('Query Cache Type') . "</b></td><td><b>" . t('Query Cache Size') . "</b></td><td><b>" . t('Warnings') . "</b></td></tr>";
echo "<tr><td>" . $qcache_litmus->Value . "</td><td>" . $qcache_type->Value . "</td><td>" . $qcache_size->Value . "</td><td>" . $error_msg . "</td></tr>";
}
elseif ($qcache_hits->Value = 0 || $qcache_inserts->Value = 0) {
if ((int) ( $qcache_inserts->Value / $qcache_hits->Value) > 0.10) {
$value_error = t("Your Query Cache Inserts/Hits ratio exceeds the recommended limit.");
echo "<tr><td>" . $qcache_hits->Variable_name . "</td><td>" . $qcache_hits->Value . "</td><td>" . $error_msg . "</td></tr>";
echo "<tr><td>" . $qcache_inserts->Variable_name . "</td><td>" . $qcache_inserts->Value . "</td><td>" . $error_msg . "</td></tr>";
echo "<tr><td><b>" . t('Have Query Cache?') . "</b></td><td><b>" . t('Query Cache Type') . "</b></td><td><b>" . t('Query Cache Size') . "</b></td><td><b>" . t('Warnings') . "</b></td></tr>";
echo "<tr><td>" . $qcache_litmus->Value . "</td><td>" . $qcache_type->Value . "</td><td>" . $qcache_size->Value . "</td><td>" . $error_msg . "</td></tr>";
}
}
else {
$value_error = "";
echo "<tr><td>" . $qcache_hits->Variable_name . "</td><td>" . $qcache_hits->Value . "</td><td>" . $value_error . "</td></tr>";
echo "<tr><td>" . $qcache_inserts->Variable_name . "</td><td>" . $qcache_inserts->Value . "</td><td>" . $value_error . "</td></tr>";
echo "<tr><td><b>" . t('Have Query Cache?') . "</b></td><td><b>" . t('Query Cache Type') . "</b></td><td><b>" . t('Query Cache Size') . "</b></td><td><b>" . t('Warnings') . "</b></td></tr>";
echo "<tr><td>" . $qcache_litmus->Value . "</td><td>" . $qcache_type->Value . "</td><td>" . $qcache_size->Value . "</td><td>" . $value_error . "</td></tr>";
}
}
function everything_else() {
$results = db_query('SHOW STATUS');
echo "<tr><td><b>" . t('Variable Name') . "</b></td><td><b>" . t('Current Value') . "</b></td><td><b>" . t('Warnings') . "</b></td></tr>";
while ($node = db_fetch_object($results)) {
switch ($node) {
case ($node->Value > 0 && $node->Variable_name == 'Slow_queries'):
$value_error = t("You Have queries which are executing slower than normal. Enable the <a href='%url2'>Slow Query Log</a> and use <a href='%url'>Explain</a> to examine your queries.", array('%url' => url('http://dev.mysql.com/doc/refman/5.0/en/explain.html')), array('%url2' => url('http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html')));
echo "<tr><td>" . $node->Variable_name . "</td><td>" . $node->Value . "</td><td>" . $value_error . "</td></tr>\n";
break;
case ($node->Variable_name == 'Select_scan'):
$value_error = t("A high value here can be an indication of bottlenecks in your server optimization. This happens because Mysql is not using the indexes for the tables and so is having to do extra work for inefficient queries. Enable the <a href='%url'>Slow Query Log</a> and use <a href='%url2'>Explain</a> to examine your queries.", array('%url' => url('http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html')), array('%url2' => url('http://dev.mysql.com/doc/refman/5.0/en/explain.html')));
echo "<tr><td>" . $node->Variable_name . "</td><td>" . $node->Value . "</td><td>" . $value_error . "</td></tr>\n";
break;
case ($node->Variable_name == 'Select_full_join'):
$value_error = t("A high value here means that MySQL is not using indexes and is therefore taking longer to build a result set. The problem can be fixed by indexing important fields of the join.Enable the <a href='%url'>Slow Query Log</a> and use <a href='%url2'>Explain</a> to examine your queries.", array('%url' => url('http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html')), array('%url2' => url('http://dev.mysql.com/doc/refman/5.0/en/explain.html')));
echo "<tr><td>" . $node->Variable_name . "</td><td>" . $node->Value . "</td><td>" . $value_error . "</td></tr>\n";
break;
default:
break;
}
}
}
function memory_check() {
$key_buffer = db_fetch_object(db_query('SHOW VARIABLES LIKE "key_buffer_size"'));
$net_buffer = db_fetch_object(db_query('SHOW VARIABLES LIKE "net_buffer_length"'));
$max_connections = db_fetch_object(db_query('SHOW VARIABLES LIKE "max_connections"'));
$read_rnd_buffer_size = db_fetch_object(db_query('SHOW VARIABLES LIKE "read_rnd_buffer_size"'));
$sort_buffer = db_fetch_object(db_query('SHOW VARIABLES LIKE "sort_buffer_size"'));
$myisam_sort_buffer = db_fetch_object(db_query('SHOW VARIABLES LIKE "myisam_sort_buffer_size"'));
$read_buffer = db_fetch_object(db_query('SHOW VARIABLES LIKE "read_buffer_size"'));
$join_buffer = db_fetch_object(db_query('SHOW VARIABLES LIKE "join_buffer_size"'));
$global_buffers = (int) ($key_buffer->Value + $net_buffer->Value);
$thread_buffers = (int) ($read_rnd_buffer_size->Value + $sort_buffer->Value + $myisam_sort_buffer->Value + $read_buffer->Value + $join_buffer->Value);
$min_memory_needed = (int) ($global_buffers + ($thread_buffers * $max_connections->Value));
echo "<tr><td><center<b>" . t('Minimum Memory Needed : ') . round(($min_memory_needed / 1048576), 0) . "M</b></center></td></tr>";
echo "<tr><td>" . t('This number is a recommendation of the minimum amount of memory your server should have available to mySQL. It is a sum of all the caches and buffers that would benefit by not using swap.') . "</td><tr>";
echo "<tr><td>" . t('The formula : Min_memory_needed = Global Buffers + (Thread Buffers * Max_Connections)') . "</td></tr>";
echo "<tr><td>" . t('Global Buffers = Key Buffer + Net Buffer') . "</td></tr>";
echo "<tr><td>" . t('Thread Buffers = Read RND Buffer Size + Sort Buffer + MyiSAM Sort Buffer + Read Buffer + Join Buffer') . "</td></tr>";
echo "<tr><td><center<b>" . t('Global Buffer : ') . $global_buffers . "</b></center></td></tr>";
echo "<tr><td><center<b>" . t('Thread Buffers : ') . $thread_buffers . "</b></center></td></tr>";
echo "<tr><td><center<b>" . t('Key Buffer : ') . $key_buffer->Value . "</b></center></td></tr>";
echo "<tr><td><center<b>" . t('Net Buffer : ') . $net_buffer->Value . "</b></center></td></tr>";
echo "<tr><td><center<b>" . t('Max Connections : ') . $max_connections->Value . "</b></center></td></tr>";
echo "<tr><td><center<b>" . t('Read Rnd Buffer Size : ') . $read_rnd_buffer_size->Value . "</b></center></td></tr>";
echo "<tr><td><center<b>" . t('Sort Buffer : ') . $sort_buffer->Value . "</b></center></td></tr>";
echo "<tr><td><center<b>" . t('Myisam Sort Buffer : ') . $myisam_sort_buffer->Value . "</b></center></td></tr>";
echo "<tr><td><center<b>" . t('Read Buffer : ') . $read_buffer->Value . "</b></center></td></tr>";
echo "<tr><td><center<b>" . t('Join Buffer : ') . $join_buffer->Value . "</b></cwww.drupenter></td></tr>";
}
echo "<center><b> Thread Cache Monitor </b></center>";
echo "<table>";
hit_ratio_monitor();
echo "</table>";
echo "<br /><center><b> Table Cache Monitor </b></center>";
echo "<table>";
table_stats();
echo "</table>";
echo "<br /><center><b> Query Cache Monitor </b></center>";
echo "<table>";
qcache_stats();
echo "</table>";
echo "<br /><center><b> Memory Monitor </b></center>";
echo "<table>";
memory_check();
echo "</table>";
echo "<br /><center><b> Select Monitor </b></center>";
echo "<table>";
everything_else();
echo "</table>";
/*
Min_memory_needed = global_buffers + (thread_buffers * max_connections)
Max_connections
Global_buffers= key_buffer + net_buffer(???)
thread_buffers= read_rnd_buffer_size = sort_buffer myisam_sort_buffer + read_buffer join_buffer
*/
?>
|
