mysql-summary

#!/bin/bash
# This program is part of Aspersa (http://code.google.com/p/aspersa/)

# ########################################################################
# A script to summarize MySQL information in a nice way.
# Goals: work well on Linux; create a compact diff-able report that is
# easy to paste into a wiki or email, and easy to scan and compare too.
# 
# To use, simply execute it.  Optionally add the same command-line options
# you would use to connect to MySQL, such as "./summary-mysql --user=foo"
#
# Author: Baron Schwartz
# ########################################################################

# ########################################################################
# Globals, helper functions
# ########################################################################

# The awk code for fuzzy rounding.  (It's used in a few places, so makes sense
# not to duplicate).  It fuzzy-rounds the variable named fuzzy_var.  It goes in
# steps of 5, 10, 25, then repeats by a factor of 10 larger (50, 100, 250), and
# so on, until it finds a number that's large enough.  The pattern is slightly
# broken between the initial 1 and 50, because rounding to the nearest 2.5
# doesn't seem right to me.
fuzzy_formula='
   rounded = 0;
   if (fuzzy_var <= 10 ) {
      rounded   = 1;
   }
   factor = 1;
   while ( rounded == 0 ) {
      if ( fuzzy_var <= 50 * factor ) {
         fuzzy_var = sprintf("%.0f", fuzzy_var / (5 * factor)) * 5 * factor;
         rounded   = 1;
      }
      else if ( fuzzy_var <= 100  * factor) {
         fuzzy_var = sprintf("%.0f", fuzzy_var / (10 * factor)) * 10 * factor;
         rounded   = 1;
      }
      else if ( fuzzy_var <= 250  * factor) {
         fuzzy_var = sprintf("%.0f", fuzzy_var / (25 * factor)) * 25 * factor;
         rounded   = 1;
      }
      factor = factor * 10;
   }'

# The temp files are for storing working results so we don't call commands many
# times (gives inconsistent results, maybe adds load on things I don't want to
# such as RAID controllers).  They must not exist -- if they did, someone would
# symlink them to /etc/passwd and then run this program as root.  Call this
# function with "check", "rm", or "touch" as an argument.
temp_files() {
   for file in /tmp/aspersa{,-mysql-variables,-mysql-status,-innodb-status} \
         /tmp/aspersa{,-mysql-databases,-mysql-processlist} \
         /tmp/aspersa-mysql{dump,-slave};
   do
      case "$1" in
      check)
         if [ -e "${file}" ]; then
            echo "For security reasons, ${file} must not exist.";
            exit 1;
         fi
         ;;
      touch)
         if ! touch "${file}"; then
            echo "I can't make my temp file ${file}";
            exit 1;
         fi
         ;;
      rm)
         rm -f "${file}"
         ;;
      esac
   done
}

# Print a space-padded string into $line.  Then translate spaces to hashes, and
# underscores to spaces.  End result is a line of hashes with words at the
# start.
section () {
   line="$(printf '#_%-60s' "$1_")"
   line="${line// /#}"
   printf "%s\n" "${line//_/ }"
}

# Print a "name | value" line.
name_val() {
   printf "%20s | %s\n" "$1" "$2"
}

# Converts a value to units of power of 2.  Optional precision is $2.
shorten() {
   unit=k
   size=1024
   if [ $1 -ge 1099511627776 ] ; then
      size=1099511627776
      unit=T
   elif [ $1 -ge 1073741824 ] ; then
      size=1073741824
      unit=G
   elif [ $1 -ge 1048576 ] ; then
      size=1048576
      unit=M
   fi
   result=$(echo "$1 $size ${2:-0}" | awk '{printf "%." $3 "f", $1 / $2}')
   echo "${result}${unit}"
}

# Accepts a number of seconds, and outputs a d+h:m:s formatted string
secs_to_time () {
   echo "$1" | awk '{
      printf( "%d+%02d:%02d:%02d", $1 / 86400, ($1 % 86400) / 3600, ($1 % 3600) / 60, $1 % 60);
   }'
}

# gets a value from /tmp/aspersa-mysql-variables.  Returns zero if it doesn't
# exist.
get_var () {
   v="$(awk "\$1 ~ /^$1$/ { print \$2 }" /tmp/aspersa-mysql-variables)"
   echo "${v:-0}"
}

# gets a value from /tmp/aspersa-mysql-status.  Returns zero if it doesn't
# exist.
get_stat () {
   v="$(awk "\$1 ~ /^$1$/ { print \$2 }" /tmp/aspersa-mysql-status)"
   echo "${v:-0}"
}

# Does fuzzy rounding: rounds to nearest interval, but the interval gets larger
# as the number gets larger.  This is to make things easier to diff.
fuzz () {
   echo $1 | awk "{fuzzy_var=\$1; ${fuzzy_formula} print fuzzy_var;}"
}

# Fuzzy computes the percent that $1 is of $2
fuzzy_pct () {
   pct=$(echo $1 $2 | awk '{ if ($2 > 0) { printf "%d", $1/$2*100; } else {print 0} }');
   echo "$(fuzz ${pct})%"
}

# ##############################################################################
# Functions for parsing specific files and getting desired info from them.
# These are called from within main() and are separated so they can be tested
# easily.  The calling convention is that the data they need to run is prepared
# first by putting it into /tmp/aspersa.  Then code that's testing just needs to
# put sample data into /tmp/aspersa and call it.
# ##############################################################################

# Parses the output of 'ps -e -o args | grep mysqld'
# which should be in /tmp/aspersa.
parse_mysqld_instances () {
   echo "  Port  Data Directory             Socket"
   echo "  ===== ========================== ======"
   grep '/mysqld ' /tmp/aspersa | while read line; do
      for word in ${line}; do
         # Some grep doesn't have -o, so I have to pull out the words I want by
         # looking at each word
         if echo "${word}" | grep -- "--socket=" > /dev/null; then
            socket="$(echo "${word}" | cut -d= -f2)"
         fi
         if echo "${word}" | grep -- "--port=" > /dev/null; then
            port="$(echo "${word}" | cut -d= -f2)"
         fi
         if echo "${word}" | grep -- "--datadir=" > /dev/null; then
            datadir="$(echo "${word}" | cut -d= -f2)"
         fi
      done
      printf "  %5d %-26s %s\n" "${port}" "${datadir}" "${socket}"
   done
}

# Gets the MySQL system time.  Uses input from /tmp/aspersa-mysql-variables.
get_mysql_timezone () {
   tz="$(get_var time_zone)"
   if [ "${tz}" = "SYSTEM" ]; then
      tz="$(get_var system_time_zone)"
   fi
   echo "${tz}"
}

# Gets the MySQL system version.  Uses input from /tmp/aspersa-mysql-variables.
get_mysql_version () {
   name_val Version "$(get_var version) $(get_var version_comment)"
   name_val "Built On" "$(get_var version_compile_os) $(get_var version_compile_machine)"
}

# Gets the system start and uptime in human readable format.  Last restart date
# should be in /tmp/aspersa.
get_mysql_uptime () {
   restart="$(cat /tmp/aspersa)"
   uptime="$(get_stat Uptime)"
   uptime="$(secs_to_time ${uptime})"
   echo "${restart} (up ${uptime})"
}

# Summarizes the output of SHOW MASTER LOGS, which is in /tmp/aspersa
summarize_binlogs () {
   name_val "Binlogs" $(wc -l /tmp/aspersa)
   name_val "Zero-Sized" $(grep -c '\<0$' /tmp/aspersa)
   size=$(awk '{t += $2} END{printf "%0.f\n", t}' /tmp/aspersa)
   name_val "Total Size" $(shorten ${size} 1)
}

# Print out binlog_do_db and binlog_ignore_db
format_binlog_filters () {
   name_val "binlog_do_db" $(cut -f3 /tmp/aspersa)
   name_val "binlog_ignore_db" $(cut -f4 /tmp/aspersa)
}

# Takes as input a file that has two samples of SHOW STATUS, columnized next to
# each other.  These should be in /tmp/aspersa.  Outputs fuzzy-ed numbers:
# absolute, all-time per second, and per-second over the interval between the
# samples.  Omits any rows that are all zeroes.
format_status_variables () {
   # First, figure out the intervals.
   utime1=$(awk '/Uptime /{print $2}' /tmp/aspersa);
   utime2=$(awk '/Uptime /{print $3}' /tmp/aspersa);
   awk "
   BEGIN {
      utime1 = ${utime1};
      utime2 = ${utime2};
      udays  = utime1 / 86400;
      udiff  = utime2 - utime1;
      format=\"%-35s %11s %11s %11s\\n\";
      printf(format, \"Variable\", \"Per day\", \"Per second\", udiff \" secs\");
   }
   \$2 ~ /^[0-9]*\$/ {
      if ( \$2 > 0 && \$2 < 18446744073709551615 ) {
         if ( udays > 0 ) {
            fuzzy_var=\$2 / udays;
            ${fuzzy_formula};
            perday=fuzzy_var;
         }
         if ( utime1 > 0 ) {
            fuzzy_var=\$2 / utime1;
            ${fuzzy_formula};
            persec=fuzzy_var;
         }
         if ( udiff > 0 ) {
            fuzzy_var=(\$3 - \$2) / udiff;
            ${fuzzy_formula};
            nowsec=fuzzy_var;
         }
         perday = int(perday);
         persec = int(persec);
         nowsec = int(nowsec);
         if ( perday + persec + nowsec > 0 ) {
            if ( perday == 0 ) { perday = \"\"; }
            if ( persec == 0 ) { persec = \"\"; }
            if ( nowsec == 0 ) { nowsec = \"\"; }
            printf(format, \$1, perday, persec, nowsec);
         }
      }
   }" /tmp/aspersa
}

# Slices the processlist a bunch of different ways.  Uses input from
# /tmp/aspersa-mysql-processlist.  The processlist should be created with the \G
# flag so it's vertical.  The parsing is a bit awkward because different
# versions of awk have limitations like "too many fields on line xyz".  So we
# use 'cut' to shorten the lines.  We count all things into temporary variables
# for each process in the processlist, and when we hit the Info: line which
# ought to be the last line in the process, we decide what to do with the temp
# variables.  If we're summarizing Command, we count everything; otherwise, only
# non-Sleep processes get counted towards the sum and max of Time.
summarize_processlist () {
   for param in Command User Host db State; do
      echo
      printf '  %-30s %8s %7s %9s %9s\n' \
         "${param}" "COUNT(*)" Working "SUM(Time)" "MAX(Time)"
      echo "  ------------------------------" \
         "-------- ------- --------- ---------"
      cut -c1-80 /tmp/aspersa-mysql-processlist \
         | awk "
         \$1 == \"${param}:\" {
            p = substr(\$0, index(\$0, \":\") + 2);
            if ( index(p, \":\") > 0 ) {
               p = substr(p, 1, index(p, \":\") - 1);
            }
            if ( length(p) > 30 ) {
               p = substr(p, 1, 30);
            }
         }
         \$1 == \"Time:\" {
            t = \$2;
         }
         \$1 == \"Command:\" {
            c = \$2;
         }
         \$1 == \"Info:\" {
            count[p]++;
            if ( c == \"Sleep\" ) {
               sleep[p]++;
            }
            if ( \"${param}\" == \"Command\" || c != \"Sleep\" ) {
               time[p] += t;
               if ( t > mtime[p] ) { mtime[p] = t; }
            }
         }
         END {
            for ( p in count ) {
               fuzzy_var=count[p]-sleep[p]; ${fuzzy_formula} fuzzy_work=fuzzy_var;
               fuzzy_var=count[p];          ${fuzzy_formula} fuzzy_count=fuzzy_var;
               fuzzy_var=time[p];           ${fuzzy_formula} fuzzy_time=fuzzy_var;
               fuzzy_var=mtime[p];          ${fuzzy_formula} fuzzy_mtime=fuzzy_var;
               printf \"  %-30s %8d %7d %9d %9d\n\", p, fuzzy_count, fuzzy_work, fuzzy_time, fuzzy_mtime;
            }
         }
      " | sort
   done
   echo
}

# Pretty-prints the my.cnf file, which should be in /tmp/aspersa.
pretty_print_cnf_file () {
   awk '/^[a-zA-Z[]/ {
      if ($0 ~ /=/) {
         fields=split($0, arr, "[[:space:]]*=[[:space:]]*");
         printf("%-35s = %s\n", arr[1], arr[2]);
      }
      else {
         print $1;
      }
   }' /tmp/aspersa
}

# Summarizes various things about InnoDB status that are not easy to see by eye.
# Input file is /tmp/aspersa-innodb-status.
format_innodb_status () {
   oldest=$(sed -n -e '/^---TRANSACTION.*[0-9] sec,/{s/^.* \([0-9]*\) sec.*$/\1/; p}' \
      /tmp/aspersa-innodb-status | sort -rn | head -n1);
   echo "Oldest Transaction ${oldest} Seconds"
   echo "Transaction States"
   awk -F, '/^---TRANSACTION/{print $2}' /tmp/aspersa-innodb-status \
      | sed -e 's/ [0-9]* sec.*//' | sort | uniq -c | sort -rn
   echo "Tables Locked"
   awk '/^TABLE LOCK table/{print $4}' /tmp/aspersa-innodb-status \
      | sort | uniq -c | sort -rn
   echo "Semaphore Waits"
   grep 'has waited at' /tmp/aspersa-innodb-status | cut -d' ' -f6-8 | sort \
      | uniq -c | sort -rn
   echo "Semaphore Holders"
   awk '/has reserved it in mode/{
      print substr($0, 1 + index($0, "("), index($0, ")") - index($0, "(") - 1);
   }' /tmp/aspersa-innodb-status | sort | uniq -c | sort -rn
}

# Summarizes per-database statistics for a bunch of different things: count of
# tables, views, etc.  Data should be in /tmp/aspersa-mysqldump and /tmp/aspersa
# gets overwritten.
format_overall_db_stats () {
   echo
   # We keep counts of everything in an associative array keyed by db name, and
   # what it is.  The num_dbs counter is to ensure sort order is consistent when
   # we run the awk commands following this one.
   awk '
      BEGIN {
         num_dbs = 0;
      }
      /^USE `.*`;$/ {
         db = substr($2, 2, length($2) - 3);
         if ( db_seen[db]++ == 0 ) {
            dbs[num_dbs] = db;
            num_dbs++;
         }
      }
      /^CREATE TABLE/ {
         counts[db ",tables"]++;
      }
      /CREATE ALGORITHM=/ {
         counts[db ",views"]++;
      }
      /03 CREATE.*03 PROCEDURE/ {
         counts[db ",sps"]++;
      }
      /03 CREATE.*03 FUNCTION/ {
         counts[db ",func"]++;
      }
      /03 CREATE.*03 TRIGGER/ {
         counts[db ",trg"]++;
      }
      /FOREIGN KEY/ {
         counts[db ",fk"]++;
      }
      /PARTITION BY/ {
         counts[db ",partn"]++;
      }
      END {
         mdb = length("Database");
         for ( i=0;i<num_dbs;i++ ) {
            if ( length(dbs[i]) > mdb ) {
               mdb = length(dbs[i]);
            }
         }
         fmt = "  %-" mdb "s %6s %5s %3s %5s %5s %5s %5s\n";
         printf fmt, "Database", "Tables", "Views", "SPs", "Trigs", "Funcs", "FKs", "Partn";
         for ( i=0;i<num_dbs;i++ ) {
            db = dbs[i];
            printf fmt, db, counts[db ",tables"], counts[db ",views"], counts[db ",sps"], counts[db ",trg"], counts[db ",func"], counts[db ",fk"], counts[db ",partn"];
         }
      }
   ' /tmp/aspersa-mysqldump > /tmp/aspersa
   head -n2 /tmp/aspersa
   tail -n+3 /tmp/aspersa | sort

   echo
   # Now do the summary of engines per DB
   awk '
      BEGIN {
         num_dbs     = 0;
         num_engines = 0;
      }
      /^USE `.*`;$/ {
         db = substr($2, 2, length($2) - 3);
         if ( db_seen[db]++ == 0 ) {
            dbs[num_dbs] = db;
            num_dbs++;
         }
      }
      /^\) ENGINE=/ {
         engine=substr($2, index($2, "=") + 1);
         if ( engine_seen[engine]++ == 0 ) {
            engines[num_engines] = engine;
            num_engines++;
         }
         counts[db "," engine]++;
      }
      END {
         mdb = length("Database");
         for ( i=0;i<num_dbs;i++ ) {
            db = dbs[i];
            if ( length(db) > mdb ) {
               mdb = length(db);
            }
         }
         fmt = "  %-" mdb "s"
         printf fmt, "Database";
         for ( i=0;i<num_engines;i++ ) {
            engine = engines[i];
            fmts[engine] = " %" length(engine) "s";
            printf fmts[engine], engine;
         }
         print "";
         for ( i=0;i<num_dbs;i++ ) {
            db = dbs[i];
            printf fmt, db;
            for ( j=0;j<num_engines;j++ ) {
               engine = engines[j];
               printf fmts[engine], counts[db "," engine];
            }
            print "";
         }
      }
   ' /tmp/aspersa-mysqldump > /tmp/aspersa
   head -n1 /tmp/aspersa
   tail -n+2 /tmp/aspersa | sort

   echo
   # Now do the summary of index types per DB. Careful -- index is a reserved
   # word in awk.
   awk '
      BEGIN {
         num_dbs   = 0;
         num_idxes = 0;
      }
      /^USE `.*`;$/ {
         db = substr($2, 2, length($2) - 3);
         if ( db_seen[db]++ == 0 ) {
            dbs[num_dbs] = db;
            num_dbs++;
         }
      }
      /KEY/ {
         idx="BTREE";
         if ( $0 ~ /SPATIAL/ ) {
            idx="SPATIAL";
         }
         if ( $0 ~ /FULLTEXT/ ) {
            idx="FULLTEXT";
         }
         if ( $0 ~ /USING RTREE/ ) {
            idx="RTREE";
         }
         if ( $0 ~ /USING HASH/ ) {
            idx="HASH";
         }
         if ( idx_seen[idx]++ == 0 ) {
            idxes[num_idxes] = idx;
            num_idxes++;
         }
         counts[db "," idx]++;
      }
      END {
         mdb = length("Database");
         for ( i=0;i<num_dbs;i++ ) {
            db = dbs[i];
            if ( length(db) > mdb ) {
               mdb = length(db);
            }
         }
         fmt = "  %-" mdb "s"
         printf fmt, "Database";
         for ( i=0;i<num_idxes;i++ ) {
            idx = idxes[i];
            fmts[idx] = " %" length(idx) "s";
            printf fmts[idx], idx;
         }
         print "";
         for ( i=0;i<num_dbs;i++ ) {
            db = dbs[i];
            printf fmt, db;
            for ( j=0;j<num_idxes;j++ ) {
               idx = idxes[j];
               printf fmts[idx], counts[db "," idx];
            }
            print "";
         }
      }
   ' /tmp/aspersa-mysqldump > /tmp/aspersa
   head -n1 /tmp/aspersa
   tail -n+2 /tmp/aspersa | sort

   echo
   # Now do the summary of datatypes per DB
   awk '
      BEGIN {
         num_dbs     = 0;
         num_types = 0;
      }
      /^USE `.*`;$/ {
         db = substr($2, 2, length($2) - 3);
         if ( db_seen[db]++ == 0 ) {
            dbs[num_dbs] = db;
            num_dbs++;
         }
      }
      /^  `/ {
         str = $0;
         str = substr(str, index(str, "`") + 1);
         str = substr(str, index(str, "`") + 2);
         if ( index(str, " ") > 0 ) {
            str = substr(str, 1, index(str, " ") - 1);
         }
         if ( index(str, ",") > 0 ) {
            str = substr(str, 1, index(str, ",") - 1);
         }
         if ( index(str, "(") > 0 ) {
            str = substr(str, 1, index(str, "(") - 1);
         }
         type = str;
         if ( type_seen[type]++ == 0 ) {
            types[num_types] = type;
            num_types++;
         }
         counts[db "," type]++;
      }
      END {
         mdb = length("Database");
         for ( i=0;i<num_dbs;i++ ) {
            db = dbs[i];
            if ( length(db) > mdb ) {
               mdb = length(db);
            }
         }
         fmt = "  %-" mdb "s"
         printf fmt, "Database";
         for ( i=0;i<num_types;i++ ) {
            type = types[i];
            fmts[type] = " %" length(type) "s";
            printf fmts[type], type;
         }
         print "";
         for ( i=0;i<num_dbs;i++ ) {
            db = dbs[i];
            printf fmt, db;
            for ( j=0;j<num_types;j++ ) {
               type = types[j];
               printf fmts[type], counts[db "," type];
            }
            print "";
         }
      }
   ' /tmp/aspersa-mysqldump > /tmp/aspersa
   head -n1 /tmp/aspersa
   tail -n+2 /tmp/aspersa | sort
   echo

}

# ##############################################################################
# The main() function is called at the end of the script.  This makes it
# testable.  Major bits of parsing are separated into functions for testability.
# ##############################################################################
main () {

   # Begin by setting the $PATH to include some common locations that are not
   # always in the $PATH, including the "sbin" locations.
   export PATH="${PATH}:/usr/local/bin:/usr/bin:/bin:/usr/libexec"
   export PATH="${PATH}:/usr/local/sbin:/usr/sbin:/sbin"

   # Set up temporary files.
   temp_files "check"
   temp_files "touch"

   # ########################################################################
   # Header for the whole thing, table of discovered instances
   # ########################################################################
   section Aspersa_MySQL_Summary_Report
   name_val "System time" "`date -u +'%F %T UTC'` (local TZ: `date +'%Z %z'`)"
   section Instances
   ps -ea -o args | grep mysqld > /tmp/aspersa
   parse_mysqld_instances

   # ########################################################################
   # Fetch some basic info so we can start
   # ########################################################################
   mysql -ss -e 'SHOW GRANTS' > /tmp/aspersa
   if [ "$?" != "0" ]; then
      echo "Cannot connect to mysql, please specify command-line options."
      temp_files "rm"
      exit 1
   fi
   user="$(sed -e 's/^.*TO //; s/ .*//g; q' /tmp/aspersa)";
   mysql -ss -e 'SHOW /*!40100 GLOBAL*/ VARIABLES' $* > /tmp/aspersa-mysql-variables
   mysql -ss -e 'SHOW /*!50000 GLOBAL*/ STATUS' $* > /tmp/aspersa-mysql-status
   mysql -ss -e 'SHOW DATABASES' $* > /tmp/aspersa-mysql-databases 2>/dev/null
   mysql -ssE -e 'SHOW SLAVE STATUS' $* > /tmp/aspersa-mysql-slave 2>/dev/null
   mysql -ssE -e 'SHOW /*!50000 ENGINE*/ INNODB STATUS' $* > /tmp/aspersa-innodb-status 2>/dev/null
   mysql -ssE -e 'SHOW FULL PROCESSLIST' $* > /tmp/aspersa-mysql-processlist 2>/dev/null
   now="$(mysql -ss -e 'SELECT NOW()' $*)"
   port="$(get_var port)"

   # ########################################################################
   # General date, hostname, etc
   # ########################################################################
   section "Report_On_Port_${port}"
   name_val User "${user}"
   name_val Time "${now} ($(get_mysql_timezone))"
   name_val Hostname "$(get_var hostname)"
   get_mysql_version

   uptime="$(get_stat Uptime)"
   mysql -ss -e "SELECT LEFT(NOW() - INTERVAL ${uptime} SECOND, 16)" $* \
      > /tmp/aspersa
   name_val Started "$(get_mysql_uptime)"

   name_val Databases "$(grep -c . /tmp/aspersa-mysql-databases)"
   name_val Datadir "$(get_var datadir)"
   procs="$(get_stat Threads_connected)"
   procr="$(get_stat Threads_running)"
   name_val Processes "$(fuzz ${procs}) connected, $(fuzz ${procr}) running"
   if [ -s /tmp/aspersa-mysql-slave ]; then slave=""; else slave="not "; fi
   slavecount=$(grep -c 'Binlog Dump' /tmp/aspersa-mysql-processlist)
   name_val Replication "Is ${slave}a slave, has ${slavecount} slaves connected"

   # ########################################################################
   # Processlist, sliced several different ways
   # ########################################################################
   section Processlist
   summarize_processlist

   # ########################################################################
   # Queries and query plans
   # ########################################################################
   section "Status_Variables_(Wait_10_Seconds)"
   sleep 10
   mysql -ss -e 'SHOW /*!50000 GLOBAL*/ STATUS' $* \
      | join /tmp/aspersa-mysql-status - > /tmp/aspersa 
   format_status_variables

   # ########################################################################
   # Table cache
   # ########################################################################
   section Table_cache
   table_cache=$(get_var table_cache)
   if [ -z "${table_cache}" ]; then
      table_cache=$(get_var table_open_cache)
   fi
   name_val Size "${table_cache}"
   open_tables=$(get_stat Open_tables)
   name_val Usage  "$(fuzzy_pct ${open_tables} ${table_cache})"

   # ########################################################################
   # Query cache
   # ########################################################################
   query_cache_size=$(get_var query_cache_size);
   if [ "$(get_var have_query_cache)" ]; then
      section Query_cache
      name_val query_cache_type $(get_var query_cache_type)
      name_val Size "$(shorten ${query_cache_size} 1)"
      used=$(( ${query_cache_size} - $(get_stat Qcache_free_memory) ))
      name_val Usage "$(fuzzy_pct ${used} ${query_cache_size})"
   fi

   # ########################################################################
   # Schema, databases, data type, other analysis.
   # ########################################################################
   section Schema
   # Assume "no" if stdin or stdout is not a terminal, so this can be run and
   # put into a file, or piped into a pager, or something else like that.
   if [ -t 0 -a -t 1 ]; then
      echo -n "Would you like to mysqldump -d the schema and analyze it? y/n "
      read reply
      reply=${reply:-n}
   fi
   if echo "${reply:-n}" | grep -i '^y' > /dev/null ; then
      mysqldump --all-databases --no-data --skip-comments \
         --skip-add-locks --skip-add-drop-table --compact \
         --skip-lock-all-tables --skip-lock-tables --skip-set-charset \
         --routines --triggers $* > /tmp/aspersa-mysqldump
      if [ "$?" -eq 0 ]; then
         format_overall_db_stats
      else
         echo "Something went wrong, skipping schema analysis"
      fi
   else
      echo "Skipping schema analysis"
   fi

   # ########################################################################
   # Noteworthy Technologies
   # ########################################################################
   section Noteworthy_Technologies
   if [ -e /tmp/aspersa-mysqldump ]; then
      if grep FULLTEXT /tmp/aspersa-mysqldump > /dev/null; then
         name_val "Full Text Indexing" Yes
      else
         name_val "Full Text Indexing" No
      fi
      if grep 'GEOMETRY\|POINT\|LINESTRING\|POLYGON' /tmp/aspersa-mysqldump > /dev/null; then
         name_val "Geospatial Types" Yes
      else
         name_val "Geospatial Types" No
      fi
      if grep 'FOREIGN KEY' /tmp/aspersa-mysqldump > /dev/null; then
         name_val "Foreign Keys" Yes
      else
         name_val "Foreign Keys" No
      fi
   fi
   if [ "$(get_stat Ssl_accepts)" -gt 0 ]; then
      name_val "SSL" Yes
   else
      name_val "SSL" No
   fi
   if [ "$(get_stat Com_lock_tables)" -gt 0 ]; then
      name_val "Explicit LOCK TABLES" Yes
   else
      name_val "Explicit LOCK TABLES" No
   fi
   if [ "$(get_stat Delayed_writes)" -gt 0 ]; then
      name_val "Delayed Insert" Yes
   else
      name_val "Delayed Insert" No
   fi
   if [ "$(get_stat Com_xa_start)" -gt 0 ]; then
      name_val "XA Transactions" Yes
   else
      name_val "XA Transactions" No
   fi
   if [ "$(get_stat Ndb_cluster_node_id)" -gt 0 ]; then
      name_val "NDB Cluster" Yes
   else
      name_val "NDB Cluster" No
   fi
   prep=$(( $(get_stat Com_stmt_prepare) + $(get_stat Com_prepare_sql) ))
   if [ "${prep}" -gt 0 ]; then
      name_val "Prepared Statements" Yes
   else
      name_val "Prepared Statements" No
   fi

   # ########################################################################
   # InnoDB
   # ########################################################################
   section InnoDB
   have_innodb=$(get_var have_innodb)
   if [ "${have_innodb}" = "YES" ]; then

      version=$(get_var innodb_version)
      name_val Version ${version:-default}

      bp_size="$(get_var innodb_buffer_pool_size)"
      name_val "Buffer Pool Size" "$(shorten ${bp_size} 1)"

      bp_pags="$(get_stat Innodb_buffer_pool_pages_total)"
      bp_free="$(get_stat Innodb_buffer_pool_pages_free)"
      bp_dirt="$(get_stat Innodb_buffer_pool_pages_dirty)"
      bp_fill=$((${bp_pags} - ${bp_free}))
      name_val "Buffer Pool Fill"   "$(fuzzy_pct ${bp_fill} ${bp_pags})"
      name_val "Buffer Pool Dirty"  "$(fuzzy_pct ${bp_dirt} ${bp_pags})"

      name_val "File Per Table"      $(get_var innodb_file_per_table)
      name_val "Page Size"           $(shorten $(get_stat Innodb_page_size))

      lg_size="$(get_var innodb_log_file_size)"
      lg_fils="$(get_var innodb_log_files_in_group)"
      lg_totl="$((${lg_size} * ${lg_fils}))"
      name_val "Log File Size" "${lg_fils} * $(shorten ${lg_size}) = $(shorten ${lg_totl})"
      name_val "Log Buffer Size"     $(shorten $(get_var innodb_log_buffer_size))
      name_val "Flush Method"        $(get_var innodb_flush_method)
      name_val "Flush Log At Commit" $(get_var innodb_flush_log_at_trx_commit)
      name_val "XA Support"          $(get_var innodb_support_xa)
      name_val "Checksums"           $(get_var innodb_checksums)
      name_val "Doublewrite"         $(get_var innodb_doublewrite)
      name_val "R/W I/O Threads"     "$(get_var innodb_read_io_threads) $(get_var innodb_write_io_threads)"
      name_val "I/O Capacity"        $(get_var innodb_io_capacity)
      name_val "Thread Concurrency"  $(get_var innodb_thread_concurrency)
      name_val "Concurrency Tickets" $(get_var innodb_concurrency_tickets)
      name_val "Commit Concurrency"  $(get_var innodb_commit_concurrency)
      name_val "Txn Isolation Level" $(get_var tx_isolation)

      if [ -s /tmp/aspersa-innodb-status ]; then
         format_innodb_status
      fi
   fi

   # ########################################################################
   # MyISAM
   # ########################################################################
   section MyISAM
   buf_size=$(get_var key_buffer_size)
   blk_size=$(get_var key_cache_block_size)
   blk_unus=$(get_stat Key_blocks_unused)
   blk_unfl=$(get_stat Key_blocks_not_flushed)
   unus=$((${blk_unus} * ${blk_size}))
   unfl=$((${blk_unfl} * ${blk_size}))
   used=$((${buf_size} - ${unus}))
   name_val "Key Cache" "$(shorten ${buf_size} 1)"
   name_val "Pct Used" "$(fuzzy_pct ${used} ${buf_size})"
   name_val "Unflushed" "$(fuzzy_pct ${unfl} ${buf_size})"

   # ########################################################################
   # Users & Security
   # ########################################################################
   section Security
   users="$(mysql -ss \
      -e 'SELECT COUNT(*), SUM(user=""), SUM(password=""), SUM(password NOT LIKE "*%") FROM mysql.user' $* 2>/dev/null \
      | awk '{printf "%d users, %d anon, %d w/o pw, %d old pw\n", $1, $2, $3, $4}')"
   name_val Users "${users}"
   name_val "Old Passwords" $(get_var old_passwords)

   # ########################################################################
   # Binary Logging
   # ########################################################################
   section Binary_Logging
   binlog=$(get_var log_bin)
   if [ "${binlog}" ]; then
      mysql -ss -e 'SHOW MASTER LOGS' $* > /tmp/aspersa 2>/dev/null
      summarize_binlogs
      format="$(get_var binlog_format)"
      name_val binlog_format "${format:-STATEMENT}"
      name_val expire_logs_days $(get_var expire_logs_days)
      name_val sync_binlog $(get_var sync_binlog)
      name_val server_id $(get_var server_id)
      mysql -ss -e 'SHOW MASTER STATUS' $* > /tmp/aspersa 2>/dev/null
      format_binlog_filters
   fi

# Replication: seconds behind, running, filters, skip_slave_start, skip_errors,
# read_only, temp tables open, slave_net_timeout, slave_exec_mode

   # ########################################################################
   # Interesting things that you just ought to know about.
   # ########################################################################
   section Noteworthy_Variables
   name_val "Auto-Inc Incr/Offset" "$(get_var auto_increment_increment)/$(get_var auto_increment_offset)"
   for v in \
      default_storage_engine flush_time init_connect init_file sql_mode;
   do
      name_val ${v} $(get_var ${v})
   done
   for v in \
      join_buffer_size sort_buffer_size read_buffer_size read_rnd_buffer_size \
      max_heap_table_size tmp_table_size thread_stack;
   do
      name_val ${v} $(shorten $(get_var ${v}))
   done
   for v in log log_error log_warnings log_slow_queries \
         log_queries_not_using_indexes log_slave_updates;
   do
      name_val ${v} $(get_var ${v})
   done

   # ########################################################################
   # If there is a my.cnf in a standard location, see if we can pretty-print it.
   # ########################################################################
   section Configuration_File
   cnf_file='/etc/my.cnf'
   if [ ! -e "${cnf_file}" ]; then
      cnf_file="/etc/mysql/my.cnf";
   fi
   if [ -e "${cnf_file}" ]; then
      name_val "Config File" "${cnf_file}"
      cat "${cnf_file}" > /tmp/aspersa
      pretty_print_cnf_file
   else
      name_val "Config File" "Not found"
   fi

   temp_files "rm"
}

# Execute the program if it was not included from another file.  This makes it
# possible to include without executing, and thus test.
if [ $(basename "$0") = "mysql-summary" ]; then main $*; fi