Changeset 37

Timestamp:

06/19/08 02:33:54 (4 months ago)

Author:

major

Message:

Version bump to 0.9.1
Added checks for fragmented tables
Fixed login routine to allow zero-length passwords without re-prompting for the password repeatedly
Added workaround for wget 1.11 timestamping bug
Fixed a math error in the temporary table calculations
Fixed an error that occurred when the status variable Open_files returned zero
Added table cache change in preparation for MySQL 6.x
Thanks to Ville Skytta, Trent Hornibrook and Luuk Vosslamber for the bug reports and feature requests!

Files:

• mysqltuner.pl (modified) (14 diffs)

mysqltuner.pl

@@ -1 +1 @@
-#!/usr/bin/perl -w
-0
+1
-# High Performance MySQL Tuning Script
-# Copyright (C) 2006-2008 Major Hayden - major@mhtx.net
@@ -28 +28 @@
-#   Nils Breunese
-#   Shawn Ashlee
+#   Luuk Vosslamber
+#   Ville Skytta
+#   Trent Hornibrook
-#
-# Inspired by Matthew Montgomery's tuning-primer.sh script:
@@ -38 +41 @@
-
-# Set up a few variables for use in the script
-0
+1
-my (@adjvars, @generalrec);
-
@@ -222 +225 @@
-                        chomp($password);
-                        chomp($name);
-
+
+
+
+
-                        my $loginstatus = `mysqladmin ping $mysqllogin 2>&1`;
-                        if ($loginstatus =~ /mysqld is alive/) {
-                                print STDERR "\n";
+                                if (! length($password)) {
+                                        # Did this go well because of a .my.cnf file or is there no password set?
+                                        my $userpath = `ls -d ~`;
+                                        chomp($userpath);
+                                        unless ( -e "$userpath/.my.cnf" ) {
+                                                badprint "Successfully authenticated with no password - SECURITY RISK!\n";
+                                        }
+                                }
-                                return 1;
-                        } else {
@@ -261 +275 @@
-        }
-        my $update;
+        my $url = "http://mysqltuner.com/versioncheck.php?v=$tunerversion";
-        if (-e "/usr/bin/curl") {
-http://mysqltuner.com/versioncheck.php?v=$tunerversion
+'$url'
-                chomp($update);
-        } elsif (-e "/usr/bin/wget") {
-T 5 -O - http://mysqltuner.com/versioncheck.php?v=$tunerversion
+e timestamping=off -T 5 -O - '$url'
-                chomp($update);
-        }
@@ -307 +322 @@
-
-# Start up a ton of storage engine counts/statistics
-
+,$fragtables
-sub check_storage_engines {
-        if ($opt{skipsize} eq 1) {
@@ -324 +339 @@
-        $engines .= (defined $myvar{'have_ndbcluster'} && $myvar{'have_ndbcluster'} eq "YES")? greenwrap "+NDBCluster " : redwrap "-NDBCluster " ;      
-        print "$engines\n";
-eq
+>=
-                # MySQL 5 servers can have table sizes calculated quickly from information schema
-                my @templist = `mysql $mysqllogin -Bse "SELECT ENGINE,SUM(DATA_LENGTH),COUNT(ENGINE) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') GROUP BY ENGINE HAVING SUM(DATA_LENGTH) > 0 ORDER BY ENGINE ASC;"`;
@@ -333 +348 @@
-                        $enginecount{$engine} = $count;
-                }
+                $fragtables = `mysql $mysqllogin -Bse "SELECT COUNT(TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND Data_free > 0"`;
+                chomp($fragtables);
-        } else {
-                # MySQL < 5 servers take a lot of work to get table sizes
@@ -343 +360 @@
-                        if ($mysqlvermajor == 3 || ($mysqlvermajor == 4 && $mysqlverminor == 0)) {
-                                # MySQL 3.23/4.0 keeps Data_Length in the 6th column
-
+,\$9
-                        } else {
-                                # MySQL 4.1+ keeps Data_Length in the 7th column
-
+,\$10
-                        }
-                }
-                # Parse through the table list to generate storage engine counts/statistics
+                $fragtables = 0;
-                foreach my $line (@tblist) {
-
+
+
-                        my $engine = $1;
-                        my $size = $2;
+                        my $datafree = $3;
-                        if ($size !~ /^\d+$/) { $size = 0; }
-                        if (defined $enginestats{$engine}) {
@@ -362 +382 @@
-                                $enginecount{$engine} = 1;
-                        }
+                        if ($datafree > 0) {
+                                $fragtables++;
+                        }
-                }
-        }
@@ -379 +402 @@
-                badprint "ISAM is enabled but isn't being used\n";
-                push(@generalrec,"Add skip-isam to MySQL configuration to disable ISAM");
+        }
+        # Fragmented tables
+        if ($fragtables > 0) {
+                badprint "Total fragmented tables: $fragtables\n";
+                push(@generalrec,"Run OPTIMIZE TABLE to defragment tables for better performance");
+        } else {
+                goodprint "Total fragmented tables: $fragtables\n";
-        }
-}
@@ -454 +484 @@
-        if ($mystat{'Created_tmp_tables'} > 0) {
-                if ($mystat{'Created_tmp_disk_tables'} > 0) {
-$mystat{'Created_tmp_tables'}
+($mystat{'Created_tmp_tables'} + $mystat{'Created_tmp_disk_tables'})
-                } else {
-                        $mycalc{'pct_temp_disk'} = 0;
@@ -468 +498 @@
-        
-        # Open files
-stat{'Open_files'} > 0 && $my
+
-                $mycalc{'pct_files_open'} = int($mystat{'Open_files'}*100/$myvar{'open_files_limit'});
-        }
@@ -671 +701 @@
-
-        # Open files
-$myvar{'open_files_limit'} > 0
+defined $mycalc{'pct_files_open'}
-                if ($mycalc{'pct_files_open'} > 85) {
-                        badprint "Open file limit used: $mycalc{'pct_files_open'}%\n";