Changeset 40

Timestamp:

09/08/08 01:16:39 (2 months ago)

Author:

major

Message:

Bumped version to 0.9.9
Added --socket option to use an alternative socket (thanks Mark!)
Added OpenBSD memory checking support (thanks Giovanni!)
Added counts to report lines to provide additional detail
Fixed error where disabling BDB was recommended even when BDB tables were present (thanks Greg!)
Fixed an issue with big integers (> 2^{32) from key calculations (thanks Aubin!)
Fixed an error on Solaris machines (thanks Bill!)
Fixed an issue where the key buffer calculation caused errors when no indexes were present
Added Giovanni Bechis, Greg Eden, Aubin Galinotti, Mark Imbriaco and Bill Bradford to the contributors list}

Files:

• mysqltuner.pl (modified) (23 diffs)

mysqltuner.pl

@@ -1 +1 @@
-#!/usr/bin/perl -w
-8
+9
-# High Performance MySQL Tuning Script
-# Copyright (C) 2006-2008 Major Hayden - major@mhtx.net
@@ -21 +21 @@
-#
-# This project would not be possible without help from:
-
-
-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
-#
-# Inspired by Matthew Montgomery's tuning-primer.sh script:
@@ -42 +39 @@
-
-# Set up a few variables for use in the script
-8
+9
-my (@adjvars, @generalrec);
-
@@ -54 +51 @@
-                "forceswap" => 0,
-                "host" => 0,
+                "socket" => 0,
-                "port" => 0,
-                "user" => 0,
@@ -70 +68 @@
-                'forceswap=i',
-                'host=s',
+                'socket=s',
-                'port=i',
-                'user=s',
@@ -95 +94 @@
-                "   Connection and Authentication\n".
-                "      --host <hostname>    Connect to a remote host to perform tests (default: localhost)\n".
+                "      --socket <socket>    Use a different socket for a local connection\n".
-                "      --port <port>        Port to use for connection (default: 3306)\n".
-                "      --user <username>    Username to use for authentication\n".
@@ -215 +215 @@
-                        $physical_memory = `sysctl -n hw.memsize` or memerror;
-                        $swap_memory = `sysctl -n vm.swapusage | awk '{print \$3}' | sed 's/\..*\$//'` or memerror;
-
+|OpenBSD
-                        $physical_memory = `sysctl -n hw.physmem` or memerror;
-                        $swap_memory = `swapctl -l | grep '^/' | awk '{ s+= \$2 } END { print s }'` or memerror;
@@ -237 +237 @@
-                exit;
-        }
+        # Are we being asked to connect via a socket?
+        if ($opt{socket} ne 0) {
+                $remotestring = " -S $opt{socket}";
+        }
-        # Are we being asked to connect to a remote server?
-        if ($opt{host} ne 0) {
@@ -277 +281 @@
-                        $mysqllogin = "";
-                        # Did this go well because of a .my.cnf file or is there no password set?
-
-
-
+
+
+
+
+
-                                badprint "Successfully authenticated with no password - SECURITY RISK!\n";
-                        }
@@ -410 +416 @@
-        if ($mysqlvermajor >= 5) {
-                # MySQL 5 servers can have table sizes calculated quickly from information schema
-HAVING SUM(DATA_LENGTH) > 0 
+
-                foreach my $line (@templist) {
-                        my ($engine,$size,$count);
@@ -464 +470 @@
-                push(@generalrec,"Add skip-innodb to MySQL configuration to disable InnoDB");
-        }
-
+erkeley
-                badprint "BDB is enabled but isn't being used\n";
-                push(@generalrec,"Add skip-bdb to MySQL configuration to disable BDB");
@@ -523 +529 @@
-                $mycalc{'pct_keys_from_mem'} = sprintf("%.1f",(100 - (($mystat{'Key_reads'} / $mystat{'Key_read_requests'}) * 100)));
-        }
-
- s
-doremote eq 1 and $
-SUM(INDEX_LENGTH
+ and $mysqlvermajor < 5
+f (\"%d\",s)
+
+IFNULL(SUM(INDEX_LENGTH),0
-        }
-        if (defined $mycalc{'total_myisam_indexes'} and $mycalc{'total_myisam_indexes'} =~ /^0\n$/) { 
@@ -624 +630 @@
-
-        # Memory usage
-per_thread_buffers'})." per thread and ".hr_bytes($mycalc{'server_buffers'})." global
+server_buffers'})." global + ".hr_bytes($mycalc{'per_thread_buffers'})." per thread ($myvar{'max_connections'} max threads)
-        if ($mycalc{'total_possible_used_memory'} > 2*1024*1024*1024 && $arch eq 32) {
-                badprint "Allocating > 2GB RAM on 32-bit systems can cause system instability\n";
@@ -672 +678 @@
-                if ($mystat{'Key_read_requests'} > 0) {
-                        if ($mycalc{'pct_keys_from_mem'} < 95) {
-
+ (".hr_num($mystat{'Key_read_requests'})." cached / ".hr_num($mystat{'Key_reads'})." reads)
-                        } else {
-
+ (".hr_num($mystat{'Key_read_requests'})." cached / ".hr_num($mystat{'Key_reads'})." reads)
-                        }
-                } else {
@@ -694 +700 @@
-        } else {
-                if ($mycalc{'query_cache_efficiency'} < 20) {
-
+ (".hr_num($mystat{'Qcache_hits'})." cached / ".hr_num($mystat{'Qcache_hits'}+$mystat{'Com_select'})." selects)
-                        push(@adjvars,"query_cache_limit (> ".hr_bytes_rnd($myvar{'query_cache_limit'}).", or use smaller result sets)");
-                } else {
-
+ (".hr_num($mystat{'Qcache_hits'})." cached / ".hr_num($mystat{'Qcache_hits'}+$mystat{'Com_select'})." selects)
-                }
-                if ($mycalc{'query_cache_prunes_per_day'} > 98) {
@@ -712 +718 @@
-                # No sorts have run yet
-        } elsif ($mycalc{'pct_temp_sort_table'} > 10) {
-
+ (".hr_num($mystat{'Sort_merge_passes'})." temp sorts / ".hr_num($mycalc{'total_sorts'})." sorts)
-                push(@adjvars,"sort_buffer_size (> ".hr_bytes_rnd($myvar{'sort_buffer_size'}).")");
-                push(@adjvars,"read_rnd_buffer_size (> ".hr_bytes_rnd($myvar{'read_rnd_buffer_size'}).")");
-        } else {
-
+ (".hr_num($mystat{'Sort_merge_passes'})." temp sorts / ".hr_num($mycalc{'total_sorts'})." sorts)
-        }
-        
@@ -732 +738 @@
-        if ($mystat{'Created_tmp_tables'} > 0) {
-                if ($mycalc{'pct_temp_disk'} > 25 && $mycalc{'max_tmp_table_size'} < 256*1024*1024) {
-
+ (".hr_num($mystat{'Created_tmp_disk_tables'})." on disk / ".hr_num($mystat{'Created_tmp_disk_tables'} + $mystat{'Created_tmp_tables'})." total)
-                        push(@adjvars,"tmp_table_size (> ".hr_bytes_rnd($myvar{'tmp_table_size'}).")");
-                        push(@adjvars,"max_heap_table_size (> ".hr_bytes_rnd($myvar{'max_heap_table_size'}).")");
@@ -738 +744 @@
-                        push(@generalrec,"Reduce your SELECT DISTINCT queries without LIMIT clauses");
-                } elsif ($mycalc{'pct_temp_disk'} > 25 && $mycalc{'max_tmp_table_size'} >= 256) {
-
+ (".hr_num($mystat{'Created_tmp_disk_tables'})." on disk / ".hr_num($mystat{'Created_tmp_disk_tables'} + $mystat{'Created_tmp_tables'})." total)
-                        push(@generalrec,"Temporary table size is already large - reduce result set size");
-                        push(@generalrec,"Reduce your SELECT DISTINCT queries without LIMIT clauses");
-                } else {
-
+ (".hr_num($mystat{'Created_tmp_disk_tables'})." on disk / ".hr_num($mystat{'Created_tmp_disk_tables'} + $mystat{'Created_tmp_tables'})." total)
-                }
-        } else {
@@ -756 +762 @@
-        } else {
-                if ($mycalc{'thread_cache_hit_rate'} <= 50) {
-
+ (".hr_num($mystat{'Threads_created'})." created / ".hr_num($mystat{'Connections'})." connections)
-                        push(@adjvars,"thread_cache_size (> $myvar{'thread_cache_size'})");
-                } else {
-
+ (".hr_num($mystat{'Threads_created'})." created / ".hr_num($mystat{'Connections'})." connections)
-                }
-        }
@@ -766 +772 @@
-        if ($mystat{'Open_tables'} > 0) {
-                if ($mycalc{'table_cache_hit_rate'} < 20) {
-
+ (".hr_num($mystat{'Open_tables'})." open / ".hr_num($mystat{'Opened_tables'})." opened)
-                        if ($mysqlvermajor eq 6) {
-                                push(@adjvars,"table_cache (> ".$myvar{'table_open_cache'}.")");
@@ -774 +780 @@
-                        push(@generalrec,"Increase table_cache gradually to avoid file descriptor limits");
-                } else {
-
+ (".hr_num($mystat{'Open_tables'})." open / ".hr_num($mystat{'Opened_tables'})." opened)
-                }
-        }
@@ -781 +787 @@
-        if (defined $mycalc{'pct_files_open'}) {
-                if ($mycalc{'pct_files_open'} > 85) {
-
+ (".hr_num($mystat{'Open_files'})."/".hr_num($myvar{'open_files_limit'}).")
-                        push(@adjvars,"open_files_limit (> ".$myvar{'open_files_limit'}.")");
-                } else {
-
+ (".hr_num($mystat{'Open_files'})."/".hr_num($myvar{'open_files_limit'}).")
-                }
-        }
@@ -794 +800 @@
-                        push(@generalrec,"Optimize queries and/or use InnoDB to reduce lock wait");
-                } else {
-
+ (".hr_num($mystat{'Table_locks_immediate'})." immediate / ".hr_num($mystat{'Table_locks_waited'}+$mystat{'Table_locks_immediate'})." locks)
-                }
-        }