#!/usr/bin/env perl
# mysqltuner.pl - Version 1.7.5
# High Performance MySQL Tuning Script
# Copyright (C) 2006-2017 Major Hayden - major@mhtx.net
#
# For the latest updates, please visit http://mysqltuner.com/
# Git repository available at http://github.com/major/MySQLTuner-perl
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program. If not, see .
#
# This project would not be possible without help from:
# Matthew Montgomery Paul Kehrer Dave Burgess
# Jonathan Hinds Mike Jackson Nils Breunese
# Shawn Ashlee Luuk Vosslamber Ville Skytta
# Trent Hornibrook Jason Gill Mark Imbriaco
# Greg Eden Aubin Galinotti Giovanni Bechis
# Bill Bradford Ryan Novosielski Michael Scheidell
# Blair Christensen Hans du Plooy Victor Trac
# Everett Barnes Tom Krouper Gary Barrueto
# Simon Greenaway Adam Stein Isart Montane
# Baptiste M. Cole Turner Major Hayden
# Joe Ashcraft Jean-Marie Renouard Christian Loos
# Julien Francoz
#
# Inspired by Matthew Montgomery's tuning-primer.sh script:
# http://forge.mysql.com/projects/view.php?id=44
#
package main;
use 5.005;
use strict;
use warnings;
use diagnostics;
use File::Spec;
use Getopt::Long;
use Pod::Usage;
use File::Basename;
use Cwd 'abs_path';
use Data::Dumper;
$Data::Dumper::Pair = " : ";
# for which()
#use Env;
# Set up a few variables for use in the script
my $tunerversion = "1.7.5";
my ( @adjvars, @generalrec );
# Set defaults
my %opt = (
"silent" => 0,
"nobad" => 0,
"nogood" => 0,
"noinfo" => 0,
"debug" => 0,
"nocolor" => ( !-t STDOUT ),
"forcemem" => 0,
"forceswap" => 0,
"host" => 0,
"socket" => 0,
"port" => 0,
"user" => 0,
"pass" => 0,
"password" => 0,
"ssl-ca" => 0,
"skipsize" => 0,
"checkversion" => 0,
"updateversion" => 0,
"buffers" => 0,
"passwordfile" => 0,
"bannedports" => '',
"maxportallowed" => 0,
"outputfile" => 0,
"dbstat" => 0,
"idxstat" => 0,
"sysstat" => 0,
"pfstat" => 0,
"skippassword" => 0,
"noask" => 0,
"template" => 0,
"json" => 0,
"prettyjson" => 0,
"reportfile" => 0,
"verbose" => 0,
"defaults-file" => '',
);
# Gather the options from the command line
GetOptions(
\%opt, 'nobad',
'nogood', 'noinfo',
'debug', 'nocolor',
'forcemem=i', 'forceswap=i',
'host=s', 'socket=s',
'port=i', 'user=s',
'pass=s', 'skipsize',
'checkversion', 'mysqladmin=s',
'mysqlcmd=s', 'help',
'buffers', 'skippassword',
'passwordfile=s', 'outputfile=s',
'silent', 'dbstat',
'json', 'prettyjson',
'idxstat', 'noask',
'template=s', 'reportfile=s',
'cvefile=s', 'bannedports=s',
'updateversion', 'maxportallowed=s',
'verbose', 'sysstat',
'password=s', 'pfstat',
'passenv=s', 'userenv=s',
'defaults-file=s', 'ssl-ca=s'
)
or pod2usage(
-exitval => 1,
-verbose => 99,
-sections => [
"NAME",
"IMPORTANT USAGE GUIDELINES",
"CONNECTION AND AUTHENTIFICATION",
"PERFORMANCE AND REPORTING OPTIONS",
"OUTPUT OPTIONS"
]
);
if ( defined $opt{'help'} && $opt{'help'} == 1 ) {
pod2usage(
-exitval => 0,
-verbose => 99,
-sections => [
"NAME",
"IMPORTANT USAGE GUIDELINES",
"CONNECTION AND AUTHENTIFICATION",
"PERFORMANCE AND REPORTING OPTIONS",
"OUTPUT OPTIONS"
]
);
}
my $devnull = File::Spec->devnull();
my $basic_password_files =
( $opt{passwordfile} eq "0" )
? abs_path( dirname(__FILE__) ) . "/basic_passwords.txt"
: abs_path( $opt{passwordfile} );
# Username from envvar
if ( exists $opt{userenv} && exists $ENV{ $opt{userenv} } ) {
$opt{user} = $ENV{ $opt{userenv} };
}
# Related to password option
if ( exists $opt{passenv} && exists $ENV{ $opt{passenv} } ) {
$opt{pass} = $ENV{ $opt{passenv} };
}
$opt{pass} = $opt{password} if ( $opt{pass} eq 0 and $opt{password} ne 0 );
# for RPM distributions
$basic_password_files = "/usr/share/mysqltuner/basic_passwords.txt"
unless -f "$basic_password_files";
# check if we need to enable verbose mode
if ( $opt{verbose} ) {
$opt{checkversion} = 1; #Check for updates to MySQLTuner
$opt{dbstat} = 1; #Print database information
$opt{idxstat} = 1; #Print index information
$opt{sysstat} = 1; #Print index information
$opt{buffers} = 1; #Print global and per-thread buffer values
$opt{pfstat} = 1; #Print performance schema info.
$opt{cvefile} = 'vulnerabilities.csv'; #CVE File for vulnerability checks
}
# for RPM distributions
$opt{cvefile} = "/usr/share/mysqltuner/vulnerabilities.csv"
unless ( defined $opt{cvefile} and -f "$opt{cvefile}" );
$opt{cvefile} = '' unless -f "$opt{cvefile}";
$opt{cvefile} = './vulnerabilities.csv' if -f './vulnerabilities.csv';
$opt{'bannedports'} = '' unless defined( $opt{'bannedports'} );
my @banned_ports = split ',', $opt{'bannedports'};
#
my $outputfile = undef;
$outputfile = abs_path( $opt{outputfile} ) unless $opt{outputfile} eq "0";
my $fh = undef;
open( $fh, '>', $outputfile )
or die("Fail opening $outputfile")
if defined($outputfile);
$opt{nocolor} = 1 if defined($outputfile);
# Setting up the colors for the print styles
my $me = `whoami`;
$me =~ s/\n//g;
# Setting up the colors for the print styles
my $good = ( $opt{nocolor} == 0 ) ? "[\e[0;32mOK\e[0m]" : "[OK]";
my $bad = ( $opt{nocolor} == 0 ) ? "[\e[0;31m!!\e[0m]" : "[!!]";
my $info = ( $opt{nocolor} == 0 ) ? "[\e[0;34m--\e[0m]" : "[--]";
my $deb = ( $opt{nocolor} == 0 ) ? "[\e[0;31mDG\e[0m]" : "[DG]";
my $cmd = ( $opt{nocolor} == 0 ) ? "\e[1;32m[CMD]($me)" : "[CMD]($me)";
my $end = ( $opt{nocolor} == 0 ) ? "\e[0m" : "";
# Checks for supported or EOL'ed MySQL versions
my ( $mysqlvermajor, $mysqlverminor, $mysqlvermicro );
# Super structure containing all information
my %result;
$result{'MySQLTuner'}{'version'} = $tunerversion;
$result{'MySQLTuner'}{'options'} = \%opt;
# Functions that handle the print styles
sub prettyprint {
print $_[0] . "\n" unless ( $opt{'silent'} or $opt{'json'} );
print $fh $_[0] . "\n" if defined($fh);
}
sub goodprint { prettyprint $good. " " . $_[0] unless ( $opt{nogood} == 1 ); }
sub infoprint { prettyprint $info. " " . $_[0] unless ( $opt{noinfo} == 1 ); }
sub badprint { prettyprint $bad. " " . $_[0] unless ( $opt{nobad} == 1 ); }
sub debugprint { prettyprint $deb. " " . $_[0] unless ( $opt{debug} == 0 ); }
sub redwrap {
return ( $opt{nocolor} == 0 ) ? "\e[0;31m" . $_[0] . "\e[0m" : $_[0];
}
sub greenwrap {
return ( $opt{nocolor} == 0 ) ? "\e[0;32m" . $_[0] . "\e[0m" : $_[0];
}
sub cmdprint { prettyprint $cmd. " " . $_[0] . $end; }
sub infoprintml {
for my $ln (@_) { $ln =~ s/\n//g; infoprint "\t$ln"; }
}
sub infoprintcmd {
cmdprint "@_";
infoprintml grep { $_ ne '' and $_ !~ /^\s*$/ } `@_ 2>&1`;
}
sub subheaderprint {
my $tln = 100;
my $sln = 8;
my $ln = length("@_") + 2;
prettyprint " ";
prettyprint "-" x $sln . " @_ " . "-" x ( $tln - $ln - $sln );
}
sub infoprinthcmd {
subheaderprint "$_[0]";
infoprintcmd "$_[1]";
}
# Calculates the number of phyiscal cores considering HyperThreading
sub cpu_cores {
my $cntCPU =
`awk -F: '/^core id/ && !P[\$2] { CORES++; P[\$2]=1 }; /^physical id/ && !N[\$2] { CPUs++; N[\$2]=1 }; END { print CPUs*CORES }' /proc/cpuinfo`;
return ( $cntCPU == 0 ? `nproc` : $cntCPU );
}
# Calculates the parameter passed in bytes, then rounds it to one decimal place
sub hr_bytes {
my $num = shift;
return "0B" unless defined($num);
return "0B" if $num eq "NULL";
if ( $num >= ( 1024**3 ) ) { #GB
return sprintf( "%.1f", ( $num / ( 1024**3 ) ) ) . "G";
}
elsif ( $num >= ( 1024**2 ) ) { #MB
return sprintf( "%.1f", ( $num / ( 1024**2 ) ) ) . "M";
}
elsif ( $num >= 1024 ) { #KB
return sprintf( "%.1f", ( $num / 1024 ) ) . "K";
}
else {
return $num . "B";
}
}
sub hr_raw {
my $num = shift;
return "0" unless defined($num);
return "0" if $num eq "NULL";
if ( $num =~ /^(\d+)G$/ ) {
return $1 * 1024 * 1024 * 1024;
}
if ( $num =~ /^(\d+)M$/ ) {
return $1 * 1024 * 1024;
}
if ( $num =~ /^(\d+)K$/ ) {
return $1 * 1024;
}
if ( $num =~ /^(\d+)$/ ) {
return $1;
}
return $num;
}
# Calculates the parameter passed in bytes, then rounds it to the nearest integer
sub hr_bytes_rnd {
my $num = shift;
return "0B" unless defined($num);
return "0B" if $num eq "NULL";
if ( $num >= ( 1024**3 ) ) { #GB
return int( ( $num / ( 1024**3 ) ) ) . "G";
}
elsif ( $num >= ( 1024**2 ) ) { #MB
return int( ( $num / ( 1024**2 ) ) ) . "M";
}
elsif ( $num >= 1024 ) { #KB
return int( ( $num / 1024 ) ) . "K";
}
else {
return $num . "B";
}
}
# Calculates the parameter passed to the nearest power of 1000, then rounds it to the nearest integer
sub hr_num {
my $num = shift;
if ( $num >= ( 1000**3 ) ) { # Billions
return int( ( $num / ( 1000**3 ) ) ) . "B";
}
elsif ( $num >= ( 1000**2 ) ) { # Millions
return int( ( $num / ( 1000**2 ) ) ) . "M";
}
elsif ( $num >= 1000 ) { # Thousands
return int( ( $num / 1000 ) ) . "K";
}
else {
return $num;
}
}
# Calculate Percentage
sub percentage {
my $value = shift;
my $total = shift;
$total = 0 unless defined $total;
$total = 0 if $total eq "NULL";
return 100, 00 if $total == 0;
return sprintf( "%.2f", ( $value * 100 / $total ) );
}
# Calculates uptime to display in a more attractive form
sub pretty_uptime {
my $uptime = shift;
my $seconds = $uptime % 60;
my $minutes = int( ( $uptime % 3600 ) / 60 );
my $hours = int( ( $uptime % 86400 ) / (3600) );
my $days = int( $uptime / (86400) );
my $uptimestring;
if ( $days > 0 ) {
$uptimestring = "${days}d ${hours}h ${minutes}m ${seconds}s";
}
elsif ( $hours > 0 ) {
$uptimestring = "${hours}h ${minutes}m ${seconds}s";
}
elsif ( $minutes > 0 ) {
$uptimestring = "${minutes}m ${seconds}s";
}
else {
$uptimestring = "${seconds}s";
}
return $uptimestring;
}
# Retrieves the memory installed on this machine
my ( $physical_memory, $swap_memory, $duflags );
sub memerror {
badprint
"Unable to determine total memory/swap; use '--forcemem' and '--forceswap'";
exit 1;
}
sub os_setup {
my $os = `uname`;
$duflags = ( $os =~ /Linux/ ) ? '-b' : '';
if ( $opt{'forcemem'} > 0 ) {
$physical_memory = $opt{'forcemem'} * 1048576;
infoprint "Assuming $opt{'forcemem'} MB of physical memory";
if ( $opt{'forceswap'} > 0 ) {
$swap_memory = $opt{'forceswap'} * 1048576;
infoprint "Assuming $opt{'forceswap'} MB of swap space";
}
else {
$swap_memory = 0;
badprint "Assuming 0 MB of swap space (use --forceswap to specify)";
}
}
else {
if ( $os =~ /Linux|CYGWIN/ ) {
$physical_memory =
`grep -i memtotal: /proc/meminfo | awk '{print \$2}'`
or memerror;
$physical_memory *= 1024;
$swap_memory =
`grep -i swaptotal: /proc/meminfo | awk '{print \$2}'`
or memerror;
$swap_memory *= 1024;
}
elsif ( $os =~ /Darwin/ ) {
$physical_memory = `sysctl -n hw.memsize` or memerror;
$swap_memory =
`sysctl -n vm.swapusage | awk '{print \$3}' | sed 's/\..*\$//'`
or memerror;
}
elsif ( $os =~ /NetBSD|OpenBSD|FreeBSD/ ) {
$physical_memory = `sysctl -n hw.physmem` or memerror;
if ( $physical_memory < 0 ) {
$physical_memory = `sysctl -n hw.physmem64` or memerror;
}
$swap_memory =
`swapctl -l | grep '^/' | awk '{ s+= \$2 } END { print s }'`
or memerror;
}
elsif ( $os =~ /BSD/ ) {
$physical_memory = `sysctl -n hw.realmem` or memerror;
$swap_memory =
`swapinfo | grep '^/' | awk '{ s+= \$2 } END { print s }'`;
}
elsif ( $os =~ /SunOS/ ) {
$physical_memory =
`/usr/sbin/prtconf | grep Memory | cut -f 3 -d ' '`
or memerror;
chomp($physical_memory);
$physical_memory = $physical_memory * 1024 * 1024;
}
elsif ( $os =~ /AIX/ ) {
$physical_memory =
`lsattr -El sys0 | grep realmem | awk '{print \$2}'`
or memerror;
chomp($physical_memory);
$physical_memory = $physical_memory * 1024;
$swap_memory = `lsps -as | awk -F"(MB| +)" '/MB /{print \$2}'`
or memerror;
chomp($swap_memory);
$swap_memory = $swap_memory * 1024 * 1024;
}
elsif ( $os =~ /windows/i ) {
$physical_memory =
`wmic ComputerSystem get TotalPhysicalMemory | perl -ne "chomp; print if /[0-9]+/;"`
or memerror;
$swap_memory =
`wmic OS get FreeVirtualMemory | perl -ne "chomp; print if /[0-9]+/;"`
or memerror;
}
}
debugprint "Physical Memory: $physical_memory";
debugprint "Swap Memory: $swap_memory";
chomp($physical_memory);
chomp($swap_memory);
chomp($os);
$result{'OS'}{'OS Type'} = $os;
$result{'OS'}{'Physical Memory'}{'bytes'} = $physical_memory;
$result{'OS'}{'Physical Memory'}{'pretty'} = hr_bytes($physical_memory);
$result{'OS'}{'Swap Memory'}{'bytes'} = $swap_memory;
$result{'OS'}{'Swap Memory'}{'pretty'} = hr_bytes($swap_memory);
$result{'OS'}{'Other Processes'}{'bytes'} = get_other_process_memory();
$result{'OS'}{'Other Processes'}{'pretty'} =
hr_bytes( get_other_process_memory() );
}
sub get_http_cli {
my $httpcli = which( "curl", $ENV{'PATH'} );
chomp($httpcli);
if ($httpcli) {
return $httpcli;
}
$httpcli = which( "wget", $ENV{'PATH'} );
chomp($httpcli);
if ($httpcli) {
return $httpcli;
}
return "";
}
# Checks for updates to MySQLTuner
sub validate_tuner_version {
if ( $opt{'checkversion'} eq 0 and $opt{'updateversion'} eq 0 ) {
print "\n" unless ( $opt{'silent'} or $opt{'json'} );
infoprint "Skipped version check for MySQLTuner script";
return;
}
my $update;
my $url =
"https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl";
my $httpcli = get_http_cli();
if ( $httpcli =~ /curl$/ ) {
debugprint "$httpcli is available.";
debugprint
"$httpcli -m 3 -silent '$url' 2>/dev/null | grep 'my \$tunerversion'| cut -d\\\" -f2";
$update =
`$httpcli -m 3 -silent '$url' 2>/dev/null | grep 'my \$tunerversion'| cut -d\\\" -f2`;
chomp($update);
debugprint "VERSION: $update";
compare_tuner_version($update);
return;
}
if ( $httpcli =~ /wget$/ ) {
debugprint "$httpcli is available.";
debugprint
"$httpcli -e timestamping=off -t 1 -T 3 -O - '$url' 2>$devnull| grep 'my \$tunerversion'| cut -d\\\" -f2";
$update =
`$httpcli -e timestamping=off -t 1 -T 3 -O - '$url' 2>$devnull| grep 'my \$tunerversion'| cut -d\\\" -f2`;
chomp($update);
compare_tuner_version($update);
return;
}
debugprint "curl and wget are not available.";
infoprint "Unable to check for the latest MySQLTuner version";
infoprint
"Using --pass and --password option is insecure during MySQLTuner execution(Password disclosure)"
if ( defined( $opt{'pass'} ) );
}
# Checks for updates to MySQLTuner
sub update_tuner_version {
if ( $opt{'updateversion'} eq 0 ) {
badprint "Skipped version update for MySQLTuner script";
print "\n" unless ( $opt{'silent'} or $opt{'json'} );
return;
}
my $update;
my $url = "https://raw.githubusercontent.com/major/MySQLTuner-perl/master/";
my @scripts =
( "mysqltuner.pl", "basic_passwords.txt", "vulnerabilities.csv" );
my $totalScripts = scalar(@scripts);
my $receivedScripts = 0;
my $httpcli = get_http_cli();
foreach my $script (@scripts) {
if ( $httpcli =~ /curl$/ ) {
debugprint "$httpcli is available.";
debugprint
"$httpcli --connect-timeout 3 '$url$script' 2>$devnull > $script";
$update =
`$httpcli --connect-timeout 3 '$url$script' 2>$devnull > $script`;
chomp($update);
debugprint "$script updated: $update";
if ( -s $script eq 0 ) {
badprint "Couldn't update $script";
}
else {
++$receivedScripts;
debugprint "$script updated: $update";
}
}
elsif ( $httpcli =~ /wget$/ ) {
debugprint "$httpcli is available.";
debugprint
"$httpcli -qe timestamping=off -t 1 -T 3 -O $script '$url$script'";
$update =
`$httpcli -qe timestamping=off -t 1 -T 3 -O $script '$url$script'`;
chomp($update);
if ( -s $script eq 0 ) {
badprint "Couldn't update $script";
}
else {
++$receivedScripts;
debugprint "$script updated: $update";
}
}
else {
debugprint "curl and wget are not available.";
infoprint "Unable to check for the latest MySQLTuner version";
}
}
if ( $receivedScripts eq $totalScripts ) {
goodprint "Successfully updated MySQLTuner script";
}
else {
badprint "Couldn't update MySQLTuner script";
}
exit 0;
}
sub compare_tuner_version {
my $remoteversion = shift;
debugprint "Remote data: $remoteversion";
#exit 0;
if ( $remoteversion ne $tunerversion ) {
badprint
"There is a new version of MySQLTuner available($remoteversion)";
update_tuner_version();
return;
}
goodprint "You have the latest version of MySQLTuner($tunerversion)";
return;
}
# Checks to see if a MySQL login is possible
my ( $mysqllogin, $doremote, $remotestring, $mysqlcmd, $mysqladmincmd );
my $osname = $^O;
if ( $osname eq 'MSWin32' ) {
eval { require Win32; } or last;
$osname = Win32::GetOSName();
infoprint "* Windows OS($osname) is not fully supported.\n";
#exit 1;
}
sub mysql_setup {
$doremote = 0;
$remotestring = '';
if ( $opt{mysqladmin} ) {
$mysqladmincmd = $opt{mysqladmin};
}
else {
$mysqladmincmd = which( "mysqladmin", $ENV{'PATH'} );
}
chomp($mysqladmincmd);
if ( !-e $mysqladmincmd && $opt{mysqladmin} ) {
badprint "Unable to find the mysqladmin command you specified: "
. $mysqladmincmd . "";
exit 1;
}
elsif ( !-e $mysqladmincmd ) {
badprint "Couldn't find mysqladmin in your \$PATH. Is MySQL installed?";
exit 1;
}
if ( $opt{mysqlcmd} ) {
$mysqlcmd = $opt{mysqlcmd};
}
else {
$mysqlcmd = which( "mysql", $ENV{'PATH'} );
}
chomp($mysqlcmd);
if ( !-e $mysqlcmd && $opt{mysqlcmd} ) {
badprint "Unable to find the mysql command you specified: "
. $mysqlcmd . "";
exit 1;
}
elsif ( !-e $mysqlcmd ) {
badprint "Couldn't find mysql in your \$PATH. Is MySQL installed?";
exit 1;
}
$mysqlcmd =~ s/\n$//g;
my $mysqlclidefaults = `$mysqlcmd --print-defaults`;
debugprint "MySQL Client: $mysqlclidefaults";
if ( $mysqlclidefaults =~ /auto-vertical-output/ ) {
badprint
"Avoid auto-vertical-output in configuration file(s) for MySQL like";
exit 1;
}
debugprint "MySQL Client: $mysqlcmd";
$opt{port} = ( $opt{port} eq 0 ) ? 3306 : $opt{port};
# Are we being asked to connect via a socket?
if ( $opt{socket} ne 0 ) {
$remotestring = " -S $opt{socket} -P $opt{port}";
}
# Are we being asked to connect to a remote server?
if ( $opt{host} ne 0 ) {
chomp( $opt{host} );
# If we're doing a remote connection, but forcemem wasn't specified, we need to exit
if ( $opt{'forcemem'} eq 0
&& ( $opt{host} ne "127.0.0.1" )
&& ( $opt{host} ne "localhost" ) )
{
badprint "The --forcemem option is required for remote connections";
exit 1;
}
infoprint "Performing tests on $opt{host}:$opt{port}";
$remotestring = " -h $opt{host} -P $opt{port}";
if ( ( $opt{host} ne "127.0.0.1" ) && ( $opt{host} ne "localhost" ) ) {
$doremote = 1;
}
}
else {
$opt{host} = '127.0.0.1';
}
if ( $opt{'ssl-ca'} ne 0 ) {
if ( -e -r -f $opt{'ssl-ca'} ) {
$remotestring .= " --ssl-ca=$opt{'ssl-ca'}";
infoprint
"Will connect using ssl public key passed on the command line";
return 1;
}
else {
badprint
"Attempted to use passed ssl public key, but it was not found or could not be read";
exit 1;
}
}
# Did we already get a username without password on the command line?
if ( $opt{user} ne 0 and $opt{pass} eq 0 ) {
$mysqllogin = "-u $opt{user} " . $remotestring;
my $loginstatus = `$mysqladmincmd ping $mysqllogin 2>&1`;
if ( $loginstatus =~ /mysqld is alive/ ) {
goodprint "Logged in using credentials passed on the command line";
return 1;
}
else {
badprint
"Attempted to use login credentials, but they were invalid";
exit 1;
}
}
# Did we already get a username and password passed on the command line?
if ( $opt{user} ne 0 and $opt{pass} ne 0 ) {
$mysqllogin = "-u $opt{user} -p'$opt{pass}'" . $remotestring;
my $loginstatus = `$mysqladmincmd ping $mysqllogin 2>&1`;
if ( $loginstatus =~ /mysqld is alive/ ) {
goodprint "Logged in using credentials passed on the command line";
return 1;
}
else {
badprint
"Attempted to use login credentials, but they were invalid";
exit 1;
}
}
my $svcprop = which( "svcprop", $ENV{'PATH'} );
if ( substr( $svcprop, 0, 1 ) =~ "/" ) {
# We are on solaris
( my $mysql_login =
`svcprop -p quickbackup/username svc:/network/mysql-quickbackup:default`
) =~ s/\s+$//;
( my $mysql_pass =
`svcprop -p quickbackup/password svc:/network/mysql-quickbackup:default`
) =~ s/\s+$//;
if ( substr( $mysql_login, 0, 7 ) ne "svcprop" ) {
# mysql-quickbackup is installed
$mysqllogin = "-u $mysql_login -p$mysql_pass";
my $loginstatus = `mysqladmin $mysqllogin ping 2>&1`;
if ( $loginstatus =~ /mysqld is alive/ ) {
goodprint "Logged in using credentials from mysql-quickbackup.";
return 1;
}
else {
badprint
"Attempted to use login credentials from mysql-quickbackup, but they failed.";
exit 1;
}
}
}
elsif ( -r "/etc/psa/.psa.shadow" and $doremote == 0 ) {
# It's a Plesk box, use the available credentials
$mysqllogin = "-u admin -p`cat /etc/psa/.psa.shadow`";
my $loginstatus = `$mysqladmincmd ping $mysqllogin 2>&1`;
unless ( $loginstatus =~ /mysqld is alive/ ) {
# Plesk 10+
$mysqllogin =
"-u admin -p`/usr/local/psa/bin/admin --show-password`";
$loginstatus = `$mysqladmincmd ping $mysqllogin 2>&1`;
unless ( $loginstatus =~ /mysqld is alive/ ) {
badprint
"Attempted to use login credentials from Plesk and Plesk 10+, but they failed.";
exit 1;
}
}
}
elsif ( -r "/usr/local/directadmin/conf/mysql.conf" and $doremote == 0 ) {
# It's a DirectAdmin box, use the available credentials
my $mysqluser =
`cat /usr/local/directadmin/conf/mysql.conf | egrep '^user=.*'`;
my $mysqlpass =
`cat /usr/local/directadmin/conf/mysql.conf | egrep '^passwd=.*'`;
$mysqluser =~ s/user=//;
$mysqluser =~ s/[\r\n]//;
$mysqlpass =~ s/passwd=//;
$mysqlpass =~ s/[\r\n]//;
$mysqllogin = "-u $mysqluser -p$mysqlpass";
my $loginstatus = `mysqladmin ping $mysqllogin 2>&1`;
unless ( $loginstatus =~ /mysqld is alive/ ) {
badprint
"Attempted to use login credentials from DirectAdmin, but they failed.";
exit 1;
}
}
elsif ( -r "/etc/mysql/debian.cnf"
and $doremote == 0
and $opt{'defaults-file'} eq '' )
{
# We have a debian maintenance account, use it
$mysqllogin = "--defaults-file=/etc/mysql/debian.cnf";
my $loginstatus = `$mysqladmincmd $mysqllogin ping 2>&1`;
if ( $loginstatus =~ /mysqld is alive/ ) {
goodprint
"Logged in using credentials from debian maintenance account.";
return 1;
}
else {
badprint "Attempted to use login credentials from debian maintena
nce account, but they failed.";
exit 1;
}
}
elsif ( $opt{'defaults-file'} ne '' and -r "$opt{'defaults-file'}" ) {
# defaults-file
debugprint "defaults file detected: $opt{'defaults-file'}";
my $mysqlclidefaults = `$mysqlcmd --print-defaults`;
debugprint "MySQL Client Default File: $opt{'defaults-file'}";
$mysqllogin = "--defaults-file=" . $opt{'defaults-file'};
my $loginstatus = `$mysqladmincmd $mysqllogin ping 2>&1`;
if ( $loginstatus =~ /mysqld is alive/ ) {
goodprint "Logged in using credentials from defaults file account.";
return 1;
}
}
else {
# It's not Plesk or debian, we should try a login
debugprint "$mysqladmincmd $remotestring ping 2>&1";
my $loginstatus = `$mysqladmincmd $remotestring ping 2>&1`;
if ( $loginstatus =~ /mysqld is alive/ ) {
# Login went just fine
$mysqllogin = " $remotestring ";
# Did this go well because of a .my.cnf file or is there no password set?
my $userpath = `printenv HOME`;
if ( length($userpath) > 0 ) {
chomp($userpath);
}
unless ( -e "${userpath}/.my.cnf" or -e "${userpath}/.mylogin.cnf" )
{
badprint
"Successfully authenticated with no password - SECURITY RISK!";
}
return 1;
}
else {
if ( $opt{'noask'} == 1 ) {
badprint
"Attempted to use login credentials, but they were invalid";
exit 1;
}
my ( $name, $password );
# If --user is defined no need to ask for username
if ( $opt{user} ne 0 ) {
$name = $opt{user};
}
else {
print STDERR "Please enter your MySQL administrative login: ";
$name = ;
}
# If --pass is defined no need to ask for password
if ( $opt{pass} ne 0 ) {
$password = $opt{pass};
}
else {
print STDERR
"Please enter your MySQL administrative password: ";
system("stty -echo >$devnull 2>&1");
$password = ;
system("stty echo >$devnull 2>&1");
}
chomp($password);
chomp($name);
$mysqllogin = "-u $name";
if ( length($password) > 0 ) {
$mysqllogin .= " -p'$password'";
}
$mysqllogin .= $remotestring;
my $loginstatus = `$mysqladmincmd ping $mysqllogin 2>&1`;
if ( $loginstatus =~ /mysqld is alive/ ) {
print STDERR "";
if ( !length($password) ) {
# Did this go well because of a .my.cnf file or is there no password set?
my $userpath = `printenv HOME`;
chomp($userpath);
unless ( -e "$userpath/.my.cnf" ) {
badprint
"Successfully authenticated with no password - SECURITY RISK!";
}
}
return 1;
}
else {
badprint
"Attempted to use login credentials, but they were invalid.";
exit 1;
}
exit 1;
}
}
}
# MySQL Request Array
sub select_array {
my $req = shift;
debugprint "PERFORM: $req ";
my @result = `$mysqlcmd $mysqllogin -Bse "\\w$req" 2>>/dev/null`;
if ( $? != 0 ) {
badprint "failed to execute: $req";
badprint "FAIL Execute SQL / return code: $?";
debugprint "CMD : $mysqlcmd";
debugprint "OPTIONS: $mysqllogin";
debugprint `$mysqlcmd $mysqllogin -Bse "$req" 2>&1`;
#exit $?;
}
debugprint "select_array: return code : $?";
chomp(@result);
return @result;
}
# MySQL Request one
sub select_one {
my $req = shift;
debugprint "PERFORM: $req ";
my $result = `$mysqlcmd $mysqllogin -Bse "\\w$req" 2>>/dev/null`;
if ( $? != 0 ) {
badprint "failed to execute: $req";
badprint "FAIL Execute SQL / return code: $?";
debugprint "CMD : $mysqlcmd";
debugprint "OPTIONS: $mysqllogin";
debugprint `$mysqlcmd $mysqllogin -Bse "$req" 2>&1`;
#exit $?;
}
debugprint "select_array: return code : $?";
chomp($result);
return $result;
}
# MySQL Request one
sub select_one_g {
my $pattern = shift;
my $req = shift;
debugprint "PERFORM: $req ";
my @result = `$mysqlcmd $mysqllogin -re "\\w$req\\G" 2>>/dev/null`;
if ( $? != 0 ) {
badprint "failed to execute: $req";
badprint "FAIL Execute SQL / return code: $?";
debugprint "CMD : $mysqlcmd";
debugprint "OPTIONS: $mysqllogin";
debugprint `$mysqlcmd $mysqllogin -Bse "$req" 2>&1`;
#exit $?;
}
debugprint "select_array: return code : $?";
chomp(@result);
return ( grep { /$pattern/ } @result )[0];
}
sub select_str_g {
my $pattern = shift;
my $req = shift;
my $str = select_one_g $pattern, $req;
my @val = split /:/, $str;
shift @val;
return trim(@val);
}
sub get_tuning_info {
my @infoconn = select_array "\\s";
my ( $tkey, $tval );
@infoconn =
grep { !/Threads:/ and !/Connection id:/ and !/pager:/ and !/Using/ }
@infoconn;
foreach my $line (@infoconn) {
if ( $line =~ /\s*(.*):\s*(.*)/ ) {
debugprint "$1 => $2";
$tkey = $1;
$tval = $2;
chomp($tkey);
chomp($tval);
$result{'MySQL Client'}{$tkey} = $tval;
}
}
$result{'MySQL Client'}{'Client Path'} = $mysqlcmd;
$result{'MySQL Client'}{'Admin Path'} = $mysqladmincmd;
$result{'MySQL Client'}{'Authentication Info'} = $mysqllogin;
}
# Populates all of the variable and status hashes
my ( %mystat, %myvar, $dummyselect, %myrepl, %myslaves );
sub arr2hash {
my $href = shift;
my $harr = shift;
my $sep = shift;
$sep = '\s' unless defined($sep);
foreach my $line (@$harr) {
next if ( $line =~ m/^\*\*\*\*\*\*\*/ );
$line =~ /([a-zA-Z_]*)\s*$sep\s*(.*)/;
$$href{$1} = $2;
debugprint "V: $1 = $2";
}
}
sub get_all_vars {
# We need to initiate at least one query so that our data is useable
$dummyselect = select_one "SELECT VERSION()";
if ( not defined($dummyselect) or $dummyselect eq "" ) {
badprint
"You probably doesn't get enough privileges for running MySQLTuner ...";
exit(256);
}
$dummyselect =~ s/(.*?)\-.*/$1/;
debugprint "VERSION: " . $dummyselect . "";
$result{'MySQL Client'}{'Version'} = $dummyselect;
my @mysqlvarlist = select_array("SHOW VARIABLES");
push( @mysqlvarlist, select_array("SHOW GLOBAL VARIABLES") );
arr2hash( \%myvar, \@mysqlvarlist );
$result{'Variables'} = \%myvar;
my @mysqlstatlist = select_array("SHOW STATUS");
push( @mysqlstatlist, select_array("SHOW GLOBAL STATUS") );
arr2hash( \%mystat, \@mysqlstatlist );
$result{'Status'} = \%mystat;
$myvar{'have_galera'} = "NO";
if ( defined( $myvar{'wsrep_provider_options'} )
&& $myvar{'wsrep_provider_options'} ne "" )
{
$myvar{'have_galera'} = "YES";
debugprint "Galera options: " . $myvar{'wsrep_provider_options'};
}
# Workaround for MySQL bug #59393 wrt. ignore-builtin-innodb
if ( ( $myvar{'ignore_builtin_innodb'} || "" ) eq "ON" ) {
$myvar{'have_innodb'} = "NO";
}
# Support GTID MODE FOR MARIADB
# Issue MariaDB GTID mode #272
$myvar{'gtid_mode'} = $myvar{'gtid_strict_mode'}
if ( defined( $myvar{'gtid_strict_mode'} ) );
$myvar{'have_threadpool'} = "NO";
if ( defined( $myvar{'thread_pool_size'} )
and $myvar{'thread_pool_size'} > 0 )
{
$myvar{'have_threadpool'} = "YES";
}
# have_* for engines is deprecated and will be removed in MySQL 5.6;
# check SHOW ENGINES and set corresponding old style variables.
# Also works around MySQL bug #59393 wrt. skip-innodb
my @mysqlenginelist = select_array "SHOW ENGINES";
foreach my $line (@mysqlenginelist) {
if ( $line =~ /^([a-zA-Z_]+)\s+(\S+)/ ) {
my $engine = lc($1);
if ( $engine eq "federated" || $engine eq "blackhole" ) {
$engine .= "_engine";
}
elsif ( $engine eq "berkeleydb" ) {
$engine = "bdb";
}
my $val = ( $2 eq "DEFAULT" ) ? "YES" : $2;
$myvar{"have_$engine"} = $val;
$result{'Storage Engines'}{$engine} = $2;
}
}
debugprint Dumper(@mysqlenginelist);
my @mysqlslave = select_array("SHOW SLAVE STATUS\\G");
arr2hash( \%myrepl, \@mysqlslave, ':' );
$result{'Replication'}{'Status'} = \%myrepl;
my @mysqlslaves = select_array "SHOW SLAVE HOSTS";
my @lineitems = ();
foreach my $line (@mysqlslaves) {
debugprint "L: $line ";
@lineitems = split /\s+/, $line;
$myslaves{ $lineitems[0] } = $line;
$result{'Replication'}{'Slaves'}{ $lineitems[0] } = $lineitems[4];
}
}
sub remove_cr {
return map {
my $line = $_;
$line =~ s/\n$//g;
$line =~ s/^\s+$//g;
$line;
} @_;
}
sub remove_empty {
grep { $_ ne '' } @_;
}
sub grep_file_contents {
my $file = shift;
my $patt;
}
sub get_file_contents {
my $file = shift;
open( my $fh, "<", $file ) or die "Can't open $file for read: $!";
my @lines = <$fh>;
close $fh or die "Cannot close $file: $!";
@lines = remove_cr @lines;
return @lines;
}
sub get_basic_passwords {
return get_file_contents(shift);
}
sub get_log_file_real_path {
my $file = shift;
my $hostname = shift;
my $datadir = shift;
if ( -f "$file" ) {
return $file;
}
elsif ( -f "$hostname.err" ) {
return "$hostname.err";
}
elsif ( $datadir ne "" ) {
return "$datadir$hostname.err";
}
else {
return $file;
}
}
sub log_file_recommandations {
$myvar{'log_error'} =
get_log_file_real_path( $myvar{'log_error'}, $myvar{'hostname'},
$myvar{'datadir'} );
subheaderprint "Log file Recommendations";
infoprint "Log file: "
. $myvar{'log_error'} . "("
. hr_bytes_rnd( ( stat $myvar{'log_error'} )[7] ) . ")";
if ( -f "$myvar{'log_error'}" ) {
goodprint "Log file $myvar{'log_error'} exists";
}
else {
badprint "Log file $myvar{'log_error'} doesn't exist";
}
if ( -r "$myvar{'log_error'}" ) {
goodprint "Log file $myvar{'log_error'} is readable.";
}
else {
badprint "Log file $myvar{'log_error'} isn't readable.";
return;
}
if ( ( stat $myvar{'log_error'} )[7] > 0 ) {
goodprint "Log file $myvar{'log_error'} is not empty";
}
else {
badprint "Log file $myvar{'log_error'} is empty";
}
if ( ( stat $myvar{'log_error'} )[7] < 32 * 1024 * 1024 ) {
goodprint "Log file $myvar{'log_error'} is smaller than 32 Mb";
}
else {
badprint "Log file $myvar{'log_error'} is bigger than 32 Mb";
push @generalrec,
$myvar{'log_error'}
. " is > 32Mb, you should analyze why or implement a rotation log strategy such as logrotate!";
}
my $numLi = 0;
my $nbWarnLog = 0;
my $nbErrLog = 0;
my @lastShutdowns;
my @lastStarts;
open( my $fh, '<', $myvar{'log_error'} )
or die "Can't open $myvar{'log_error'} for read: $!";
while ( my $logLi = <$fh> ) {
chomp $logLi;
$numLi++;
debugprint "$numLi: $logLi" if $logLi =~ /warning|error/i;
$nbErrLog++ if $logLi =~ /error/i;
$nbWarnLog++ if $logLi =~ /warning/i;
push @lastShutdowns, $logLi
if $logLi =~ /Shutdown complete/ and $logLi !~ /Innodb/i;
push @lastStarts, $logLi if $logLi =~ /ready for connections/;
}
close $fh;
if ( $nbWarnLog > 0 ) {
badprint "$myvar{'log_error'} contains $nbWarnLog warning(s).";
push @generalrec,
"Control warning line(s) into $myvar{'log_error'} file";
}
else {
goodprint "$myvar{'log_error'} doesn't contain any warning.";
}
if ( $nbErrLog > 0 ) {
badprint "$myvar{'log_error'} contains $nbErrLog error(s).";
push @generalrec, "Control error line(s) into $myvar{'log_error'} file";
}
else {
goodprint "$myvar{'log_error'} doesn't contain any error.";
}
infoprint scalar @lastStarts . " start(s) detected in $myvar{'log_error'}";
my $nStart = 0;
my $nEnd = 10;
if ( scalar @lastStarts < $nEnd ) {
$nEnd = scalar @lastStarts;
}
for my $startd ( reverse @lastStarts[ -$nEnd .. -1 ] ) {
$nStart++;
infoprint "$nStart) $startd";
}
infoprint scalar @lastShutdowns
. " shutdown(s) detected in $myvar{'log_error'}";
$nStart = 0;
$nEnd = 10;
if ( scalar @lastShutdowns < $nEnd ) {
$nEnd = scalar @lastShutdowns;
}
for my $shutd ( reverse @lastShutdowns[ -$nEnd .. -1 ] ) {
$nStart++;
infoprint "$nStart) $shutd";
}
#exit 0;
}
sub cve_recommendations {
subheaderprint "CVE Security Recommendations";
unless ( defined( $opt{cvefile} ) && -f "$opt{cvefile}" ) {
infoprint "Skipped due to --cvefile option undefined";
return;
}
#$mysqlvermajor=10;
#$mysqlverminor=1;
#$mysqlvermicro=17;
#prettyprint "Look for related CVE for $myvar{'version'} or lower in $opt{cvefile}";
my $cvefound = 0;
open( my $fh, "<", $opt{cvefile} )
or die "Can't open $opt{cvefile} for read: $!";
while ( my $cveline = <$fh> ) {
my @cve = split( ';', $cveline );
debugprint
"Comparing $mysqlvermajor\.$mysqlverminor\.$mysqlvermicro with $cve[1]\.$cve[2]\.$cve[3] : "
. ( mysql_version_le( $cve[1], $cve[2], $cve[3] ) ? '<=' : '>' );
# Avoid not major/minor version corresponding CVEs
next
unless ( int( $cve[1] ) == $mysqlvermajor
&& int( $cve[2] ) == $mysqlverminor );
if ( int( $cve[3] ) >= $mysqlvermicro ) {
badprint "$cve[4](<= $cve[1]\.$cve[2]\.$cve[3]) : $cve[6]";
$result{'CVE'}{'List'}{$cvefound} =
"$cve[4](<= $cve[1]\.$cve[2]\.$cve[3]) : $cve[6]";
$cvefound++;
}
}
close $fh or die "Cannot close $opt{cvefile}: $!";
$result{'CVE'}{'nb'} = $cvefound;
my $cve_warning_notes = "";
if ( $cvefound == 0 ) {
goodprint "NO SECURITY CVE FOUND FOR YOUR VERSION";
return;
}
if ( $mysqlvermajor eq 5 and $mysqlverminor eq 5 ) {
infoprint
"False positive CVE(s) for MySQL and MariaDB 5.5.x can be found.";
infoprint "Check careful each CVE for those particular versions";
}
badprint $cvefound . " CVE(s) found for your MySQL release.";
push( @generalrec,
$cvefound
. " CVE(s) found for your MySQL release. Consider upgrading your version !"
);
}
sub get_opened_ports {
my @opened_ports = `netstat -ltn`;
@opened_ports = map {
my $v = $_;
$v =~ s/.*:(\d+)\s.*$/$1/;
$v =~ s/\D//g;
$v;
} @opened_ports;
@opened_ports = sort { $a <=> $b } grep { !/^$/ } @opened_ports;
debugprint Dumper \@opened_ports;
$result{'Network'}{'TCP Opened'} = \@opened_ports;
return @opened_ports;
}
sub is_open_port {
my $port = shift;
if ( grep { /^$port$/ } get_opened_ports ) {
return 1;
}
return 0;
}
sub get_process_memory {
my $pid = shift;
my @mem = `ps -p $pid -o rss`;
return 0 if scalar @mem != 2;
return $mem[1] * 1024;
}
sub get_other_process_memory {
my @procs = `ps eaxo pid,command`;
@procs = map {
my $v = $_;
$v =~ s/.*PID.*//;
$v =~ s/.*mysqld.*//;
$v =~ s/.*\[.*\].*//;
$v =~ s/^\s+$//g;
$v =~ s/.*PID.*CMD.*//;
$v =~ s/.*systemd.*//;
$v =~ s/\s*?(\d+)\s*.*/$1/g;
$v;
} @procs;
@procs = remove_cr @procs;
@procs = remove_empty @procs;
my $totalMemOther = 0;
map { $totalMemOther += get_process_memory($_); } @procs;
return $totalMemOther;
}
sub get_os_release {
if ( -f "/etc/lsb-release" ) {
my @info_release = get_file_contents "/etc/lsb-release";
my $os_relase = $info_release[3];
$os_relase =~ s/.*="//;
$os_relase =~ s/"$//;
return $os_relase;
}
if ( -f "/etc/system-release" ) {
my @info_release = get_file_contents "/etc/system-release";
return $info_release[0];
}
if ( -f "/etc/os-release" ) {
my @info_release = get_file_contents "/etc/os-release";
my $os_relase = $info_release[0];
$os_relase =~ s/.*="//;
$os_relase =~ s/"$//;
return $os_relase;
}
if ( -f "/etc/issue" ) {
my @info_release = get_file_contents "/etc/issue";
my $os_relase = $info_release[0];
$os_relase =~ s/\s+\\n.*//;
return $os_relase;
}
return "Unknown OS release";
}
sub get_fs_info {
my @sinfo = `df -P | grep '%'`;
my @iinfo = `df -Pi| grep '%'`;
shift @iinfo;
@sinfo = map {
my $v = $_;
$v =~ s/.*\s(\d+)%\s+(.*)/$1\t$2/g;
$v;
} @sinfo;
foreach my $info (@sinfo) {
next if $info =~ m{(\d+)\t/(run|dev|sys|proc)($|/)};
if ( $info =~ /(\d+)\t(.*)/ ) {
if ( $1 > 85 ) {
badprint "mount point $2 is using $1 % total space";
push( @generalrec, "Add some space to $2 mountpoint." );
}
else {
infoprint "mount point $2 is using $1 % of total space";
}
$result{'Filesystem'}{'Space Pct'}{$2} = $1;
}
}
@iinfo = map {
my $v = $_;
$v =~ s/.*\s(\d+)%\s+(.*)/$1\t$2/g;
$v;
} @iinfo;
foreach my $info (@iinfo) {
next if $info =~ m{(\d+)\t/(run|dev|sys|proc)($|/)};
if ( $info =~ /(\d+)\t(.*)/ ) {
if ( $1 > 85 ) {
badprint "mount point $2 is using $1 % of max allowed inodes";
push( @generalrec,
"Cleanup files from $2 mountpoint or reformat you filesystem."
);
}
else {
infoprint "mount point $2 is using $1 % of max allowed inodes";
}
$result{'Filesystem'}{'Inode Pct'}{$2} = $1;
}
}
}
sub merge_hash {
my $h1 = shift;
my $h2 = shift;
my %result = {};
foreach my $substanceref ( $h1, $h2 ) {
while ( my ( $k, $v ) = each %$substanceref ) {
next if ( exists $result{$k} );
$result{$k} = $v;
}
}
return \%result;
}
sub is_virtual_machine {
my $isVm = `grep -Ec '^flags.*\ hypervisor\ ' /proc/cpuinfo`;
return ( $isVm == 0 ? 0 : 1 );
}
sub infocmd {
my $cmd = "@_";
debugprint "CMD: $cmd";
my @result = `$cmd`;
@result = remove_cr @result;
for my $l (@result) {
infoprint "$l";
}
}
sub infocmd_tab {
my $cmd = "@_";
debugprint "CMD: $cmd";
my @result = `$cmd`;
@result = remove_cr @result;
for my $l (@result) {
infoprint "\t$l";
}
}
sub infocmd_one {
my $cmd = "@_";
my @result = `$cmd`;
@result = remove_cr @result;
return join ', ', @result;
}
sub get_kernel_info {
my @params = (
'fs.aio-max-nr', 'fs.aio-nr',
'fs.file-max', 'sunrpc.tcp_fin_timeout',
'sunrpc.tcp_max_slot_table_entries', 'sunrpc.tcp_slot_table_entries',
'vm.swappiness'
);
infoprint "Information about kernel tuning:";
foreach my $param (@params) {
infocmd_tab("sysctl $param 2>/dev/null");
$result{'OS'}{'Config'}{$param} = `sysctl -n $param 2>/dev/null`;
}
if ( `sysctl -n vm.swappiness` > 10 ) {
badprint
"Swappiness is > 10, please consider having a value lower than 10";
push @generalrec, "setup swappiness lower or equals to 10";
push @adjvars,
'vm.swappiness <= 10 (echo 10 > /proc/sys/vm/swappiness)';
}
else {
infoprint "Swappiness is < 10.";
}
# only if /proc/sys/sunrpc exists
my $tcp_slot_entries =
`sysctl -n sunrpc.tcp_slot_table_entries 2>/dev/null`;
if ( -f "/proc/sys/sunrpc"
and ( $tcp_slot_entries eq '' or $tcp_slot_entries < 100 ) )
{
badprint
"Initial TCP slot entries is < 1M, please consider having a value greater than 100";
push @generalrec, "setup Initial TCP slot entries greater than 100";
push @adjvars,
'sunrpc.tcp_slot_table_entries > 100 (echo 128 > /proc/sys/sunrpc/tcp_slot_table_entries)';
}
else {
infoprint "TCP slot entries is > 100.";
}
if ( `sysctl -n fs.aio-max-nr` < 1000000 ) {
badprint
"Max running total of the number of events is < 1M, please consider having a value greater than 1M";
push @generalrec, "setup Max running number events greater than 1M";
push @adjvars,
'fs.aio-max-nr > 1M (echo 1048576 > /proc/sys/fs/aio-max-nr)';
}
else {
infoprint "Max Number of AIO events is > 1M.";
}
}
sub get_system_info {
$result{'OS'}{'Release'} = get_os_release();
infoprint get_os_release;
if (is_virtual_machine) {
infoprint "Machine type : Virtual machine";
$result{'OS'}{'Virtual Machine'} = 'YES';
}
else {
infoprint "Machine type : Physical machine";
$result{'OS'}{'Virtual Machine'} = 'NO';
}
$result{'Network'}{'Connected'} = 'NO';
`ping -c 1 ipecho.net &>/dev/null`;
my $isConnected = $?;
if ( $? == 0 ) {
infoprint "Internet : Connected";
$result{'Network'}{'Connected'} = 'YES';
}
else {
badprint "Internet : Disconnected";
}
$result{'OS'}{'NbCore'} = cpu_cores;
infoprint "Number of Core CPU : " . cpu_cores;
$result{'OS'}{'Type'} = `uname -o`;
infoprint "Operating System Type : " . infocmd_one "uname -o";
$result{'OS'}{'Kernel'} = `uname -r`;
infoprint "Kernel Release : " . infocmd_one "uname -r";
$result{'OS'}{'Hostname'} = `hostname`;
$result{'Network'}{'Internal Ip'} = `hostname -I`;
infoprint "Hostname : " . infocmd_one "hostname";
infoprint "Network Cards : ";
infocmd_tab "ifconfig| grep -A1 mtu";
infoprint "Internal IP : " . infocmd_one "hostname -I";
my $httpcli = get_http_cli();
infoprint "HTTP client found: $httpcli" if defined $httpcli;
my $ext_ip = "";
if ( $httpcli =~ /curl$/ ) {
$ext_ip = infocmd_one "$httpcli -m 3 ipecho.net/plain";
}
elsif ( $httpcli =~ /wget$/ ) {
$ext_ip = infocmd_one "$httpcli -t 1 -T 3 -q -O - ipecho.net/plain";
}
infoprint "External IP : " . $ext_ip;
$result{'Network'}{'External Ip'} = $ext_ip;
badprint
"External IP : Can't check because of Internet connectivity"
unless defined($httpcli);
infoprint "Name Servers : "
. infocmd_one "grep 'nameserver' /etc/resolv.conf \| awk '{print \$2}'";
infoprint "Logged In users : ";
infocmd_tab "who";
$result{'OS'}{'Logged users'} = `who`;
infoprint "Ram Usages in Mb : ";
infocmd_tab "free -m | grep -v +";
$result{'OS'}{'Free Memory RAM'} = `free -m | grep -v +`;
infoprint "Load Average : ";
infocmd_tab "top -n 1 -b | grep 'load average:'";
$result{'OS'}{'Load Average'} = `top -n 1 -b | grep 'load average:'`;
#infoprint "System Uptime Days/(HH:MM) : `uptime | awk '{print $3,$4}' | cut -f1 -d,`";
}
sub system_recommendations {
return if ( $opt{sysstat} == 0 );
subheaderprint "System Linux Recommendations";
my $os = `uname`;
unless ( $os =~ /Linux/i ) {
infoprint "Skipped due to non Linux server";
return;
}
prettyprint "Look for related Linux system recommendations";
#prettyprint '-'x78;
get_system_info();
my $omem = get_other_process_memory;
infoprint "User process except mysqld used "
. hr_bytes_rnd($omem) . " RAM.";
if ( ( 0.15 * $physical_memory ) < $omem ) {
badprint
"Other user process except mysqld used more than 15% of total physical memory "
. percentage( $omem, $physical_memory ) . "% ("
. hr_bytes_rnd($omem) . " / "
. hr_bytes_rnd($physical_memory) . ")";
push( @generalrec,
"Consider stopping or dedicate server for additional process other than mysqld."
);
push( @adjvars,
"DON'T APPLY SETTINGS BECAUSE THERE ARE TOO MANY PROCESSES RUNNING ON THIS SERVER. OOM KILL CAN OCCUR!"
);
}
else {
infoprint
"Other user process except mysqld used less than 15% of total physical memory "
. percentage( $omem, $physical_memory ) . "% ("
. hr_bytes_rnd($omem) . " / "
. hr_bytes_rnd($physical_memory) . ")";
}
if ( $opt{'maxportallowed'} > 0 ) {
my @opened_ports = get_opened_ports;
infoprint "There is "
. scalar @opened_ports
. " listening port(s) on this server.";
if ( scalar(@opened_ports) > $opt{'maxportallowed'} ) {
badprint "There is too many listening ports: "
. scalar(@opened_ports)
. " opened > "
. $opt{'maxportallowed'}
. "allowed.";
push( @generalrec,
"Consider dedicating a server for your database installation with less services running on !"
);
}
else {
goodprint "There is less than "
. $opt{'maxportallowed'}
. " opened ports on this server.";
}
}
foreach my $banport (@banned_ports) {
if ( is_open_port($banport) ) {
badprint "Banned port: $banport is opened..";
push( @generalrec,
"Port $banport is opened. Consider stopping program handling this port."
);
}
else {
goodprint "$banport is not opened.";
}
}
get_fs_info;
get_kernel_info;
}
sub security_recommendations {
subheaderprint "Security Recommendations";
if ( $opt{skippassword} eq 1 ) {
infoprint "Skipped due to --skippassword option";
return;
}
my $PASS_COLUMN_NAME = 'password';
if ( $myvar{'version'} =~ /5.7/ ) {
$PASS_COLUMN_NAME = 'authentication_string';
}
debugprint "Password column = $PASS_COLUMN_NAME";
# Looking for Anonymous users
my @mysqlstatlist = select_array
"SELECT CONCAT(user, '\@', host) FROM mysql.user WHERE TRIM(USER) = '' OR USER IS NULL";
debugprint Dumper \@mysqlstatlist;
#exit 0;
if (@mysqlstatlist) {
foreach my $line ( sort @mysqlstatlist ) {
chomp($line);
badprint "User '" . $line . "' is an anonymous account.";
}
push( @generalrec,
"Remove Anonymous User accounts - there are "
. scalar(@mysqlstatlist)
. " anonymous accounts." );
}
else {
goodprint "There are no anonymous accounts for any database users";
}
if ( mysql_version_le( 5, 1 ) ) {
badprint "No more password checks for MySQL version <=5.1";
badprint "MySQL version <=5.1 are deprecated and end of support.";
return;
}
# Looking for Empty Password
if ( mysql_version_ge( 5, 5 ) ) {
@mysqlstatlist = select_array
"SELECT CONCAT(user, '\@', host) FROM mysql.user WHERE ($PASS_COLUMN_NAME = '' OR $PASS_COLUMN_NAME IS NULL) AND plugin NOT IN ('unix_socket', 'win_socket', 'auth_pam_compat')";
}
else {
@mysqlstatlist = select_array
"SELECT CONCAT(user, '\@', host) FROM mysql.user WHERE ($PASS_COLUMN_NAME = '' OR $PASS_COLUMN_NAME IS NULL)";
}
if (@mysqlstatlist) {
foreach my $line ( sort @mysqlstatlist ) {
chomp($line);
badprint "User '" . $line . "' has no password set.";
}
push( @generalrec,
"Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'\@'SpecificDNSorIp' = PASSWORD('secure_password'); )"
);
}
else {
goodprint "All database users have passwords assigned";
}
if ( mysql_version_ge( 5, 7 ) ) {
my $valPlugin = select_one(
"select count(*) from information_schema.plugins where PLUGIN_NAME='validate_password' AND PLUGIN_STATUS='ACTIVE'"
);
if ( $valPlugin >= 1 ) {
infoprint
"Bug #80860 MySQL 5.7: Avoid testing password when validate_password is activated";
return;
}
}
# Looking for User with user/ uppercase /capitalise user as password
@mysqlstatlist = select_array
"SELECT CONCAT(user, '\@', host) FROM mysql.user WHERE CAST($PASS_COLUMN_NAME as Binary) = PASSWORD(user) OR CAST($PASS_COLUMN_NAME as Binary) = PASSWORD(UPPER(user)) OR CAST($PASS_COLUMN_NAME as Binary) = PASSWORD(CONCAT(UPPER(LEFT(User, 1)), SUBSTRING(User, 2, LENGTH(User))))";
if (@mysqlstatlist) {
foreach my $line ( sort @mysqlstatlist ) {
chomp($line);
badprint "User '" . $line . "' has user name as password.";
}
push( @generalrec,
"Set up a Secure Password for user\@host ( SET PASSWORD FOR 'user'\@'SpecificDNSorIp' = PASSWORD('secure_password'); )"
);
}
@mysqlstatlist = select_array
"SELECT CONCAT(user, '\@', host) FROM mysql.user WHERE HOST='%'";
if (@mysqlstatlist) {
foreach my $line ( sort @mysqlstatlist ) {
chomp($line);
badprint "User '" . $line . "' hasn't specific host restriction.";
}
push( @generalrec,
"Restrict Host for user\@% to user\@SpecificDNSorIp" );
}
unless ( -f $basic_password_files ) {
badprint "There is no basic password file list!";
return;
}
my @passwords = get_basic_passwords $basic_password_files;
infoprint "There are "
. scalar(@passwords)
. " basic passwords in the list.";
my $nbins = 0;
my $passreq;
if (@passwords) {
my $nbInterPass = 0;
foreach my $pass (@passwords) {
$nbInterPass++;
$pass =~ s/\s//g;
$pass =~ s/\'/\\\'/g;
chomp($pass);
# Looking for User with user/ uppercase /capitalise weak password
@mysqlstatlist =
select_array
"SELECT CONCAT(user, '\@', host) FROM mysql.user WHERE $PASS_COLUMN_NAME = PASSWORD('"
. $pass
. "') OR $PASS_COLUMN_NAME = PASSWORD(UPPER('"
. $pass
. "')) OR $PASS_COLUMN_NAME = PASSWORD(CONCAT(UPPER(LEFT('"
. $pass
. "', 1)), SUBSTRING('"
. $pass
. "', 2, LENGTH('"
. $pass . "'))))";
debugprint "There is " . scalar(@mysqlstatlist) . " items.";
if (@mysqlstatlist) {
foreach my $line (@mysqlstatlist) {
chomp($line);
badprint "User '" . $line
. "' is using weak password: $pass in a lower, upper or capitalize derivative version.";
$nbins++;
}
}
debugprint "$nbInterPass / " . scalar(@passwords)
if ( $nbInterPass % 1000 == 0 );
}
}
if ( $nbins > 0 ) {
push( @generalrec, $nbins . " user(s) used basic or weak password." );
}
}
sub get_replication_status {
subheaderprint "Replication Metrics";
infoprint "Galera Synchronous replication: " . $myvar{'have_galera'};
if ( scalar( keys %myslaves ) == 0 ) {
infoprint "No replication slave(s) for this server.";
}
else {
infoprint "This server is acting as master for "
. scalar( keys %myslaves )
. " server(s).";
}
if ( scalar( keys %myrepl ) == 0 and scalar( keys %myslaves ) == 0 ) {
infoprint "This is a standalone server.";
return;
}
if ( scalar( keys %myrepl ) == 0 ) {
infoprint "No replication setup for this server.";
return;
}
$result{'Replication'}{'status'} = \%myrepl;
my ($io_running) = $myrepl{'Slave_IO_Running'};
debugprint "IO RUNNING: $io_running ";
my ($sql_running) = $myrepl{'Slave_SQL_Running'};
debugprint "SQL RUNNING: $sql_running ";
my ($seconds_behind_master) = $myrepl{'Seconds_Behind_Master'};
debugprint "SECONDS : $seconds_behind_master ";
if ( defined($io_running)
and ( $io_running !~ /yes/i or $sql_running !~ /yes/i ) )
{
badprint
"This replication slave is not running but seems to be configured.";
}
if ( defined($io_running)
&& $io_running =~ /yes/i
&& $sql_running =~ /yes/i )
{
if ( $myvar{'read_only'} eq 'OFF' ) {
badprint
"This replication slave is running with the read_only option disabled.";
}
else {
goodprint
"This replication slave is running with the read_only option enabled.";
}
if ( $seconds_behind_master > 0 ) {
badprint
"This replication slave is lagging and slave has $seconds_behind_master second(s) behind master host.";
}
else {
goodprint "This replication slave is up to date with master.";
}
}
}
sub validate_mysql_version {
( $mysqlvermajor, $mysqlverminor, $mysqlvermicro ) =
$myvar{'version'} =~ /^(\d+)(?:\.(\d+)|)(?:\.(\d+)|)/;
$mysqlverminor ||= 0;
$mysqlvermicro ||= 0;
if ( !mysql_version_ge( 5, 1 ) ) {
badprint "Your MySQL version "
. $myvar{'version'}
. " is EOL software! Upgrade soon!";
}
elsif ( ( mysql_version_ge(6) and mysql_version_le(9) )
or mysql_version_ge(12) )
{
badprint "Currently running unsupported MySQL version "
. $myvar{'version'} . "";
}
else {
goodprint "Currently running supported MySQL version "
. $myvar{'version'} . "";
}
}
# Checks if MySQL version is greater than equal to (major, minor, micro)
sub mysql_version_ge {
my ( $maj, $min, $mic ) = @_;
$min ||= 0;
$mic ||= 0;
return
int($mysqlvermajor) > int($maj)
|| ( int($mysqlvermajor) == int($maj) && int($mysqlverminor) > int($min) )
|| ( int($mysqlvermajor) == int($maj)
&& int($mysqlverminor) == int($min)
&& int($mysqlvermicro) >= int($mic) );
}
# Checks if MySQL version is lower than equal to (major, minor, micro)
sub mysql_version_le {
my ( $maj, $min, $mic ) = @_;
$min ||= 0;
$mic ||= 0;
return
int($mysqlvermajor) < int($maj)
|| ( int($mysqlvermajor) == int($maj) && int($mysqlverminor) < int($min) )
|| ( int($mysqlvermajor) == int($maj)
&& int($mysqlverminor) == int($min)
&& int($mysqlvermicro) <= int($mic) );
}
# Checks if MySQL micro version is lower than equal to (major, minor, micro)
sub mysql_micro_version_le {
my ( $maj, $min, $mic ) = @_;
return $mysqlvermajor == $maj
&& ( $mysqlverminor == $min
&& $mysqlvermicro <= $mic );
}
# Checks for 32-bit boxes with more than 2GB of RAM
my ($arch);
sub check_architecture {
if ( $doremote eq 1 ) { return; }
if ( `uname` =~ /SunOS/ && `isainfo -b` =~ /64/ ) {
$arch = 64;
goodprint "Operating on 64-bit architecture";
}
elsif ( `uname` !~ /SunOS/ && `uname -m` =~ /64/ ) {
$arch = 64;
goodprint "Operating on 64-bit architecture";
}
elsif ( `uname` =~ /AIX/ && `bootinfo -K` =~ /64/ ) {
$arch = 64;
goodprint "Operating on 64-bit architecture";
}
elsif ( `uname` =~ /NetBSD|OpenBSD/ && `sysctl -b hw.machine` =~ /64/ ) {
$arch = 64;
goodprint "Operating on 64-bit architecture";
}
elsif ( `uname` =~ /FreeBSD/ && `sysctl -b hw.machine_arch` =~ /64/ ) {
$arch = 64;
goodprint "Operating on 64-bit architecture";
}
elsif ( `uname` =~ /Darwin/ && `uname -m` =~ /Power Macintosh/ ) {
# Darwin box.local 9.8.0 Darwin Kernel Version 9.8.0: Wed Jul 15 16:57:01 PDT 2009; root:xnu1228.15.4~1/RELEASE_PPC Power Macintosh
$arch = 64;
goodprint "Operating on 64-bit architecture";
}
elsif ( `uname` =~ /Darwin/ && `uname -m` =~ /x86_64/ ) {
# Darwin gibas.local 12.3.0 Darwin Kernel Version 12.3.0: Sun Jan 6 22:37:10 PST 2013; root:xnu-2050.22.13~1/RELEASE_X86_64 x86_64
$arch = 64;
goodprint "Operating on 64-bit architecture";
}
else {
$arch = 32;
if ( $physical_memory > 2147483648 ) {
badprint
"Switch to 64-bit OS - MySQL cannot currently use all of your RAM";
}
else {
goodprint "Operating on 32-bit architecture with less than 2GB RAM";
}
}
$result{'OS'}{'Architecture'} = "$arch bits";
}
# Start up a ton of storage engine counts/statistics
my ( %enginestats, %enginecount, $fragtables );
sub check_storage_engines {
if ( $opt{skipsize} eq 1 ) {
subheaderprint "Storage Engine Statistics";
infoprint "Skipped due to --skipsize option";
return;
}
subheaderprint "Storage Engine Statistics";
my $engines;
if ( mysql_version_ge( 5, 5 ) ) {
my @engineresults = select_array
"SELECT ENGINE,SUPPORT FROM information_schema.ENGINES ORDER BY ENGINE ASC";
foreach my $line (@engineresults) {
my ( $engine, $engineenabled );
( $engine, $engineenabled ) = $line =~ /([a-zA-Z_]*)\s+([a-zA-Z]+)/;
$result{'Engine'}{$engine}{'Enabled'} = $engineenabled;
$engines .=
( $engineenabled eq "YES" || $engineenabled eq "DEFAULT" )
? greenwrap "+" . $engine . " "
: redwrap "-" . $engine . " ";
}
}
elsif ( mysql_version_ge( 5, 1, 5 ) ) {
my @engineresults = select_array
"SELECT ENGINE,SUPPORT FROM information_schema.ENGINES WHERE ENGINE NOT IN ('performance_schema','MyISAM','MERGE','MEMORY') ORDER BY ENGINE ASC";
foreach my $line (@engineresults) {
my ( $engine, $engineenabled );
( $engine, $engineenabled ) = $line =~ /([a-zA-Z_]*)\s+([a-zA-Z]+)/;
$result{'Engine'}{$engine}{'Enabled'} = $engineenabled;
$engines .=
( $engineenabled eq "YES" || $engineenabled eq "DEFAULT" )
? greenwrap "+" . $engine . " "
: redwrap "-" . $engine . " ";
}
}
else {
$engines .=
( defined $myvar{'have_archive'} && $myvar{'have_archive'} eq "YES" )
? greenwrap "+Archive "
: redwrap "-Archive ";
$engines .=
( defined $myvar{'have_bdb'} && $myvar{'have_bdb'} eq "YES" )
? greenwrap "+BDB "
: redwrap "-BDB ";
$engines .=
( defined $myvar{'have_federated_engine'}
&& $myvar{'have_federated_engine'} eq "YES" )
? greenwrap "+Federated "
: redwrap "-Federated ";
$engines .=
( defined $myvar{'have_innodb'} && $myvar{'have_innodb'} eq "YES" )
? greenwrap "+InnoDB "
: redwrap "-InnoDB ";
$engines .=
( defined $myvar{'have_isam'} && $myvar{'have_isam'} eq "YES" )
? greenwrap "+ISAM "
: redwrap "-ISAM ";
$engines .=
( defined $myvar{'have_ndbcluster'}
&& $myvar{'have_ndbcluster'} eq "YES" )
? greenwrap "+NDBCluster "
: redwrap "-NDBCluster ";
}
my @dblist = grep { $_ ne 'lost+found' } select_array "SHOW DATABASES";
$result{'Databases'}{'List'} = [@dblist];
infoprint "Status: $engines";
if ( mysql_version_ge( 5, 1, 5 ) ) {
# MySQL 5 servers can have table sizes calculated quickly from information schema
my @templist = select_array
"SELECT ENGINE,SUM(DATA_LENGTH+INDEX_LENGTH),COUNT(ENGINE),SUM(DATA_LENGTH),SUM(INDEX_LENGTH) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql') AND ENGINE IS NOT NULL GROUP BY ENGINE ORDER BY ENGINE ASC;";
my ( $engine, $size, $count, $dsize, $isize );
foreach my $line (@templist) {
( $engine, $size, $count, $dsize, $isize ) =
$line =~ /([a-zA-Z_]+)\s+(\d+)\s+(\d+)\s+(\d+)\s+(\d+)/;
debugprint "Engine Found: $engine";
next unless ( defined($engine) );
$size = 0 unless defined($size);
$isize = 0 unless defined($isize);
$dsize = 0 unless defined($dsize);
$count = 0 unless defined($count);
$enginestats{$engine} = $size;
$enginecount{$engine} = $count;
$result{'Engine'}{$engine}{'Table Number'} = $count;
$result{'Engine'}{$engine}{'Total Size'} = $size;
$result{'Engine'}{$engine}{'Data Size'} = $dsize;
$result{'Engine'}{$engine}{'Index Size'} = $isize;
}
my $not_innodb = '';
if ( not defined $result{'Variables'}{'innodb_file_per_table'} ) {
$not_innodb = "AND NOT ENGINE='InnoDB'";
}
elsif ( $result{'Variables'}{'innodb_file_per_table'} eq 'OFF' ) {
$not_innodb = "AND NOT ENGINE='InnoDB'";
}
$result{'Tables'}{'Fragmented tables'} =
[ select_array
"SELECT CONCAT(CONCAT(TABLE_SCHEMA, '.'), TABLE_NAME),DATA_FREE FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','performance_schema', 'mysql') AND DATA_LENGTH/1024/1024>100 AND DATA_FREE*100/(DATA_LENGTH+INDEX_LENGTH+DATA_FREE) > 10 AND NOT ENGINE='MEMORY' $not_innodb"
];
$fragtables = scalar @{ $result{'Tables'}{'Fragmented tables'} };
}
else {
# MySQL < 5 servers take a lot of work to get table sizes
my @tblist;
# Now we build a database list, and loop through it to get storage engine stats for tables
foreach my $db (@dblist) {
chomp($db);
if ( $db eq "information_schema"
or $db eq "performance_schema"
or $db eq "mysql"
or $db eq "lost+found" )
{
next;
}
my @ixs = ( 1, 6, 9 );
if ( !mysql_version_ge( 4, 1 ) ) {
# MySQL 3.23/4.0 keeps Data_Length in the 5th (0-based) column
@ixs = ( 1, 5, 8 );
}
push( @tblist,
map { [ (split)[@ixs] ] }
select_array "SHOW TABLE STATUS FROM \\\`$db\\\`" );
}
# Parse through the table list to generate storage engine counts/statistics
$fragtables = 0;
foreach my $tbl (@tblist) {
debugprint "Data dump " . Dumper(@$tbl);
my ( $engine, $size, $datafree ) = @$tbl;
next if $engine eq 'NULL';
$size = 0 if $size eq 'NULL';
$datafree = 0 if $datafree eq 'NULL';
if ( defined $enginestats{$engine} ) {
$enginestats{$engine} += $size;
$enginecount{$engine} += 1;
}
else {
$enginestats{$engine} = $size;
$enginecount{$engine} = 1;
}
if ( $datafree > 0 ) {
$fragtables++;
}
}
}
while ( my ( $engine, $size ) = each(%enginestats) ) {
infoprint "Data in $engine tables: "
. hr_bytes_rnd($size)
. " (Tables: "
. $enginecount{$engine} . ")" . "";
}
# If the storage engine isn't being used, recommend it to be disabled
if ( !defined $enginestats{'InnoDB'}
&& defined $myvar{'have_innodb'}
&& $myvar{'have_innodb'} eq "YES" )
{
badprint "InnoDB is enabled but isn't being used";
push( @generalrec,
"Add skip-innodb to MySQL configuration to disable InnoDB" );
}
if ( !defined $enginestats{'BerkeleyDB'}
&& defined $myvar{'have_bdb'}
&& $myvar{'have_bdb'} eq "YES" )
{
badprint "BDB is enabled but isn't being used";
push( @generalrec,
"Add skip-bdb to MySQL configuration to disable BDB" );
}
if ( !defined $enginestats{'ISAM'}
&& defined $myvar{'have_isam'}
&& $myvar{'have_isam'} eq "YES" )
{
badprint "MYISAM is enabled but isn't being used";
push( @generalrec,
"Add skip-isam to MySQL configuration to disable ISAM (MySQL > 4.1.0)"
);
}
# Fragmented tables
if ( $fragtables > 0 ) {
badprint "Total fragmented tables: $fragtables";
push( @generalrec,
"Run OPTIMIZE TABLE to defragment tables for better performance" );
my $total_free = 0;
foreach my $table_line ( @{ $result{'Tables'}{'Fragmented tables'} } ) {
my ( $full_table_name, $data_free ) = split( /\s+/, $table_line );
$data_free = 0 if ( !defined($data_free) or $data_free eq '' );
$data_free = $data_free / 1024 / 1024;
$total_free += $data_free;
my ( $table_schema, $table_name ) = split( /\./, $full_table_name );
push( @generalrec,
" OPTIMIZE TABLE `$table_schema`.`$table_name`; -- can free $data_free MB"
);
}
push( @generalrec,
"Total freed space after theses OPTIMIZE TABLE : $total_free Mb" );
}
else {
goodprint "Total fragmented tables: $fragtables";
}
# Auto increments
my %tblist;
# Find the maximum integer
my $maxint = select_one "SELECT ~0";
$result{'MaxInt'} = $maxint;
# Now we use a database list, and loop through it to get storage engine stats for tables
foreach my $db (@dblist) {
chomp($db);
if ( !$tblist{$db} ) {
$tblist{$db} = ();
}
if ( $db eq "information_schema" ) { next; }
my @ia = ( 0, 10 );
if ( !mysql_version_ge( 4, 1 ) ) {
# MySQL 3.23/4.0 keeps Data_Length in the 5th (0-based) column
@ia = ( 0, 9 );
}
push(
@{ $tblist{$db} },
map { [ (split)[@ia] ] }
select_array "SHOW TABLE STATUS FROM \\\`$db\\\`"
);
}
my @dbnames = keys %tblist;
foreach my $db (@dbnames) {
foreach my $tbl ( @{ $tblist{$db} } ) {
my ( $name, $autoincrement ) = @$tbl;
if ( $autoincrement =~ /^\d+?$/ ) {
my $percent = percentage( $autoincrement, $maxint );
$result{'PctAutoIncrement'}{"$db.$name"} = $percent;
if ( $percent >= 75 ) {
badprint
"Table '$db.$name' has an autoincrement value near max capacity ($percent%)";
}
}
}
}
}
my %mycalc;
sub calculations {
if ( $mystat{'Questions'} < 1 ) {
badprint
"Your server has not answered any queries - cannot continue...";
exit 2;
}
# Per-thread memory
if ( mysql_version_ge(4) ) {
$mycalc{'per_thread_buffers'} =
$myvar{'read_buffer_size'} +
$myvar{'read_rnd_buffer_size'} +
$myvar{'sort_buffer_size'} +
$myvar{'thread_stack'} +
$myvar{'join_buffer_size'};
}
else {
$mycalc{'per_thread_buffers'} =
$myvar{'record_buffer'} +
$myvar{'record_rnd_buffer'} +
$myvar{'sort_buffer'} +
$myvar{'thread_stack'} +
$myvar{'join_buffer_size'};
}
$mycalc{'total_per_thread_buffers'} =
$mycalc{'per_thread_buffers'} * $myvar{'max_connections'};
$mycalc{'max_total_per_thread_buffers'} =
$mycalc{'per_thread_buffers'} * $mystat{'Max_used_connections'};
# Server-wide memory
$mycalc{'max_tmp_table_size'} =
( $myvar{'tmp_table_size'} > $myvar{'max_heap_table_size'} )
? $myvar{'max_heap_table_size'}
: $myvar{'tmp_table_size'};
$mycalc{'server_buffers'} =
$myvar{'key_buffer_size'} + $mycalc{'max_tmp_table_size'};
$mycalc{'server_buffers'} +=
( defined $myvar{'innodb_buffer_pool_size'} )
? $myvar{'innodb_buffer_pool_size'}
: 0;
$mycalc{'server_buffers'} +=
( defined $myvar{'innodb_additional_mem_pool_size'} )
? $myvar{'innodb_additional_mem_pool_size'}
: 0;
$mycalc{'server_buffers'} +=
( defined $myvar{'innodb_log_buffer_size'} )
? $myvar{'innodb_log_buffer_size'}
: 0;
$mycalc{'server_buffers'} +=
( defined $myvar{'query_cache_size'} ) ? $myvar{'query_cache_size'} : 0;
$mycalc{'server_buffers'} +=
( defined $myvar{'aria_pagecache_buffer_size'} )
? $myvar{'aria_pagecache_buffer_size'}
: 0;
# Global memory
# Max used memory is memory used by MySQL based on Max_used_connections
# This is the max memory used theorically calculated with the max concurrent connection number reached by mysql
$mycalc{'max_used_memory'} =
$mycalc{'server_buffers'} +
$mycalc{"max_total_per_thread_buffers"} +
get_pf_memory() +
get_gcache_memory();
$mycalc{'pct_max_used_memory'} =
percentage( $mycalc{'max_used_memory'}, $physical_memory );
# Total possible memory is memory needed by MySQL based on max_connections
# This is the max memory MySQL can theorically used if all connections allowed has opened by mysql
$mycalc{'max_peak_memory'} =
$mycalc{'server_buffers'} +
$mycalc{'total_per_thread_buffers'} +
get_pf_memory() +
get_gcache_memory();
$mycalc{'pct_max_physical_memory'} =
percentage( $mycalc{'max_peak_memory'}, $physical_memory );
debugprint "Max Used Memory: "
. hr_bytes( $mycalc{'max_used_memory'} ) . "";
debugprint "Max Used Percentage RAM: "
. $mycalc{'pct_max_used_memory'} . "%";
debugprint "Max Peak Memory: "
. hr_bytes( $mycalc{'max_peak_memory'} ) . "";
debugprint "Max Peak Percentage RAM: "
. $mycalc{'pct_max_physical_memory'} . "%";
# Slow queries
$mycalc{'pct_slow_queries'} =
int( ( $mystat{'Slow_queries'} / $mystat{'Questions'} ) * 100 );
# Connections
$mycalc{'pct_connections_used'} = int(
( $mystat{'Max_used_connections'} / $myvar{'max_connections'} ) * 100 );
$mycalc{'pct_connections_used'} =
( $mycalc{'pct_connections_used'} > 100 )
? 100
: $mycalc{'pct_connections_used'};
# Aborted Connections
$mycalc{'pct_connections_aborted'} =
percentage( $mystat{'Aborted_connects'}, $mystat{'Connections'} );
debugprint "Aborted_connects: " . $mystat{'Aborted_connects'} . "";
debugprint "Connections: " . $mystat{'Connections'} . "";
debugprint "pct_connections_aborted: "
. $mycalc{'pct_connections_aborted'} . "";
# Key buffers
if ( mysql_version_ge( 4, 1 ) && $myvar{'key_buffer_size'} > 0 ) {
$mycalc{'pct_key_buffer_used'} = sprintf(
"%.1f",
(
1 - (
(
$mystat{'Key_blocks_unused'} *
$myvar{'key_cache_block_size'}
) / $myvar{'key_buffer_size'}
)
) * 100
);
}
else {
$mycalc{'pct_key_buffer_used'} = 0;
}
if ( $mystat{'Key_read_requests'} > 0 ) {
$mycalc{'pct_keys_from_mem'} = sprintf(
"%.1f",
(
100 - (
( $mystat{'Key_reads'} / $mystat{'Key_read_requests'} ) *
100
)
)
);
}
else {
$mycalc{'pct_keys_from_mem'} = 0;
}
if ( defined $mystat{'Aria_pagecache_read_requests'}
&& $mystat{'Aria_pagecache_read_requests'} > 0 )
{
$mycalc{'pct_aria_keys_from_mem'} = sprintf(
"%.1f",
(
100 - (
(
$mystat{'Aria_pagecache_reads'} /
$mystat{'Aria_pagecache_read_requests'}
) * 100
)
)
);
}
else {
$mycalc{'pct_aria_keys_from_mem'} = 0;
}
if ( $mystat{'Key_write_requests'} > 0 ) {
$mycalc{'pct_wkeys_from_mem'} = sprintf( "%.1f",
( ( $mystat{'Key_writes'} / $mystat{'Key_write_requests'} ) * 100 )
);
}
else {
$mycalc{'pct_wkeys_from_mem'} = 0;
}
if ( $doremote eq 0 and !mysql_version_ge(5) ) {
my $size = 0;
$size += (split)[0]
for
`find $myvar{'datadir'} -name "*.MYI" 2>&1 | xargs du -L $duflags 2>&1`;
$mycalc{'total_myisam_indexes'} = $size;
$mycalc{'total_aria_indexes'} = 0;
}
elsif ( mysql_version_ge(5) ) {
$mycalc{'total_myisam_indexes'} = select_one
"SELECT IFNULL(SUM(INDEX_LENGTH),0) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema') AND ENGINE = 'MyISAM';";
$mycalc{'total_aria_indexes'} = select_one
"SELECT IFNULL(SUM(INDEX_LENGTH),0) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema') AND ENGINE = 'Aria';";
}
if ( defined $mycalc{'total_myisam_indexes'}
and $mycalc{'total_myisam_indexes'} == 0 )
{
$mycalc{'total_myisam_indexes'} = "fail";
}
elsif ( defined $mycalc{'total_myisam_indexes'} ) {
chomp( $mycalc{'total_myisam_indexes'} );
}
if ( defined $mycalc{'total_aria_indexes'}
and $mycalc{'total_aria_indexes'} == 0 )
{
$mycalc{'total_aria_indexes'} = 1;
}
elsif ( defined $mycalc{'total_aria_indexes'} ) {
chomp( $mycalc{'total_aria_indexes'} );
}
# Query cache
if ( mysql_version_ge(4) ) {
$mycalc{'query_cache_efficiency'} = sprintf(
"%.1f",
(
$mystat{'Qcache_hits'} /
( $mystat{'Com_select'} + $mystat{'Qcache_hits'} )
) * 100
);
if ( $myvar{'query_cache_size'} ) {
$mycalc{'pct_query_cache_used'} = sprintf(
"%.1f",
100 - (
$mystat{'Qcache_free_memory'} / $myvar{'query_cache_size'}
) * 100
);
}
if ( $mystat{'Qcache_lowmem_prunes'} == 0 ) {
$mycalc{'query_cache_prunes_per_day'} = 0;
}
else {
$mycalc{'query_cache_prunes_per_day'} = int(
$mystat{'Qcache_lowmem_prunes'} / ( $mystat{'Uptime'} / 86400 )
);
}
}
# Sorting
$mycalc{'total_sorts'} = $mystat{'Sort_scan'} + $mystat{'Sort_range'};
if ( $mycalc{'total_sorts'} > 0 ) {
$mycalc{'pct_temp_sort_table'} = int(
( $mystat{'Sort_merge_passes'} / $mycalc{'total_sorts'} ) * 100 );
}
# Joins
$mycalc{'joins_without_indexes'} =
$mystat{'Select_range_check'} + $mystat{'Select_full_join'};
$mycalc{'joins_without_indexes_per_day'} =
int( $mycalc{'joins_without_indexes'} / ( $mystat{'Uptime'} / 86400 ) );
# Temporary tables
if ( $mystat{'Created_tmp_tables'} > 0 ) {
if ( $mystat{'Created_tmp_disk_tables'} > 0 ) {
$mycalc{'pct_temp_disk'} = int(
(
$mystat{'Created_tmp_disk_tables'} /
$mystat{'Created_tmp_tables'}
) * 100
);
}
else {
$mycalc{'pct_temp_disk'} = 0;
}
}
# Table cache
if ( $mystat{'Opened_tables'} > 0 ) {
$mycalc{'table_cache_hit_rate'} =
int( $mystat{'Open_tables'} * 100 / $mystat{'Opened_tables'} );
}
else {
$mycalc{'table_cache_hit_rate'} = 100;
}
# Open files
if ( $myvar{'open_files_limit'} > 0 ) {
$mycalc{'pct_files_open'} =
int( $mystat{'Open_files'} * 100 / $myvar{'open_files_limit'} );
}
# Table locks
if ( $mystat{'Table_locks_immediate'} > 0 ) {
if ( $mystat{'Table_locks_waited'} == 0 ) {
$mycalc{'pct_table_locks_immediate'} = 100;
}
else {
$mycalc{'pct_table_locks_immediate'} = int(
$mystat{'Table_locks_immediate'} * 100 / (
$mystat{'Table_locks_waited'} +
$mystat{'Table_locks_immediate'}
)
);
}
}
# Thread cache
$mycalc{'thread_cache_hit_rate'} =
int( 100 -
( ( $mystat{'Threads_created'} / $mystat{'Connections'} ) * 100 ) );
# Other
if ( $mystat{'Connections'} > 0 ) {
$mycalc{'pct_aborted_connections'} =
int( ( $mystat{'Aborted_connects'} / $mystat{'Connections'} ) * 100 );
}
if ( $mystat{'Questions'} > 0 ) {
$mycalc{'total_reads'} = $mystat{'Com_select'};
$mycalc{'total_writes'} =
$mystat{'Com_delete'} +
$mystat{'Com_insert'} +
$mystat{'Com_update'} +
$mystat{'Com_replace'};
if ( $mycalc{'total_reads'} == 0 ) {
$mycalc{'pct_reads'} = 0;
$mycalc{'pct_writes'} = 100;
}
else {
$mycalc{'pct_reads'} = int(
(
$mycalc{'total_reads'} /
( $mycalc{'total_reads'} + $mycalc{'total_writes'} )
) * 100
);
$mycalc{'pct_writes'} = 100 - $mycalc{'pct_reads'};
}
}
# InnoDB
if ( $myvar{'have_innodb'} eq "YES" ) {
$mycalc{'innodb_log_size_pct'} =
( $myvar{'innodb_log_file_size'} *
$myvar{'innodb_log_files_in_group'} * 100 /
$myvar{'innodb_buffer_pool_size'} );
}
# InnoDB Buffer pool read cache effiency
(
$mystat{'Innodb_buffer_pool_read_requests'},
$mystat{'Innodb_buffer_pool_reads'}
)
= ( 1, 1 )
unless defined $mystat{'Innodb_buffer_pool_reads'};
$mycalc{'pct_read_efficiency'} = percentage(
(
$mystat{'Innodb_buffer_pool_read_requests'} -
$mystat{'Innodb_buffer_pool_reads'}
),
$mystat{'Innodb_buffer_pool_read_requests'}
) if defined $mystat{'Innodb_buffer_pool_read_requests'};
debugprint "pct_read_efficiency: " . $mycalc{'pct_read_efficiency'} . "";
debugprint "Innodb_buffer_pool_reads: "
. $mystat{'Innodb_buffer_pool_reads'} . "";
debugprint "Innodb_buffer_pool_read_requests: "
. $mystat{'Innodb_buffer_pool_read_requests'} . "";
# InnoDB log write cache effiency
( $mystat{'Innodb_log_write_requests'}, $mystat{'Innodb_log_writes'} ) =
( 1, 1 )
unless defined $mystat{'Innodb_log_writes'};
$mycalc{'pct_write_efficiency'} = percentage(
( $mystat{'Innodb_log_write_requests'} - $mystat{'Innodb_log_writes'} ),
$mystat{'Innodb_log_write_requests'}
) if defined $mystat{'Innodb_log_write_requests'};
debugprint "pct_write_efficiency: " . $mycalc{'pct_write_efficiency'} . "";
debugprint "Innodb_log_writes: " . $mystat{'Innodb_log_writes'} . "";
debugprint "Innodb_log_write_requests: "
. $mystat{'Innodb_log_write_requests'} . "";
$mycalc{'pct_innodb_buffer_used'} = percentage(
(
$mystat{'Innodb_buffer_pool_pages_total'} -
$mystat{'Innodb_buffer_pool_pages_free'}
),
$mystat{'Innodb_buffer_pool_pages_total'}
) if defined $mystat{'Innodb_buffer_pool_pages_total'};
# Binlog Cache
if ( $myvar{'log_bin'} ne 'OFF' ) {
$mycalc{'pct_binlog_cache'} = percentage(
$mystat{'Binlog_cache_use'} - $mystat{'Binlog_cache_disk_use'},
$mystat{'Binlog_cache_use'} );
}
}
sub mysql_stats {
subheaderprint "Performance Metrics";
# Show uptime, queries per second, connections, traffic stats
my $qps;
if ( $mystat{'Uptime'} > 0 ) {
$qps = sprintf( "%.3f", $mystat{'Questions'} / $mystat{'Uptime'} );
}
push( @generalrec,
"MySQL started within last 24 hours - recommendations may be inaccurate"
) if ( $mystat{'Uptime'} < 86400 );
infoprint "Up for: "
. pretty_uptime( $mystat{'Uptime'} ) . " ("
. hr_num( $mystat{'Questions'} ) . " q ["
. hr_num($qps)
. " qps], "
. hr_num( $mystat{'Connections'} )
. " conn," . " TX: "
. hr_bytes_rnd( $mystat{'Bytes_sent'} )
. ", RX: "
. hr_bytes_rnd( $mystat{'Bytes_received'} ) . ")";
infoprint "Reads / Writes: "
. $mycalc{'pct_reads'} . "% / "
. $mycalc{'pct_writes'} . "%";
# Binlog Cache
if ( $myvar{'log_bin'} eq 'OFF' ) {
infoprint "Binary logging is disabled";
}
else {
infoprint "Binary logging is enabled (GTID MODE: "
. ( defined( $myvar{'gtid_mode'} ) ? $myvar{'gtid_mode'} : "OFF" )
. ")";
}
# Memory usage
infoprint "Physical Memory : " . hr_bytes($physical_memory);
infoprint "Max MySQL memory : " . hr_bytes( $mycalc{'max_peak_memory'} );
infoprint "Other process memory: " . hr_bytes( get_other_process_memory() );
#print hr_bytes( $mycalc{'server_buffers'} );
infoprint "Total buffers: "
. hr_bytes( $mycalc{'server_buffers'} )
. " global + "
. hr_bytes( $mycalc{'per_thread_buffers'} )
. " per thread ($myvar{'max_connections'} max threads)";
infoprint "P_S Max memory usage: " . hr_bytes_rnd( get_pf_memory() );
$result{'P_S'}{'memory'} = get_other_process_memory();
$result{'P_S'}{'pretty_memory'} =
hr_bytes_rnd( get_other_process_memory() );
infoprint "Galera GCache Max memory usage: "
. hr_bytes_rnd( get_gcache_memory() );
$result{'Galera'}{'GCache'}{'memory'} = get_gcache_memory();
$result{'Galera'}{'GCache'}{'pretty_memory'} =
hr_bytes_rnd( get_gcache_memory() );
if ( $opt{buffers} ne 0 ) {
infoprint "Global Buffers";
infoprint " +-- Key Buffer: "
. hr_bytes( $myvar{'key_buffer_size'} ) . "";
infoprint " +-- Max Tmp Table: "
. hr_bytes( $mycalc{'max_tmp_table_size'} ) . "";
if ( defined $myvar{'query_cache_type'} ) {
infoprint "Query Cache Buffers";
infoprint " +-- Query Cache: "
. $myvar{'query_cache_type'} . " - "
. (
$myvar{'query_cache_type'} eq 0 |
$myvar{'query_cache_type'} eq 'OFF' ? "DISABLED"
: (
$myvar{'query_cache_type'} eq 1 ? "ALL REQUESTS"
: "ON DEMAND"
)
) . "";
infoprint " +-- Query Cache Size: "
. hr_bytes( $myvar{'query_cache_size'} ) . "";
}
infoprint "Per Thread Buffers";
infoprint " +-- Read Buffer: "
. hr_bytes( $myvar{'read_buffer_size'} ) . "";
infoprint " +-- Read RND Buffer: "
. hr_bytes( $myvar{'read_rnd_buffer_size'} ) . "";
infoprint " +-- Sort Buffer: "
. hr_bytes( $myvar{'sort_buffer_size'} ) . "";
infoprint " +-- Thread stack: "
. hr_bytes( $myvar{'thread_stack'} ) . "";
infoprint " +-- Join Buffer: "
. hr_bytes( $myvar{'join_buffer_size'} ) . "";
if ( $myvar{'log_bin'} ne 'OFF' ) {
infoprint "Binlog Cache Buffers";
infoprint " +-- Binlog Cache: "
. hr_bytes( $myvar{'binlog_cache_size'} ) . "";
}
}
if ( $arch
&& $arch == 32
&& $mycalc{'max_used_memory'} > 2 * 1024 * 1024 * 1024 )
{
badprint
"Allocating > 2GB RAM on 32-bit systems can cause system instability";
badprint "Maximum reached memory usage: "
. hr_bytes( $mycalc{'max_used_memory'} )
. " ($mycalc{'pct_max_used_memory'}% of installed RAM)";
}
elsif ( $mycalc{'pct_max_used_memory'} > 85 ) {
badprint "Maximum reached memory usage: "
. hr_bytes( $mycalc{'max_used_memory'} )
. " ($mycalc{'pct_max_used_memory'}% of installed RAM)";
}
else {
goodprint "Maximum reached memory usage: "
. hr_bytes( $mycalc{'max_used_memory'} )
. " ($mycalc{'pct_max_used_memory'}% of installed RAM)";
}
if ( $mycalc{'pct_max_physical_memory'} > 85 ) {
badprint "Maximum possible memory usage: "
. hr_bytes( $mycalc{'max_peak_memory'} )
. " ($mycalc{'pct_max_physical_memory'}% of installed RAM)";
push( @generalrec,
"Reduce your overall MySQL memory footprint for system stability\n See notes on accuracy of this recommendation below" );
}
else {
goodprint "Maximum possible memory usage: "
. hr_bytes( $mycalc{'max_peak_memory'} )
. " ($mycalc{'pct_max_physical_memory'}% of installed RAM)";
}
if ( $physical_memory <
( $mycalc{'max_peak_memory'} + get_other_process_memory() ) )
{
badprint
"Overall possible memory usage with other process exceeded memory";
push( @generalrec,
"Dedicate this server to your database for highest performance." );
}
else {
goodprint
"Overall possible memory usage with other process is compatible with memory available";
}
# Slow queries
if ( $mycalc{'pct_slow_queries'} > 5 ) {
badprint "Slow queries: $mycalc{'pct_slow_queries'}% ("
. hr_num( $mystat{'Slow_queries'} ) . "/"
. hr_num( $mystat{'Questions'} ) . ")";
}
else {
goodprint "Slow queries: $mycalc{'pct_slow_queries'}% ("
. hr_num( $mystat{'Slow_queries'} ) . "/"
. hr_num( $mystat{'Questions'} ) . ")";
}
if ( $myvar{'long_query_time'} > 10 ) {
push( @adjvars, "long_query_time (<= 10)" );
}
if ( defined( $myvar{'log_slow_queries'} ) ) {
if ( $myvar{'log_slow_queries'} eq "OFF" ) {
push( @generalrec,
"Enable the slow query log to troubleshoot bad queries" );
}
}
# Connections
if ( $mycalc{'pct_connections_used'} > 85 ) {
badprint
"Highest connection usage: $mycalc{'pct_connections_used'}% ($mystat{'Max_used_connections'}/$myvar{'max_connections'})";
push( @adjvars,
"max_connections (> " . $myvar{'max_connections'} . ")" );
push( @adjvars,
"wait_timeout (< " . $myvar{'wait_timeout'} . ")",
"interactive_timeout (< " . $myvar{'interactive_timeout'} . ")" );
push( @generalrec,
"Reduce or eliminate persistent connections to reduce connection usage"
);
}
else {
goodprint
"Highest usage of available connections: $mycalc{'pct_connections_used'}% ($mystat{'Max_used_connections'}/$myvar{'max_connections'})";
}
# Aborted Connections
if ( $mycalc{'pct_connections_aborted'} > 3 ) {
badprint
"Aborted connections: $mycalc{'pct_connections_aborted'}% ($mystat{'Aborted_connects'}/$mystat{'Connections'})";
push( @generalrec,
"Reduce or eliminate unclosed connections and network issues" );
}
else {
goodprint
"Aborted connections: $mycalc{'pct_connections_aborted'}% ($mystat{'Aborted_connects'}/$mystat{'Connections'})";
}
# name resolution
if ( defined( $result{'Variables'}{'skip_networking'} )
&& $result{'Variables'}{'skip_networking'} eq 'ON' )
{
infoprint
"Skipped name resolution test due to skip_networking=ON in system variables.";
}
elsif ( not defined( $result{'Variables'}{'skip_name_resolve'} ) ) {
infoprint
"Skipped name resolution test due to missing skip_name_resolve in system variables.";
}
elsif ( $result{'Variables'}{'skip_name_resolve'} eq 'OFF' ) {
badprint
"name resolution is active : a reverse name resolution is made for each new connection and can reduce performance";
push( @generalrec,
"Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1"
);
}
# Query cache
if ( !mysql_version_ge(4) ) {
# MySQL versions < 4.01 don't support query caching
push( @generalrec,
"Upgrade MySQL to version 4+ to utilize query caching" );
}
elsif ( $myvar{'query_cache_size'} < 1
and $myvar{'query_cache_type'} eq "OFF" )
{
goodprint
"Query cache is disabled by default due to mutex contention on multiprocessor machines.";
}
elsif ( $mystat{'Com_select'} == 0 ) {
badprint
"Query cache cannot be analyzed - no SELECT statements executed";
}
else {
badprint
"Query cache may be disabled by default due to mutex contention.";
push( @adjvars, "query_cache_size (=0)" );
push( @adjvars, "query_cache_type (=0)" );
if ( $mycalc{'query_cache_efficiency'} < 20 ) {
badprint
"Query cache efficiency: $mycalc{'query_cache_efficiency'}% ("
. 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 {
goodprint
"Query cache efficiency: $mycalc{'query_cache_efficiency'}% ("
. hr_num( $mystat{'Qcache_hits'} )
. " cached / "
. hr_num( $mystat{'Qcache_hits'} + $mystat{'Com_select'} )
. " selects)";
}
if ( $mycalc{'query_cache_prunes_per_day'} > 98 ) {
badprint
"Query cache prunes per day: $mycalc{'query_cache_prunes_per_day'}";
if ( $myvar{'query_cache_size'} >= 128 * 1024 * 1024 ) {
push( @generalrec,
"Increasing the query_cache size over 128M may reduce performance"
);
push( @adjvars,
"query_cache_size (> "
. hr_bytes_rnd( $myvar{'query_cache_size'} )
. ") [see warning above]" );
}
else {
push( @adjvars,
"query_cache_size (> "
. hr_bytes_rnd( $myvar{'query_cache_size'} )
. ")" );
}
}
else {
goodprint
"Query cache prunes per day: $mycalc{'query_cache_prunes_per_day'}";
}
}
# Sorting
if ( $mycalc{'total_sorts'} == 0 ) {
goodprint "No Sort requiring temporary tables";
}
elsif ( $mycalc{'pct_temp_sort_table'} > 10 ) {
badprint
"Sorts requiring temporary tables: $mycalc{'pct_temp_sort_table'}% ("
. 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 {
goodprint
"Sorts requiring temporary tables: $mycalc{'pct_temp_sort_table'}% ("
. hr_num( $mystat{'Sort_merge_passes'} )
. " temp sorts / "
. hr_num( $mycalc{'total_sorts'} )
. " sorts)";
}
# Joins
if ( $mycalc{'joins_without_indexes_per_day'} > 250 ) {
badprint
"Joins performed without indexes: $mycalc{'joins_without_indexes'}";
push( @adjvars,
"join_buffer_size (> "
. hr_bytes( $myvar{'join_buffer_size'} )
. ", or always use indexes with joins)" );
push( @generalrec,
"Adjust your join queries to always utilize indexes. Please note this \n calculation is made by adding Select_full_join + Select_range_check\n status values and triggered when the total >250" );
}
else {
goodprint "No joins without indexes";
# No joins have run without indexes
}
# Temporary tables
if ( $mystat{'Created_tmp_tables'} > 0 ) {
if ( $mycalc{'pct_temp_disk'} > 25
&& $mycalc{'max_tmp_table_size'} < 256 * 1024 * 1024 )
{
badprint
"Temporary tables created on disk: $mycalc{'pct_temp_disk'}% ("
. hr_num( $mystat{'Created_tmp_disk_tables'} )
. " on disk / "
. hr_num( $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'} )
. ")" );
push( @generalrec,
"When making adjustments, make tmp_table_size/max_heap_table_size equal"
);
push( @generalrec,
"Reduce your SELECT DISTINCT queries which have no LIMIT clause"
);
}
elsif ($mycalc{'pct_temp_disk'} > 25
&& $mycalc{'max_tmp_table_size'} >= 256 * 1024 * 1024 )
{
badprint
"Temporary tables created on disk: $mycalc{'pct_temp_disk'}% ("
. hr_num( $mystat{'Created_tmp_disk_tables'} )
. " on disk / "
. hr_num( $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 {
goodprint
"Temporary tables created on disk: $mycalc{'pct_temp_disk'}% ("
. hr_num( $mystat{'Created_tmp_disk_tables'} )
. " on disk / "
. hr_num( $mystat{'Created_tmp_tables'} )
. " total)";
}
}
else {
goodprint "No tmp tables created on disk";
}
# Thread cache
if ( $myvar{'thread_cache_size'} eq 0 ) {
badprint "Thread cache is disabled";
push( @generalrec, "Set thread_cache_size to 4 as a starting value" );
push( @adjvars, "thread_cache_size (start at 4)" );
}
else {
if ( defined( $myvar{'thread_handling'} )
and $myvar{'thread_handling'} eq 'pools-of-threads' )
{
infoprint "Thread cache hit rate: not used with pool-of-threads";
}
else {
if ( $mycalc{'thread_cache_hit_rate'} <= 50 ) {
badprint
"Thread cache hit rate: $mycalc{'thread_cache_hit_rate'}% ("
. hr_num( $mystat{'Threads_created'} )
. " created / "
. hr_num( $mystat{'Connections'} )
. " connections)";
push( @adjvars,
"thread_cache_size (> $myvar{'thread_cache_size'})" );
}
else {
goodprint
"Thread cache hit rate: $mycalc{'thread_cache_hit_rate'}% ("
. hr_num( $mystat{'Threads_created'} )
. " created / "
. hr_num( $mystat{'Connections'} )
. " connections)";
}
}
}
# Table cache
my $table_cache_var = "";
if ( $mystat{'Open_tables'} > 0 ) {
if ( $mycalc{'table_cache_hit_rate'} < 20 ) {
badprint "Table cache hit rate: $mycalc{'table_cache_hit_rate'}% ("
. hr_num( $mystat{'Open_tables'} )
. " open / "
. hr_num( $mystat{'Opened_tables'} )
. " opened)";
if ( mysql_version_ge( 5, 1 ) ) {
$table_cache_var = "table_open_cache";
}
else {
$table_cache_var = "table_cache";
}
push( @adjvars,
$table_cache_var . " (> " . $myvar{$table_cache_var} . ")" );
push( @generalrec,
"Increase "
. $table_cache_var
. " gradually to avoid file descriptor limits" );
push( @generalrec,
"Read this before increasing "
. $table_cache_var
. " over 64: http://bit.ly/1mi7c4C\n (exception MariaDB 10.1.x mariadb.com/kb/en/mariadb/optimizing-table_open_cache/)" );
push( @generalrec,
"Beware that open_files_limit ("
. $myvar{'open_files_limit'}
. ") variable " );
push( @generalrec,
"should be greater than $table_cache_var ("
. $myvar{$table_cache_var}
. ")" );
}
else {
goodprint "Table cache hit rate: $mycalc{'table_cache_hit_rate'}% ("
. hr_num( $mystat{'Open_tables'} )
. " open / "
. hr_num( $mystat{'Opened_tables'} )
. " opened)";
}
}
# Open files
if ( defined $mycalc{'pct_files_open'} ) {
if ( $mycalc{'pct_files_open'} > 85 ) {
badprint "Open file limit used: $mycalc{'pct_files_open'}% ("
. hr_num( $mystat{'Open_files'} ) . "/"
. hr_num( $myvar{'open_files_limit'} ) . ")";
push( @adjvars,
"open_files_limit (> " . $myvar{'open_files_limit'} . ")" );
}
else {
goodprint "Open file limit used: $mycalc{'pct_files_open'}% ("
. hr_num( $mystat{'Open_files'} ) . "/"
. hr_num( $myvar{'open_files_limit'} ) . ")";
}
}
# Table locks
if ( defined $mycalc{'pct_table_locks_immediate'} ) {
if ( $mycalc{'pct_table_locks_immediate'} < 95 ) {
badprint
"Table locks acquired immediately: $mycalc{'pct_table_locks_immediate'}%";
push( @generalrec,
"Optimize queries and/or use InnoDB to reduce lock wait" );
}
else {
goodprint
"Table locks acquired immediately: $mycalc{'pct_table_locks_immediate'}% ("
. hr_num( $mystat{'Table_locks_immediate'} )
. " immediate / "
. hr_num( $mystat{'Table_locks_waited'} +
$mystat{'Table_locks_immediate'} )
. " locks)";
}
}
# Binlog cache
if ( defined $mycalc{'pct_binlog_cache'} ) {
if ( $mycalc{'pct_binlog_cache'} < 90
&& $mystat{'Binlog_cache_use'} > 0 )
{
badprint "Binlog cache memory access: "
. $mycalc{'pct_binlog_cache'} . "% ("
. (
$mystat{'Binlog_cache_use'} - $mystat{'Binlog_cache_disk_use'} )
. " Memory / "
. $mystat{'Binlog_cache_use'}
. " Total)";
push( @generalrec,
"Increase binlog_cache_size (Actual value: "
. $myvar{'binlog_cache_size'}
. ")" );
push( @adjvars,
"binlog_cache_size ("
. hr_bytes( $myvar{'binlog_cache_size'} + 16 * 1024 * 1024 )
. ")" );
}
else {
goodprint "Binlog cache memory access: "
. $mycalc{'pct_binlog_cache'} . "% ("
. (
$mystat{'Binlog_cache_use'} - $mystat{'Binlog_cache_disk_use'} )
. " Memory / "
. $mystat{'Binlog_cache_use'}
. " Total)";
debugprint "Not enough data to validate binlog cache size\n"
if $mystat{'Binlog_cache_use'} < 10;
}
}
# Performance options
if ( !mysql_version_ge( 5, 1 ) ) {
push( @generalrec, "Upgrade to MySQL 5.5+ to use asynchronous write" );
}
elsif ( $myvar{'concurrent_insert'} eq "OFF" ) {
push( @generalrec, "Enable concurrent_insert by setting it to 'ON'" );
}
elsif ( $myvar{'concurrent_insert'} eq 0 ) {
push( @generalrec, "Enable concurrent_insert by setting it to 1" );
}
}
# Recommendations for MyISAM
sub mysql_myisam {
subheaderprint "MyISAM Metrics";
# Key buffer usage
if ( defined( $mycalc{'pct_key_buffer_used'} ) ) {
if ( $mycalc{'pct_key_buffer_used'} < 90 ) {
badprint "Key buffer used: $mycalc{'pct_key_buffer_used'}% ("
. hr_num( $myvar{'key_buffer_size'} *
$mycalc{'pct_key_buffer_used'} /
100 )
. " used / "
. hr_num( $myvar{'key_buffer_size'} )
. " cache)";
#push(@adjvars,"key_buffer_size (\~ ".hr_num( $myvar{'key_buffer_size'} * $mycalc{'pct_key_buffer_used'} / 100).")");
}
else {
goodprint "Key buffer used: $mycalc{'pct_key_buffer_used'}% ("
. hr_num( $myvar{'key_buffer_size'} *
$mycalc{'pct_key_buffer_used'} /
100 )
. " used / "
. hr_num( $myvar{'key_buffer_size'} )
. " cache)";
}
}
else {
# No queries have run that would use keys
debugprint "Key buffer used: $mycalc{'pct_key_buffer_used'}% ("
. hr_num(
$myvar{'key_buffer_size'} * $mycalc{'pct_key_buffer_used'} / 100 )
. " used / "
. hr_num( $myvar{'key_buffer_size'} )
. " cache)";
}
# Key buffer
if ( !defined( $mycalc{'total_myisam_indexes'} ) and $doremote == 1 ) {
push( @generalrec,
"Unable to calculate MyISAM indexes on remote MySQL server < 5.0.0"
);
}
elsif ( $mycalc{'total_myisam_indexes'} =~ /^fail$/ ) {
badprint
"Cannot calculate MyISAM index size - re-run script as root user";
}
elsif ( $mycalc{'total_myisam_indexes'} == "0" ) {
badprint
"None of your MyISAM tables are indexed - add indexes immediately";
}
else {
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'} ) . "";
push( @adjvars,
"key_buffer_size (> "
. hr_bytes( $mycalc{'total_myisam_indexes'} )
. ")" );
}
else {
goodprint "Key buffer size / total MyISAM indexes: "
. hr_bytes( $myvar{'key_buffer_size'} ) . "/"
. hr_bytes( $mycalc{'total_myisam_indexes'} ) . "";
}
if ( $mystat{'Key_read_requests'} > 0 ) {
if ( $mycalc{'pct_keys_from_mem'} < 95 ) {
badprint
"Read Key buffer hit rate: $mycalc{'pct_keys_from_mem'}% ("
. hr_num( $mystat{'Key_read_requests'} )
. " cached / "
. hr_num( $mystat{'Key_reads'} )
. " reads)";
}
else {
goodprint
"Read Key buffer hit rate: $mycalc{'pct_keys_from_mem'}% ("
. hr_num( $mystat{'Key_read_requests'} )
. " cached / "
. hr_num( $mystat{'Key_reads'} )
. " reads)";
}
}
else {
# No queries have run that would use keys
debugprint "Key buffer size / total MyISAM indexes: "
. hr_bytes( $myvar{'key_buffer_size'} ) . "/"
. hr_bytes( $mycalc{'total_myisam_indexes'} ) . "";
}
if ( $mystat{'Key_write_requests'} > 0 ) {
if ( $mycalc{'pct_wkeys_from_mem'} < 95 ) {
badprint
"Write Key buffer hit rate: $mycalc{'pct_wkeys_from_mem'}% ("
. hr_num( $mystat{'Key_write_requests'} )
. " cached / "
. hr_num( $mystat{'Key_writes'} )
. " writes)";
}
else {
goodprint
"Write Key buffer hit rate: $mycalc{'pct_wkeys_from_mem'}% ("
. hr_num( $mystat{'Key_write_requests'} )
. " cached / "
. hr_num( $mystat{'Key_writes'} )
. " writes)";
}
}
else {
# No queries have run that would use keys
debugprint
"Write Key buffer hit rate: $mycalc{'pct_wkeys_from_mem'}% ("
. hr_num( $mystat{'Key_write_requests'} )
. " cached / "
. hr_num( $mystat{'Key_writes'} )
. " writes)";
}
}
}
# Recommendations for ThreadPool
sub mariadb_threadpool {
subheaderprint "ThreadPool Metrics";
# AriaDB
unless ( defined $myvar{'have_threadpool'}
&& $myvar{'have_threadpool'} eq "YES" )
{
infoprint "ThreadPool stat is disabled.";
return;
}
infoprint "ThreadPool stat is enabled.";
infoprint "Thread Pool Size: " . $myvar{'thread_pool_size'} . " thread(s).";
if ( $myvar{'version'} =~ /mariadb|percona/i ) {
infoprint "Using default value is good enough for your version ("
. $myvar{'version'} . ")";
return;
}
if ( $myvar{'have_innodb'} eq 'YES' ) {
if ( $myvar{'thread_pool_size'} < 16
or $myvar{'thread_pool_size'} > 36 )
{
badprint
"thread_pool_size between 16 and 36 when using InnoDB storage engine.";
push( @generalrec,
"Thread pool size for InnoDB usage ("
. $myvar{'thread_pool_size'}
. ")" );
push( @adjvars,
"thread_pool_size between 16 and 36 for InnoDB usage" );
}
else {
goodprint
"thread_pool_size between 16 and 36 when using InnoDB storage engine.";
}
return;
}
if ( $myvar{'have_isam'} eq 'YES' ) {
if ( $myvar{'thread_pool_size'} < 4 or $myvar{'thread_pool_size'} > 8 )
{
badprint
"thread_pool_size between 4 and 8 when using MyIsam storage engine.";
push( @generalrec,
"Thread pool size for MyIsam usage ("
. $myvar{'thread_pool_size'}
. ")" );
push( @adjvars,
"thread_pool_size between 4 and 8 for MyIsam usage" );
}
else {
goodprint
"thread_pool_size between 4 and 8 when using MyISAM storage engine.";
}
}
}
sub get_pf_memory {
# Performance Schema
return 0 unless defined $myvar{'performance_schema'};
return 0 if $myvar{'performance_schema'} eq 'OFF';
my @infoPFSMemory = grep /performance_schema.memory/,
select_array("SHOW ENGINE PERFORMANCE_SCHEMA STATUS");
return 0 if scalar(@infoPFSMemory) == 0;
$infoPFSMemory[0] =~ s/.*\s+(\d+)$/$1/g;
return $infoPFSMemory[0];
}
# Recommendations for Performance Schema
sub mysqsl_pfs {
subheaderprint "Performance schema";
# Performance Schema
$myvar{'performance_schema'} = 'OFF'
unless defined( $myvar{'performance_schema'} );
unless ( $myvar{'performance_schema'} eq 'ON' ) {
infoprint "Performance schema is disabled.";
if ( mysql_version_ge( 5, 6 ) ) {
push( @generalrec,
"Performance should be activated for better diagnostics" );
push( @adjvars, "performance_schema = ON enable PFS" );
}
}
else {
if ( mysql_version_le( 5, 5 ) ) {
push( @generalrec,
"Performance shouldn't be activated for MySQL and MariaDB 5.5 and lower version"
);
push( @adjvars, "performance_schema = OFF disable PFS" );
}
}
debugprint "Performance schema is " . $myvar{'performance_schema'};
infoprint "Memory used by P_S: " . hr_bytes( get_pf_memory() );
unless ( grep /^sys$/, select_array("SHOW DATABASES") ) {
infoprint "Sys schema isn't installed.";
push( @generalrec,
"Consider installing Sys schema from https://github.com/mysql/mysql-sys"
) unless ( mysql_version_le( 5, 5 ) );
return;
}
else {
infoprint "Sys schema is installed.";
}
return if ( $opt{pfstat} == 0 or $myvar{'performance_schema'} ne 'ON' );
infoprint "Sys schema Version: "
. select_one("select sys_version from sys.version");
# Top user per connection
subheaderprint "Performance schema: Top 5 user per connection";
my $nbL = 1;
for my $lQuery (
select_array(
'select user, total_connections from sys.user_summary order by total_connections desc LIMIT 5'
)
)
{
infoprint " +-- $nbL: $lQuery conn(s)";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top user per statement
subheaderprint "Performance schema: Top 5 user per statement";
$nbL = 1;
for my $lQuery (
select_array(
'select user, statements from sys.user_summary order by statements desc LIMIT 5'
)
)
{
infoprint " +-- $nbL: $lQuery stmt(s)";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top user per statement latency
subheaderprint "Performance schema: Top 5 user per statement latency";
$nbL = 1;
for my $lQuery (
select_array(
'select user, statement_avg_latency from sys.user_summary order by statement_avg_latency desc LIMIT 5'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top user per lock latency
subheaderprint "Performance schema: Top 5 user per lock latency";
$nbL = 1;
for my $lQuery (
select_array(
'select user, lock_latency from sys.user_summary_by_statement_latency order by lock_latency desc LIMIT 5'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top user per full scans
subheaderprint "Performance schema: Top 5 user per nb full scans";
$nbL = 1;
for my $lQuery (
select_array(
'select user, full_scans from sys.user_summary_by_statement_latency order by full_scans desc LIMIT 5'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top user per row_sent
subheaderprint "Performance schema: Top 5 user per rows sent";
$nbL = 1;
for my $lQuery (
select_array(
'select user, rows_sent from sys.user_summary_by_statement_latency order by rows_sent desc LIMIT 5'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top user per row modified
subheaderprint "Performance schema: Top 5 user per rows modified";
$nbL = 1;
for my $lQuery (
select_array(
'select user, rows_affected from sys.user_summary_by_statement_latency order by rows_affected desc LIMIT 5'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top user per io
subheaderprint "Performance schema: Top 5 user per io";
$nbL = 1;
for my $lQuery (
select_array(
'select user, file_ios from sys.user_summary order by file_ios desc LIMIT 5'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top user per io latency
subheaderprint "Performance schema: Top 5 user per io latency";
$nbL = 1;
for my $lQuery (
select_array(
'select user, file_io_latency from sys.user_summary order by file_io_latency desc LIMIT 5'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top host per connection
subheaderprint "Performance schema: Top 5 host per connection";
$nbL = 1;
for my $lQuery (
select_array(
'select host, total_connections from sys.host_summary order by total_connections desc LIMIT 5'
)
)
{
infoprint " +-- $nbL: $lQuery conn(s)";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top host per statement
subheaderprint "Performance schema: Top 5 host per statement";
$nbL = 1;
for my $lQuery (
select_array(
'select host, statements from sys.host_summary order by statements desc LIMIT 5'
)
)
{
infoprint " +-- $nbL: $lQuery stmt(s)";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top host per statement latency
subheaderprint "Performance schema: Top 5 host per statement latency";
$nbL = 1;
for my $lQuery (
select_array(
'select host, statement_avg_latency from sys.host_summary order by statement_avg_latency desc LIMIT 5'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top host per lock latency
subheaderprint "Performance schema: Top 5 host per lock latency";
$nbL = 1;
for my $lQuery (
select_array(
'select host, lock_latency from sys.host_summary_by_statement_latency order by lock_latency desc LIMIT 5'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top host per full scans
subheaderprint "Performance schema: Top 5 host per nb full scans";
$nbL = 1;
for my $lQuery (
select_array(
'select host, full_scans from sys.host_summary_by_statement_latency order by full_scans desc LIMIT 5'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top host per rows sent
subheaderprint "Performance schema: Top 5 host per rows sent";
$nbL = 1;
for my $lQuery (
select_array(
'select host, rows_sent from sys.host_summary_by_statement_latency order by rows_sent desc LIMIT 5'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top host per rows modified
subheaderprint "Performance schema: Top 5 host per rows modified";
$nbL = 1;
for my $lQuery (
select_array(
'select host, rows_affected from sys.host_summary_by_statement_latency order by rows_affected desc LIMIT 5'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top host per io
subheaderprint "Performance schema: Top 5 host per io";
$nbL = 1;
for my $lQuery (
select_array(
'select host, file_ios from sys.host_summary order by file_ios desc LIMIT 5'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top 5 host per io latency
subheaderprint "Performance schema: Top 5 host per io latency";
$nbL = 1;
for my $lQuery (
select_array(
'select host, file_io_latency from sys.host_summary order by file_io_latency desc LIMIT 5'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top IO type order by total io
subheaderprint "Performance schema: Top IO type order by total io";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select substring(event_name,14), SUM(total)AS total from sys.host_summary_by_file_io_type GROUP BY substring(event_name,14) ORDER BY total DESC;'
)
)
{
infoprint " +-- $nbL: $lQuery i/o";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top IO type order by total latency
subheaderprint "Performance schema: Top IO type order by total latency";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select substring(event_name,14), format_time(ROUND(SUM(total_latency),1)) AS total_latency from sys.host_summary_by_file_io_type GROUP BY substring(event_name,14) ORDER BY total_latency DESC;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top IO type order by max latency
subheaderprint "Performance schema: Top IO type order by max latency";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select substring(event_name,14), MAX(max_latency) as max_latency from sys.host_summary_by_file_io_type GROUP BY substring(event_name,14) ORDER BY max_latency DESC;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top Stages order by total io
subheaderprint "Performance schema: Top Stages order by total io";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select substring(event_name,7), SUM(total)AS total from sys.host_summary_by_stages GROUP BY substring(event_name,7) ORDER BY total DESC;'
)
)
{
infoprint " +-- $nbL: $lQuery i/o";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top Stages order by total latency
subheaderprint "Performance schema: Top Stages order by total latency";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select substring(event_name,7), format_time(ROUND(SUM(total_latency),1)) AS total_latency from sys.host_summary_by_stages GROUP BY substring(event_name,7) ORDER BY total_latency DESC;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top Stages order by avg latency
subheaderprint "Performance schema: Top Stages order by avg latency";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select substring(event_name,7), MAX(avg_latency) as avg_latency from sys.host_summary_by_stages GROUP BY substring(event_name,7) ORDER BY avg_latency DESC;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top host per table scans
subheaderprint "Performance schema: Top 5 host per table scans";
$nbL = 1;
for my $lQuery (
select_array(
'select host, table_scans from sys.host_summary order by table_scans desc LIMIT 5'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# InnoDB Buffer Pool by schema
subheaderprint "Performance schema: InnoDB Buffer Pool by schema";
$nbL = 1;
for my $lQuery (
select_array(
'select object_schema, allocated, data, pages from sys.innodb_buffer_stats_by_schema ORDER BY pages DESC'
)
)
{
infoprint " +-- $nbL: $lQuery page(s)";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# InnoDB Buffer Pool by table
subheaderprint "Performance schema: InnoDB Buffer Pool by table";
$nbL = 1;
for my $lQuery (
select_array(
"select CONCAT(object_schema,CONCAT('.', object_name)), allocated,data, pages from sys.innodb_buffer_stats_by_table ORDER BY pages DESC"
)
)
{
infoprint " +-- $nbL: $lQuery page(s)";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Process per allocated memory
subheaderprint "Performance schema: Process per allocated memory";
$nbL = 1;
for my $lQuery (
select_array(
"select concat(user,concat('/', IFNULL(Command,'NONE'))) AS PROC, current_memory from sys.processlist ORDER BY current_memory DESC;"
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# InnoDB Lock Waits
subheaderprint "Performance schema: InnoDB Lock Waits";
$nbL = 1;
for my $lQuery (
select_array(
"use sys;select wait_age_secs, locked_table, locked_type, waiting_query from innodb_lock_waits order by wait_age_secs DESC;"
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Threads IO Latency
subheaderprint "Performance schema: Thread IO Latency";
$nbL = 1;
for my $lQuery (
select_array(
"use sys;select user, total_latency, max_latency from io_by_thread_by_latency order by total_latency;"
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# High Cost SQL statements
subheaderprint "Performance schema: Top 5 Most latency statements";
$nbL = 1;
for my $lQuery (
select_array(
'select query, avg_latency from sys.statement_analysis order by avg_latency desc LIMIT 5'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top 5% slower queries
subheaderprint "Performance schema: Top 5 slower queries";
$nbL = 1;
for my $lQuery (
select_array(
'select query, exec_count from sys.statements_with_runtimes_in_95th_percentile order by exec_count desc LIMIT 5'
)
)
{
infoprint " +-- $nbL: $lQuery s";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top 10 nb statement type
subheaderprint "Performance schema: Top 10 nb statement type";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select statement, sum(total) as total from host_summary_by_statement_type group by statement order by total desc LIMIT 10;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top statement by total latency
subheaderprint "Performance schema: Top statement by total latency";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select statement, sum(total_latency) as total from sys.host_summary_by_statement_type group by statement order by total desc LIMIT 10;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top statement by lock latency
subheaderprint "Performance schema: Top statement by lock latency";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select statement, sum(lock_latency) as total from sys.host_summary_by_statement_type group by statement order by total desc LIMIT 10;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top statement by full scans
subheaderprint "Performance schema: Top statement by full scans";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select statement, sum(full_scans) as total from sys.host_summary_by_statement_type group by statement order by total desc LIMIT 10;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top statement by rows sent
subheaderprint "Performance schema: Top statement by rows sent";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select statement, sum(rows_sent) as total from sys.host_summary_by_statement_type group by statement order by total desc LIMIT 10;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Top statement by rows modified
subheaderprint "Performance schema: Top statement by rows modified";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select statement, sum(rows_affected) as total from sys.host_summary_by_statement_type group by statement order by total desc LIMIT 10;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Use temporary tables
subheaderprint "Performance schema: Some queries using temp table";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select query from sys.statements_with_temp_tables LIMIT 20'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Unused Indexes
subheaderprint "Performance schema: Unused indexes";
$nbL = 1;
for my $lQuery ( select_array('select * from sys.schema_unused_indexes') ) {
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Full table scans
subheaderprint "Performance schema: Tables with full table scans";
$nbL = 1;
for my $lQuery (
select_array(
'select * from sys.schema_tables_with_full_table_scans order by rows_full_scanned DESC'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Latest file IO by latency
subheaderprint "Performance schema: Latest FILE IO by latency";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select thread, file, latency, operation from latest_file_io ORDER BY latency LIMIT 10;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# FILE by IO read bytes
subheaderprint "Performance schema: FILE by IO read bytes";
$nbL = 1;
for my $lQuery (
select_array(
"use sys;(select file, total_read from io_global_by_file_by_bytes where total_read like '%MiB' order by total_read DESC) UNION (select file, total_read from io_global_by_file_by_bytes where total_read like '%KiB' order by total_read DESC LIMIT 15);"
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# FILE by IO written bytes
subheaderprint "Performance schema: FILE by IO written bytes";
$nbL = 1;
for my $lQuery (
select_array(
"use sys;(select file, total_written from io_global_by_file_by_bytes where total_written like '%MiB' order by total_written DESC) UNION (select file, total_written from io_global_by_file_by_bytes where total_written like '%KiB' order by total_written DESC LIMIT 15);"
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# file per IO total latency
subheaderprint "Performance schema: file per IO total latency";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select file, total_latency from io_global_by_file_by_latency ORDER BY total_latency DESC LIMIT 20;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# file per IO read latency
subheaderprint "Performance schema: file per IO read latency";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select file, read_latency from io_global_by_file_by_latency ORDER BY read_latency DESC LIMIT 20;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# file per IO write latency
subheaderprint "Performance schema: file per IO write latency";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select file, write_latency from io_global_by_file_by_latency ORDER BY write_latency DESC LIMIT 20;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Event Wait by read bytes
subheaderprint "Performance schema: Event Wait by read bytes";
$nbL = 1;
for my $lQuery (
select_array(
"use sys;(select event_name, total_read from io_global_by_wait_by_bytes where total_read like '%MiB' order by total_read DESC) UNION (select event_name, total_read from io_global_by_wait_by_bytes where total_read like '%KiB' order by total_read DESC LIMIT 15);"
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Event Wait by write bytes
subheaderprint "Performance schema: Event Wait written bytes";
$nbL = 1;
for my $lQuery (
select_array(
"use sys;(select event_name, total_written from io_global_by_wait_by_bytes where total_written like '%MiB' order by total_written DESC) UNION (select event_name, total_written from io_global_by_wait_by_bytes where total_written like '%KiB' order by total_written DESC LIMIT 15);"
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# event per wait total latency
subheaderprint "Performance schema: event per wait total latency";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select event_name, total_latency from io_global_by_wait_by_latency ORDER BY total_latency DESC LIMIT 20;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# event per wait read latency
subheaderprint "Performance schema: event per wait read latency";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select event_name, read_latency from io_global_by_wait_by_latency ORDER BY read_latency DESC LIMIT 20;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# event per wait write latency
subheaderprint "Performance schema: event per wait write latency";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select event_name, write_latency from io_global_by_wait_by_latency ORDER BY write_latency DESC LIMIT 20;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
#schema_index_statistics
# TOP 15 most read index
subheaderprint "Performance schema: TOP 15 most read indexes";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select table_schema, table_name,index_name, rows_selected from schema_index_statistics ORDER BY ROWs_selected DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# TOP 15 most used index
subheaderprint "Performance schema: TOP 15 most modified indexes";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select table_schema, table_name,index_name, rows_inserted+rows_updated+rows_deleted AS changes from schema_index_statistics ORDER BY rows_inserted+rows_updated+rows_deleted DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# TOP 15 high read latency index
subheaderprint "Performance schema: TOP 15 high read latency index";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select table_schema, table_name,index_name, select_latency from schema_index_statistics ORDER BY select_latency DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# TOP 15 high insert latency index
subheaderprint "Performance schema: TOP 15 most modified indexes";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select table_schema, table_name,index_name, insert_latency from schema_index_statistics ORDER BY insert_latency DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# TOP 15 high update latency index
subheaderprint "Performance schema: TOP 15 high update latency index";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select table_schema, table_name,index_name, update_latency from schema_index_statistics ORDER BY update_latency DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# TOP 15 high delete latency index
subheaderprint "Performance schema: TOP 15 high delete latency index";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select table_schema, table_name,index_name, delete_latency from schema_index_statistics ORDER BY delete_latency DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# TOP 15 most read tables
subheaderprint "Performance schema: TOP 15 most read tables";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select table_schema, table_name, rows_fetched from schema_table_statistics ORDER BY ROWs_fetched DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# TOP 15 most used tables
subheaderprint "Performance schema: TOP 15 most modified tables";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select table_schema, table_name, rows_inserted+rows_updated+rows_deleted AS changes from schema_table_statistics ORDER BY rows_inserted+rows_updated+rows_deleted DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# TOP 15 high read latency tables
subheaderprint "Performance schema: TOP 15 high read latency tables";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select table_schema, table_name, fetch_latency from schema_table_statistics ORDER BY fetch_latency DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# TOP 15 high insert latency tables
subheaderprint "Performance schema: TOP 15 high insert latency tables";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select table_schema, table_name, insert_latency from schema_table_statistics ORDER BY insert_latency DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# TOP 15 high update latency tables
subheaderprint "Performance schema: TOP 15 high update latency tables";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select table_schema, table_name, update_latency from schema_table_statistics ORDER BY update_latency DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# TOP 15 high delete latency tables
subheaderprint "Performance schema: TOP 15 high delete latency tables";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select table_schema, table_name, delete_latency from schema_table_statistics ORDER BY delete_latency DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
# Redundant indexes
subheaderprint "Performance schema: Redundant indexes";
$nbL = 1;
for my $lQuery (
select_array('use sys;select * from schema_redundant_indexes;') )
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint "Performance schema: Tables not using InnoDB buffer";
$nbL = 1;
for my $lQuery (
select_array(
' Select table_schema, table_name from sys.schema_table_statistics_with_buffer where innodb_buffer_allocated IS NULL;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint "Performance schema: Table not using InnoDB buffer";
$nbL = 1;
for my $lQuery (
select_array(
' Select table_schema, table_name from sys.schema_table_statistics_with_buffer where innodb_buffer_allocated IS NULL;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint "Performance schema: Table not using InnoDB buffer";
$nbL = 1;
for my $lQuery (
select_array(
' Select table_schema, table_name from sys.schema_table_statistics_with_buffer where innodb_buffer_allocated IS NULL;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint "Performance schema: Top 15 Tables using InnoDB buffer";
$nbL = 1;
for my $lQuery (
select_array(
'select table_schema,table_name,innodb_buffer_allocated from sys.schema_table_statistics_with_buffer where innodb_buffer_allocated IS NOT NULL ORDER BY innodb_buffer_allocated DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint "Performance schema: Top 15 Tables with InnoDB buffer free";
$nbL = 1;
for my $lQuery (
select_array(
'select table_schema,table_name,innodb_buffer_free from sys.schema_table_statistics_with_buffer where innodb_buffer_allocated IS NOT NULL ORDER BY innodb_buffer_free DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint "Performance schema: Top 15 Most executed queries";
$nbL = 1;
for my $lQuery (
select_array(
'select db, query, exec_count from sys.statement_analysis order by exec_count DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint
"Performance schema: Latest SQL queries in errors or warnings";
$nbL = 1;
for my $lQuery (
select_array(
'select query, last_seen from sys.statements_with_errors_or_warnings ORDER BY last_seen LIMIT 100;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint "Performance schema: Top 20 queries with full table scans";
$nbL = 1;
for my $lQuery (
select_array(
'select db, query, exec_count from sys.statements_with_full_table_scans order BY exec_count DESC LIMIT 20;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint "Performance schema: Last 50 queries with full table scans";
$nbL = 1;
for my $lQuery (
select_array(
'select db, query, last_seen from sys.statements_with_full_table_scans order BY last_seen DESC LIMIT 50;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint "Performance schema: TOP 15 reader queries (95% percentile)";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select db, query , rows_sent from statements_with_runtimes_in_95th_percentile ORDER BY ROWs_sent DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint
"Performance schema: TOP 15 most row look queries (95% percentile)";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select db, query, rows_examined AS search from statements_with_runtimes_in_95th_percentile ORDER BY rows_examined DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint
"Performance schema: TOP 15 total latency queries (95% percentile)";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select db, query, total_latency AS search from statements_with_runtimes_in_95th_percentile ORDER BY total_latency DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint
"Performance schema: TOP 15 max latency queries (95% percentile)";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select db, query, max_latency AS search from statements_with_runtimes_in_95th_percentile ORDER BY max_latency DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint
"Performance schema: TOP 15 average latency queries (95% percentile)";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select db, query, avg_latency AS search from statements_with_runtimes_in_95th_percentile ORDER BY avg_latency DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint "Performance schema: Top 20 queries with sort";
$nbL = 1;
for my $lQuery (
select_array(
'select db, query, exec_count from sys.statements_with_sorting order BY exec_count DESC LIMIT 20;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint "Performance schema: Last 50 queries with sort";
$nbL = 1;
for my $lQuery (
select_array(
'select db, query, last_seen from sys.statements_with_sorting order BY last_seen DESC LIMIT 50;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint "Performance schema: TOP 15 row sorting queries with sort";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select db, query , rows_sorted from statements_with_sorting ORDER BY ROWs_sorted DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint "Performance schema: TOP 15 total latency queries with sort";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select db, query, total_latency AS search from statements_with_sorting ORDER BY total_latency DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint "Performance schema: TOP 15 merge queries with sort";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select db, query, sort_merge_passes AS search from statements_with_sorting ORDER BY sort_merge_passes DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint
"Performance schema: TOP 15 average sort merges queries with sort";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select db, query, avg_sort_merges AS search from statements_with_sorting ORDER BY avg_sort_merges DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint "Performance schema: TOP 15 scans queries with sort";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select db, query, sorts_using_scans AS search from statements_with_sorting ORDER BY sorts_using_scans DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint "Performance schema: TOP 15 range queries with sort";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select db, query, sort_using_range AS search from statements_with_sorting ORDER BY sort_using_range DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
##################################################################################
#statements_with_temp_tables
#mysql> desc statements_with_temp_tables;
#+--------------------------+---------------------+------+-----+---------------------+-------+
#| Field | Type | Null | Key | Default | Extra |
#+--------------------------+---------------------+------+-----+---------------------+-------+
#| query | longtext | YES | | NULL | |
#| db | varchar(64) | YES | | NULL | |
#| exec_count | bigint(20) unsigned | NO | | NULL | |
#| total_latency | text | YES | | NULL | |
#| memory_tmp_tables | bigint(20) unsigned | NO | | NULL | |
#| disk_tmp_tables | bigint(20) unsigned | NO | | NULL | |
#| avg_tmp_tables_per_query | decimal(21,0) | NO | | 0 | |
#| tmp_tables_to_disk_pct | decimal(24,0) | NO | | 0 | |
#| first_seen | timestamp | NO | | 0000-00-00 00:00:00 | |
#| last_seen | timestamp | NO | | 0000-00-00 00:00:00 | |
#| digest | varchar(32) | YES | | NULL | |
#+--------------------------+---------------------+------+-----+---------------------+-------+
#11 rows in set (0,01 sec)#
#
subheaderprint "Performance schema: Top 20 queries with temp table";
$nbL = 1;
for my $lQuery (
select_array(
'select db, query, exec_count from sys.statements_with_temp_tables order BY exec_count DESC LIMIT 20;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint "Performance schema: Last 50 queries with temp table";
$nbL = 1;
for my $lQuery (
select_array(
'select db, query, last_seen from sys.statements_with_temp_tables order BY last_seen DESC LIMIT 50;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint
"Performance schema: TOP 15 total latency queries with temp table";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select db, query, total_latency AS search from statements_with_temp_tables ORDER BY total_latency DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint "Performance schema: TOP 15 queries with temp table to disk";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select db, query, disk_tmp_tables from statements_with_temp_tables ORDER BY disk_tmp_tables DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
##################################################################################
#wait_classes_global_by_latency
#ysql> select * from wait_classes_global_by_latency;
#-----------------+-------+---------------+-------------+-------------+-------------+
# event_class | total | total_latency | min_latency | avg_latency | max_latency |
#-----------------+-------+---------------+-------------+-------------+-------------+
# wait/io/file | 15381 | 1.23 s | 0 ps | 80.12 us | 230.64 ms |
# wait/io/table | 59 | 7.57 ms | 5.45 us | 128.24 us | 3.95 ms |
# wait/lock/table | 69 | 3.22 ms | 658.84 ns | 46.64 us | 1.10 ms |
#-----------------+-------+---------------+-------------+-------------+-------------+
# rows in set (0,00 sec)
subheaderprint "Performance schema: TOP 15 class events by number";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select event_class, total from wait_classes_global_by_latency ORDER BY total DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint "Performance schema: TOP 30 events by number";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select events, total from waits_global_by_latency ORDER BY total DESC LIMIT 30;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint "Performance schema: TOP 15 class events by total latency";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select event_class, total_latency from wait_classes_global_by_latency ORDER BY total_latency DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint "Performance schema: TOP 30 events by total latency";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select events, total_latency from waits_global_by_latency ORDER BY total_latency DESC LIMIT 30;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint "Performance schema: TOP 15 class events by max latency";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select event_class, max_latency from wait_classes_global_by_latency ORDER BY max_latency DESC LIMIT 15;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
subheaderprint "Performance schema: TOP 30 events by max latency";
$nbL = 1;
for my $lQuery (
select_array(
'use sys;select events, max_latency from waits_global_by_latency ORDER BY max_latency DESC LIMIT 30;'
)
)
{
infoprint " +-- $nbL: $lQuery";
$nbL++;
}
infoprint "No information found or indicators desactivated."
if ( $nbL == 1 );
}
# Recommendations for Ariadb
sub mariadb_ariadb {
subheaderprint "AriaDB Metrics";
# AriaDB
unless ( defined $myvar{'have_aria'}
and $myvar{'have_aria'} eq "YES" )
{
infoprint "AriaDB is disabled.";
return;
}
infoprint "AriaDB is enabled.";
# Aria pagecache
if ( !defined( $mycalc{'total_aria_indexes'} ) and $doremote == 1 ) {
push( @generalrec,
"Unable to calculate Aria indexes on remote MySQL server < 5.0.0" );
}
elsif ( $mycalc{'total_aria_indexes'} =~ /^fail$/ ) {
badprint
"Cannot calculate Aria index size - re-run script as root user";
}
elsif ( $mycalc{'total_aria_indexes'} == "0" ) {
badprint
"None of your Aria tables are indexed - add indexes immediately";
}
else {
if (
$myvar{'aria_pagecache_buffer_size'} < $mycalc{'total_aria_indexes'}
&& $mycalc{'pct_aria_keys_from_mem'} < 95 )
{
badprint "Aria pagecache size / total Aria indexes: "
. hr_bytes( $myvar{'aria_pagecache_buffer_size'} ) . "/"
. hr_bytes( $mycalc{'total_aria_indexes'} ) . "";
push( @adjvars,
"aria_pagecache_buffer_size (> "
. hr_bytes( $mycalc{'total_aria_indexes'} )
. ")" );
}
else {
goodprint "Aria pagecache size / total Aria indexes: "
. hr_bytes( $myvar{'aria_pagecache_buffer_size'} ) . "/"
. hr_bytes( $mycalc{'total_aria_indexes'} ) . "";
}
if ( $mystat{'Aria_pagecache_read_requests'} > 0 ) {
if ( $mycalc{'pct_aria_keys_from_mem'} < 95 ) {
badprint
"Aria pagecache hit rate: $mycalc{'pct_aria_keys_from_mem'}% ("
. hr_num( $mystat{'Aria_pagecache_read_requests'} )
. " cached / "
. hr_num( $mystat{'Aria_pagecache_reads'} )
. " reads)";
}
else {
goodprint
"Aria pagecache hit rate: $mycalc{'pct_aria_keys_from_mem'}% ("
. hr_num( $mystat{'Aria_pagecache_read_requests'} )
. " cached / "
. hr_num( $mystat{'Aria_pagecache_reads'} )
. " reads)";
}
}
else {
# No queries have run that would use keys
}
}
}
# Recommendations for TokuDB
sub mariadb_tokudb {
subheaderprint "TokuDB Metrics";
# AriaDB
unless ( defined $myvar{'have_tokudb'}
&& $myvar{'have_tokudb'} eq "YES" )
{
infoprint "TokuDB is disabled.";
return;
}
infoprint "TokuDB is enabled.";
# All is to done here
}
# Recommendations for XtraDB
sub mariadb_xtradb {
subheaderprint "XtraDB Metrics";
# XtraDB
unless ( defined $myvar{'have_xtradb'}
&& $myvar{'have_xtradb'} eq "YES" )
{
infoprint "XtraDB is disabled.";
return;
}
infoprint "XtraDB is enabled.";
infoprint "Note that MariaDB 10.2 makes use of InnoDB, not XtraDB."
# All is to done here
}
# Recommendations for RocksDB
sub mariadb_rockdb {
subheaderprint "RocksDB Metrics";
# RocksDB
unless ( defined $myvar{'have_rocksdb'}
&& $myvar{'have_rocksdb'} eq "YES" )
{
infoprint "RocksDB is disabled.";
return;
}
infoprint "RocksDB is enabled.";
# All is to done here
}
# Recommendations for Spider
sub mariadb_spider {
subheaderprint "Spider Metrics";
# Spider
unless ( defined $myvar{'have_spider'}
&& $myvar{'have_spider'} eq "YES" )
{
infoprint "Spider is disabled.";
return;
}
infoprint "Spider is enabled.";
# All is to done here
}
# Recommendations for Connect
sub mariadb_connect {
subheaderprint "Connect Metrics";
# Connect
unless ( defined $myvar{'have_connect'}
&& $myvar{'have_connect'} eq "YES" )
{
infoprint "Connect is disabled.";
return;
}
infoprint "TokuDB is enabled.";
# All is to done here
}
# Perl trim function to remove whitespace from the start and end of the string
sub trim {
my $string = shift;
$string =~ s/^\s+//;
$string =~ s/\s+$//;
return $string;
}
sub get_wsrep_options {
return () unless defined $myvar{'wsrep_provider_options'};
my @galera_options = split /;/, $myvar{'wsrep_provider_options'};
my $wsrep_slave_threads = $myvar{'wsrep_slave_threads'};
push @galera_options, ' wsrep_slave_threads = ' . $wsrep_slave_threads;
@galera_options = remove_cr @galera_options;
@galera_options = remove_empty @galera_options;
debugprint Dumper( \@galera_options );
return @galera_options;
}
sub get_gcache_memory {
my $gCacheMem = hr_raw( get_wsrep_option('gcache.size') );
return 0 unless defined $gCacheMem and $gCacheMem ne '';
return $gCacheMem;
}
sub get_wsrep_option {
my $key = shift;
return '' unless defined $myvar{'wsrep_provider_options'};
my @galera_options = get_wsrep_options;
return '' unless scalar(@galera_options) > 0;
my @memValues = grep /\s*$key =/, @galera_options;
my $memValue = $memValues[0];
return 0 unless defined $memValue;
$memValue =~ s/.*=\s*(.+)$/$1/g;
return $memValue;
}
# Recommendations for Galera
sub mariadb_galera {
subheaderprint "Galera Metrics";
# Galera Cluster
unless ( defined $myvar{'have_galera'}
&& $myvar{'have_galera'} eq "YES" )
{
infoprint "Galera is disabled.";
return;
}
infoprint "Galera is enabled.";
debugprint "Galera variables:";
foreach my $gvar ( keys %myvar ) {
next unless $gvar =~ /^wsrep.*/;
next if $gvar eq 'wsrep_provider_options';
debugprint "\t" . trim($gvar) . " = " . $myvar{$gvar};
$result{'Galera'}{'variables'}{$gvar} = $myvar{$gvar};
}
debugprint "Galera wsrep provider Options:";
my @galera_options = get_wsrep_options;
$result{'Galera'}{'wsrep options'} = get_wsrep_options();
foreach my $gparam (@galera_options) {
debugprint "\t" . trim($gparam);
}
debugprint "Galera status:";
foreach my $gstatus ( keys %mystat ) {
next unless $gstatus =~ /^wsrep.*/;
debugprint "\t" . trim($gstatus) . " = " . $mystat{$gstatus};
$result{'Galera'}{'status'}{$gstatus} = $myvar{$gstatus};
}
infoprint "GCache is using "
. hr_bytes_rnd( get_wsrep_option('gcache.mem_size') );
my @primaryKeysNbTables = select_array(
"Select CONCAT(c.table_schema,CONCAT('.', c.table_name))
from information_schema.columns c
join information_schema.tables t using (TABLE_SCHEMA, TABLE_NAME)
where c.table_schema not in ('mysql', 'information_schema', 'performance_schema')
and t.table_type != 'VIEW'
group by c.table_schema,c.table_name
having sum(if(c.column_key in ('PRI','UNI'), 1,0)) = 0"
);
if ( get_wsrep_option('wsrep_slave_threads') > (cpu_cores) * 4
or get_wsrep_option('wsrep_slave_threads') < (cpu_cores) * 3 )
{
badprint
"wsrep_slave_threads is not equal to 2, 3 or 4 times number of CPU(s)";
push @adjvars, "wsrep_slave_threads= Nb of Core CPU * 4";
}
else {
goodprint
"wsrep_slave_threads is equal to 2, 3 or 4 times number of CPU(s)";
}
if ( get_wsrep_option('gcs.fc_limit') !=
get_wsrep_option('wsrep_slave_threads') * 5 )
{
badprint "gcs.fc_limit should be equal to 5 * wsrep_slave_threads";
push @adjvars, "gcs.fc_limit= wsrep_slave_threads * 5";
}
else {
goodprint "gcs.fc_limit should be equal to 5 * wsrep_slave_threads";
}
if ( get_wsrep_option('wsrep_slave_threads') > 1 ) {
infoprint
"wsrep parallel slave can cause frequent inconsistency crash.";
push @adjvars,
"Set wsrep_slave_threads to 1 in case of HA_ERR_FOUND_DUPP_KEY crash on slave";
# check options for parallel slave
if ( get_wsrep_option('wsrep_slave_FK_checks') eq "OFF" ) {
badprint "wsrep_slave_FK_checks is off with parallel slave";
push @adjvars,
"wsrep_slave_FK_checks should be ON when using parallel slave";
}
# wsrep_slave_UK_checks seems useless in MySQL source code
if ( $myvar{'innodb_autoinc_lock_mode'} != 2 ) {
badprint
"innodb_autoinc_lock_mode is incorrect with parallel slave";
push @adjvars,
"innodb_autoinc_lock_mode should be 2 when using parallel slave";
}
}
if ( get_wsrep_option('gcs.fc_limit') != $myvar{'wsrep_slave_threads'} * 5 )
{
badprint "gcs.fc_limit should be equal to 5 * wsrep_slave_threads";
push @adjvars, "gcs.fc_limit= wsrep_slave_threads * 5";
}
else {
goodprint "gcs.fc_limit is equal to 5 * wsrep_slave_threads";
}
if ( get_wsrep_option('gcs.fc_factor') != 0.8 ) {
badprint "gcs.fc_factor should be equal to 0.8";
push @adjvars, "gcs.fc_factor=0.8";
}
else {
goodprint "gcs.fc_factor is equal to 0.8";
}
if ( get_wsrep_option('wsrep_flow_control_paused') > 0.02 ) {
badprint "Fraction of time node pause flow control > 0.02";
}
else {
goodprint
"Flow control fraction seems to be OK (wsrep_flow_control_paused<=0.02)";
}
if ( scalar(@primaryKeysNbTables) > 0 ) {
badprint "Following table(s) don't have primary key:";
foreach my $badtable (@primaryKeysNbTables) {
badprint "\t$badtable";
push @{ $result{'Tables without PK'} }, $badtable;
}
}
else {
goodprint "All tables get a primary key";
}
my @nonInnoDBTables = select_array(
"select CONCAT(table_schema,CONCAT('.', table_name)) from information_schema.tables where ENGINE <> 'InnoDB' and table_schema not in ('mysql', 'performance_schema', 'information_schema')"
);
if ( scalar(@nonInnoDBTables) > 0 ) {
badprint "Following table(s) are not InnoDB table:";
push @generalrec,
"Ensure that all table(s) are InnoDB tables for Galera replication";
foreach my $badtable (@nonInnoDBTables) {
badprint "\t$badtable";
}
}
else {
goodprint "All tables are InnoDB tables";
}
if ( $myvar{'binlog_format'} ne 'ROW' ) {
badprint "Binlog format should be in ROW mode.";
push @adjvars, "binlog_format = ROW";
}
else {
goodprint "Binlog format is in ROW mode.";
}
if ( $myvar{'innodb_flush_log_at_trx_commit'} != 0 ) {
badprint "InnoDB flush log at each commit should be disabled.";
push @adjvars, "innodb_flush_log_at_trx_commit = 0";
}
else {
goodprint "InnoDB flush log at each commit is disabled for Galera.";
}
infoprint "Read consistency mode :" . $myvar{'wsrep_causal_reads'};
if ( defined( $myvar{'wsrep_cluster_name'} )
and $myvar{'wsrep_on'} eq "ON" )
{
goodprint "Galera WsREP is enabled.";
if ( defined( $myvar{'wsrep_cluster_address'} )
and trim("$myvar{'wsrep_cluster_address'}") ne "" )
{
goodprint "Galera Cluster address is defined: "
. $myvar{'wsrep_cluster_address'};
my @NodesTmp = split /,/, $myvar{'wsrep_cluster_address'};
my $nbNodes = @NodesTmp;
infoprint "There are $nbNodes nodes in wsrep_cluster_address";
my $nbNodesSize = trim( $mystat{'wsrep_cluster_size'} );
if ( $nbNodesSize == 3 or $nbNodesSize == 5 ) {
goodprint "There are $nbNodesSize nodes in wsrep_cluster_size.";
}
else {
badprint
"There are $nbNodesSize nodes in wsrep_cluster_size. Prefer 3 or 5 nodes architecture.";
push @generalrec, "Prefer 3 or 5 nodes architecture.";
}
# wsrep_cluster_address doesn't include garbd nodes
if ( $nbNodes > $nbNodesSize ) {
badprint
"All cluster nodes are not detected. wsrep_cluster_size less then node count in wsrep_cluster_address";
}
else {
goodprint "All cluster nodes detected.";
}
}
else {
badprint "Galera Cluster address is undefined";
push @adjvars,
"set up wsrep_cluster_address variable for Galera replication";
}
if ( defined( $myvar{'wsrep_cluster_name'} )
and trim( $myvar{'wsrep_cluster_name'} ) ne "" )
{
goodprint "Galera Cluster name is defined: "
. $myvar{'wsrep_cluster_name'};
}
else {
badprint "Galera Cluster name is undefined";
push @adjvars,
"set up wsrep_cluster_name variable for Galera replication";
}
if ( defined( $myvar{'wsrep_node_name'} )
and trim( $myvar{'wsrep_node_name'} ) ne "" )
{
goodprint "Galera Node name is defined: "
. $myvar{'wsrep_node_name'};
}
else {
badprint "Galera node name is undefined";
push @adjvars,
"set up wsrep_node_name variable for Galera replication";
}
if ( trim( $myvar{'wsrep_notify_cmd'} ) ne "" ) {
goodprint "Galera Notify command is defined.";
}
else {
badprint "Galera Notify command is not defined.";
push( @adjvars, "set up parameter wsrep_notify_cmd to be notify" );
}
if ( trim( $myvar{'wsrep_sst_method'} ) !~ "^xtrabackup.*" ) {
badprint "Galera SST method is not xtrabackup based.";
push( @adjvars,
"set up parameter wsrep_sst_method to xtrabackup based parameter"
);
}
else {
goodprint "SST Method is based on xtrabackup.";
}
if (
(
defined( $myvar{'wsrep_OSU_method'} )
&& trim( $myvar{'wsrep_OSU_method'} ) eq "TOI"
)
|| ( defined( $myvar{'wsrep_osu_method'} )
&& trim( $myvar{'wsrep_osu_method'} ) eq "TOI" )
)
{
goodprint "TOI is default mode for upgrade.";
}
else {
badprint "Schema upgrade are not replicated automatically";
push( @adjvars, "set up parameter wsrep_OSU_method to TOI" );
}
infoprint "Max WsRep message : "
. hr_bytes( $myvar{'wsrep_max_ws_size'} );
}
else {
badprint "Galera WsREP is disabled";
}
if ( defined( $mystat{'wsrep_connected'} )
and $mystat{'wsrep_connected'} eq "ON" )
{
goodprint "Node is connected";
}
else {
badprint "Node is disconnected";
}
if ( defined( $mystat{'wsrep_ready'} ) and $mystat{'wsrep_ready'} eq "ON" )
{
goodprint "Node is ready";
}
else {
badprint "Node is not ready";
}
infoprint "Cluster status :" . $mystat{'wsrep_cluster_status'};
if ( defined( $mystat{'wsrep_cluster_status'} )
and $mystat{'wsrep_cluster_status'} eq "Primary" )
{
goodprint "Galera cluster is consistent and ready for operations";
}
else {
badprint "Cluster is not consistent and ready";
}
if ( $mystat{'wsrep_local_state_uuid'} eq
$mystat{'wsrep_cluster_state_uuid'} )
{
goodprint "Node and whole cluster at the same level: "
. $mystat{'wsrep_cluster_state_uuid'};
}
else {
badprint "Node and whole cluster not the same level";
infoprint "Node state uuid: " . $mystat{'wsrep_local_state_uuid'};
infoprint "Cluster state uuid: " . $mystat{'wsrep_cluster_state_uuid'};
}
if ( $mystat{'wsrep_local_state_comment'} eq 'Synced' ) {
goodprint "Node is synced with whole cluster.";
}
else {
badprint "Node is not synced";
infoprint "Node State : " . $mystat{'wsrep_local_state_comment'};
}
if ( $mystat{'wsrep_local_cert_failures'} == 0 ) {
goodprint "There is no certification failures detected.";
}
else {
badprint "There is "
. $mystat{'wsrep_local_cert_failures'}
. " certification failure(s)detected.";
}
for my $key ( keys %mystat ) {
if ( $key =~ /wsrep_|galera/i ) {
debugprint "WSREP: $key = $mystat{$key}";
}
}
debugprint Dumper get_wsrep_options();
}
# Recommendations for InnoDB
sub mysql_innodb {
subheaderprint "InnoDB Metrics";
# InnoDB
unless ( defined $myvar{'have_innodb'}
&& $myvar{'have_innodb'} eq "YES"
&& defined $enginestats{'InnoDB'} )
{
infoprint "InnoDB is disabled.";
if ( mysql_version_ge( 5, 5 ) ) {
badprint
"InnoDB Storage engine is disabled. InnoDB is the default storage engine";
}
return;
}
infoprint "InnoDB is enabled.";
if ( $opt{buffers} ne 0 ) {
infoprint "InnoDB Buffers";
if ( defined $myvar{'innodb_buffer_pool_size'} ) {
infoprint " +-- InnoDB Buffer Pool: "
. hr_bytes( $myvar{'innodb_buffer_pool_size'} ) . "";
}
if ( defined $myvar{'innodb_buffer_pool_instances'} ) {
infoprint " +-- InnoDB Buffer Pool Instances: "
. $myvar{'innodb_buffer_pool_instances'} . "";
}
if ( defined $myvar{'innodb_buffer_pool_chunk_size'} ) {
infoprint " +-- InnoDB Buffer Pool Chunk Size: "
. hr_bytes( $myvar{'innodb_buffer_pool_chunk_size'} ) . "";
}
if ( defined $myvar{'innodb_additional_mem_pool_size'} ) {
infoprint " +-- InnoDB Additional Mem Pool: "
. hr_bytes( $myvar{'innodb_additional_mem_pool_size'} ) . "";
}
if ( defined $myvar{'innodb_log_file_size'} ) {
infoprint " +-- InnoDB Log File Size: "
. hr_bytes( $myvar{'innodb_log_file_size'} );
}
if ( defined $myvar{'innodb_log_files_in_group'} ) {
infoprint " +-- InnoDB Log File In Group: "
. $myvar{'innodb_log_files_in_group'};
}
if ( defined $myvar{'innodb_log_files_in_group'} ) {
infoprint " +-- InnoDB Total Log File Size: "
. hr_bytes( $myvar{'innodb_log_files_in_group'} *
$myvar{'innodb_log_file_size'} )
. "("
. $mycalc{'innodb_log_size_pct'}
. " % of buffer pool)";
}
if ( defined $myvar{'innodb_log_buffer_size'} ) {
infoprint " +-- InnoDB Log Buffer: "
. hr_bytes( $myvar{'innodb_log_buffer_size'} );
}
if ( defined $mystat{'Innodb_buffer_pool_pages_free'} ) {
infoprint " +-- InnoDB Log Buffer Free: "
. hr_bytes( $mystat{'Innodb_buffer_pool_pages_free'} ) . "";
}
if ( defined $mystat{'Innodb_buffer_pool_pages_total'} ) {
infoprint " +-- InnoDB Log Buffer Used: "
. hr_bytes( $mystat{'Innodb_buffer_pool_pages_total'} ) . "";
}
}
if ( defined $myvar{'innodb_thread_concurrency'} ) {
infoprint "InnoDB Thread Concurrency: "
. $myvar{'innodb_thread_concurrency'};
}
# InnoDB Buffer Pool Size
if ( $myvar{'innodb_file_per_table'} eq "ON" ) {
goodprint "InnoDB File per table is activated";
}
else {
badprint "InnoDB File per table is not activated";
push( @adjvars, "innodb_file_per_table=ON" );
}
# InnoDB Buffer Pool Size
if ( $myvar{'innodb_buffer_pool_size'} > $enginestats{'InnoDB'} ) {
goodprint "InnoDB buffer pool / data size: "
. hr_bytes( $myvar{'innodb_buffer_pool_size'} ) . "/"
. hr_bytes( $enginestats{'InnoDB'} ) . "";
}
else {
badprint "InnoDB buffer pool / data size: "
. hr_bytes( $myvar{'innodb_buffer_pool_size'} ) . "/"
. hr_bytes( $enginestats{'InnoDB'} ) . "";
push( @adjvars,
"innodb_buffer_pool_size (>= "
. hr_bytes_rnd( $enginestats{'InnoDB'} )
. ") if possible." );
}
if ( $mycalc{'innodb_log_size_pct'} < 20
or $mycalc{'innodb_log_size_pct'} > 30 )
{
badprint "Ratio InnoDB log file size / InnoDB Buffer pool size ("
. $mycalc{'innodb_log_size_pct'} . " %): "
. hr_bytes( $myvar{'innodb_log_file_size'} ) . " * "
. $myvar{'innodb_log_files_in_group'} . "/"
. hr_bytes( $myvar{'innodb_buffer_pool_size'} )
. " should be equal 25%";
push(
@adjvars,
"innodb_log_file_size should be (="
. hr_bytes_rnd(
$myvar{'innodb_buffer_pool_size'} /
$myvar{'innodb_log_files_in_group'} / 4
)
. ") if possible, so InnoDB total log files size equals to 25% of buffer pool size."
);
push( @generalrec,
"Read this before changing innodb_log_file_size and/or innodb_log_files_in_group: http://bit.ly/2wgkDvS"
);
}
else {
goodprint "Ratio InnoDB log file size / InnoDB Buffer pool size: "
. hr_bytes( $myvar{'innodb_log_file_size'} ) . " * "
. $myvar{'innodb_log_files_in_group'} . "/"
. hr_bytes( $myvar{'innodb_buffer_pool_size'} )
. " should be equal 25%";
}
# InnoDB Buffer Pool Instances (MySQL 5.6.6+)
if ( defined( $myvar{'innodb_buffer_pool_instances'} ) ) {
# Bad Value if > 64
if ( $myvar{'innodb_buffer_pool_instances'} > 64 ) {
badprint "InnoDB buffer pool instances: "
. $myvar{'innodb_buffer_pool_instances'} . "";
push( @adjvars, "innodb_buffer_pool_instances (<= 64)" );
}
# InnoDB Buffer Pool Size > 1Go
if ( $myvar{'innodb_buffer_pool_size'} > 1024 * 1024 * 1024 ) {
# InnoDB Buffer Pool Size / 1Go = InnoDB Buffer Pool Instances limited to 64 max.
# InnoDB Buffer Pool Size > 64Go
my $max_innodb_buffer_pool_instances =
int( $myvar{'innodb_buffer_pool_size'} / ( 1024 * 1024 * 1024 ) );
$max_innodb_buffer_pool_instances = 64
if ( $max_innodb_buffer_pool_instances > 64 );
if ( $myvar{'innodb_buffer_pool_instances'} !=
$max_innodb_buffer_pool_instances )
{
badprint "InnoDB buffer pool instances: "
. $myvar{'innodb_buffer_pool_instances'} . "";
push( @adjvars,
"innodb_buffer_pool_instances(="
. $max_innodb_buffer_pool_instances
. ")" );
}
else {
goodprint "InnoDB buffer pool instances: "
. $myvar{'innodb_buffer_pool_instances'} . "";
}
# InnoDB Buffer Pool Size < 1Go
}
else {
if ( $myvar{'innodb_buffer_pool_instances'} != 1 ) {
badprint
"InnoDB buffer pool <= 1G and Innodb_buffer_pool_instances(!=1).";
push( @adjvars, "innodb_buffer_pool_instances (=1)" );
}
else {
goodprint "InnoDB buffer pool instances: "
. $myvar{'innodb_buffer_pool_instances'} . "";
}
}
}
# InnoDB Used Buffer Pool Size vs CHUNK size
if ( !defined( $myvar{'innodb_buffer_pool_chunk_size'} ) ) {
infoprint
"InnoDB Buffer Pool Chunk Size not used or defined in your version";
}
else {
infoprint "Number of InnoDB Buffer Pool Chunk : "
. int( $myvar{'innodb_buffer_pool_size'} ) /
int( $myvar{'innodb_buffer_pool_chunk_size'} ) . " for "
. $myvar{'innodb_buffer_pool_instances'}
. " Buffer Pool Instance(s)";
if (
int( $myvar{'innodb_buffer_pool_size'} ) % (
int( $myvar{'innodb_buffer_pool_chunk_size'} ) *
int( $myvar{'innodb_buffer_pool_instances'} )
) eq 0
)
{
goodprint
"Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances";
}
else {
badprint
"Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances";
#push( @adjvars, "Adjust innodb_buffer_pool_instances, innodb_buffer_pool_chunk_size with innodb_buffer_pool_size" );
push( @adjvars,
"innodb_buffer_pool_size must always be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances"
);
}
}
# InnoDB Read efficency
if ( defined $mycalc{'pct_read_efficiency'}
&& $mycalc{'pct_read_efficiency'} < 90 )
{
badprint "InnoDB Read buffer efficiency: "
. $mycalc{'pct_read_efficiency'} . "% ("
. ( $mystat{'Innodb_buffer_pool_read_requests'} -
$mystat{'Innodb_buffer_pool_reads'} )
. " hits/ "
. $mystat{'Innodb_buffer_pool_read_requests'}
. " total)";
}
else {
goodprint "InnoDB Read buffer efficiency: "
. $mycalc{'pct_read_efficiency'} . "% ("
. ( $mystat{'Innodb_buffer_pool_read_requests'} -
$mystat{'Innodb_buffer_pool_reads'} )
. " hits/ "
. $mystat{'Innodb_buffer_pool_read_requests'}
. " total)";
}
# InnoDB Write efficiency
if ( defined $mycalc{'pct_write_efficiency'}
&& $mycalc{'pct_write_efficiency'} < 90 )
{
badprint "InnoDB Write Log efficiency: "
. abs( $mycalc{'pct_write_efficiency'} ) . "% ("
. abs( $mystat{'Innodb_log_write_requests'} -
$mystat{'Innodb_log_writes'} )
. " hits/ "
. $mystat{'Innodb_log_write_requests'}
. " total)";
}
else {
goodprint "InnoDB Write log efficiency: "
. $mycalc{'pct_write_efficiency'} . "% ("
. ( $mystat{'Innodb_log_write_requests'} -
$mystat{'Innodb_log_writes'} )
. " hits/ "
. $mystat{'Innodb_log_write_requests'}
. " total)";
}
# InnoDB Log Waits
if ( defined $mystat{'Innodb_log_waits'}
&& $mystat{'Innodb_log_waits'} > 0 )
{
badprint "InnoDB log waits: "
. percentage( $mystat{'Innodb_log_waits'},
$mystat{'Innodb_log_writes'} )
. "% ("
. $mystat{'Innodb_log_waits'}
. " waits / "
. $mystat{'Innodb_log_writes'}
. " writes)";
push( @adjvars,
"innodb_log_buffer_size (>= "
. hr_bytes_rnd( $myvar{'innodb_log_buffer_size'} )
. ")" );
}
else {
goodprint "InnoDB log waits: "
. percentage( $mystat{'Innodb_log_waits'},
$mystat{'Innodb_log_writes'} )
. "% ("
. $mystat{'Innodb_log_waits'}
. " waits / "
. $mystat{'Innodb_log_writes'}
. " writes)";
}
$result{'Calculations'} = {%mycalc};
}
# Recommendations for Database metrics
sub mysql_databases {
return if ( $opt{dbstat} == 0 );
subheaderprint "Database Metrics";
unless ( mysql_version_ge( 5, 5 ) ) {
infoprint
"Skip Database metrics from information schema missing in this version";
return;
}
my @dblist = select_array(
"SELECT DISTINCT TABLE_SCHEMA FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ( 'mysql', 'performance_schema', 'information_schema', 'sys' );"
);
infoprint "There is " . scalar(@dblist) . " Database(s).";
my @totaldbinfo = split /\s/,
select_one(
"SELECT SUM(TABLE_ROWS), SUM(DATA_LENGTH), SUM(INDEX_LENGTH) , SUM(DATA_LENGTH+INDEX_LENGTH), COUNT(TABLE_NAME),COUNT(DISTINCT(TABLE_COLLATION)),COUNT(DISTINCT(ENGINE)) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ( 'mysql', 'performance_schema', 'information_schema', 'sys' );"
);
infoprint "All User Databases:";
infoprint " +-- TABLE : "
. ( $totaldbinfo[4] eq 'NULL' ? 0 : $totaldbinfo[4] ) . "";
infoprint " +-- ROWS : "
. ( $totaldbinfo[0] eq 'NULL' ? 0 : $totaldbinfo[0] ) . "";
infoprint " +-- DATA : "
. hr_bytes( $totaldbinfo[1] ) . "("
. percentage( $totaldbinfo[1], $totaldbinfo[3] ) . "%)";
infoprint " +-- INDEX : "
. hr_bytes( $totaldbinfo[2] ) . "("
. percentage( $totaldbinfo[2], $totaldbinfo[3] ) . "%)";
infoprint " +-- SIZE : " . hr_bytes( $totaldbinfo[3] ) . "";
infoprint " +-- COLLA : "
. ( $totaldbinfo[5] eq 'NULL' ? 0 : $totaldbinfo[5] ) . " ("
. (
join ", ",
select_array(
"SELECT DISTINCT(TABLE_COLLATION) FROM information_schema.TABLES;")
) . ")";
infoprint " +-- ENGIN : "
. ( $totaldbinfo[6] eq 'NULL' ? 0 : $totaldbinfo[6] ) . " ("
. (
join ", ",
select_array("SELECT DISTINCT(ENGINE) FROM information_schema.TABLES;")
) . ")";
$result{'Databases'}{'All databases'}{'Rows'} =
( $totaldbinfo[0] eq 'NULL' ? 0 : $totaldbinfo[0] );
$result{'Databases'}{'All databases'}{'Data Size'} = $totaldbinfo[1];
$result{'Databases'}{'All databases'}{'Data Pct'} =
percentage( $totaldbinfo[1], $totaldbinfo[3] ) . "%";
$result{'Databases'}{'All databases'}{'Index Size'} = $totaldbinfo[2];
$result{'Databases'}{'All databases'}{'Index Pct'} =
percentage( $totaldbinfo[2], $totaldbinfo[3] ) . "%";
$result{'Databases'}{'All databases'}{'Total Size'} = $totaldbinfo[3];
print "\n" unless ( $opt{'silent'} or $opt{'json'} );
foreach (@dblist) {
my @dbinfo = split /\s/,
select_one(
"SELECT TABLE_SCHEMA, SUM(TABLE_ROWS), SUM(DATA_LENGTH), SUM(INDEX_LENGTH) , SUM(DATA_LENGTH+INDEX_LENGTH), COUNT(DISTINCT ENGINE),COUNT(TABLE_NAME),COUNT(DISTINCT(TABLE_COLLATION)),COUNT(DISTINCT(ENGINE)) FROM information_schema.TABLES WHERE TABLE_SCHEMA='$_' GROUP BY TABLE_SCHEMA ORDER BY TABLE_SCHEMA"
);
next unless defined $dbinfo[0];
infoprint "Database: " . $dbinfo[0] . "";
infoprint " +-- TABLE: "
. ( !defined( $dbinfo[6] ) or $dbinfo[6] eq 'NULL' ? 0 : $dbinfo[6] )
. "";
infoprint " +-- COLL : "
. ( $dbinfo[7] eq 'NULL' ? 0 : $dbinfo[7] ) . " ("
. (
join ", ",
select_array(
"SELECT DISTINCT(TABLE_COLLATION) FROM information_schema.TABLES WHERE TABLE_SCHEMA='$_';"
)
) . ")";
infoprint " +-- ROWS : "
. ( !defined( $dbinfo[1] ) or $dbinfo[1] eq 'NULL' ? 0 : $dbinfo[1] )
. "";
infoprint " +-- DATA : "
. hr_bytes( $dbinfo[2] ) . "("
. percentage( $dbinfo[2], $dbinfo[4] ) . "%)";
infoprint " +-- INDEX: "
. hr_bytes( $dbinfo[3] ) . "("
. percentage( $dbinfo[3], $dbinfo[4] ) . "%)";
infoprint " +-- TOTAL: " . hr_bytes( $dbinfo[4] ) . "";
infoprint " +-- ENGIN : "
. ( $dbinfo[8] eq 'NULL' ? 0 : $dbinfo[8] ) . " ("
. (
join ", ",
select_array(
"SELECT DISTINCT(ENGINE) FROM information_schema.TABLES WHERE TABLE_SCHEMA='$_'"
)
) . ")";
badprint "Index size is larger than data size for $dbinfo[0] \n"
if ( $dbinfo[2] ne 'NULL' )
and ( $dbinfo[3] ne 'NULL' )
and ( $dbinfo[2] < $dbinfo[3] );
badprint "There are " . $dbinfo[5] . " storage engines. Be careful. \n"
if $dbinfo[5] > 1;
$result{'Databases'}{ $dbinfo[0] }{'Rows'} = $dbinfo[1];
$result{'Databases'}{ $dbinfo[0] }{'Tables'} = $dbinfo[6];
$result{'Databases'}{ $dbinfo[0] }{'Collations'} = $dbinfo[7];
$result{'Databases'}{ $dbinfo[0] }{'Data Size'} = $dbinfo[2];
$result{'Databases'}{ $dbinfo[0] }{'Data Pct'} =
percentage( $dbinfo[2], $dbinfo[4] ) . "%";
$result{'Databases'}{ $dbinfo[0] }{'Index Size'} = $dbinfo[3];
$result{'Databases'}{ $dbinfo[0] }{'Index Pct'} =
percentage( $dbinfo[3], $dbinfo[4] ) . "%";
$result{'Databases'}{ $dbinfo[0] }{'Total Size'} = $dbinfo[4];
if ( $dbinfo[7] > 1 ) {
badprint $dbinfo[7]
. " different collations for database "
. $dbinfo[0];
push( @generalrec,
"Check all table collations are identical for all tables in "
. $dbinfo[0]
. " database." );
}
else {
goodprint $dbinfo[7]
. " collation for "
. $dbinfo[0]
. " database.";
}
if ( $dbinfo[8] > 1 ) {
badprint $dbinfo[8]
. " different engines for database "
. $dbinfo[0];
push( @generalrec,
"Check all table engines are identical for all tables in "
. $dbinfo[0]
. " database." );
}
else {
goodprint $dbinfo[8] . " engine for " . $dbinfo[0] . " database.";
}
my @distinct_column_charset = select_array(
"select DISTINCT(CHARACTER_SET_NAME) from information_schema.COLUMNS where CHARACTER_SET_NAME IS NOT NULL AND TABLE_SCHEMA ='$_'"
);
infoprint "Charsets for $dbinfo[0] database table column: "
. join( ', ', @distinct_column_charset );
if ( scalar(@distinct_column_charset) > 1 ) {
badprint $dbinfo[0]
. " table column(s) has several charsets defined for all text like column(s).";
push( @generalrec,
"Limit charset for column to one charset if possible for "
. $dbinfo[0]
. " database." );
}
else {
goodprint $dbinfo[0]
. " table column(s) has same charset defined for all text like column(s).";
}
my @distinct_column_collation = select_array(
"select DISTINCT(COLLATION_NAME) from information_schema.COLUMNS where COLLATION_NAME IS NOT NULL AND TABLE_SCHEMA ='$_'"
);
infoprint "Collations for $dbinfo[0] database table column: "
. join( ', ', @distinct_column_collation );
if ( scalar(@distinct_column_collation) > 1 ) {
badprint $dbinfo[0]
. " table column(s) has several collations defined for all text like column(s).";
push( @generalrec,
"Limit collations for column to one collation if possible for "
. $dbinfo[0]
. " database." );
}
else {
goodprint $dbinfo[0]
. " table column(s) has same collation defined for all text like column(s).";
}
}
}
# Recommendations for database columns
sub mysql_tables {
return if ( $opt{dbstat} == 0 );
subheaderprint "Table Column Metrics";
unless ( mysql_version_ge( 5, 5 ) ) {
infoprint
"Skip Database metrics from information schema missing in this version";
return;
}
my @dblist = select_array(
"SELECT DISTINCT TABLE_SCHEMA FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ( 'mysql', 'performance_schema', 'information_schema', 'sys' );"
);
foreach (@dblist) {
my $dbname = $_;
next unless defined $_;
infoprint "Database: " . $_ . "";
my @dbtable = select_array(
"SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='$dbname' AND TABLE_TYPE='BASE TABLE' ORDER BY TABLE_NAME"
);
foreach (@dbtable) {
my $tbname = $_;
infoprint " +-- TABLE: $tbname";
my @tbcol = select_array(
"SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='$dbname' AND TABLE_NAME='$tbname'"
);
foreach (@tbcol) {
my $ctype = select_one(
"SELECT COLUMN_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='$dbname' AND TABLE_NAME='$tbname' AND COLUMN_NAME='$_' "
);
my $isnull = select_one(
"SELECT IS_NULLABLE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='$dbname' AND TABLE_NAME='$tbname' AND COLUMN_NAME='$_' "
);
infoprint " +-- Column $tbname.$_:";
my $current_type =
uc($ctype) . ( $isnull eq 'NO' ? " NOT NULL" : "" );
my $optimal_type = select_str_g( "Optimal_fieldtype",
"SELECT $_ FROM $dbname.$tbname PROCEDURE ANALYSE(100000)"
);
if ( $current_type ne $optimal_type ) {
infoprint " Current Fieldtype: $current_type";
infoprint " Optimal Fieldtype: $optimal_type";
badprint
"Consider changing type for column $_ in table $dbname.$tbname";
push( @generalrec,
"ALTER TABLE $dbname.$tbname MODIFY $_ $optimal_type;"
);
}
else {
goodprint "$dbname.$tbname ($_) type: $current_type";
}
}
}
}
}
# Recommendations for Indexes metrics
sub mysql_indexes {
return if ( $opt{idxstat} == 0 );
subheaderprint "Indexes Metrics";
unless ( mysql_version_ge( 5, 5 ) ) {
infoprint
"Skip Index metrics from information schema missing in this version";
return;
}
# unless ( mysql_version_ge( 5, 6 ) ) {
# infoprint
#"Skip Index metrics from information schema due to erroneous information provided in this version";
# return;
# }
my $selIdxReq = <<'ENDSQL';
SELECT
CONCAT(CONCAT(t.TABLE_SCHEMA, '.'),t.TABLE_NAME) AS 'table'
, CONCAT(CONCAT(CONCAT(s.INDEX_NAME, '('),s.COLUMN_NAME), ')') AS 'index'
, s.SEQ_IN_INDEX AS 'seq'
, s2.max_columns AS 'maxcol'
, s.CARDINALITY AS 'card'
, t.TABLE_ROWS AS 'est_rows'
, INDEX_TYPE as type
, ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS 'sel'
FROM INFORMATION_SCHEMA.STATISTICS s
INNER JOIN INFORMATION_SCHEMA.TABLES t
ON s.TABLE_SCHEMA = t.TABLE_SCHEMA
AND s.TABLE_NAME = t.TABLE_NAME
INNER JOIN (
SELECT
TABLE_SCHEMA
, TABLE_NAME
, INDEX_NAME
, MAX(SEQ_IN_INDEX) AS max_columns
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
AND INDEX_TYPE <> 'FULLTEXT'
GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME
) AS s2
ON s.TABLE_SCHEMA = s2.TABLE_SCHEMA
AND s.TABLE_NAME = s2.TABLE_NAME
AND s.INDEX_NAME = s2.INDEX_NAME
WHERE t.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
AND t.TABLE_ROWS > 10
AND s.CARDINALITY IS NOT NULL
AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 8.00
ORDER BY sel
LIMIT 10;
ENDSQL
my @idxinfo = select_array($selIdxReq);
infoprint "Worst selectivity indexes:";
foreach (@idxinfo) {
debugprint "$_";
my @info = split /\s/;
infoprint "Index: " . $info[1] . "";
infoprint " +-- COLUMN : " . $info[0] . "";
infoprint " +-- NB SEQS : " . $info[2] . " sequence(s)";
infoprint " +-- NB COLS : " . $info[3] . " column(s)";
infoprint " +-- CARDINALITY : " . $info[4] . " distinct values";
infoprint " +-- NB ROWS : " . $info[5] . " rows";
infoprint " +-- TYPE : " . $info[6];
infoprint " +-- SELECTIVITY : " . $info[7] . "%";
$result{'Indexes'}{ $info[1] }{'Column'} = $info[0];
$result{'Indexes'}{ $info[1] }{'Sequence number'} = $info[2];
$result{'Indexes'}{ $info[1] }{'Number of column'} = $info[3];
$result{'Indexes'}{ $info[1] }{'Cardinality'} = $info[4];
$result{'Indexes'}{ $info[1] }{'Row number'} = $info[5];
$result{'Indexes'}{ $info[1] }{'Index Type'} = $info[6];
$result{'Indexes'}{ $info[1] }{'Selectivity'} = $info[7];
if ( $info[7] < 25 ) {
badprint "$info[1] has a low selectivity";
}
}
return
unless ( defined( $myvar{'performance_schema'} )
and $myvar{'performance_schema'} eq 'ON' );
$selIdxReq = <<'ENDSQL';
SELECT CONCAT(CONCAT(object_schema,'.'),object_name) AS 'table', index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
AND count_star =0
AND index_name <> 'PRIMARY'
AND object_schema != 'mysql'
ORDER BY count_star, object_schema, object_name;
ENDSQL
@idxinfo = select_array($selIdxReq);
infoprint "Unused indexes:";
push( @generalrec, "Remove unused indexes." ) if ( scalar(@idxinfo) > 0 );
foreach (@idxinfo) {
debugprint "$_";
my @info = split /\s/;
badprint "Index: $info[1] on $info[0] is not used.";
push @{ $result{'Indexes'}{'Unused Indexes'} },
$info[0] . "." . $info[1];
}
}
# Take the two recommendation arrays and display them at the end of the output
sub make_recommendations {
$result{'Recommendations'} = \@generalrec;
$result{'Adjust variables'} = \@adjvars;
subheaderprint "Recommendations";
if ( @generalrec > 0 ) {
prettyprint "General recommendations:";
foreach (@generalrec) { prettyprint " " . $_ . ""; }
}
if ( @adjvars > 0 ) {
prettyprint "Variables to adjust:";
if ( $mycalc{'pct_max_physical_memory'} > 90 ) {
prettyprint
" *** MySQL's maximum memory usage is dangerously high ***\n *** Add RAM before increasing MySQL buffer variables ***\n"
. " *** Please note this recommendation is not entirely accurate. \n The formula used to calculate max memory usage assumes all queries utilise\n all memory buffers simultaneously. When in reality it is very rare for a\n query to engage & utilise all memory buffers simultaneously.\n\n Formula also assumes all predefined max_connections are reached.\n You could have set max_connections = 1000 and in a whole year\n of usage never hit beyond 50 max_used_connections. So your real MySQL\n memory usage is only 1/20th of theorectical max memory usage reported.\n\n So real max memory usage will never reach this peak.\n So do not be too concerned with this warning.\n\n It is better to monitor your real MySQL max_used_connection and MySQL\n memory usage over time and adjust accordingly.\n\n You can use tools such as Cacti, Munin or mysqlmymonlite.sh at mysqlmymon.com to\n monitor your MySQL memory usage over time.\n\n MySQL performs optimally when its required amount of memory is met.\n Reducing and starving MySQL memory allocation to adhere to this\n artificial max memory warning - of which in reality will never be reached,\n will only reduce MySQL performance in many cases ***\n\n";
}
foreach (@adjvars) { prettyprint " " . $_ . ""; }
}
if ( @generalrec == 0 && @adjvars == 0 ) {
prettyprint "No additional performance recommendations are available.";
}
}
sub close_outputfile {
close($fh) if defined($fh);
}
sub headerprint {
prettyprint
" >> MySQLTuner $tunerversion - Major Hayden \n"
. " >> Bug reports, feature requests, and downloads at http://mysqltuner.com/\n"
. " >> Modified by George Liu (eva2000) at https://centminmod.com/\n"
. " >> Run with '--help' for additional options and output filtering";
}
sub string2file {
my $filename = shift;
my $content = shift;
open my $fh, q(>), $filename
or die
"Unable to open $filename in write mode. Please check permissions for this file or directory";
print $fh $content if defined($content);
close $fh;
debugprint $content if ( $opt{'debug'} );
}
sub file2array {
my $filename = shift;
debugprint "* reading $filename" if ( $opt{'debug'} );
my $fh;
open( $fh, q(<), "$filename" )
or die "Couldn't open $filename for reading: $!\n";
my @lines = <$fh>;
close($fh);
return @lines;
}
sub file2string {
return join( '', file2array(@_) );
}
my $templateModel;
if ( $opt{'template'} ne 0 ) {
$templateModel = file2string( $opt{'template'} );
}
else {
# DEFAULT REPORT TEMPLATE
$templateModel = <<'END_TEMPLATE';
MySQLTuner Report
Result output
{$data}
END_TEMPLATE
}
sub dump_result {
if ( $opt{'debug'} ) {
debugprint Dumper( \%result );
}
debugprint "HTML REPORT: $opt{'reportfile'}";
if ( $opt{'reportfile'} ne 0 ) {
eval { require Text::Template };
if ($@) {
badprint "Text::Template Module is needed.";
exit 1;
}
my $vars = { 'data' => Dumper( \%result ) };
my $template;
{
no warnings 'once';
$template = Text::Template->new(
TYPE => 'STRING',
PREPEND => q{;},
SOURCE => $templateModel
) or die "Couldn't construct template: $Text::Template::ERROR";
}
open my $fh, q(>), $opt{'reportfile'}
or die
"Unable to open $opt{'reportfile'} in write mode. please check permissions for this file or directory";
$template->fill_in( HASH => $vars, OUTPUT => $fh );
close $fh;
}
if ( $opt{'json'} ne 0 ) {
eval { require JSON };
if ($@) {
print "$bad JSON Module is needed.\n";
exit 1;
}
my $json = JSON->new->allow_nonref;
print $json->utf8(1)->pretty( ( $opt{'prettyjson'} ? 1 : 0 ) )
->encode( \%result );
}
}
sub which {
my $prog_name = shift;
my $path_string = shift;
my @path_array = split /:/, $ENV{'PATH'};
for my $path (@path_array) {
if ( -x "$path/$prog_name" ) {
return "$path/$prog_name";
}
}
return 0;
}
# ---------------------------------------------------------------------------
# BEGIN 'MAIN'
# ---------------------------------------------------------------------------
headerprint; # Header Print
validate_tuner_version; # Check last version
mysql_setup; # Gotta login first
os_setup; # Set up some OS variables
get_all_vars; # Toss variables/status into hashes
get_tuning_info; # Get information about the tuning connexion
validate_mysql_version; # Check current MySQL version
check_architecture; # Suggest 64-bit upgrade
system_recommendations; # avoid to many service on the same host
log_file_recommandations; # check log file content
check_storage_engines; # Show enabled storage engines
mysql_databases; # Show informations about databases
mysql_tables; # Show informations about table column
mysql_indexes; # Show informations about indexes
security_recommendations; # Display some security recommendations
cve_recommendations; # Display related CVE
calculations; # Calculate everything we need
mysql_stats; # Print the server stats
mysqsl_pfs; # Print Performance schema info
mariadb_threadpool; # Print MaraiDB ThreadPool stats
mysql_myisam; # Print MyISAM stats
mysql_innodb; # Print InnoDB stats
mariadb_ariadb; # Print MaraiDB AriaDB stats
mariadb_tokudb; # Print MariaDB Tokudb stats
mariadb_xtradb; # Print MariaDB XtraDB stats
mariadb_rockdb; # Print MariaDB RockDB stats
mariadb_spider; # Print MariaDB Spider stats
mariadb_connect; # Print MariaDB Connect stats
mariadb_galera; # Print MariaDB Galera Cluster stats
get_replication_status; # Print replication info
make_recommendations; # Make recommendations based on stats
dump_result; # Dump result if debug is on
close_outputfile; # Close reportfile if needed
# ---------------------------------------------------------------------------
# END 'MAIN'
# ---------------------------------------------------------------------------
1;
__END__
=pod
=encoding UTF-8
=head1 NAME
MySQLTuner 1.7.5 - MySQL High Performance Tuning Script
=head1 IMPORTANT USAGE GUIDELINES
To run the script with the default options, run the script without arguments
Allow MySQL server to run for at least 24-48 hours before trusting suggestions
Some routines may require root level privileges (script will provide warnings)
You must provide the remote server's total memory when connecting to other servers
=head1 CONNECTION AND AUTHENTIFICATION
--host Connect to a remote host to perform tests (default: localhost)
--socket Use a different socket for a local connection
--port Port to use for connection (default: 3306)
--user Username to use for authentication
--userenv Name of env variable which contains username to use for authentication
--pass Password to use for authentication
--passenv Name of env variable which contains password to use for authentication
--ssl-ca Path to public key
--mysqladmin Path to a custom mysqladmin executable
--mysqlcmd Path to a custom mysql executable
--defaults-file Path to a custom .my.cnf
=head1 PERFORMANCE AND REPORTING OPTIONS
--skipsize Don't enumerate tables and their types/sizes (default: on)
(Recommended for servers with many tables)
--skippassword Don't perform checks on user passwords(default: off)
--checkversion Check for updates to MySQLTuner (default: don't check)
--updateversion Check for updates to MySQLTuner and update when newer version is available (default: don't check)
--forcemem Amount of RAM installed in megabytes
--forceswap Amount of swap memory configured in megabytes
--passwordfile Path to a password file list(one password by line)
=head1 OUTPUT OPTIONS
--silent Don't output anything on screen
--nogood Remove OK responses
--nobad Remove negative/suggestion responses
--noinfo Remove informational responses
--debug Print debug information
--dbstat Print database information
--idxstat Print index information
--sysstat Print system information
--pfstat Print Performance schema
--bannedports Ports banned separated by comma(,)
--maxportallowed Number of ports opened allowed on this hosts
--cvefile CVE File for vulnerability checks
--nocolor Don't print output in color
--json Print result as JSON string
--buffers Print global and per-thread buffer values
--outputfile Path to a output txt file
--reportfile Path to a report txt file
--template Path to a template file
--verbose Prints out all options (default: no verbose)
=head1 PERLDOC
You can find documentation for this module with the perldoc command.
perldoc mysqltuner
=head2 INTERNALS
L
Internal documentation
=head1 AUTHORS
Major Hayden - major@mhtx.net
=head1 CONTRIBUTORS
=over 4
=item *
Matthew Montgomery
=item *
Paul Kehrer
=item *
Dave Burgess
=item *
Jonathan Hinds
=item *
Mike Jackson
=item *
Nils Breunese
=item *
Shawn Ashlee
=item *
Luuk Vosslamber
=item *
Ville Skytta
=item *
Trent Hornibrook
=item *
Jason Gill
=item *
Mark Imbriaco
=item *
Greg Eden
=item *
Aubin Galinotti
=item *
Giovanni Bechis
=item *
Bill Bradford
=item *
Ryan Novosielski
=item *
Michael Scheidell
=item *
Blair Christensen
=item *
Hans du Plooy
=item *
Victor Trac
=item *
Everett Barnes
=item *
Tom Krouper
=item *
Gary Barrueto
=item *
Simon Greenaway
=item *
Adam Stein
=item *
Isart Montane
=item *
Baptiste M.
=item *
Cole Turner
=item *
Major Hayden
=item *
Joe Ashcraft
=item *
Jean-Marie Renouard
=item *
Stephan GroBberndt
=item *
Christian Loos
=back
=head1 SUPPORT
Bug reports, feature requests, and downloads at http://mysqltuner.com/
Bug tracker can be found at https://github.com/major/MySQLTuner-perl/issues
Maintained by Major Hayden (major\@mhtx.net) - Licensed under GPL
=head1 SOURCE CODE
L
git clone https://github.com/major/MySQLTuner-perl.git
=head1 COPYRIGHT AND LICENSE
Copyright (C) 2006-2017 Major Hayden - major@mhtx.net
For the latest updates, please visit http://mysqltuner.com/
Git repository available at http://github.com/major/MySQLTuner-perl
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
See the GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program. If not, see .
=cut
# Local variables:
# indent-tabs-mode: t
# cperl-indent-level: 8
# perl-indent-level: 8
# End: