Changeset 22

Timestamp:

12/01/07 22:04:00 (1 year ago)

Author:

major

Message:

* Added innodb_log_file_size checks
* Added 32-bit/64-bit checks and warnings (maximum memory > 2GB = bad)
* Fixed temporary table recommendation bug (would never recommend increase)
* Fixed thread cache recommendations/warnings
* Merged increase/decrease variable recommendations into one category

Files:

• mysqltuner.pl (modified) (19 diffs)

mysqltuner.pl

@@ -183 +183 @@
-my (%mystat,%myvar,$dummyselect);
-sub get_all_vars {
+    # We need to initiate at least one query so that our data is useable
-    $dummyselect = `mysql $mysqllogin -Bse "SELECT VERSION();"`;
-    my @mysqlvarlist = `mysql $mysqllogin -Bse "SHOW /*!50000 GLOBAL */ VARIABLES;"`;
@@ -205 +206 @@
-    } else {
-        goodprint "Currently running supported MySQL version ".$myvar{'version'}."\n";
+    }
+}
+
+my ($arch);
+sub check_architecture {
+    if (`uname -m` =~ /64/) {
+        $arch = 64;
+        goodprint "Operating on 64-bit architecture\n";
+    } else {
+        $arch = 32;
+        if ($physical_memory > 2*1024*1024*1024) {
+            badprint "Switch to 64-bit OS - MySQL cannot currenty use all of your RAM\n";
+        } else {
+            goodprint "Operating on 32-bit architecture with less than 2GB RAM\n";
+        }
-    }
-}
@@ -383 +399 @@
-                }
-        }
-
-
-
+
+
+
+
+
+
+
+
+
-sub mysql_stats {
-    print "-------- General Statistics --------------------------------------------------\n";
@@ -401 +423 @@
-    infoprint "Total buffers per thread: ".hr_bytes($mycalc{'per_thread_buffers'})."\n";
-    infoprint "Total global buffers: ".hr_bytes($mycalc{'server_buffers'})."\n";
-
+
+
+
+
-        badprint "Maximum possible memory usage: ".hr_bytes($mycalc{'total_possible_used_memory'})." ($mycalc{'pct_physical_memory'}% of installed RAM)\n";
-        push(@generalrec,"Reduce your overall MySQL memory footprint for system stability");
@@ -414 +439 @@
-        goodprint "Slow queries: $mycalc{'pct_slow_queries'}%\n";
-    }
-dec
+adj
-    if (defined($myvar{'log_slow_queries'})) {
-            if ($myvar{'log_slow_queries'} eq "OFF") { push(@generalrec,"Enable the slow query log to troubleshoot bad queries"); }
@@ -422 +447 @@
-    if ($mycalc{'pct_connections_used'} > 85) {
-        badprint "Highest connection usage: $mycalc{'pct_connections_used'}%\n";
-inc
-dec
+adj
+adj
-        push(@generalrec,"Reduce or eliminate persistent connections to reduce connection usage")
-    } else {
@@ -437 +462 @@
-        if ($myvar{'key_buffer_size'} < $mycalc{'total_myisam_indexes'} && $mycalc{'pct_keys_from_mem'} < 95) {
-            badprint "Key buffer size / total MyISAM indexes: ".hr_bytes($myvar{'key_buffer_size'})."/".hr_bytes($mycalc{'total_myisam_indexes'})."\n";
-inc
+adj
-        } else {
-            goodprint "Key buffer size / total MyISAM indexes: ".hr_bytes($myvar{'key_buffer_size'})."/".hr_bytes($mycalc{'total_myisam_indexes'})."\n";
@@ -460 +485 @@
-    } elsif ($myvar{'query_cache_size'} < 1) {
-        badprint "Query cache is disabled\n";
-inc
+adj
-    } elsif ($mystat{'Com_select'} == 0) {
-        badprint "Query cache cannot be analyzed - no SELECT statements executed\n";
@@ -466 +491 @@
-        if ($mycalc{'query_cache_efficiency'} < 20) {
-            badprint "Query cache efficiency: $mycalc{'query_cache_efficiency'}%\n";
-inc
+adj
-        } else {
-            goodprint "Query cache efficiency: $mycalc{'query_cache_efficiency'}%\n";
@@ -472 +497 @@
-        if ($mycalc{'query_cache_prunes_per_day'} > 98) {
-            badprint "Query cache prunes per day: $mycalc{'query_cache_prunes_per_day'}\n";
-inc
+adj
-        } else {
-            goodprint "Query cache prunes per day: $mycalc{'query_cache_prunes_per_day'}\n";
@@ -484 +509 @@
-    } elsif ($mycalc{'pct_temp_sort_table'} > 10) {
-        badprint "Sorts requiring temporary tables: $mycalc{'pct_temp_sort_table'}%\n";
-inc
-inc
+adj
+adj
-    } else {
-        goodprint "Sorts requiring temporary tables: $mycalc{'pct_temp_sort_table'}%\n";
@@ -493 +518 @@
-    if ($mycalc{'joins_without_indexes_per_day'} > 250) {
-        badprint "Joins performed without indexes: $mycalc{'joins_without_indexes'}\n";
-inc
+adj
-        push(@generalrec,"Adjust your join queries to always utilize indexes");
-    } else {
@@ -502 +527 @@
-    # Temporary tables
-    if ($mystat{'Created_tmp_tables'} > 0) {
-
+*1024*1024
-            badprint "Temporary tables created on disk: $mycalc{'pct_temp_disk'}%\n";
-inc
-inc
+adj
+adj
-            push(@generalrec,"Be sure that tmp_table_size/max_heap_table_size are equal");
-            push(@generalrec,"Reduce your SELECT DISTINCT queries without LIMIT clauses");
@@ -521 +546 @@
-
-    # Thread cache
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
-
-    # Table cache
@@ -531 +564 @@
-        if ($mycalc{'table_cache_hit_rate'} < 20) {
-            badprint "Table cache hit rate: $mycalc{'table_cache_hit_rate'}%\n";
-inc
+adj
-            push(@generalrec,"Increase table_cache gradually to avoid file descriptor limits");
-        } else {
@@ -542 +575 @@
-        if ($mycalc{'pct_files_open'} > 85) {
-            badprint "Open file limit used: $mycalc{'pct_files_open'}%\n";
-inc
+adj
-        } else {
-            goodprint "Open file limit used: $mycalc{'pct_files_open'}%\n";
@@ -570 +603 @@
-                push(@generalrec,"Your applications are not closing MySQL connections properly");
-        }
+
+    # InnoDB
+    if ($myvar{'have_innodb'} eq "YES") {
+        if ($mycalc{'innodb_log_size_pct'} > 20 && $mycalc{'innodb_log_size_pct'} < 30) {
+            goodprint "InnoDB log size is ".hr_bytes($myvar{'innodb_log_file_size'})." ($mycalc{'innodb_log_size_pct'}% of InnoDB buffer pool)\n";
+        } else {
+            badprint "InnoDB log size is ".hr_bytes($myvar{'innodb_log_file_size'})." ($mycalc{'innodb_log_size_pct'}% of InnoDB buffer pool)\n";
+                push(@generalrec,"Set innodb_log_file_size to 25% of InnoDB buffer pool");
+                push(@adjvars,"innodb_log_file_size (".hr_bytes($myvar{'innodb_buffer_pool_size'}*.25).", which is 25% of InnoDB buffer pool)");
+        }
+    }
+
-}
-
@@ -578 +623 @@
-        foreach (@generalrec) { print "     ".$_."\n"; }
-    }
-inc
-increase
+adj
+adjust
-        if ($mycalc{'pct_physical_memory'} > 85) {
-            print "     *** MySQL's maximum memory usage exceeds your installed memory ***\n".
-                  "     *** Add more RAM before increasing any MySQL buffer variables  ***\n";
-        }
-
-
-
-
-
+
-    }
-}
@@ -602 +643 @@
-get_all_vars;                       # Toss variables/status into hashes
-validate_mysql_version;             # Check current MySQL version
+check_architecture;                 # Suggest 64-bit upgrade
-calculations;                       # Calculate everything we need
-mysql_stats;                        # Print the server stats