API tools faq
paste
Advertisement
Guest User

Teste lolol

a guest
Jul 26th, 2016
113
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 76.85 KB | None | 0 0
raw download clone embed print report
  1. #!/usr/bin/env perl
  2. # mysqltuner.pl - Version 1.5.0
  3. # High Performance MySQL Tuning Script
  4. # Copyright (C) 2006-2015 Major Hayden - major@mhtx.net
  5. #
  6. # For the latest updates, please visit http://mysqltuner.com/
  7. # Git repository available at http://github.com/major/MySQLTuner-perl
  8. #
  9. # This program is free software: you can redistribute it and/or modify
  10. # it under the terms of the GNU General Public License as published by
  11. # the Free Software Foundation, either version 3 of the License, or
  12. # (at your option) any later version.
  13. #
  14. # This program is distributed in the hope that it will be useful,
  15. # but WITHOUT ANY WARRANTY; without even the implied warranty of
  16. # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  17. # GNU General Public License for more details.
  18. #
  19. # You should have received a copy of the GNU General Public License
  20. # along with this program. If not, see <http://www.gnu.org/licenses/>.
  21. #
  22. # This project would not be possible without help from:
  23. # Matthew Montgomery Paul Kehrer Dave Burgess
  24. # Jonathan Hinds Mike Jackson Nils Breunese
  25. # Shawn Ashlee Luuk Vosslamber Ville Skytta
  26. # Trent Hornibrook Jason Gill Mark Imbriaco
  27. # Greg Eden Aubin Galinotti Giovanni Bechis
  28. # Bill Bradford Ryan Novosielski Michael Scheidell
  29. # Blair Christensen Hans du Plooy Victor Trac
  30. # Everett Barnes Tom Krouper Gary Barrueto
  31. # Simon Greenaway Adam Stein Isart Montane
  32. # Baptiste M. Cole Turner Major Hayden
  33. # Joe Ashcraft Jean-Marie Renouard
  34. #
  35. # Inspired by Matthew Montgomery's tuning-primer.sh script:
  36. # http://forge.mysql.com/projects/view.php?id=44
  37. #
  38. use strict;
  39. use warnings;
  40. use diagnostics;
  41. use File::Spec;
  42. use Getopt::Long;
  43. use File::Basename;
  44. use Cwd 'abs_path';
  45. use Data::Dumper qw/Dumper/;
  46. #use JSON;
  47. # Set up a few variables for use in the script
  48. my $tunerversion = "1.5.0";
  49. my (@adjvars, @generalrec);
  50.  
  51. # Set defaults
  52. my %opt = (
  53. "nobad" => 0,
  54. "nogood" => 0,
  55. "noinfo" => 0,
  56. "debug" => 0,
  57. "nocolor" => 0,
  58. "forcemem" => 0,
  59. "forceswap" => 0,
  60. "host" => 0,
  61. "socket" => 0,
  62. "port" => 0,
  63. "user" => 0,
  64. "pass" => 0,
  65. "skipsize" => 0,
  66. "checkversion" => 0,
  67. "buffers" => 0,
  68. "passwordfile" => 0,
  69. "reportfile" => 0,
  70. "dbstat" => 0,
  71. "idxstat" => 0,
  72. "skippassword" => 0,
  73. );
  74.  
  75.  
  76. # Gather the options from the command line
  77. GetOptions(\%opt,
  78. 'nobad',
  79. 'nogood',
  80. 'noinfo',
  81. 'debug',
  82. 'nocolor',
  83. 'forcemem=i',
  84. 'forceswap=i',
  85. 'host=s',
  86. 'socket=s',
  87. 'port=i',
  88. 'user=s',
  89. 'pass=s',
  90. 'skipsize',
  91. 'checkversion',
  92. 'mysqladmin=s',
  93. 'mysqlcmd=s',
  94. 'help',
  95. 'buffers',
  96. 'skippassword',
  97. 'passwordfile=s',
  98. 'reportfile=s',
  99. 'silent',
  100. 'dbstat',
  101. 'idxstat',
  102. );
  103.  
  104. if (defined $opt{'help'} && $opt{'help'} == 1) { usage(); }
  105.  
  106. sub usage {
  107. # Shown with --help option passed
  108. print " MySQLTuner $tunerversion - MySQL High Performance Tuning Script\n".
  109. " Bug reports, feature requests, and downloads at http://mysqltuner.com/\n".
  110. " Maintained by Major Hayden (major\@mhtx.net) - Licensed under GPL\n".
  111. "\n".
  112. " Important Usage Guidelines:\n".
  113. " To run the script with the default options, run the script without arguments\n".
  114. " Allow MySQL server to run for at least 24-48 hours before trusting suggestions\n".
  115. " Some routines may require root level privileges (script will provide warnings)\n".
  116. " You must provide the remote server's total memory when connecting to other servers\n".
  117. "\n".
  118. " Connection and Authentication\n".
  119. " --host <hostname> Connect to a remote host to perform tests (default: localhost)\n".
  120. " --socket <socket> Use a different socket for a local connection\n".
  121. " --port <port> Port to use for connection (default: 3306)\n".
  122. " --user <username> Username to use for authentication\n".
  123. " --pass <password> Password to use for authentication\n".
  124. " --mysqladmin <path> Path to a custom mysqladmin executable\n".
  125. " --mysqlcmd <path> Path to a custom mysql executable\n".
  126. "\n".
  127. " Performance and Reporting Options\n".
  128. " --skipsize Don't enumerate tables and their types/sizes (default: on)\n".
  129. " (Recommended for servers with many tables)\n".
  130. " --skippassword Don't perform checks on user passwords(default: off)\n".
  131. " --checkversion Check for updates to MySQLTuner (default: don't check)\n".
  132. " --forcemem <size> Amount of RAM installed in megabytes\n".
  133. " --forceswap <size> Amount of swap memory configured in megabytes\n".
  134. " --passwordfile <path>Path to a password file list(one password by line)\n".
  135. " --reportfile <path> Path to a report txt file\n".
  136. "\n".
  137. " Output Options:\n".
  138. " --nogood Remove OK responses\n".
  139. " --nobad Remove negative/suggestion responses\n".
  140. " --noinfo Remove informational responses\n".
  141. " --debug Print debug information\n".
  142. " --dbstat Print database information\n".
  143. " --idxstat Print index information\n".
  144. " --nocolor Don't print output in color\n".
  145. " --buffers Print global and per-thread buffer values\n";
  146. exit;
  147. }
  148.  
  149. my $devnull = File::Spec->devnull();
  150. my $basic_password_files=($opt{passwordfile} eq "0")? abs_path(dirname(__FILE__))."/basic_passwords.txt" : abs_path($opt{passwordfile}) ;
  151.  
  152. # for RPM distributions
  153. $basic_password_files="/usr/share/mysqltuner/basic_passwords.txt" unless -f "$basic_password_files";
  154.  
  155. #
  156. my $reportfile=undef;
  157. $reportfile=abs_path($opt{reportfile}) unless $opt{reportfile} eq "0";
  158.  
  159. my $fh=undef;
  160. open($fh, '>', $reportfile) or die("Fail opening $reportfile") if defined($reportfile);
  161. $opt{nocolor} = 1 if defined($reportfile);
  162.  
  163. # Setting up the colors for the print styles
  164. my $good = ($opt{nocolor} == 0)? "[\e[0;32mOK\e[0m]" : "[OK]" ;
  165. my $bad = ($opt{nocolor} == 0)? "[\e[0;31m!!\e[0m]" : "[!!]" ;
  166. my $info = ($opt{nocolor} == 0)? "[\e[0;34m--\e[0m]" : "[--]" ;
  167. my $deb = ($opt{nocolor} == 0)? "[\e[0;31mDG\e[0m]" : "[DG]" ;
  168.  
  169. # Super sturucture containing all informations
  170. my %result;
  171.  
  172. # Functions that handle the print styles
  173. sub prettyprint {
  174. print $_[0];
  175. print $fh $_[0] if defined($fh);
  176. }
  177. sub goodprint { prettyprint $good." ".$_[0] unless ($opt{nogood} == 1); }
  178. sub infoprint { prettyprint $info." ".$_[0] unless ($opt{noinfo} == 1); }
  179. sub badprint { prettyprint $bad. " ".$_[0] unless ($opt{nobad} == 1); }
  180. sub debugprint { prettyprint $deb. " ".$_[0] unless ($opt{debug} == 0); }
  181. sub redwrap { return ($opt{nocolor} == 0) ? "\e[0;31m".$_[0]."\e[0m" : $_[0] ; }
  182. sub greenwrap { return ($opt{nocolor} == 0) ? "\e[0;32m".$_[0]."\e[0m" : $_[0] ; }
  183.  
  184. # Calculates the parameter passed in bytes, and then rounds it to one decimal place
  185. sub hr_bytes {
  186. my $num = shift;
  187. if ($num >= (1024**3)) { #GB
  188. return sprintf("%.1f",($num/(1024**3)))."G";
  189. } elsif ($num >= (1024**2)) { #MB
  190. return sprintf("%.1f",($num/(1024**2)))."M";
  191. } elsif ($num >= 1024) { #KB
  192. return sprintf("%.1f",($num/1024))."K";
  193. } else {
  194. return $num."B";
  195. }
  196. }
  197.  
  198. # Calculates the parameter passed in bytes, and then rounds it to the nearest integer
  199. sub hr_bytes_rnd {
  200. my $num = shift;
  201. if ($num >= (1024**3)) { #GB
  202. return int(($num/(1024**3)))."G";
  203. } elsif ($num >= (1024**2)) { #MB
  204. return int(($num/(1024**2)))."M";
  205. } elsif ($num >= 1024) { #KB
  206. return int(($num/1024))."K";
  207. } else {
  208. return $num."B";
  209. }
  210. }
  211.  
  212. # Calculates the parameter passed to the nearest power of 1000, then rounds it to the nearest integer
  213. sub hr_num {
  214. my $num = shift;
  215. if ($num >= (1000**3)) { # Billions
  216. return int(($num/(1000**3)))."B";
  217. } elsif ($num >= (1000**2)) { # Millions
  218. return int(($num/(1000**2)))."M";
  219. } elsif ($num >= 1000) { # Thousands
  220. return int(($num/1000))."K";
  221. } else {
  222. return $num;
  223. }
  224. }
  225.  
  226. # Calculate Percentage
  227. sub percentage{
  228. my $value=shift;
  229. my $total=shift;
  230. $total=0 unless defined $total;
  231. return 100,00 if $total == 0;
  232. return sprintf("%.2f", ($value*100/$total) );
  233. }
  234.  
  235. # Calculates uptime to display in a more attractive form
  236. sub pretty_uptime {
  237. my $uptime = shift;
  238. my $seconds = $uptime % 60;
  239. my $minutes = int(($uptime % 3600) / 60);
  240. my $hours = int(($uptime % 86400) / (3600));
  241. my $days = int($uptime / (86400));
  242. my $uptimestring;
  243. if ($days > 0) {
  244. $uptimestring = "${days}d ${hours}h ${minutes}m ${seconds}s";
  245. } elsif ($hours > 0) {
  246. $uptimestring = "${hours}h ${minutes}m ${seconds}s";
  247. } elsif ($minutes > 0) {
  248. $uptimestring = "${minutes}m ${seconds}s";
  249. } else {
  250. $uptimestring = "${seconds}s";
  251. }
  252. return $uptimestring;
  253. }
  254.  
  255. # Retrieves the memory installed on this machine
  256. my ($physical_memory,$swap_memory,$duflags);
  257. sub os_setup {
  258. sub memerror {
  259. badprint "Unable to determine total memory/swap; use '--forcemem' and '--forceswap'\n";
  260. exit;
  261. }
  262. my $os = `uname`;
  263. $duflags = ($os =~ /Linux/) ? '-b' : '';
  264. if ($opt{'forcemem'} > 0) {
  265. $physical_memory = $opt{'forcemem'} * 1048576;
  266. infoprint "Assuming $opt{'forcemem'} MB of physical memory\n";
  267. if ($opt{'forceswap'} > 0) {
  268. $swap_memory = $opt{'forceswap'} * 1048576;
  269. infoprint "Assuming $opt{'forceswap'} MB of swap space\n";
  270. } else {
  271. $swap_memory = 0;
  272. badprint "Assuming 0 MB of swap space (use --forceswap to specify)\n";
  273. }
  274. } else {
  275. if ($os =~ /Linux/) {
  276. $physical_memory = `LANG=en free -b | grep Mem | awk '{print \$2}'` or memerror;
  277. $swap_memory = `LANG=en free -b | grep Swap | awk '{print \$2}'` or memerror;
  278. } elsif ($os =~ /Darwin/) {
  279. $physical_memory = `sysctl -n hw.memsize` or memerror;
  280. $swap_memory = `sysctl -n vm.swapusage | awk '{print \$3}' | sed 's/\..*\$//'` or memerror;
  281. } elsif ($os =~ /NetBSD|OpenBSD|FreeBSD/) {
  282. $physical_memory = `sysctl -n hw.physmem` or memerror;
  283. if ($physical_memory < 0) {
  284. $physical_memory = `sysctl -n hw.physmem64` or memerror;
  285. }
  286. $swap_memory = `swapctl -l | grep '^/' | awk '{ s+= \$2 } END { print s }'` or memerror;
  287. } elsif ($os =~ /BSD/) {
  288. $physical_memory = `sysctl -n hw.realmem` or memerror;
  289. $swap_memory = `swapinfo | grep '^/' | awk '{ s+= \$2 } END { print s }'`;
  290. } elsif ($os =~ /SunOS/) {
  291. $physical_memory = `/usr/sbin/prtconf | grep Memory | cut -f 3 -d ' '` or memerror;
  292. chomp($physical_memory);
  293. $physical_memory = $physical_memory*1024*1024;
  294. } elsif ($os =~ /AIX/) {
  295. $physical_memory = `lsattr -El sys0 | grep realmem | awk '{print \$2}'` or memerror;
  296. chomp($physical_memory);
  297. $physical_memory = $physical_memory*1024;
  298. $swap_memory = `lsps -as | awk -F"(MB| +)" '/MB /{print \$2}'` or memerror;
  299. chomp($swap_memory);
  300. $swap_memory = $swap_memory*1024*1024;
  301. }
  302. }
  303. chomp($physical_memory);
  304. chomp($swap_memory);
  305. chomp($os);
  306. $result{'OS'}{'OS Type'}=$os;
  307. $result{'OS'}{'Physical Memory'}{'bytes'}=$physical_memory;
  308. $result{'OS'}{'Physical Memory'}{'pretty'}=hr_bytes($physical_memory);
  309. $result{'OS'}{'Swap Memory'}{'bytes'}=$swap_memory;
  310. $result{'OS'}{'Swap Memory'}{'pretty'}=hr_bytes($swap_memory);
  311.  
  312. }
  313.  
  314. # Checks to see if a MySQL login is possible
  315. my ($mysqllogin,$doremote,$remotestring,$mysqlcmd,$mysqladmincmd);
  316. sub mysql_setup {
  317. $doremote = 0;
  318. $remotestring = '';
  319. if ($opt{mysqladmin}) {
  320. $mysqladmincmd = $opt{mysqladmin};
  321. } else {
  322. $mysqladmincmd = `which mysqladmin`;
  323. }
  324. chomp($mysqladmincmd);
  325. if (! -e $mysqladmincmd && $opt{mysqladmin}) {
  326. badprint "Unable to find the mysqladmin command you specified: ".$mysqladmincmd."\n";
  327. exit;
  328. } elsif (! -e $mysqladmincmd) {
  329. badprint "Couldn't find mysqladmin in your \$PATH. Is MySQL installed?\n";
  330. exit;
  331. }
  332. if ($opt{mysqlcmd}) {
  333. $mysqlcmd = $opt{mysqlcmd};
  334. } else {
  335. $mysqlcmd = `which mysql`;
  336. }
  337. chomp($mysqlcmd);
  338. if (! -e $mysqlcmd && $opt{mysqlcmd}) {
  339. badprint "Unable to find the mysql command you specified: ".$mysqlcmd."\n";
  340. exit;
  341. } elsif (! -e $mysqlcmd) {
  342. badprint "Couldn't find mysql in your \$PATH. Is MySQL installed?\n";
  343. exit;
  344. }
  345.  
  346. # Are we being asked to connect via a socket?
  347. if ($opt{socket} ne 0) {
  348. $remotestring = " -S $opt{socket}";
  349. }
  350. # Are we being asked to connect to a remote server?
  351. if ($opt{host} ne 0) {
  352. chomp($opt{host});
  353. $opt{port} = ($opt{port} eq 0)? 3306 : $opt{port} ;
  354. # If we're doing a remote connection, but forcemem wasn't specified, we need to exit
  355. if ($opt{'forcemem'} eq 0) {
  356. badprint "The --forcemem option is required for remote connections\n";
  357. exit;
  358. }
  359. infoprint "Performing tests on $opt{host}:$opt{port}\n";
  360. $remotestring = " -h $opt{host} -P $opt{port}";
  361. $doremote = 1;
  362. }
  363. # Did we already get a username and password passed on the command line?
  364. if ($opt{user} ne 0 and $opt{pass} ne 0) {
  365. $mysqllogin = "-u $opt{user} -p'$opt{pass}'".$remotestring;
  366. my $loginstatus = `$mysqladmincmd ping $mysqllogin 2>&1`;
  367. if ($loginstatus =~ /mysqld is alive/) {
  368. goodprint "Logged in using credentials passed on the command line\n";
  369. return 1;
  370. } else {
  371. badprint "Attempted to use login credentials, but they were invalid\n";
  372. exit 0;
  373. }
  374. }
  375. my $svcprop = `which svcprop 2>/dev/null`;
  376. if (substr($svcprop, 0, 1) =~ "/") {
  377. # We are on solaris
  378. (my $mysql_login = `svcprop -p quickbackup/username svc:/network/mysql-quickbackup:default`) =~ s/\s+$//;
  379. (my $mysql_pass = `svcprop -p quickbackup/password svc:/network/mysql-quickbackup:default`) =~ s/\s+$//;
  380. if ( substr($mysql_login, 0, 7) ne "svcprop" ) {
  381. # mysql-quickbackup is installed
  382. $mysqllogin = "-u $mysql_login -p$mysql_pass";
  383. my $loginstatus = `mysqladmin $mysqllogin ping 2>&1`;
  384. if ($loginstatus =~ /mysqld is alive/) {
  385. goodprint "Logged in using credentials from mysql-quickbackup.\n";
  386. return 1;
  387. } else {
  388. badprint "Attempted to use login credentials from mysql-quickbackup, but they failed.\n";
  389. exit 0;
  390. }
  391. }
  392. } elsif ( -r "/etc/psa/.psa.shadow" and $doremote == 0 ) {
  393. # It's a Plesk box, use the available credentials
  394. $mysqllogin = "-u admin -p`cat /etc/psa/.psa.shadow`";
  395. my $loginstatus = `$mysqladmincmd ping $mysqllogin 2>&1`;
  396. unless ($loginstatus =~ /mysqld is alive/) {
  397. badprint "Attempted to use login credentials from Plesk, but they failed.\n";
  398. exit 0;
  399. }
  400. } elsif ( -r "/usr/local/directadmin/conf/mysql.conf" and $doremote == 0 ){
  401. # It's a DirectAdmin box, use the available credentials
  402. my $mysqluser=`cat /usr/local/directadmin/conf/mysql.conf | egrep '^user=.*'`;
  403. my $mysqlpass=`cat /usr/local/directadmin/conf/mysql.conf | egrep '^passwd=.*'`;
  404.  
  405. $mysqluser =~ s/user=//;
  406. $mysqluser =~ s/[\r\n]//;
  407. $mysqlpass =~ s/passwd=//;
  408. $mysqlpass =~ s/[\r\n]//;
  409.  
  410. $mysqllogin = "-u $mysqluser -p$mysqlpass";
  411.  
  412. my $loginstatus = `mysqladmin ping $mysqllogin 2>&1`;
  413. unless ($loginstatus =~ /mysqld is alive/) {
  414. badprint "Attempted to use login credentials from DirectAdmin, but they failed.\n";
  415. exit 0;
  416. }
  417. } elsif ( -r "/etc/mysql/debian.cnf" and $doremote == 0 ){
  418. # We have a debian maintenance account, use it
  419. $mysqllogin = "--defaults-file=/etc/mysql/debian.cnf";
  420. my $loginstatus = `$mysqladmincmd $mysqllogin ping 2>&1`;
  421. if ($loginstatus =~ /mysqld is alive/) {
  422. goodprint "Logged in using credentials from debian maintenance account.\n";
  423. return 1;
  424. } else {
  425. badprint "Attempted to use login credentials from debian maintenance account, but they failed.\n";
  426. exit 0;
  427. }
  428. } else {
  429. # It's not Plesk or debian, we should try a login
  430. debugprint "$mysqladmincmd $remotestring ping 2>&1";
  431. my $loginstatus = `$mysqladmincmd $remotestring ping 2>&1`;
  432. if ($loginstatus =~ /mysqld is alive/) {
  433. # Login went just fine
  434. $mysqllogin = " $remotestring ";
  435. # Did this go well because of a .my.cnf file or is there no password set?
  436. my $userpath = `printenv HOME`;
  437. if (length($userpath) > 0) {
  438. chomp($userpath);
  439. }
  440. unless ( -e "${userpath}/.my.cnf" or -e "${userpath}/.mylogin.cnf" ) {
  441. badprint "Successfully authenticated with no password - SECURITY RISK!\n";
  442. }
  443. return 1;
  444. } else {
  445. print STDERR "Please enter your MySQL administrative login: ";
  446. my $name = <>;
  447. print STDERR "Please enter your MySQL administrative password: ";
  448. system("stty -echo >$devnull 2>&1");
  449. my $password = <>;
  450. system("stty echo >$devnull 2>&1");
  451. chomp($password);
  452. chomp($name);
  453. $mysqllogin = "-u $name";
  454. if (length($password) > 0) {
  455. $mysqllogin .= " -p'$password'";
  456. }
  457. $mysqllogin .= $remotestring;
  458. my $loginstatus = `$mysqladmincmd ping $mysqllogin 2>&1`;
  459. if ($loginstatus =~ /mysqld is alive/) {
  460. print STDERR "\n";
  461. if (! length($password)) {
  462. # Did this go well because of a .my.cnf file or is there no password set?
  463. my $userpath = `ls -d ~`;
  464. chomp($userpath);
  465. unless ( -e "$userpath/.my.cnf" ) {
  466. badprint "Successfully authenticated with no password - SECURITY RISK!\n";
  467. }
  468. }
  469. return 1;
  470. } else {
  471. print "\n".$bad." Attempted to use login credentials, but they were invalid.\n";
  472. exit 0;
  473. }
  474. exit 0;
  475. }
  476. }
  477. }
  478.  
  479. # MySQL Request Array
  480. sub select_array {
  481. my $req=shift;
  482. debugprint "PERFORM: $req \n";
  483. my @result=`$mysqlcmd $mysqllogin -Bse "$req"`;
  484. chomp (@result);
  485. return @result;
  486. }
  487.  
  488. # MySQL Request one
  489. sub select_one {
  490. my $req=shift;
  491. debugprint "PERFORM: $req \n";
  492. my $result=`$mysqlcmd $mysqllogin -Bse "$req"`;
  493. chomp ($result);
  494. return $result;
  495. }
  496.  
  497. sub get_tuning_info {
  498. my @infoconn = select_array "\\s";
  499. my ($tkey, $tval);
  500. @infoconn = grep {!/Threads:/ and !/Connection id:/ and !/pager:/ and !/Using/ } @infoconn;
  501. foreach my $line (@infoconn) {
  502. if ($line =~ /\s*(.*):\s*(.*)/) {
  503. debugprint "$1 => $2\n";
  504. $tkey=$1;
  505. $tval=$2;
  506. chomp($tkey);
  507. chomp($tval);
  508. $result{'MySQL Client'}{$tkey} = $tval;
  509. }
  510. }
  511. $result{'MySQL Client'}{'Client Path'}=$mysqlcmd;
  512. $result{'MySQL Client'}{'Admin Path'}=$mysqladmincmd;
  513. $result{'MySQL Client'}{'Authentication Info'}=$mysqllogin;
  514.  
  515. }
  516. # Populates all of the variable and status hashes
  517. my (%mystat,%myvar,$dummyselect,%myrepl, %myslaves);
  518. sub get_all_vars {
  519. # We need to initiate at least one query so that our data is useable
  520. $dummyselect = select_one "SELECT VERSION()";
  521. debugprint "VERSION: ".$dummyselect."\n";
  522. $result{'MySQL Client'}{'Version'}=$dummyselect;
  523. my @mysqlvarlist = select_array "SHOW /*!50000 GLOBAL */ VARIABLES";
  524. foreach my $line (@mysqlvarlist) {
  525. $line =~ /([a-zA-Z_]*)\s*(.*)/;
  526. $myvar{$1} = $2;
  527. $result{'Variables'}{$1}=$2;
  528. debugprint "V: $1 = $2\n";
  529. }
  530.  
  531. my @mysqlstatlist = select_array "SHOW /*!50000 GLOBAL */ STATUS";
  532. foreach my $line (@mysqlstatlist) {
  533. $line =~ /([a-zA-Z_]*)\s*(.*)/;
  534. $mystat{$1} = $2;
  535. $result{'Status'}{$1}=$2;
  536. debugprint "S: $1 = $2\n";
  537. }
  538. # Workaround for MySQL bug #59393 wrt. ignore-builtin-innodb
  539. if (($myvar{'ignore_builtin_innodb'} || "") eq "ON") {
  540. $myvar{'have_innodb'} = "NO";
  541. }
  542. # have_* for engines is deprecated and will be removed in MySQL 5.6;
  543. # check SHOW ENGINES and set corresponding old style variables.
  544. # Also works around MySQL bug #59393 wrt. skip-innodb
  545. my @mysqlenginelist = select_array "SHOW ENGINES";
  546. foreach my $line (@mysqlenginelist) {
  547. if ($line =~ /^([a-zA-Z_]+)\s+(\S+)/) {
  548. my $engine = lc($1);
  549.  
  550. if ($engine eq "federated" || $engine eq "blackhole") {
  551. $engine .= "_engine";
  552. } elsif ($engine eq "berkeleydb") {
  553. $engine = "bdb";
  554. }
  555. my $val = ($2 eq "DEFAULT") ? "YES" : $2;
  556. $myvar{"have_$engine"} = $val;
  557. $result{'Storage Engines'}{$engine}=$2;
  558. }
  559. }
  560.  
  561. my @mysqlslave = select_array "SHOW SLAVE STATUS\\G";
  562.  
  563. foreach my $line (@mysqlslave) {
  564. if ($line =~ /\s*(.*):\s*(.*)/) {
  565. debugprint "$1 => $2\n";
  566. $myrepl{"$1"} = $2;
  567. $result{'Replication'}{'Status'}{$1}=$2;
  568. }
  569. }
  570. #print Dumper(%myrepl);
  571. #exit 0;
  572. my @mysqlslaves = select_array "SHOW SLAVE HOSTS";
  573. my @lineitems=();
  574. foreach my $line (@mysqlslaves) {
  575. debugprint "L: $line \n";
  576. @lineitems=split /\s+/, $line;
  577. $myslaves{$lineitems[0]}=$line;
  578. $result{'Replication'}{'Slaves'}{$lineitems[0]}=$lineitems[4];
  579. }
  580. }
  581.  
  582. sub get_basic_passwords {
  583. my $file=shift;
  584. open (FH, "< $file") or die "Can't open $file for read: $!";
  585. my @lines = <FH>;
  586. close FH or die "Cannot close $file: $!";
  587. return @lines
  588. }
  589.  
  590. sub security_recommendations {
  591. prettyprint "\n-------- Security Recommendations -------------------------------------------\n";
  592. if ($opt{skippassword} eq 1) {
  593. infoprint "Skipped due to --skippassword option\n";
  594. return;
  595. }
  596. # Looking for Anonymous users
  597. my @mysqlstatlist = select_array "SELECT CONCAT(user, '\@', host) FROM mysql.user WHERE TRIM(USER) = '' OR USER IS NULL";
  598. if (@mysqlstatlist) {
  599. foreach my $line (sort @mysqlstatlist) {
  600. chomp($line);
  601. badprint "User '".$line."' is an anonymous account.\n";
  602. }
  603. push(@generalrec, "Remove Anonymous User account - there is ".scalar(@mysqlstatlist). " Anonymous account.");
  604. } else {
  605. goodprint "There is no anonymous account in all database users\n";
  606. }
  607.  
  608. # Looking for Empty Password
  609. @mysqlstatlist = select_array "SELECT CONCAT(user, '\@', host) FROM mysql.user WHERE password = '' OR password IS NULL";
  610. if (@mysqlstatlist) {
  611. foreach my $line (sort @mysqlstatlist) {
  612. chomp($line);
  613. badprint "User '".$line."' has no password set.\n";
  614. }
  615. push(@generalrec, "Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'\@'SpecificDNSorIp' = PASSWORD('secure_password'); )");
  616. } else {
  617. goodprint "All database users have passwords assigned\n";
  618. }
  619.  
  620. # Looking for User with user/ uppercase /capitalise user as password
  621. @mysqlstatlist = select_array "SELECT CONCAT(user, '\@', host) FROM mysql.user WHERE CAST(password as Binary) = PASSWORD(user) OR CAST(password as Binary) = PASSWORD(UPPER(user)) OR CAST(password as Binary) = PASSWORD(UPPER(LEFT(User, 1)) + SUBSTRING(User, 2, LENGTH(User)))";
  622. if (@mysqlstatlist) {
  623. foreach my $line (sort @mysqlstatlist) {
  624. chomp($line);
  625. badprint "User '".$line."' has user name as password.\n";
  626. }
  627. push(@generalrec, "Set up a Secure Password for user\@host ( SET PASSWORD FOR 'user'\@'SpecificDNSorIp' = PASSWORD('secure_password'); )");
  628. }
  629.  
  630. @mysqlstatlist = select_array "SELECT CONCAT(user, '\@', host) FROM mysql.user WHERE HOST='%'";
  631. if (@mysqlstatlist) {
  632. foreach my $line (sort @mysqlstatlist) {
  633. chomp($line);
  634. badprint "User '".$line."' hasn't specific host restriction.\n";
  635. }
  636. push(@generalrec, "Restrict Host for user\@% to user\@SpecificDNSorIp");
  637. }
  638.  
  639. unless (-f $basic_password_files) {
  640. badprint "There is not basic password file list !\n";
  641. return;
  642. }
  643.  
  644. my @passwords=get_basic_passwords $basic_password_files;
  645. infoprint "There is ". scalar(@passwords). " basic passwords in the list.\n";
  646. my $nbins=0;
  647. my $passreq;
  648. if (@passwords) {
  649. foreach my $pass (@passwords) {
  650. $pass=~s/\s//g;
  651. chomp($pass);
  652. # Looking for User with user/ uppercase /capitalise weak password
  653. @mysqlstatlist = select_array "SELECT CONCAT(user, '\@', host) FROM mysql.user WHERE password = PASSWORD('".$pass."') OR password = PASSWORD(UPPER('".$pass."')) OR password = PASSWORD(UPPER(LEFT('".$pass."', 1)) + SUBSTRING('".$pass."', 2, LENGTH('".$pass."')))";
  654. debugprint "There is ".scalar (@mysqlstatlist). " items.\n";
  655. if (@mysqlstatlist) {
  656. foreach my $line (@mysqlstatlist) {
  657. chomp($line);
  658. badprint "User '".$line."' is using weak pasword: $pass in a lower, upper or capitalize derivated version.\n";
  659. $nbins++;
  660. }
  661. }
  662. }
  663. }
  664. if ($nbins>0) {
  665. push(@generalrec, $nbins. " user(s) used basic or weaked password.");
  666. }
  667. }
  668.  
  669. sub get_replication_status {
  670. prettyprint "\n-------- Replication Metrics -------------------------------------------------\n";
  671.  
  672. if( scalar(keys %myslaves)==0 ) {
  673. infoprint "No replication slave(s) for this server.\n";
  674. } else {
  675. infoprint "This server is acting as master for ".scalar(keys %myslaves)." server(s).\n";
  676. }
  677.  
  678. if( scalar(keys %myrepl)==0 and scalar(keys %myslaves)==0 ) {
  679. infoprint "This is a standalone server..\n";
  680. return;
  681. }
  682. if( scalar(keys %myrepl)==0 ) {
  683. infoprint "No replication setup for this server.\n";
  684. }
  685. my ($io_running) = $myrepl{'Slave_IO_Running'};
  686. debugprint "IO RUNNING: $io_running \n";
  687. my ($sql_running) = $myrepl{'Slave_SQL_Running'};
  688. debugprint "SQL RUNNING: $sql_running \n";
  689. my ($seconds_behind_master) = $myrepl{'Seconds_Behind_Master'};
  690. debugprint "SECONDS : $seconds_behind_master \n";
  691.  
  692. if (defined($io_running) and ($io_running !~ '/yes/i' or $sql_running !~ '/yes/i' )) {
  693. badprint "This replication slave is not running but seems to be configurated.";
  694. }
  695. if (defined($io_running ) && $io_running =~ '/yes/i' && $sql_running =~ '/yes/i') {
  696. if ($myvar{'read_only'} eq 'OFF') {
  697. badprint "This replication slave is running with the read_only option disabled.";
  698. } else {
  699. goodprint "This replication slave is running with the read_only option enabled.";
  700. }
  701. if ($seconds_behind_master>0) {
  702. badprint "This replication slave is lagging and slave has $seconds_behind_master second(s) behind master host.";
  703. } else {
  704. goodprint "This replication slave is uptodate with master.";
  705. }
  706. }
  707. }
  708.  
  709. # Checks for supported or EOL'ed MySQL versions
  710. my ($mysqlvermajor,$mysqlverminor, $mysqlvermicro);
  711. sub validate_mysql_version {
  712. ($mysqlvermajor,$mysqlverminor,$mysqlvermicro) = $myvar{'version'} =~ /^(\d+)(?:\.(\d+)|)(?:\.(\d+)|)/;
  713. $mysqlverminor ||= 0;
  714. $mysqlvermicro ||= 0;
  715. if (!mysql_version_ge(5, 1)) {
  716. badprint "Your MySQL version ".$myvar{'version'}." is EOL software! Upgrade soon!\n";
  717. } elsif (mysql_version_ge(6)) {
  718. badprint "Currently running unsupported MySQL version ".$myvar{'version'}."\n";
  719. } else {
  720. goodprint "Currently running supported MySQL version ".$myvar{'version'}."\n";
  721. }
  722. }
  723.  
  724. # Checks if MySQL version is greater than equal to (major, minor, micro)
  725. sub mysql_version_ge {
  726. my ($maj, $min, $mic) = @_;
  727. $min ||= 0;
  728. $mic ||= 0;
  729. return $mysqlvermajor > $maj || $mysqlvermajor == $maj && ($mysqlverminor > $min || $mysqlverminor == $min && $mysqlvermicro >= $mic);
  730. }
  731.  
  732. # Checks for 32-bit boxes with more than 2GB of RAM
  733. my ($arch);
  734. sub check_architecture {
  735. if ($doremote eq 1) { return; }
  736. if (`uname` =~ /SunOS/ && `isainfo -b` =~ /64/) {
  737. $arch = 64;
  738. goodprint "Operating on 64-bit architecture\n";
  739. } elsif (`uname` !~ /SunOS/ && `uname -m` =~ /64/) {
  740. $arch = 64;
  741. goodprint "Operating on 64-bit architecture\n";
  742. } elsif (`uname` =~ /AIX/ && `bootinfo -K` =~ /64/) {
  743. $arch = 64;
  744. goodprint "Operating on 64-bit architecture\n";
  745. } elsif (`uname` =~ /NetBSD|OpenBSD/ && `sysctl -b hw.machine` =~ /64/) {
  746. $arch = 64;
  747. goodprint "Operating on 64-bit architecture\n";
  748. } elsif (`uname` =~ /FreeBSD/ && `sysctl -b hw.machine_arch` =~ /64/) {
  749. $arch = 64;
  750. goodprint "Operating on 64-bit architecture\n";
  751. } elsif (`uname` =~ /Darwin/ && `uname -m` =~ /Power Macintosh/) {
  752. # 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
  753. $arch = 64;
  754. goodprint "Operating on 64-bit architecture\n";
  755. } elsif (`uname` =~ /Darwin/ && `uname -m` =~ /x86_64/) {
  756. # 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
  757. $arch = 64;
  758. goodprint "Operating on 64-bit architecture\n";
  759. } else {
  760. $arch = 32;
  761. if ($physical_memory > 2147483648) {
  762. badprint "Switch to 64-bit OS - MySQL cannot currently use all of your RAM\n";
  763. } else {
  764. goodprint "Operating on 32-bit architecture with less than 2GB RAM\n";
  765. }
  766. }
  767. $result{'OS'}{'Architecture'}="$arch bits";
  768. }
  769.  
  770. # Start up a ton of storage engine counts/statistics
  771. my (%enginestats,%enginecount,$fragtables);
  772. sub check_storage_engines {
  773. if ($opt{skipsize} eq 1) {
  774. prettyprint "\n-------- Storage Engine Statistics -------------------------------------------\n";
  775. infoprint "Skipped due to --skipsize option\n";
  776. return;
  777. }
  778. prettyprint "\n-------- Storage Engine Statistics -------------------------------------------\n";
  779.  
  780. my $engines;
  781. if (mysql_version_ge(5, 1, 5)) {
  782. my @engineresults = select_array "SELECT ENGINE,SUPPORT FROM information_schema.ENGINES WHERE ENGINE NOT IN ('performance_schema','MyISAM','MERGE','MEMORY') ORDER BY ENGINE ASC";
  783. foreach my $line (@engineresults) {
  784. my ($engine,$engineenabled);
  785. ($engine,$engineenabled) = $line =~ /([a-zA-Z_]*)\s+([a-zA-Z]+)/;
  786. $engines .= ($engineenabled eq "YES" || $engineenabled eq "DEFAULT") ? greenwrap "+".$engine." " : redwrap "-".$engine." ";
  787. }
  788. } else {
  789. $engines .= (defined $myvar{'have_archive'} && $myvar{'have_archive'} eq "YES")? greenwrap "+Archive " : redwrap "-Archive " ;
  790. $engines .= (defined $myvar{'have_bdb'} && $myvar{'have_bdb'} eq "YES")? greenwrap "+BDB " : redwrap "-BDB " ;
  791. $engines .= (defined $myvar{'have_federated_engine'} && $myvar{'have_federated_engine'} eq "YES")? greenwrap "+Federated " : redwrap "-Federated " ;
  792. $engines .= (defined $myvar{'have_innodb'} && $myvar{'have_innodb'} eq "YES")? greenwrap "+InnoDB " : redwrap "-InnoDB " ;
  793. $engines .= (defined $myvar{'have_isam'} && $myvar{'have_isam'} eq "YES")? greenwrap "+ISAM " : redwrap "-ISAM " ;
  794. $engines .= (defined $myvar{'have_ndbcluster'} && $myvar{'have_ndbcluster'} eq "YES")? greenwrap "+NDBCluster " : redwrap "-NDBCluster " ;
  795. }
  796. infoprint "Status: $engines\n";
  797. if (mysql_version_ge(5, 1, 5)) {
  798. # MySQL 5 servers can have table sizes calculated quickly from information schema
  799. my @templist = select_array "SELECT ENGINE,SUM(DATA_LENGTH+INDEX_LENGTH),COUNT(ENGINE) 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;";
  800.  
  801. foreach my $line (@templist) {
  802. my ($engine,$size,$count);
  803. ($engine,$size,$count) = $line =~ /([a-zA-Z_]*)\s+(\d+)\s+(\d+)/;
  804. if (!defined($size)) { next; }
  805. $enginestats{$engine} = $size;
  806. $enginecount{$engine} = $count;
  807. }
  808. $fragtables = select_one "SELECT COUNT(TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','performance_schema', 'mysql') AND Data_free > 0 AND NOT ENGINE='MEMORY'";
  809. chomp($fragtables);
  810. } else {
  811. # MySQL < 5 servers take a lot of work to get table sizes
  812. my @tblist;
  813. # Now we build a database list, and loop through it to get storage engine stats for tables
  814. my @dblist = select_array "SHOW DATABASES";
  815. foreach my $db (@dblist) {
  816. chomp($db);
  817. if ($db eq "information_schema" or $db eq "performance_schema" or $db eq "mysql") { next; }
  818. my @ixs = (1, 6, 9);
  819. if (!mysql_version_ge(4, 1)) {
  820. # MySQL 3.23/4.0 keeps Data_Length in the 5th (0-based) column
  821. @ixs = (1, 5, 8);
  822. }
  823. push(@tblist, map { [ (split)[@ixs] ] } select_array "SHOW TABLE STATUS FROM \\\`$db\\\`");
  824. }
  825. # Parse through the table list to generate storage engine counts/statistics
  826. $fragtables = 0;
  827. foreach my $tbl (@tblist) {
  828. my ($engine, $size, $datafree) = @$tbl;
  829. if (defined $enginestats{$engine}) {
  830. $enginestats{$engine} += $size;
  831. $enginecount{$engine} += 1;
  832. } else {
  833. $enginestats{$engine} = $size;
  834. $enginecount{$engine} = 1;
  835. }
  836. if ($datafree > 0) {
  837. $fragtables++;
  838. }
  839. }
  840. }
  841. while (my ($engine,$size) = each(%enginestats)) {
  842. infoprint "Data in $engine tables: ".hr_bytes_rnd($size)." (Tables: ".$enginecount{$engine}.")"."\n";
  843. }
  844. # If the storage engine isn't being used, recommend it to be disabled
  845. if (!defined $enginestats{'InnoDB'} && defined $myvar{'have_innodb'} && $myvar{'have_innodb'} eq "YES") {
  846. badprint "InnoDB is enabled but isn't being used\n";
  847. push(@generalrec,"Add skip-innodb to MySQL configuration to disable InnoDB");
  848. }
  849. if (!defined $enginestats{'BerkeleyDB'} && defined $myvar{'have_bdb'} && $myvar{'have_bdb'} eq "YES") {
  850. badprint "BDB is enabled but isn't being used\n";
  851. push(@generalrec,"Add skip-bdb to MySQL configuration to disable BDB");
  852. }
  853. if (!defined $enginestats{'ISAM'} && defined $myvar{'have_isam'} && $myvar{'have_isam'} eq "YES") {
  854. badprint "MYISAM is enabled but isn't being used\n";
  855. push(@generalrec,"Add skip-isam to MySQL configuration to disable ISAM (MySQL > 4.1.0)");
  856. }
  857. # Fragmented tables
  858. if ($fragtables > 0) {
  859. badprint "Total fragmented tables: $fragtables\n";
  860. push(@generalrec,"Run OPTIMIZE TABLE to defragment tables for better performance");
  861. } else {
  862. goodprint "Total fragmented tables: $fragtables\n";
  863. }
  864.  
  865.  
  866. # Auto increments
  867. my %tblist;
  868. # Find the maximum integer
  869. my $maxint = select_one "SELECT ~0";
  870.  
  871. # Now we build a database list, and loop through it to get storage engine stats for tables
  872. my @dblist = select_array "SHOW DATABASES";
  873. foreach my $db (@dblist) {
  874. chomp($db);
  875.  
  876. if(!$tblist{$db})
  877. {
  878. $tblist{$db} = ();
  879. }
  880.  
  881. if ($db eq "information_schema") { next; }
  882. my @ia = (0, 10);
  883. if (!mysql_version_ge(4, 1)) {
  884. # MySQL 3.23/4.0 keeps Data_Length in the 5th (0-based) column
  885. @ia = (0, 9);
  886. }
  887. push(@{$tblist{$db}}, map { [ (split)[@ia] ] } select_array "SHOW TABLE STATUS FROM \\\`$db\\\`");
  888. }
  889.  
  890. my @dbnames = keys %tblist;
  891.  
  892. foreach my $db (@dbnames) {
  893. foreach my $tbl (@{$tblist{$db}}) {
  894. my ($name, $autoincrement) = @$tbl;
  895.  
  896. if ($autoincrement =~ /^\d+?$/) {
  897. my $percent = ($autoincrement / $maxint) * 100;
  898. if($percent >= 75) {
  899. badprint "Table '$db.$name' has an autoincrement value near max capacity ($percent%)\n";
  900. }
  901. }
  902. }
  903. }
  904.  
  905. }
  906.  
  907. my %mycalc;
  908. sub calculations {
  909. if ($mystat{'Questions'} < 1) {
  910. badprint "Your server has not answered any queries - cannot continue...";
  911. exit 0;
  912. }
  913. # Per-thread memory
  914. if (mysql_version_ge(4)) {
  915. $mycalc{'per_thread_buffers'} = $myvar{'read_buffer_size'} + $myvar{'read_rnd_buffer_size'} + $myvar{'sort_buffer_size'} + $myvar{'thread_stack'} + $myvar{'join_buffer_size'};
  916. } else {
  917. $mycalc{'per_thread_buffers'} = $myvar{'record_buffer'} + $myvar{'record_rnd_buffer'} + $myvar{'sort_buffer'} + $myvar{'thread_stack'} + $myvar{'join_buffer_size'};
  918. }
  919. $mycalc{'total_per_thread_buffers'} = $mycalc{'per_thread_buffers'} * $myvar{'max_connections'};
  920. $mycalc{'max_total_per_thread_buffers'} = $mycalc{'per_thread_buffers'} * $mystat{'Max_used_connections'};
  921.  
  922. # Server-wide memory
  923. $mycalc{'max_tmp_table_size'} = ($myvar{'tmp_table_size'} > $myvar{'max_heap_table_size'}) ? $myvar{'max_heap_table_size'} : $myvar{'tmp_table_size'} ;
  924. $mycalc{'server_buffers'} = $myvar{'key_buffer_size'} + $mycalc{'max_tmp_table_size'};
  925. $mycalc{'server_buffers'} += (defined $myvar{'innodb_buffer_pool_size'}) ? $myvar{'innodb_buffer_pool_size'} : 0 ;
  926. $mycalc{'server_buffers'} += (defined $myvar{'innodb_additional_mem_pool_size'}) ? $myvar{'innodb_additional_mem_pool_size'} : 0 ;
  927. $mycalc{'server_buffers'} += (defined $myvar{'innodb_log_buffer_size'}) ? $myvar{'innodb_log_buffer_size'} : 0 ;
  928. $mycalc{'server_buffers'} += (defined $myvar{'query_cache_size'}) ? $myvar{'query_cache_size'} : 0 ;
  929.  
  930. # Global memory
  931. # Max used memory is memory used by MySQL based on Max_used_connections
  932. # This is the max memory used theorically calculated with the max concurrent connection number reached by mysql
  933. $mycalc{'max_used_memory'} = $mycalc{'server_buffers'} + $mycalc{"max_total_per_thread_buffers"};
  934.  
  935. # Total possible memory is memory needed by MySQL based on max_connections
  936. # This is the max memory MySQL can theorically used if all connections allowed has opened by mysql
  937. $mycalc{'total_possible_used_memory'} = $mycalc{'server_buffers'} + $mycalc{'total_per_thread_buffers'};
  938.  
  939. $mycalc{'pct_physical_memory'} = int(($mycalc{'total_possible_used_memory'} * 100) / $physical_memory);
  940.  
  941. # Maximum memory limit
  942. $mycalc{'max_peak_memory'}=0;
  943. foreach my $key ( 'key_buffer_size', 'query_cache_size', 'tmp_table_size',
  944. 'innodb_buffer_pool_size', 'innodb_additional_mem_pool_size',
  945. 'innodb_log_buffer_size') {
  946. $mycalc{'max_peak_memory'}+=$myvar{$key} if defined $myvar{$key};
  947. }
  948. foreach my $key ( 'sort_buffer_size', 'read_buffer_size', 'read_rnd_buffer_size', 'join_buffer_size',
  949. 'thread_stack', 'binlog_cache_size' ) {
  950. $mycalc{'max_peak_memory'}+=($myvar{$key}*$myvar{'max_connections'}) if defined $myvar{$key};
  951. }
  952. debugprint "Max Peak Memory: ".hr_bytes($mycalc{'max_peak_memory'})."\n";
  953. $mycalc{'pct_max_physical_memory'} = percentage($mycalc{'max_peak_memory'}, $physical_memory);
  954. debugprint "Max Percentage RAM: ".$mycalc{'pct_max_physical_memory'}."%\n";
  955.  
  956. # Slow queries
  957. $mycalc{'pct_slow_queries'} = int(($mystat{'Slow_queries'}/$mystat{'Questions'}) * 100);
  958.  
  959. # Connections
  960. $mycalc{'pct_connections_used'} = int(($mystat{'Max_used_connections'}/$myvar{'max_connections'}) * 100);
  961. $mycalc{'pct_connections_used'} = ($mycalc{'pct_connections_used'} > 100) ? 100 : $mycalc{'pct_connections_used'} ;
  962.  
  963. # Aborted Connections
  964. $mycalc{'pct_connections_aborted'} = percentage($mystat{'Aborted_connects'}, $mystat{'Connections'});
  965. debugprint "Aborted_connects: ".$mystat{'Aborted_connects'}."\n";
  966. debugprint "Connections: ".$mystat{'Connections'}."\n";
  967. debugprint "pct_connections_aborted: ".$mycalc{'pct_connections_aborted'}."\n";
  968.  
  969. # Key buffers
  970. if (mysql_version_ge(4, 1) && $myvar{'key_buffer_size'} > 0) {
  971. $mycalc{'pct_key_buffer_used'} = sprintf("%.1f",(1 - (($mystat{'Key_blocks_unused'} * $myvar{'key_cache_block_size'}) / $myvar{'key_buffer_size'})) * 100);
  972. } else {
  973. $mycalc{'pct_key_buffer_used'} = 0;
  974. }
  975.  
  976. if ($mystat{'Key_read_requests'} > 0) {
  977. $mycalc{'pct_keys_from_mem'} = sprintf("%.1f",(100 - (($mystat{'Key_reads'} / $mystat{'Key_read_requests'}) * 100)));
  978. } else {
  979. $mycalc{'pct_keys_from_mem'} = 0;
  980. }
  981.  
  982. if ($mystat{'Key_write_requests'} > 0) {
  983. $mycalc{'pct_wkeys_from_mem'} = sprintf("%.1f",(100 - (($mystat{'Key_writes'} / $mystat{'Key_write_requests'}) * 100)));
  984. } else {
  985. $mycalc{'pct_wkeys_from_mem'} = 0;
  986. }
  987.  
  988. if ($doremote eq 0 and !mysql_version_ge(5)) {
  989. my $size = 0;
  990. $size += (split)[0] for `find $myvar{'datadir'} -name "*.MYI" 2>&1 | xargs du -L $duflags 2>&1`;
  991. $mycalc{'total_myisam_indexes'} = $size;
  992. } elsif (mysql_version_ge(5)) {
  993. $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';";
  994. }
  995. if (defined $mycalc{'total_myisam_indexes'} and $mycalc{'total_myisam_indexes'} == 0) {
  996. $mycalc{'total_myisam_indexes'} = "fail";
  997. } elsif (defined $mycalc{'total_myisam_indexes'}) {
  998. chomp($mycalc{'total_myisam_indexes'});
  999. }
  1000.  
  1001. # Query cache
  1002. if (mysql_version_ge(4)) {
  1003. $mycalc{'query_cache_efficiency'} = sprintf("%.1f",($mystat{'Qcache_hits'} / ($mystat{'Com_select'} + $mystat{'Qcache_hits'})) * 100);
  1004. if ($myvar{'query_cache_size'}) {
  1005. $mycalc{'pct_query_cache_used'} = sprintf("%.1f",100 - ($mystat{'Qcache_free_memory'} / $myvar{'query_cache_size'}) * 100);
  1006. }
  1007. if ($mystat{'Qcache_lowmem_prunes'} == 0) {
  1008. $mycalc{'query_cache_prunes_per_day'} = 0;
  1009. } else {
  1010. $mycalc{'query_cache_prunes_per_day'} = int($mystat{'Qcache_lowmem_prunes'} / ($mystat{'Uptime'}/86400));
  1011. }
  1012. }
  1013.  
  1014. # Sorting
  1015. $mycalc{'total_sorts'} = $mystat{'Sort_scan'} + $mystat{'Sort_range'};
  1016. if ($mycalc{'total_sorts'} > 0) {
  1017. $mycalc{'pct_temp_sort_table'} = int(($mystat{'Sort_merge_passes'} / $mycalc{'total_sorts'}) * 100);
  1018. }
  1019.  
  1020. # Joins
  1021. $mycalc{'joins_without_indexes'} = $mystat{'Select_range_check'} + $mystat{'Select_full_join'};
  1022. $mycalc{'joins_without_indexes_per_day'} = int($mycalc{'joins_without_indexes'} / ($mystat{'Uptime'}/86400));
  1023.  
  1024. # Temporary tables
  1025. if ($mystat{'Created_tmp_tables'} > 0) {
  1026. if ($mystat{'Created_tmp_disk_tables'} > 0) {
  1027. $mycalc{'pct_temp_disk'} = int(($mystat{'Created_tmp_disk_tables'} / $mystat{'Created_tmp_tables'}) * 100);
  1028. } else {
  1029. $mycalc{'pct_temp_disk'} = 0;
  1030. }
  1031. }
  1032.  
  1033. # Table cache
  1034. if ($mystat{'Opened_tables'} > 0) {
  1035. $mycalc{'table_cache_hit_rate'} = int($mystat{'Open_tables'}*100/$mystat{'Opened_tables'});
  1036. } else {
  1037. $mycalc{'table_cache_hit_rate'} = 100;
  1038. }
  1039.  
  1040. # Open files
  1041. if ($myvar{'open_files_limit'} > 0) {
  1042. $mycalc{'pct_files_open'} = int($mystat{'Open_files'}*100/$myvar{'open_files_limit'});
  1043. }
  1044.  
  1045. # Table locks
  1046. if ($mystat{'Table_locks_immediate'} > 0) {
  1047. if ($mystat{'Table_locks_waited'} == 0) {
  1048. $mycalc{'pct_table_locks_immediate'} = 100;
  1049. } else {
  1050. $mycalc{'pct_table_locks_immediate'} = int($mystat{'Table_locks_immediate'}*100/($mystat{'Table_locks_waited'} + $mystat{'Table_locks_immediate'}));
  1051. }
  1052. }
  1053.  
  1054. # Thread cache
  1055. $mycalc{'thread_cache_hit_rate'} = int(100 - (($mystat{'Threads_created'} / $mystat{'Connections'}) * 100));
  1056.  
  1057. # Other
  1058. if ($mystat{'Connections'} > 0) {
  1059. $mycalc{'pct_aborted_connections'} = int(($mystat{'Aborted_connects'}/$mystat{'Connections'}) * 100);
  1060. }
  1061. if ($mystat{'Questions'} > 0) {
  1062. $mycalc{'total_reads'} = $mystat{'Com_select'};
  1063. $mycalc{'total_writes'} = $mystat{'Com_delete'} + $mystat{'Com_insert'} + $mystat{'Com_update'} + $mystat{'Com_replace'};
  1064. if ($mycalc{'total_reads'} == 0) {
  1065. $mycalc{'pct_reads'} = 0;
  1066. $mycalc{'pct_writes'} = 100;
  1067. } else {
  1068. $mycalc{'pct_reads'} = int(($mycalc{'total_reads'}/($mycalc{'total_reads'}+$mycalc{'total_writes'})) * 100);
  1069. $mycalc{'pct_writes'} = 100-$mycalc{'pct_reads'};
  1070. }
  1071. }
  1072.  
  1073. # InnoDB
  1074. if ($myvar{'have_innodb'} eq "YES") {
  1075. $mycalc{'innodb_log_size_pct'} = ($myvar{'innodb_log_file_size'} * 100 / $myvar{'innodb_buffer_pool_size'});
  1076. }
  1077. ($mystat{'Innodb_buffer_pool_read_requests'}, $mystat{'Innodb_buffer_pool_reads'})=(1,1) unless defined $mystat{'Innodb_buffer_pool_reads'};
  1078. $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'};
  1079. debugprint "pct_read_efficiency: ".$mycalc{'pct_read_efficiency'}."\n";
  1080. debugprint "Innodb_buffer_pool_reads: ".$mystat{'Innodb_buffer_pool_reads'}."\n";
  1081. debugprint "Innodb_buffer_pool_read_requests: ".$mystat{'Innodb_buffer_pool_read_requests'}."\n";
  1082. ($mystat{'Innodb_buffer_pool_write_requests'}, $mystat{'Innodb_buffer_pool_writes'})=(1,1) unless defined $mystat{'Innodb_buffer_pool_writes'};
  1083. $mycalc{'pct_write_efficiency'}=percentage(($mystat{'Innodb_buffer_pool_write_requests'}-$mystat{'Innodb_buffer_pool_writes'}), $mystat{'Innodb_buffer_pool_write_requests'}) if defined $mystat{'Innodb_buffer_pool_write_requests'};
  1084. debugprint "pct_write_efficiency: ".$mycalc{'pct_read_efficiency'}."\n";
  1085. debugprint "Innodb_buffer_pool_writes: ".$mystat{'Innodb_buffer_pool_writes'}."\n";
  1086. debugprint "Innodb_buffer_pool_write_requests: ".$mystat{'Innodb_buffer_pool_write_requests'}."\n";
  1087. $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'};
  1088.  
  1089. # Binlog Cache
  1090. if ($myvar{'log_bin'} ne 'OFF') {
  1091. $mycalc{'pct_binlog_cache'} = percentage( $mystat{'Binlog_cache_use'} - $mystat{'Binlog_cache_disk_use'}, $mystat{'Binlog_cache_use'} );
  1092. }
  1093. }
  1094.  
  1095. sub mysql_stats {
  1096. prettyprint "\n-------- Performance Metrics -------------------------------------------------\n";
  1097. # Show uptime, queries per second, connections, traffic stats
  1098. my $qps;
  1099. if ($mystat{'Uptime'} > 0) { $qps = sprintf("%.3f",$mystat{'Questions'}/$mystat{'Uptime'}); }
  1100. if ($mystat{'Uptime'} < 86400) { push(@generalrec,"MySQL started within last 24 hours - recommendations may be inaccurate"); }
  1101. infoprint "Up for: ".pretty_uptime($mystat{'Uptime'})." (".hr_num($mystat{'Questions'}).
  1102. " q [".hr_num($qps)." qps], ".hr_num($mystat{'Connections'})." conn,".
  1103. " TX: ".hr_num($mystat{'Bytes_sent'}).", RX: ".hr_num($mystat{'Bytes_received'}).")\n";
  1104. infoprint "Reads / Writes: ".$mycalc{'pct_reads'}."% / ".$mycalc{'pct_writes'}."%\n";
  1105. # Binlog Cache
  1106. if ($myvar{'log_bin'} eq 'OFF') {
  1107. infoprint "Binary logging is disabled\n";
  1108. } else {
  1109. infoprint "Binary logging is enabled (GTID MODE: ".(defined ($myvar{'gtid_mode'})?$myvar{'gtid_mode'}:"OFF").")\n";
  1110. }
  1111.  
  1112. # Memory usage
  1113. infoprint "Total buffers: ".hr_bytes($mycalc{'server_buffers'})." global + ".hr_bytes($mycalc{'per_thread_buffers'})." per thread ($myvar{'max_connections'} max threads)\n";
  1114.  
  1115. if ($opt{buffers} ne 0) {
  1116. infoprint "Global Buffers\n";
  1117. infoprint " +-- Key Buffer: " . hr_bytes($myvar{'key_buffer_size'}) . "\n";
  1118. infoprint " +-- Max Tmp Table: ".hr_bytes($mycalc{'max_tmp_table_size'})."\n";
  1119.  
  1120. if (defined $myvar{'query_cache_type'}) {
  1121. infoprint "Query Cache Buffers\n";
  1122. 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")) . "\n";
  1123. infoprint " +-- Query Cache Size: " . hr_bytes($myvar{'query_cache_size'}) . "\n";
  1124. }
  1125.  
  1126. infoprint "Per Thread Buffers\n";
  1127. infoprint " +-- Read Buffer: " . hr_bytes($myvar{'read_buffer_size'}) . "\n";
  1128. infoprint " +-- Read RND Buffer: " . hr_bytes($myvar{'read_rnd_buffer_size'}) . "\n";
  1129. infoprint " +-- Sort Buffer: " . hr_bytes($myvar{'sort_buffer_size'}) . "\n";
  1130. infoprint " +-- Thread stack: " . hr_bytes($myvar{'thread_stack'}) . "\n";
  1131. infoprint " +-- Join Buffer: " . hr_bytes($myvar{'join_buffer_size'}) . "\n";
  1132. if ( $myvar{'log_bin'} ne 'OFF') {
  1133. infoprint "Binlog Cache Buffers\n";
  1134. infoprint " +-- Binlog Cache: ". hr_bytes($myvar{'binlog_cache_size'})."\n";
  1135. }
  1136. }
  1137.  
  1138. if ($arch && $arch == 32 && $mycalc{'total_possible_used_memory'} > 2*1024*1024*1024) {
  1139. badprint "Allocating > 2GB RAM on 32-bit systems can cause system instability\n";
  1140. badprint "Maximum reached memory usage: ".hr_bytes($mycalc{'total_possible_used_memory'})." ($mycalc{'pct_physical_memory'}% of installed RAM)\n";
  1141. } elsif ($mycalc{'pct_physical_memory'} > 85) {
  1142. badprint "Maximum reached memory usage: ".hr_bytes($mycalc{'total_possible_used_memory'})." ($mycalc{'pct_physical_memory'}% of installed RAM)\n";
  1143. } else {
  1144. goodprint "Maximum reached memory usage: ".hr_bytes($mycalc{'total_possible_used_memory'})." ($mycalc{'pct_physical_memory'}% of installed RAM)\n";
  1145. }
  1146.  
  1147. if ($mycalc{'pct_max_physical_memory'} > 85) {
  1148. badprint "Maximum possible memory usage: ".hr_bytes($mycalc{'max_peak_memory'})." ($mycalc{'pct_max_physical_memory'}% of installed RAM)\n";
  1149. push(@generalrec,"Reduce your overall MySQL memory footprint for system stability");
  1150. } else {
  1151. goodprint "Maximum possible memory usage: ".hr_bytes($mycalc{'max_peak_memory'})." ($mycalc{'pct_max_physical_memory'}% of installed RAM)\n";
  1152. }
  1153.  
  1154. # Slow queries
  1155. if ($mycalc{'pct_slow_queries'} > 5) {
  1156. badprint "Slow queries: $mycalc{'pct_slow_queries'}% (".hr_num($mystat{'Slow_queries'})."/".hr_num($mystat{'Questions'}).")\n";
  1157. } else {
  1158. goodprint "Slow queries: $mycalc{'pct_slow_queries'}% (".hr_num($mystat{'Slow_queries'})."/".hr_num($mystat{'Questions'}).")\n";
  1159. }
  1160. if ($myvar{'long_query_time'} > 10) { push(@adjvars,"long_query_time (<= 10)"); }
  1161. if (defined($myvar{'log_slow_queries'})) {
  1162. if ($myvar{'log_slow_queries'} eq "OFF") { push(@generalrec,"Enable the slow query log to troubleshoot bad queries"); }
  1163. }
  1164.  
  1165. # Connections
  1166. if ($mycalc{'pct_connections_used'} > 85) {
  1167. badprint "Highest connection usage: $mycalc{'pct_connections_used'}% ($mystat{'Max_used_connections'}/$myvar{'max_connections'})\n";
  1168. push(@adjvars,"max_connections (> ".$myvar{'max_connections'}.")");
  1169. push(@adjvars,"wait_timeout (< ".$myvar{'wait_timeout'}.")","interactive_timeout (< ".$myvar{'interactive_timeout'}.")");
  1170. push(@generalrec,"Reduce or eliminate persistent connections to reduce connection usage")
  1171. } else {
  1172. goodprint "Highest usage of available connections: $mycalc{'pct_connections_used'}% ($mystat{'Max_used_connections'}/$myvar{'max_connections'})\n";
  1173. }
  1174.  
  1175. # Aborted Connections
  1176. if ($mycalc{'pct_connections_aborted'} > 3) {
  1177. badprint "Aborted connections: $mycalc{'pct_connections_aborted'}% ($mystat{'Aborted_connects'}/$mystat{'Connections'})\n";
  1178. push(@generalrec,"Reduce or eliminate unclosed connections and network issues")
  1179. } else {
  1180. goodprint "Aborted connections: $mycalc{'pct_connections_aborted'}% ($mystat{'Aborted_connects'}/$mystat{'Connections'})\n";
  1181. }
  1182.  
  1183. # Key buffer usage
  1184. if (defined($mycalc{'pct_key_buffer_used'})) {
  1185. if ($mycalc{'pct_key_buffer_used'} < 90) {
  1186. 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)\n";
  1187. #push(@adjvars,"key_buffer_size (\~ ".hr_num( $myvar{'key_buffer_size'} * $mycalc{'pct_key_buffer_used'} / 100).")");
  1188. } else {
  1189. 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)\n";
  1190. }
  1191. } else {
  1192. # No queries have run that would use keys
  1193. 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)\n";
  1194. }
  1195. # Key buffer
  1196. if (!defined($mycalc{'total_myisam_indexes'}) and $doremote == 1) {
  1197. push(@generalrec,"Unable to calculate MyISAM indexes on remote MySQL server < 5.0.0");
  1198. } elsif ($mycalc{'total_myisam_indexes'} =~ /^fail$/) {
  1199. badprint "Cannot calculate MyISAM index size - re-run script as root user\n";
  1200. } elsif ($mycalc{'total_myisam_indexes'} == "0") {
  1201. badprint "None of your MyISAM tables are indexed - add indexes immediately\n";
  1202. } else {
  1203. if ($myvar{'key_buffer_size'} < $mycalc{'total_myisam_indexes'} && $mycalc{'pct_keys_from_mem'} < 95) {
  1204. badprint "Key buffer size / total MyISAM indexes: ".hr_bytes($myvar{'key_buffer_size'})."/".hr_bytes($mycalc{'total_myisam_indexes'})."\n";
  1205. push(@adjvars,"key_buffer_size (> ".hr_bytes($mycalc{'total_myisam_indexes'}).")");
  1206. } else {
  1207. goodprint "Key buffer size / total MyISAM indexes: ".hr_bytes($myvar{'key_buffer_size'})."/".hr_bytes($mycalc{'total_myisam_indexes'})."\n";
  1208. }
  1209. if ($mystat{'Key_read_requests'} > 0) {
  1210. if ($mycalc{'pct_keys_from_mem'} < 95) {
  1211. badprint "Read Key buffer hit rate: $mycalc{'pct_keys_from_mem'}% (".hr_num($mystat{'Key_read_requests'})." cached / ".hr_num($mystat{'Key_reads'})." reads)\n";
  1212. } else {
  1213. goodprint "Read Key buffer hit rate: $mycalc{'pct_keys_from_mem'}% (".hr_num($mystat{'Key_read_requests'})." cached / ".hr_num($mystat{'Key_reads'})." reads)\n";
  1214. }
  1215. } else {
  1216. # No queries have run that would use keys
  1217. debugprint "Key buffer size / total MyISAM indexes: ".hr_bytes($myvar{'key_buffer_size'})."/".hr_bytes($mycalc{'total_myisam_indexes'})."\n";
  1218. }
  1219. if ($mystat{'Key_write_requests'} > 0) {
  1220. if ($mycalc{'pct_wkeys_from_mem'} < 95) {
  1221. badprint "Write Key buffer hit rate: $mycalc{'pct_wkeys_from_mem'}% (".hr_num($mystat{'Key_write_requests'})." cached / ".hr_num($mystat{'Key_writes'})." writes)\n";
  1222. } else {
  1223. goodprint "Write Key buffer hit rate: $mycalc{'pct_wkeys_from_mem'}% (".hr_num($mystat{'Key_write_requests'})." cached / ".hr_num($mystat{'Key_writes'})." writes)\n";
  1224. }
  1225. } else {
  1226. # No queries have run that would use keys
  1227. debugprint "Write Key buffer hit rate: $mycalc{'pct_wkeys_from_mem'}% (".hr_num($mystat{'Key_write_requests'})." cached / ".hr_num($mystat{'Key_writes'})." writes)\n";
  1228. }
  1229. }
  1230.  
  1231. # Query cache
  1232. if (!mysql_version_ge(4)) {
  1233. # MySQL versions < 4.01 don't support query caching
  1234. push(@generalrec,"Upgrade MySQL to version 4+ to utilize query caching");
  1235. } elsif ($myvar{'query_cache_size'} < 1) {
  1236. badprint "Query cache is disabled\n";
  1237. push(@adjvars,"query_cache_size (>= 8M)");
  1238. } elsif ($myvar{'query_cache_type'} eq "OFF") {
  1239. badprint "Query cache is disabled\n";
  1240. push(@adjvars,"query_cache_type (=1)");
  1241. } elsif ($mystat{'Com_select'} == 0) {
  1242. badprint "Query cache cannot be analyzed - no SELECT statements executed\n";
  1243. } else {
  1244. if ($mycalc{'query_cache_efficiency'} < 20) {
  1245. badprint "Query cache efficiency: $mycalc{'query_cache_efficiency'}% (".hr_num($mystat{'Qcache_hits'})." cached / ".hr_num($mystat{'Qcache_hits'}+$mystat{'Com_select'})." selects)\n";
  1246. push(@adjvars,"query_cache_limit (> ".hr_bytes_rnd($myvar{'query_cache_limit'}).", or use smaller result sets)");
  1247. } else {
  1248. goodprint "Query cache efficiency: $mycalc{'query_cache_efficiency'}% (".hr_num($mystat{'Qcache_hits'})." cached / ".hr_num($mystat{'Qcache_hits'}+$mystat{'Com_select'})." selects)\n";
  1249. }
  1250. if ($mycalc{'query_cache_prunes_per_day'} > 98) {
  1251. badprint "Query cache prunes per day: $mycalc{'query_cache_prunes_per_day'}\n";
  1252. if ($myvar{'query_cache_size'} >= 128*1024*1024) {
  1253. push(@generalrec,"Increasing the query_cache size over 128M may reduce performance");
  1254. push(@adjvars,"query_cache_size (> ".hr_bytes_rnd($myvar{'query_cache_size'}).") [see warning above]");
  1255. } else {
  1256. push(@adjvars,"query_cache_size (> ".hr_bytes_rnd($myvar{'query_cache_size'}).")");
  1257. }
  1258. } else {
  1259. goodprint "Query cache prunes per day: $mycalc{'query_cache_prunes_per_day'}\n";
  1260. }
  1261. }
  1262.  
  1263. # Sorting
  1264. if ($mycalc{'total_sorts'} == 0) {
  1265. # For the sake of space, we will be quiet here
  1266. # No sorts have run yet
  1267. } elsif ($mycalc{'pct_temp_sort_table'} > 10) {
  1268. badprint "Sorts requiring temporary tables: $mycalc{'pct_temp_sort_table'}% (".hr_num($mystat{'Sort_merge_passes'})." temp sorts / ".hr_num($mycalc{'total_sorts'})." sorts)\n";
  1269. push(@adjvars,"sort_buffer_size (> ".hr_bytes_rnd($myvar{'sort_buffer_size'}).")");
  1270. push(@adjvars,"read_rnd_buffer_size (> ".hr_bytes_rnd($myvar{'read_rnd_buffer_size'}).")");
  1271. } else {
  1272. goodprint "Sorts requiring temporary tables: $mycalc{'pct_temp_sort_table'}% (".hr_num($mystat{'Sort_merge_passes'})." temp sorts / ".hr_num($mycalc{'total_sorts'})." sorts)\n";
  1273. }
  1274.  
  1275. # Joins
  1276. if ($mycalc{'joins_without_indexes_per_day'} > 250) {
  1277. badprint "Joins performed without indexes: $mycalc{'joins_without_indexes'}\n";
  1278. push(@adjvars,"join_buffer_size (> ".hr_bytes($myvar{'join_buffer_size'}).", or always use indexes with joins)");
  1279. push(@generalrec,"Adjust your join queries to always utilize indexes");
  1280. } else {
  1281. # For the sake of space, we will be quiet here
  1282. # No joins have run without indexes
  1283. }
  1284.  
  1285. # Temporary tables
  1286. if ($mystat{'Created_tmp_tables'} > 0) {
  1287. if ($mycalc{'pct_temp_disk'} > 25 && $mycalc{'max_tmp_table_size'} < 256*1024*1024) {
  1288. 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)\n";
  1289. push(@adjvars,"tmp_table_size (> ".hr_bytes_rnd($myvar{'tmp_table_size'}).")");
  1290. push(@adjvars,"max_heap_table_size (> ".hr_bytes_rnd($myvar{'max_heap_table_size'}).")");
  1291. push(@generalrec,"When making adjustments, make tmp_table_size/max_heap_table_size equal");
  1292. push(@generalrec,"Reduce your SELECT DISTINCT queries without LIMIT clauses");
  1293. } elsif ($mycalc{'pct_temp_disk'} > 25 && $mycalc{'max_tmp_table_size'} >= 256*1024*1024) {
  1294. 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)\n";
  1295. push(@generalrec,"Temporary table size is already large - reduce result set size");
  1296. push(@generalrec,"Reduce your SELECT DISTINCT queries without LIMIT clauses");
  1297. } else {
  1298. 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)\n";
  1299. }
  1300. } else {
  1301. # For the sake of space, we will be quiet here
  1302. # No temporary tables have been created
  1303. }
  1304.  
  1305. # Thread cache
  1306. if ($myvar{'thread_cache_size'} eq 0) {
  1307. badprint "Thread cache is disabled\n";
  1308. push(@generalrec,"Set thread_cache_size to 4 as a starting value");
  1309. push(@adjvars,"thread_cache_size (start at 4)");
  1310. } else {
  1311. if ($mycalc{'thread_cache_hit_rate'} <= 50) {
  1312. badprint "Thread cache hit rate: $mycalc{'thread_cache_hit_rate'}% (".hr_num($mystat{'Threads_created'})." created / ".hr_num($mystat{'Connections'})." connections)\n";
  1313. push(@adjvars,"thread_cache_size (> $myvar{'thread_cache_size'})");
  1314. } else {
  1315. goodprint "Thread cache hit rate: $mycalc{'thread_cache_hit_rate'}% (".hr_num($mystat{'Threads_created'})." created / ".hr_num($mystat{'Connections'})." connections)\n";
  1316. }
  1317. }
  1318.  
  1319. # Table cache
  1320. my $table_cache_var = "";
  1321. if ($mystat{'Open_tables'} > 0) {
  1322. if ($mycalc{'table_cache_hit_rate'} < 20) {
  1323. badprint "Table cache hit rate: $mycalc{'table_cache_hit_rate'}% (".hr_num($mystat{'Open_tables'})." open / ".hr_num($mystat{'Opened_tables'})." opened)\n";
  1324. if (mysql_version_ge(5, 1)) {
  1325. $table_cache_var = "table_open_cache";
  1326. } else {
  1327. $table_cache_var = "table_cache";
  1328. }
  1329. push(@adjvars,$table_cache_var." (> ".$myvar{$table_cache_var}.")");
  1330. push(@generalrec,"Increase ".$table_cache_var." gradually to avoid file descriptor limits");
  1331. push(@generalrec,"Read this before increasing ".$table_cache_var." over 64: http://bit.ly/1mi7c4C");
  1332. } else {
  1333. goodprint "Table cache hit rate: $mycalc{'table_cache_hit_rate'}% (".hr_num($mystat{'Open_tables'})." open / ".hr_num($mystat{'Opened_tables'})." opened)\n";
  1334. }
  1335. }
  1336.  
  1337. # Open files
  1338. if (defined $mycalc{'pct_files_open'}) {
  1339. if ($mycalc{'pct_files_open'} > 85) {
  1340. badprint "Open file limit used: $mycalc{'pct_files_open'}% (".hr_num($mystat{'Open_files'})."/".hr_num($myvar{'open_files_limit'}).")\n";
  1341. push(@adjvars,"open_files_limit (> ".$myvar{'open_files_limit'}.")");
  1342. } else {
  1343. goodprint "Open file limit used: $mycalc{'pct_files_open'}% (".hr_num($mystat{'Open_files'})."/".hr_num($myvar{'open_files_limit'}).")\n";
  1344. }
  1345. }
  1346.  
  1347. # Table locks
  1348. if (defined $mycalc{'pct_table_locks_immediate'}) {
  1349. if ($mycalc{'pct_table_locks_immediate'} < 95) {
  1350. badprint "Table locks acquired immediately: $mycalc{'pct_table_locks_immediate'}%\n";
  1351. push(@generalrec,"Optimize queries and/or use InnoDB to reduce lock wait");
  1352. } else {
  1353. 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)\n";
  1354. }
  1355. }
  1356.  
  1357. # Binlog cache
  1358. if (defined $mycalc{'pct_binlog_cache'}) {
  1359. if ($mycalc{'pct_binlog_cache'} < 90 && $mystat{'Binlog_cache_use'}>0 ) {
  1360. badprint "Binlog cache memory access: ".$mycalc{'pct_binlog_cache'}."% ( ".($mystat{'Binlog_cache_use'}-$mystat{'Binlog_cache_disk_use'})." Memory / ".$mystat{'Binlog_cache_use'}." Total)\n";
  1361. push(@generalrec,"Increase binlog_cache_size (Actual value: ".$myvar{'binlog_cache_size'}.") ");
  1362. push(@adjvars,"binlog_cache_size (".hr_bytes($myvar{'binlog_cache_size'}+16*1024*1024)." ) ");
  1363. } else {
  1364. goodprint "Binlog cache memory access: ". $mycalc{'pct_binlog_cache'}."% ( ".($mystat{'Binlog_cache_use'}-$mystat{'Binlog_cache_disk_use'})." Memory / ".$mystat{'Binlog_cache_use'}." Total)\n";
  1365. debugprint "Not enought data to validate binlog cache size\n" if $mystat{'Binlog_cache_use'}<10;
  1366. }
  1367. }
  1368.  
  1369. # Performance options
  1370. if (!mysql_version_ge(5, 1)) {
  1371. push(@generalrec,"Upgrade to MySQL 5.5+ to use asynchrone write");
  1372. } elsif ($myvar{'concurrent_insert'} eq "OFF") {
  1373. push(@generalrec,"Enable concurrent_insert by setting it to 'ON'");
  1374. } elsif ($myvar{'concurrent_insert'} eq 0) {
  1375. push(@generalrec,"Enable concurrent_insert by setting it to 1");
  1376. }
  1377. if ($mycalc{'pct_aborted_connections'} > 5) {
  1378. badprint "Connections aborted: ".$mycalc{'pct_aborted_connections'}."%\n";
  1379. push(@generalrec,"Your applications are not closing MySQL connections properly");
  1380. }
  1381.  
  1382. }
  1383. # Recommandations for Innodb
  1384. sub mysql_innodb {
  1385. prettyprint "\n-------- InnoDB Metrics -----------------------------------------------------\n";
  1386. # InnoDB
  1387. unless (defined $myvar{'have_innodb'} && $myvar{'have_innodb'} eq "YES" && defined $enginestats{'InnoDB'}) {
  1388. infoprint "InnoDB is disabled.";
  1389. if (mysql_version_ge(5,5)) {
  1390. badprint "InnoDB Storage engine is disabled. InnoDB is the default storage engine\n";
  1391. }
  1392. return;
  1393. }
  1394. infoprint "InnoDB is enabled.\n";
  1395.  
  1396. if ($opt{buffers} ne 0) {
  1397. infoprint "InnoDB Buffers\n";
  1398. if (defined $myvar{'innodb_buffer_pool_size'}) {
  1399. infoprint " +-- InnoDB Buffer Pool: " . hr_bytes($myvar{'innodb_buffer_pool_size'}) . "\n";
  1400. }
  1401. if (defined $myvar{'innodb_buffer_pool_instances'}) {
  1402. infoprint " +-- InnoDB Buffer Pool Instances: " . $myvar{'innodb_buffer_pool_instances'} . "\n";
  1403. }
  1404. if (defined $myvar{'innodb_additional_mem_pool_size'}) {
  1405. infoprint " +-- InnoDB Additional Mem Pool: " . hr_bytes($myvar{'innodb_additional_mem_pool_size'}) . "\n";
  1406. }
  1407. if (defined $myvar{'innodb_log_buffer_size'}) {
  1408. infoprint " +-- InnoDB Log Buffer: " . hr_bytes($myvar{'innodb_log_buffer_size'}) . "\n";
  1409. }
  1410. if (defined $mystat{'Innodb_buffer_pool_pages_free'}) {
  1411. infoprint " +-- InnoDB Log Buffer Free: " . hr_bytes($mystat{'Innodb_buffer_pool_pages_free'}) . "\n";
  1412. }
  1413. if (defined $mystat{'Innodb_buffer_pool_pages_total'}) {
  1414. infoprint " +-- InnoDB Log Buffer Used: " . hr_bytes($mystat{'Innodb_buffer_pool_pages_total'}) . "\n";
  1415. }
  1416. }
  1417. # InnoDB Buffer Pull Size
  1418. if ($myvar{'innodb_buffer_pool_size'} > $enginestats{'InnoDB'}) {
  1419. goodprint "InnoDB buffer pool / data size: ".hr_bytes($myvar{'innodb_buffer_pool_size'})."/".hr_bytes($enginestats{'InnoDB'})."\n";
  1420. } else {
  1421. badprint "InnoDB buffer pool / data size: ".hr_bytes($myvar{'innodb_buffer_pool_size'})."/".hr_bytes($enginestats{'InnoDB'})."\n";
  1422. push(@adjvars,"innodb_buffer_pool_size (>= ".hr_bytes_rnd($enginestats{'InnoDB'}).") if possible.");
  1423. }
  1424.  
  1425. # InnoDB Buffer Pull Instances (MySQL 5.6.6+)
  1426. if (defined($myvar{'innodb_buffer_pool_instances'})) {
  1427. # Bad Value if > 64
  1428. if ($myvar{'innodb_buffer_pool_instances'} > 64) {
  1429. badprint "InnoDB buffer pool instances: ".$myvar{'innodb_buffer_pool_instances'}."\n";
  1430. push(@adjvars,"innodb_buffer_pool_instances (<= 64)");
  1431. }
  1432.  
  1433. # InnoDB Buffer Pull Size > 1Go
  1434. if ($myvar{'innodb_buffer_pool_size'} > 1024*1024*1024) {
  1435. # InnoDB Buffer Pull Size / 1Go = InnoDB Buffer Pull Instances
  1436. if ($myvar{'innodb_buffer_pool_instances'} != int($myvar{'innodb_buffer_pool_size'}/(1024*1024*1024))) {
  1437. badprint "InnoDB buffer pool instances: ".$myvar{'innodb_buffer_pool_instances'}."\n";
  1438. push(@adjvars,"innodb_buffer_pool_instances(=".int($myvar{'innodb_buffer_pool_size'}/(1024*1024*1024)).")");
  1439. } else {
  1440. goodprint "InnoDB buffer pool instances: ".$myvar{'innodb_buffer_pool_instances'}."\n";
  1441. }
  1442. # InnoDB Buffer Pull Size < 1Go
  1443. } else {
  1444. if ($myvar{'innodb_buffer_pool_instances'} != 1) {
  1445. badprint "InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).\n";
  1446. push(@adjvars,"innodb_buffer_pool_instances (=1)");
  1447. } else {
  1448. goodprint "InnoDB buffer pool instances: ".$myvar{'innodb_buffer_pool_instances'}."\n";
  1449. }
  1450. }
  1451. }
  1452.  
  1453. # InnoDB Used Buffer Pool
  1454. if (defined $mycalc{'pct_innodb_buffer_used'} && $mycalc{'pct_innodb_buffer_used'} < 80 ) {
  1455. badprint "InnoDB Used buffer: ".$mycalc{'pct_innodb_buffer_used'}. "% (".($mystat{'Innodb_buffer_pool_pages_total'} - $mystat{'Innodb_buffer_pool_pages_free'})." used/ ".$mystat{'Innodb_buffer_pool_pages_total'}." total)\n";
  1456. } else {
  1457. goodprint "InnoDB Used buffer: ".$mycalc{'pct_innodb_buffer_used'}. "% (".($mystat{'Innodb_buffer_pool_pages_total'} - $mystat{'Innodb_buffer_pool_pages_free'})." used/ ".$mystat{'Innodb_buffer_pool_pages_total'}." total)\n";
  1458. }
  1459.  
  1460. # InnoDB Read efficency
  1461. if (defined $mycalc{'pct_read_efficiency'} && $mycalc{'pct_read_efficiency'} < 90 ) {
  1462. 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)\n";
  1463. } else {
  1464. 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)\n";
  1465. }
  1466.  
  1467. # InnoDB Write efficency
  1468. if (defined $mycalc{'pct_write_efficiency'} && $mycalc{'pct_write_efficiency'} < 90 ) {
  1469. badprint "InnoDB Write buffer efficiency: ".$mycalc{'pct_write_efficiency'}. "% (".($mystat{'Innodb_buffer_pool_write_requests'} - $mystat{'Innodb_buffer_pool_writes'})." hits/ ".$mystat{'Innodb_buffer_pool_write_requests'}." total)\n";
  1470. } else {
  1471. goodprint "InnoDB Write buffer efficiency: ".$mycalc{'pct_write_efficiency'}. "% (".($mystat{'Innodb_buffer_pool_write_requests'} - $mystat{'Innodb_buffer_pool_writes'})." hits/ ".$mystat{'Innodb_buffer_pool_write_requests'}." total)\n";
  1472. }
  1473.  
  1474. # InnoDB Log Waits
  1475. if (defined $mystat{'Innodb_log_waits'} && $mystat{'Innodb_log_waits'} > 0) {
  1476. badprint "InnoDB log waits: ".percentage($mystat{'Innodb_log_waits'}, $mystat{'Innodb_log_writes'})."% (".$mystat{'Innodb_log_waits'}. " waits / ".$mystat{'Innodb_log_writes'}." writes)\n";
  1477. push(@adjvars,"innodb_log_buffer_size (>= ".hr_bytes_rnd($myvar{'innodb_log_buffer_size'}).")");
  1478. } else {
  1479. goodprint "InnoDB log waits: ".percentage($mystat{'Innodb_log_waits'}, $mystat{'Innodb_log_writes'})."% (".$mystat{'Innodb_log_waits'}. " waits / ".$mystat{'Innodb_log_writes'}." writes)\n";
  1480. }
  1481. }
  1482.  
  1483. # Recommandations for MySQL Databases
  1484. sub mysql_databases {
  1485. if ($opt{dbstat} == 0) {
  1486. return;
  1487. }
  1488. prettyprint "\n-------- Database Metrics ------------------------------------------------\n";
  1489. unless (mysql_version_ge(5,5)) {
  1490. infoprint "Skip Database metrics from information schema missing in this version\n";
  1491. return;
  1492. }
  1493.  
  1494. my @dblist=select_array("SHOW DATABASES;");
  1495. infoprint "There is ".scalar(@dblist). " Database(s).\n";
  1496. my @totaldbinfo=split /\s/, select_one("SELECT SUM(TABLE_ROWS), SUM(DATA_LENGTH), SUM(INDEX_LENGTH) , SUM(DATA_LENGTH+INDEX_LENGTH) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema');");
  1497. infoprint "All Databases:\n";
  1498. infoprint " +-- ROWS : ".($totaldbinfo[0] eq 'NULL'?0:$totaldbinfo[0])."\n";
  1499. infoprint " +-- DATA : ".hr_bytes($totaldbinfo[1])."(".percentage($totaldbinfo[1], $totaldbinfo[3])."%)\n";
  1500. infoprint " +-- INDEX: ".hr_bytes($totaldbinfo[2])."(".percentage($totaldbinfo[2], $totaldbinfo[3])."%)\n";
  1501. infoprint " +-- SIZE : ".hr_bytes($totaldbinfo[3])."\n";
  1502. badprint "Index size is larger than data size \n" if $totaldbinfo[1]<$totaldbinfo[2];
  1503. foreach (@dblist) {
  1504. chomp($_);
  1505. if ( $_ eq "information_schema" or $_ eq "performance_schema" or $_ eq "mysql" ) { next; }
  1506.  
  1507. 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) FROM information_schema.TABLES WHERE TABLE_SCHEMA='$_' GROUP BY TABLE_SCHEMA ORDER BY TABLE_SCHEMA");
  1508. infoprint "Database: ".$dbinfo[0]."\n";
  1509. infoprint " +-- ROWS : ".(!defined($dbinfo[1]) or $dbinfo[1] eq 'NULL'?0:$dbinfo[1])."\n";
  1510. infoprint " +-- DATA : ".hr_bytes($dbinfo[2])."(".percentage($dbinfo[2], $dbinfo[4])."%)\n";
  1511. infoprint " +-- INDEX: ".hr_bytes($dbinfo[3])."(".percentage($dbinfo[3], $dbinfo[4])."%)\n";
  1512. infoprint " +-- TOTAL: ".hr_bytes($dbinfo[4])."\n";
  1513. badprint "Index size is larger than data size for $dbinfo[0] \n" if $dbinfo[2]<$dbinfo[3];
  1514. badprint "There ".$dbinfo[5]. " storage engines. Be careful \n" if $dbinfo[5]>1;
  1515. }
  1516. }
  1517.  
  1518. # Recommandations for MySQL Databases
  1519. sub mysql_indexes {
  1520. return if ($opt{idxstat} == 0);
  1521.  
  1522. prettyprint "\n-------- Indexes Metrics -------------------------------------------------\n";
  1523. unless (mysql_version_ge(5,5)) {
  1524. infoprint "Skip Index metrics from information schema missing in this version\n";
  1525. return;
  1526. }
  1527. my $selIdxReq= <<'ENDSQL';
  1528. SELECT
  1529. CONCAT(CONCAT(t.TABLE_SCHEMA, '.'),t.TABLE_NAME) AS 'table'
  1530. , CONCAT(CONCAT(CONCAT(s.INDEX_NAME, '('),s.COLUMN_NAME), ')') AS 'index'
  1531. , s.SEQ_IN_INDEX AS 'seq'
  1532. , s2.max_columns AS 'maxcol'
  1533. , s.CARDINALITY AS 'card'
  1534. , t.TABLE_ROWS AS 'est_rows'
  1535. , ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS 'sel'
  1536. FROM INFORMATION_SCHEMA.STATISTICS s
  1537. INNER JOIN INFORMATION_SCHEMA.TABLES t
  1538. ON s.TABLE_SCHEMA = t.TABLE_SCHEMA
  1539. AND s.TABLE_NAME = t.TABLE_NAME
  1540. INNER JOIN (
  1541. SELECT
  1542. TABLE_SCHEMA
  1543. , TABLE_NAME
  1544. , INDEX_NAME
  1545. , MAX(SEQ_IN_INDEX) AS max_columns
  1546. FROM INFORMATION_SCHEMA.STATISTICS
  1547. WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
  1548. GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME
  1549. ) AS s2
  1550. ON s.TABLE_SCHEMA = s2.TABLE_SCHEMA
  1551. AND s.TABLE_NAME = s2.TABLE_NAME
  1552. AND s.INDEX_NAME = s2.INDEX_NAME
  1553. WHERE t.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
  1554. AND t.TABLE_ROWS > 10
  1555. AND s.CARDINALITY IS NOT NULL
  1556. AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 8.00
  1557. ORDER BY sel
  1558. LIMIT 10;
  1559. ENDSQL
  1560. my @idxinfo=select_array($selIdxReq);
  1561. infoprint "Worst selectivity indexes:\n";
  1562. foreach (@idxinfo) {
  1563. debugprint "$_\n";
  1564. my @info= split /\s/;
  1565. infoprint "Index: ".$info[1]."\n";
  1566.  
  1567. infoprint " +-- COLONNE : ".$info[0]."\n";
  1568. infoprint " +-- NB SEQS : ".$info[2]." sequence(s)\n";
  1569. infoprint " +-- NB COLS : ".$info[3]." column(s)\n";
  1570. infoprint " +-- CARDINALITY : ".$info[4]." distinct values\n";
  1571. infoprint " +-- NB ROWS : ".$info[5]." rows\n";
  1572. infoprint " +-- SELECTIVITY : ".$info[6]."%\n";
  1573. if ($info[6] < 25) {
  1574. badprint "$info[1] has a low selectivity\n";
  1575. }
  1576. }
  1577.  
  1578. return unless (defined($myvar{'performance_schema'}) and $myvar{'performance_schema'} eq 'ON' );
  1579.  
  1580. $selIdxReq= <<'ENDSQL';
  1581. SELECT CONCAT(CONCAT(object_schema,'.'),object_name) AS 'table', index_name
  1582. FROM performance_schema.table_io_waits_summary_by_index_usage
  1583. WHERE index_name IS NOT NULL
  1584. AND count_star =0
  1585. AND index_name <> 'PRIMARY'
  1586. AND object_schema != 'mysql'
  1587. ORDER BY count_star, object_schema, object_name;
  1588. ENDSQL
  1589. @idxinfo=select_array($selIdxReq);
  1590. infoprint "Unsused indexes:\n";
  1591. push(@generalrec, "Remove unused indexes.") if (scalar(@idxinfo)>0);
  1592. foreach (@idxinfo) {
  1593. debugprint "$_\n";
  1594. my @info= split /\s/;
  1595. badprint "Index: $info[1] on $info[0] is not used.\n";
  1596. }
  1597. }
  1598. # Take the two recommendation arrays and display them at the end of the output
  1599. sub make_recommendations {
  1600. prettyprint "\n-------- Recommendations -----------------------------------------------------\n";
  1601. if (@generalrec > 0) {
  1602. prettyprint "General recommendations:\n";
  1603. foreach (@generalrec) { prettyprint " ".$_."\n"; }
  1604. }
  1605. if (@adjvars > 0) {
  1606. prettyprint "Variables to adjust:\n";
  1607. if ($mycalc{'pct_physical_memory'} > 90) {
  1608. prettyprint " *** MySQL's maximum memory usage is dangerously high ***\n".
  1609. " *** Add RAM before increasing MySQL buffer variables ***\n";
  1610. }
  1611. foreach (@adjvars) { prettyprint " ".$_."\n"; }
  1612. }
  1613. if (@generalrec == 0 && @adjvars ==0) {
  1614. prettyprint "No additional performance recommendations are available.\n"
  1615. }
  1616. }
  1617. sub close_reportfile {
  1618. close($fh) if defined($fh);
  1619. }
  1620.  
  1621. sub headerprint {
  1622. prettyprint " >> MySQLTuner $tunerversion - Major Hayden <major\@mhtx.net>\n".
  1623. " >> Bug reports, feature requests, and downloads at http://mysqltuner.com/\n".
  1624. " >> Run with '--help' for additional options and output filtering\n";
  1625. }
  1626.  
  1627. sub dump_result {
  1628. $Data::Dumper::Pair = " : ";
  1629. print Dumper(\%result);
  1630. exit 0;
  1631. }
  1632. # ---------------------------------------------------------------------------
  1633. # BEGIN 'MAIN'
  1634. # ---------------------------------------------------------------------------
  1635. headerprint # Header Print
  1636. mysql_setup; # Gotta login first
  1637. os_setup; # Set up some OS variables
  1638. get_all_vars; # Toss variables/status into hashes
  1639. get_tuning_info; # Get information about the tuning connexion
  1640. validate_mysql_version; # Check current MySQL version
  1641. check_architecture; # Suggest 64-bit upgrade
  1642. check_storage_engines; # Show enabled storage engines
  1643. mysql_databases; # Show informations about databases
  1644. mysql_indexes; # Show informations about indexes
  1645. security_recommendations; # Display some security recommendations
  1646. calculations; # Calculate everything we need
  1647. mysql_stats; # Print the server stats
  1648. mysql_innodb; # Print InnoDB stats
  1649. get_replication_status; # Print replication info
  1650. make_recommendations; # Make recommendations based on stats
  1651. close_reportfile; # Close reportfile if needed
  1652.  
  1653. # ---------------------------------------------------------------------------
  1654. # END 'MAIN'
  1655. # ---------------------------------------------------------------------------
  1656. __END__
  1657.  
  1658. =pod
  1659.  
  1660. =encoding UTF-8
  1661.  
  1662. =head1 NAME
  1663.  
  1664. MySQLTuner 1.5.0 - MySQL High Performance Tuning Script
  1665.  
  1666. =head1 IMPORTANT USAGE GUIDELINES
  1667.  
  1668. To run the script with the default options, run the script without arguments
  1669. Allow MySQL server to run for at least 24-48 hours before trusting suggestions
  1670. Some routines may require root level privileges (script will provide warnings)
  1671. You must provide the remote server's total memory when connecting to other servers
  1672.  
  1673. =head1 CONNECTION AND AUTHENTIFICATION
  1674.  
  1675. --host <hostname> Connect to a remote host to perform tests (default: localhost)
  1676. --socket <socket> Use a different socket for a local connection
  1677. --port <port> Port to use for connection (default: 3306)
  1678. --user <username> Username to use for authentication
  1679. --pass <password> Password to use for authentication
  1680. --mysqladmin <path> Path to a custom mysqladmin executable
  1681. --mysqlcmd <path> Path to a custom mysql executable
  1682.  
  1683. =head1 PERFORMANCE AND REPORTING OPTIONS
  1684.  
  1685. --skipsize Don't enumerate tables and their types/sizes (default: on)
  1686. (Recommended for servers with many tables)
  1687. --skippassword Don't perform checks on user passwords(default: off)
  1688. --checkversion Check for updates to MySQLTuner (default: don't check)
  1689. --forcemem <size> Amount of RAM installed in megabytes
  1690. --forceswap <size> Amount of swap memory configured in megabytes
  1691. --passwordfile <path>Path to a password file list(one password by line)
  1692. --reportfile <path> Path to a report txt file
  1693.  
  1694. =head1 OUTPUT OPTIONS
  1695.  
  1696. --nogood Remove OK responses
  1697. --nobad Remove negative/suggestion responses
  1698. --noinfo Remove informational responses
  1699. --debug Print debug information
  1700. --dbstat Print database information
  1701. --idxstat Print index information
  1702. --nocolor Don't print output in color
  1703. --buffers Print global and per-thread buffer values
  1704.  
  1705. =head1 PERLDOC
  1706.  
  1707. You can find documentation for this module with the perldoc command.
  1708.  
  1709. perldoc mysqltuner
  1710.  
  1711. =head1 AUTHORS
  1712.  
  1713. Major Hayden - major@mhtx.net
  1714.  
  1715. =head1 CONTRIBUTORS
  1716.  
  1717. =over 4
  1718.  
  1719. =item *
  1720.  
  1721. Matthew Montgomery
  1722.  
  1723. =item *
  1724.  
  1725. Paul Kehrer
  1726.  
  1727. =item *
  1728.  
  1729. Dave Burgess
  1730.  
  1731. =item *
  1732.  
  1733. Jonathan Hinds
  1734.  
  1735. =item *
  1736.  
  1737. Mike Jackson
  1738.  
  1739. =item *
  1740.  
  1741. Nils Breunese
  1742.  
  1743. =item *
  1744.  
  1745. Shawn Ashlee
  1746.  
  1747. =item *
  1748.  
  1749. Luuk Vosslamber
  1750.  
  1751. =item *
  1752.  
  1753. Ville Skytta
  1754.  
  1755. =item *
  1756.  
  1757. Trent Hornibrook
  1758.  
  1759. =item *
  1760.  
  1761. Jason Gill
  1762.  
  1763. =item *
  1764.  
  1765. Mark Imbriaco
  1766.  
  1767. =item *
  1768.  
  1769. Greg Eden
  1770.  
  1771. =item *
  1772.  
  1773. Aubin Galinotti
  1774.  
  1775. =item *
  1776.  
  1777. Giovanni Bechis
  1778.  
  1779. =item *
  1780.  
  1781. Bill Bradford
  1782.  
  1783. =item *
  1784.  
  1785. Ryan Novosielski
  1786.  
  1787. =item *
  1788.  
  1789. Michael Scheidell
  1790.  
  1791. =item *
  1792.  
  1793. Blair Christensen
  1794.  
  1795. =item *
  1796.  
  1797. Hans du Plooy
  1798.  
  1799. =item *
  1800.  
  1801. Victor Trac
  1802.  
  1803. =item *
  1804.  
  1805. Everett Barnes
  1806.  
  1807. =item *
  1808.  
  1809. Tom Krouper
  1810.  
  1811. =item *
  1812.  
  1813. Gary Barrueto
  1814.  
  1815. =item *
  1816.  
  1817. Simon Greenaway
  1818.  
  1819. =item *
  1820.  
  1821. Adam Stein
  1822.  
  1823. =item *
  1824.  
  1825. Isart Montane
  1826.  
  1827. =item *
  1828.  
  1829. Baptiste M.
  1830.  
  1831. =item *
  1832.  
  1833. Cole Turner
  1834.  
  1835. =item *
  1836.  
  1837. Major Hayden
  1838.  
  1839. =item *
  1840.  
  1841. Joe Ashcraft
  1842.  
  1843. =item *
  1844.  
  1845. Jean-Marie Renouard
  1846.  
  1847. =back
  1848.  
  1849. =head1 SUPPORT
  1850.  
  1851.  
  1852. Bug reports, feature requests, and downloads at http://mysqltuner.com/
  1853.  
  1854. Bug tracker can be found at https://github.com/major/MySQLTuner-perl/issues
  1855.  
  1856. Maintained by Major Hayden (major\@mhtx.net) - Licensed under GPL
  1857.  
  1858. =head1 SOURCE CODE
  1859.  
  1860. L<https://github.com/major/MySQLTuner-perl>
  1861.  
  1862. git clone https://github.com/major/MySQLTuner-perl.git
  1863.  
  1864. =head1 COPYRIGHT AND LICENSE
  1865.  
  1866. Copyright (C) 2006-2015 Major Hayden - major@mhtx.net
  1867.  
  1868. For the latest updates, please visit http://mysqltuner.com/
  1869.  
  1870. Git repository available at http://github.com/major/MySQLTuner-perl
  1871.  
  1872. This program is free software: you can redistribute it and/or modify
  1873. it under the terms of the GNU General Public License as published by
  1874. the Free Software Foundation, either version 3 of the License, or
  1875. (at your option) any later version.
  1876.  
  1877. This program is distributed in the hope that it will be useful,
  1878. but WITHOUT ANY WARRANTY; without even the implied warranty of
  1879. MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
  1880.  
  1881. See the GNU General Public License for more details.
  1882.  
  1883. You should have received a copy of the GNU General Public License
  1884. along with this program. If not, see <http://www.gnu.org/licenses/>.
  1885.  
  1886. =cut
  1887.  
  1888. # Local variables:
  1889. # indent-tabs-mode: t
  1890. # cperl-indent-level: 8
  1891. # perl-indent-level: 8
  1892. # End:
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!