MySQL Analysis

Apr 17, 2025

Value, 0, 3) == 4.1) {
$mysql_version = substr($version_object->Value, 0, 3);
}
elseif (substr($version_object->Value, 0, 1) == 4) {
$mysql_version = substr($version_object->Value, 0, 1);
}
elseif (substr($version_object->Value, 0, 1) == 5) {
$mysql_version = substr($version_object->Value, 0, 1);
}
return $mysql_version;
}
function dbtuning_hit_ratio_monitor() {
echo “

” . t(‘Threads Created’) . “ ” . t(‘Connections’) . “ ” . t(‘Thread Cache Size’) . “ ” . t(‘Hit Ratio’) . “ ” . t(‘Warnings’) . “

“;
$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 = (((int) $threads_created->Value / (int) $mysql_connections->Value));
if ($hit_ratio < 0.90 || $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 SET Syntax‘, array(‘%url’ => url(‘http://dev.mysql.com/doc/refman/5.1/en/set-option.html’, NULL, NULL, TRUE)));
}
else {
$error_value = “”;
}
echo “

” . $threads_created->Value . “ ” . $mysql_connections->Value . “ ” . $thread_cache_size->Value . “ ” . (round($hit_ratio, 3) * 100) . “% ” . $error_value . “

“;
}
function dbtuning_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 “

” . $stats_to_calc->Variable_name . “ ” . $stat_hour . “ ” . $stat_day . “ ” . $stat_year . “ ” . $stats_to_calc->Value . “

n”;
}
function dbtuning_uptime_stats() {
echo “

” . t(‘Database Action’) . “ ” . t(‘Per-Hour’) . “ ” . t(‘Per-Day’) . “ ” . t(‘Per-Year’) . “ ” . t(‘Total’) . “

“;
$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”);
dbtuning_calc_uptime_stats($stats);
break;
case ($stats->Variable_name == ‘Handler_update’):
$stats->Variable_name = t(“Updates to DB”);
dbtuning_calc_uptime_stats($stats);
break;
case ($stats->Variable_name == ‘Handler_delete’):
$stats->Variable_name = t(“Deletes from DB”);
dbtuning_calc_uptime_stats($stats);
break;
default:
break;
}
}
}
function dbtuning_table_stats() {
echo “

” . t(‘Table Cache’) . “ ” . t(‘Open Tables’) . “ ” . t(‘Opened Tables’) . “ ” . t(‘Warnings’) . “

“;
$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 " ” . $table_cache->Value . “ ” . $open_tables->Value . “ ” . $opened_tables->Value . “ ” . $error_msg . “ Uptime: ” . $uptime->Value . “

“;
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 here. 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 here.”, array(‘%set’ => url(‘http://dev.mysql.com/doc/refman/5.0/en/set-option.html’, NULL, NULL, TRUE)), array(‘%url’ => url(‘http://dev.mysql.com/doc/refman/5.0/en/table-cache.html’, NULL, NULL, TRUE))) ;
echo “

” . $table_cache->Value . “ ” . $open_tables->Value . “ ” . $opened_tables->Value . “ ” . $error_msg . “

“;
break;
case ($table_cache->Value == $open_tables->Value && $opened_tables->Value < 1000): $error_msg = t("Your table cache is currently full. Normally this can 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 " ” . $table_cache->Value . “ ” . $open_tables->Value . “ ” . $opened_tables->Value . “ ” . $error_msg . “

“;
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 here. 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 here.”, array(‘%set’ => url(‘http://dev.mysql.com/doc/refman/5.0/en/set-option.html’, NULL, NULL, TRUE)), array(‘%url’ => url(‘http://dev.mysql.com/doc/refman/5.0/en/table-cache.html’, NULL, NULL, TRUE))) ;
echo “

” . $table_cache->Value . “ ” . $open_tables->Value . “ ” . $opened_tables->Value . “ ” . $error_msg . “

“;
break;
default:
$error_msg = “”;
echo “

” . $table_cache->Value . “ ” . $open_tables->Value . “ ” . $opened_tables->Value . “ ” . $error_msg . “

“;
break;
}
}
function dbtuning_qcache_stats_calc($qcache_vars) {
if ($qcache_vars[‘ratio’] != 0) {
if (round($qcache_vars[‘ratio’], 2) > .10) {
$qcache_errs[‘ratio’] = t(‘Your query cache is not maintaining a healthy hit ratio of 1 / 10 – Inserts / Hits. This can mean that your mySQL instance has become hard drive bound. This statistic can be improved by using your mySQL thread, table, and query cache effectively. You can also improve this from a code standpoint. Maintain a level of consistency in your code. Whether or not a query will be placed in the cache is case sensitive, as mySQL checks for a byte identical match.’);
}
}
else {
$qcache_errs[‘ratio’] = “Your Query Cache is disabled.”;
}
if ($qcache_vars[‘litmus’]->Value == ‘NO’) {
$qcache_errs[‘litmus’] = 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 here for more information on compiling MySQL.”, array(‘%url’ => url(‘http://dev.mysql.com/doc/refman/5.0/en/installing-source.html’, NULL, NULL, TRUE)));
}
if ($qcache_vars[‘size’]->Value == 0) {
$qcache_errs[‘size’] = 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 here and more information on configuring your Query Cache can be found here“, array(‘%set’ => url(‘http://dev.mysql.com/doc/refman/5.0/en/set-option.html’, NULL, NULL, TRUE)), array(‘%qcache’ => url(‘http://dev.mysql.com/doc/refman/5.0/en/query-cache-configuration.html’, NULL, NULL, TRUE)));
}
if ($qcache_vars[‘type’]->Value == ‘OFF’) {
$qcache_errs[‘type’] = 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 here and more information on configuring your Query Cache can be found here“, array(‘%set’ => url(‘http://dev.mysql.com/doc/refman/5.0/en/set-option.html’, NULL, NULL, TRUE)), array(‘%qcache’ => url(‘http://dev.mysql.com/doc/refman/5.0/en/query-cache-configuration.html’, NULL, NULL, TRUE)));
}
if ($qcache_vars[‘free_blocks’]->Value > 100) {
$qcache_errs[‘free_blocks’] = t(‘If your Query Cache has a lot of free blocks in it. This can indicate fragmentation in your Query Cache. This can decrease performance and generally just be a waste of resoures. Fix this problem by issuing a ‘FLUSH QUERY CACHE’ command. Read more about query cache maintenance here.’, array(‘%url’ => url(‘http://dev.mysql.com/doc/refman/5.0/en/query-cache-status-and-maintenance.html’, NULL, NULL, TRUE)));
}
return $qcache_errs;
}
function dbtuning_qcache_gather_vars() {
$qcache_vars = array (
“uptime” => db_fetch_object( db_query(‘SHOW STATUS LIKE “Uptime”‘)),
“hits” => db_fetch_object( db_query(‘SHOW STATUS LIKE “qcache_hits”‘)),
“inserts” => db_fetch_object( db_query(‘SHOW STATUS LIKE “qcache_inserts”‘)),
“litmus” => db_fetch_object( db_query(‘SHOW VARIABLES LIKE “have_query_cache”‘)),
“size” => db_fetch_object( db_query(‘SHOW VARIABLES LIKE “query_cache_size”‘)),
“type” => db_fetch_object( db_query(‘SHOW VARIABLES LIKE “query_cache_type”‘)),
“free_memory” => db_fetch_object( db_query(‘SHOW STATUS LIKE “qcache_free_memory”‘)),
“free_blocks” => db_fetch_object( db_query(‘SHOW STATUS LIKE “qcache_free_blocks”‘)),
“lowmem_prunes” => db_fetch_object( db_query(‘SHOW STATUS LIKE “qcache_lowmem_prunes”‘)),
“limit” => db_fetch_object( db_query(‘SHOW VARIABLES LIKE “query_cache_limit”‘)),
“not_cached” => db_fetch_object (db_query(‘SHOW STATUS LIKE “qcache_not_cached”‘)));
if ($qcache_vars[‘inserts’]->Value == 0 || $qcache_vars[‘hits’]->Value == 0) {
$qcache_vars[‘ratio’] = 0;
}
else {
$qcache_vars[‘ratio’] = round(($qcache_vars[‘inserts’]->Value / $qcache_vars[‘hits’]->Value), 2);
}
return $qcache_vars;
}
function dbtuning_qcache_stats() {
$qcache_vars = dbtuning_qcache_gather_vars();
$qcache_errs = dbtuning_qcache_stats_calc($qcache_vars);
echo “

” . t(‘Query Cache Ratio’) . “ ” . $qcache_vars[‘ratio’] . “ ” . $qcache_errs[‘ratio’] . “

“;
echo “

” . t(‘Query Cache Inserts’) . “ ” . $qcache_vars[‘inserts’]->Value . “ ” . $qcache_errs[‘inserts’] . “

“;
echo “

” . t(‘Query Cache Hits’) . “ ” . $qcache_vars[‘hits’]->Value . “ ” . $qcache_errs[‘hits’] . “

“;
echo “

” . t(‘Query Cache Available?’) . “ ” . $qcache_vars[‘litmus’]->Value . “ ” . $qcache_errs[‘litmus’] . “

“;
echo “

” . t(‘Query Cache Size’) . “ ” . $qcache_vars[‘size’]->Value . “ ” . $qcache_errs[‘size’] . “

“;
echo “

” . t(‘Query Cache Type’) . “ ” . $qcache_vars[‘type’]->Value . “ ” . $qcache_errs[‘type’] . “

“;
echo “

” . t(‘Query Cache Free Memory’) . “ ” . $qcache_vars[‘free_memory’]->Value . “ ” . $qcache_errs[‘free_memory’] . “

“;
echo “

” . t(‘Query Cache Free Blocks’) . “ ” . $qcache_vars[‘free_blocks’]->Value . “ ” . $qcache_errs[‘free_blocks’] . “

“;
echo “

” . t(‘Query Cache Prunes’) . “ ” . $qcache_vars[‘lowmem_prunes’]->Value . “ ” . $qcache_errs[‘lowmem_prunes’] . “

“;
echo “

” . t(‘Query Cache Limit’) . “ ” . $qcache_vars[‘limit’]->Value . “ ” . $qcache_errs[‘limit’] . “

“;
echo “

” . t(‘Queries Not Cached’) . “ ” . $qcache_vars[‘not_cached’]->Value . “ ” . $qcache_errs[‘not_cached’] . “

“;
}
function dbtuning_everything_else() {
$mysql_version = dbtuning_get_mysql_version();
$results = db_query(‘SHOW STATUS’);
echo “

” . t(‘Variable Name’) . “ ” . t(‘Current Value’) . “ ” . t(‘Warnings’) . “

“;
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 Slow Query Log and use Explain to examine your queries.”, array(‘%url’ => url(‘http://dev.mysql.com/doc/refman/en/slow-query-log.html’, NULL, NULL, TRUE)), array(‘%url2’ => url(‘http://dev.mysql.com/doc/refman/en/explain.html’, NULL, NULL, TRUE)));
echo “

” . $node->Variable_name . “ ” . $node->Value . “ ” . $value_error . “

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 Slow Query Log and use Explain to examine your queries.”, array(‘%url’ => url(‘http://dev.mysql.com/doc/refman/en/slow-query-log.html’, NULL, NULL, TRUE)), array(‘%url2’ => url(‘http://dev.mysql.com/doc/refman/en/explain.html’, NULL, NULL, TRUE)));
echo “

” . $node->Variable_name . “ ” . $node->Value . “ ” . $value_error . “

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 Slow Query Log and use Explain to examine your queries.”, array(‘%url’ => url(‘http://dev.mysql.com/doc/refman/en/slow-query-log.html’, NULL, NULL, TRUE)), array(‘%url2’ => url(‘http://dev.mysql.com/doc/refman/en/explain.html’, NULL, NULL, TRUE)));
echo “

” . $node->Variable_name . “ ” . $node->Value . “ ” . $value_error . “

n”;
break;
default:
break;
}
}
}
function dbtuning_memory_check() {
/*
key_buffer + innodb_buffer_pool + innodb_log_buffer + innodb_additional_mem_pool + net_buffer + max_connections *
(read_buffer + join_buffer + sort_buffer + myisam_sort_buffer + thread_stack + tmp_table_size + read_rnd_buffer)
*/
$memory_vars = array (
“innodb_buffer_pool” => db_fetch_object( db_query(‘SHOW VARIABLES LIKE “innodb_buffer_pool_size”‘)),
“innodb_additional_mem_pool” => db_fetch_object( db_query(‘SHOW VARIABLES LIKE “innodb_additional_mem_pool_size”‘)),
“innodb_log_buffer” => db_fetch_object( db_query(‘SHOW VARIABLES LIKE “innodb_log_buffer_size”‘)),
“thread_stack” => db_fetch_object( db_query(‘SHOW VARIABLES LIKE “thread_stack”‘)),
“tmp_table_size” => db_fetch_object( db_query(‘SHOW VARIABLES LIKE “tmp_table_size”‘)),
“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”‘)),
“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”‘)),
“read_rnd_buffer” => db_fetch_object( db_query(‘SHOW VARIABLES LIKE “read_rnd_buffer_size”‘)));
$global_buffer = ((floatval($memory_vars[‘read_buffer’]->Value) + floatval($memory_vars[‘join_buffer’]->Value) + floatval($memory_vars[‘sort_buffer’]->Value) + floatval($memory_vars[‘myisam_sort_buffer’]->Value) + floatval($memory_vars[‘thread_stack’]->Value) + floatval($memory_vars[‘tmp_table_size’]->Value) + floatval($memory_vars[‘read_rnd_buffer’]->Value)));
$per_thread_mem = ((floatval($memory_vars[‘key_buffer’]->Value) + floatval($memory_vars[‘innodb_buffer_pool’]->Value) + floatval($memory_vars[‘innodb_log_buffer’]->Value) + floatval($memory_vars[‘innodb_additional_mem_pool’]->Value) + floatval($memory_vars[‘net_buffer’]->Value)));
/*
$multiply_me = ((($memory_vars[‘read_buffer’]->Value + $memory_vars[‘join_buffer’]->Value + $memory_vars[‘sort_buffer’]->Value + $memory_vars[‘myisam_sort_buffer’]->Value + $memory_vars[‘thread_stack’]->Value + $memory_vars[‘tmp_table_size’]->Value + $memory_vars[‘read_rnd_buffer’]->Value) / 1024.0) / 1024.0);
$first_part = ((($memory_vars[‘key_buffer’]->Value + $memory_vars[‘innodb_buffer_pool’]->Value + $memory_vars[‘innodb_log_buffer’]->Value + $memory_vars[‘innodb_additional_mem_pool’]->Value + $memory_vars[‘net_buffer’]->Value + $memory_vars[‘max_connections’]->Value) / 1024.0) / 1024.0);
*/
// $min_memory_needed = ($memory_vars[‘max_connections’]->Value * $per_thread_mem) + $global_buffer;
$thread_usage = $memory_vars[‘max_connections’]->Value * $per_thread_mem;
$min_memory_needed = $thread_usage + $global_buffer;

/*
“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 “

” . t(‘Minimum Memory Needed : ‘) . $min_memory_needed . “

“;
echo “

” . t(‘This number is a calculation of the maximum amount of memory your mySQL instance will consume on your server . It is a sum of all the caches and buffers that would benefit by not using swap or not having to goto the HD. Having a number larger than your total available amount of memory will result in swapping.’) . “ “;
echo “

” . t(‘Min_memory_needed = key_buffer + innodb_buffer_pool + innodb_log_buffer + innodb_additional_mem_pool + net_buffer + max_connections * (read_buffer + join_buffer + sort_buffer + myisam_sort_buffer + thread_stack + tmp_table_size + read_rnd_buffer)’) . “

“;
echo “

” . t(‘Key Buffer : ‘) . $memory_vars[‘key_buffer’]->Value . “

“;
echo “

” . t(‘InnoDB Buffer Pool : ‘) . $memory_vars[‘innodb_buffer_pool’]->Value . “

“;
echo “

” . t(‘InnoDB Log Buffer : ‘) . $memory_vars[‘innodb_log_buffer’]->Value . “

“;
echo “

” . t(‘InnoDB Additional Memory Pool : ‘) . $memory_vars[‘innodb_additional_mem_pool’]->Value . “

“;
echo “

” . t(‘Net Buffer : ‘) . $memory_vars[‘net_buffer’]->Value . “

“;
echo “

” . t(‘Max Connections : ‘) . $memory_vars[‘max_connections’]->Value . “

“;
echo “

” . t(‘Read Buffer : ‘) . $memory_vars[‘read_buffer’]->Value . “

“;
echo “

” . t(‘Join Buffer : ‘) . $memory_vars[‘join_buffer’]->Value . “

“;
echo “

” . t(‘Sort Buffer : ‘) . $memory_vars[‘sort_buffer’]->Value . “

“;
echo “

” . t(‘Myisam Sort Buffer : ‘) . $memory_vars[‘myisam_sort_buffer’]->Value . “

“;
echo “

” . t(‘Thread Stack : ‘) . $memory_vars[‘thread_stack’]->Value . “

“;
echo “

” . t(‘Tmp Table Size : ‘) . $memory_vars[‘tmp_table_size’]->Value . “

“;
echo “

” . t(‘Read Rnd Buffer Size : ‘) . $memory_vars[‘read_rnd_buffer’]->Value . “

“;
}
echo “

Thread Cache Monitor
“;
echo “

“;
dbtuning_hit_ratio_monitor();
echo “

“;
echo “

Table Cache Monitor
“;
echo “

“;
dbtuning_table_stats();
echo “

“;
echo “

Query Cache Monitor
“;
echo “

“;
dbtuning_qcache_stats();
echo “

“;
echo “

Memory Monitor
“;
echo “

“;
dbtuning_memory_check();
echo “

“;
echo “

Select Monitor
“;
echo “

“;
dbtuning_everything_else($mysql_version);
echo “

“;
/*
Make all the links dynamically generated.
Pull version number from mysql – then put 4.1 or 5.0 in the URL
http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html
-Making the buffer pool bigger can help, but do not set it equal to more than 80% of physical memory.
-Make your log files big, even as big as the buffer pool.
-Make the log buffer quite large as well (on the order of 8MB).
http://dev.mysql.com/doc/refman/5.0/en/mysql-benchmarks.html
http://dev.mysql.com/doc/refman/5.0/en/compile-and-link-options.html
http://dev.mysql.com/doc/refman/5.0/en/explain.html
http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html
*/
?>

Explore More Insights

Editing Services

I provide comprehensive editorial services for both fiction and nonfiction, with over 20 years of experience. ProofreadingCorrecting surface errors in grammar, spelling, punctuation and language. $2 per page* Copy EditingFine-tuning your work for grammar, spelling,...

read more
Software Development Documentation

Software Development Documentation

Documentation is essential for interactive games and other complex projects. "The goal of the reiterative development approach is to methodically evolve every object in the system - from abstract concept to actual code - as quickly and clearly as possible, without...

read more

Sceneplay – Hero’s Journey Template

"The Hero's Journey" (aka Joseph Campbell's Monomyth) provides a template for a very common type of story, considered by many to be the archetypal narrative structure for most or all western narratives. The attached template breaks The Hero's Journey down into...

read more