| 1 |
#!/usr/bin/perl |
| 2 |
|
| 3 |
# mariadb-report v4.0 Oct 23 2015 |
| 4 |
# renamed to from mysqlreport in 2020 |
| 5 |
# http://hackmysql.com/mysqlreport |
| 6 |
|
| 7 |
# mariadb-report makes an easy-to-read report of important MySQL/MariaDB status values. |
| 8 |
# Copyright 2006-2008 Daniel Nichter |
| 9 |
# Copyright 2012-2015 Jean Weisbuch |
| 10 |
# |
| 11 |
# This program is free software; you can redistribute it and/or |
| 12 |
# modify it under the terms of the GNU General Public License |
| 13 |
# as published by the Free Software Foundation; either version 2 |
| 14 |
# of the License, or (at your option) any later version. |
| 15 |
# |
| 16 |
# This program is distributed in the hope that it will be useful, |
| 17 |
# but WITHOUT ANY WARRANTY; without even the implied warranty of |
| 18 |
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
| 19 |
# GNU General Public License for more details. |
| 20 |
# |
| 21 |
# The GNU General Public License is available at: |
| 22 |
# http://www.gnu.org/copyleft/gpl.html |
| 23 |
|
| 24 |
use strict; |
| 25 |
use File::Temp qw(tempfile); |
| 26 |
use DBI; |
| 27 |
use Getopt::Long; |
| 28 |
eval { require Term::ReadKey; }; |
| 29 |
my $RK = ($@ ? 0 : 1); |
| 30 |
|
| 31 |
sub have_op; |
| 32 |
|
| 33 |
my $WIN = ($^O eq 'MSWin32' ? 1 : 0); |
| 34 |
my %op; |
| 35 |
my %mycnf; # ~/.my.cnf |
| 36 |
my ($tmpfile_fh, $tmpfile); |
| 37 |
my ($stat_name, $stat_val, $stat_label); |
| 38 |
my $MySQL_version; |
| 39 |
my (%stats, %vars); # SHOW STATUS, SHOW VARIABLES |
| 40 |
my (%DMS_vals, %Com_vals, %ib_vals); |
| 41 |
my $dbh; |
| 42 |
my ($questions, $key_read_ratio, $key_write_ratio, $dms, $slow_query_t); |
| 43 |
my ($key_cache_block_size, $key_buffer_used, $key_buffer_usage); |
| 44 |
my ($qc_mem_used, $qc_hi_r, $qc_ip_r); # Query Cache |
| 45 |
my ($ib_bp_used, $ib_bp_total, $ib_bp_read_ratio); |
| 46 |
my ($relative_live, $relative_infiles); |
| 47 |
my $real_uptime; |
| 48 |
my (%stats_present, %stats_past); # For relative reports |
| 49 |
my ($pagecache_read_ratio, $pagecache_write_ratio, $pagecache_block_size, $pagecache_buffer_used, $pagecache_buffer_usage); # AriaDB pagecache stats |
| 50 |
my ($binlog_cache_ratio, $binlog_stmt_cache_ratio); # binary log cache |
| 51 |
my $dbms; |
| 52 |
my ($rows, $rows_using_indexes); |
| 53 |
|
| 54 |
GetOptions ( |
| 55 |
\%op, |
| 56 |
"user=s", |
| 57 |
"password:s", |
| 58 |
"host=s", |
| 59 |
"port=s", |
| 60 |
"socket=s", |
| 61 |
"no-mycnf", |
| 62 |
"infile|in=s", |
| 63 |
"outfile=s", |
| 64 |
"flush-status", |
| 65 |
"email=s", |
| 66 |
"r|relative:i", |
| 67 |
"c|report-count=i", |
| 68 |
"detach", |
| 69 |
"help|?", |
| 70 |
"debug" |
| 71 |
); |
| 72 |
|
| 73 |
show_help_and_exit() if $op{'help'}; |
| 74 |
|
| 75 |
get_user_mycnf() unless $op{'no-mycnf'}; |
| 76 |
|
| 77 |
# Command line options override ~/.my.cnf |
| 78 |
$mycnf{'host'} = $op{'host'} if have_op 'host'; |
| 79 |
$mycnf{'port'} = $op{'port'} if have_op 'port'; |
| 80 |
$mycnf{'socket'} = $op{'socket'} if have_op 'socket'; |
| 81 |
$mycnf{'user'} = $op{'user'} if have_op 'user'; |
| 82 |
|
| 83 |
$mycnf{'user'} ||= $ENV{'USER'}; |
| 84 |
|
| 85 |
if(exists $op{'password'}) |
| 86 |
{ |
| 87 |
if($op{'password'} eq '') # Prompt for password |
| 88 |
{ |
| 89 |
Term::ReadKey::ReadMode(2) if $RK; |
| 90 |
print "Password for database user $mycnf{'user'}: "; |
| 91 |
chomp($mycnf{'pass'} = <STDIN>); |
| 92 |
Term::ReadKey::ReadMode(0), print "\n" if $RK; |
| 93 |
} |
| 94 |
else { $mycnf{'pass'} = $op{'password'}; } # Use password given on command line |
| 95 |
} |
| 96 |
|
| 97 |
$op{'com'} ||= 3; |
| 98 |
$op{'c'} ||= 1; # Used in collect_reports() if --r given integer value |
| 99 |
|
| 100 |
$relative_live = 0; |
| 101 |
$relative_infiles = 0; |
| 102 |
|
| 103 |
if(defined $op{'r'}) |
| 104 |
{ |
| 105 |
if($op{r}) { $relative_live = 1; } # if -r was given an integer value |
| 106 |
else { $relative_infiles = 1; } |
| 107 |
} |
| 108 |
|
| 109 |
# The report is written to a tmp file first. |
| 110 |
# Later it will be moved to $op{'outfile'} or emailed $op{'email'} if needed. |
| 111 |
($tmpfile_fh, $tmpfile) = tempfile() or die "Cannot open temporary file for writing: $!\n"; |
| 112 |
|
| 113 |
if($op{'detach'}) |
| 114 |
{ |
| 115 |
$SIG{'TERM'} = 'sig_handler'; |
| 116 |
|
| 117 |
if(fork()) |
| 118 |
{ |
| 119 |
print "mariadb-report has forked and detached.\n"; |
| 120 |
print "While running detached, mariadb-report writes reports to '$tmpfile'.\n"; |
| 121 |
|
| 122 |
exit; |
| 123 |
} |
| 124 |
|
| 125 |
open(STDIN, "</dev/null"); |
| 126 |
open(STDOUT, "> $tmpfile") or die "Cannot dup STDOUT: $!\n"; |
| 127 |
open(STDERR, "> $tmpfile") or die "Cannot dup STDERR: $!\n"; |
| 128 |
} |
| 129 |
|
| 130 |
select $tmpfile_fh; |
| 131 |
$| = 1 if ($op{'detach'} || $relative_live); |
| 132 |
|
| 133 |
print "tmp file: $tmpfile\n" if $op{debug}; |
| 134 |
|
| 135 |
# Connect to MySQL/MariaDB |
| 136 |
if(!$op{'infile'} && !$relative_infiles) |
| 137 |
{ |
| 138 |
connect_to_MySQL(); |
| 139 |
} |
| 140 |
|
| 141 |
my $have_innodb_vals = 1; # This might be set to 0 later in get_MySQL_version() |
| 142 |
my $have_aria_vals = 0; |
| 143 |
my $have_subquerycache_vals = 0; |
| 144 |
my $have_binlog_vals = 0; |
| 145 |
my $use_thread_pool = 0; |
| 146 |
|
| 147 |
if(defined $op{'r'}) |
| 148 |
{ |
| 149 |
if($relative_live) |
| 150 |
{ |
| 151 |
print STDERR "mariadb-report is writing relative reports to '$tmpfile'.\n" unless $op{'detach'}; |
| 152 |
get_MySQL_version(); |
| 153 |
collect_reports(); |
| 154 |
} |
| 155 |
|
| 156 |
if($relative_infiles) { read_relative_infiles(); } |
| 157 |
} |
| 158 |
else |
| 159 |
{ |
| 160 |
if(!$op{'infile'}) |
| 161 |
{ |
| 162 |
get_MySQL_version(); |
| 163 |
get_vals(); |
| 164 |
get_vars(); |
| 165 |
} |
| 166 |
else |
| 167 |
{ |
| 168 |
read_infile($op{'infile'}); |
| 169 |
} |
| 170 |
|
| 171 |
get_Com_values(); |
| 172 |
|
| 173 |
set_myisam_vals(); |
| 174 |
set_ib_vals() if $have_innodb_vals; |
| 175 |
set_aria_vals() if $have_aria_vals; |
| 176 |
set_subquerycache_vals() if $have_subquerycache_vals; |
| 177 |
set_binlog_vals() if $have_binlog_vals; |
| 178 |
|
| 179 |
write_report(); |
| 180 |
} |
| 181 |
|
| 182 |
exit_tasks_and_cleanup(); |
| 183 |
|
| 184 |
exit; |
| 185 |
|
| 186 |
# |
| 187 |
# Subroutines |
| 188 |
# |
| 189 |
sub show_help_and_exit |
| 190 |
{ |
| 191 |
print <<"HELP"; |
| 192 |
mariadb-report v4.0 Oct 23 2015 |
| 193 |
mariadb-report makes an easy-to-read report of important MySQL/MariaDB status values. |
| 194 |
|
| 195 |
Command line options (abbreviations work): |
| 196 |
--user USER Connect to MySQL as USER |
| 197 |
--password PASS Use PASS or prompt for MySQL user's password |
| 198 |
--host ADDRESS Connect to MySQL at ADDRESS |
| 199 |
--port PORT Connect to MySQL at PORT |
| 200 |
--socket SOCKET Connect to MySQL at SOCKET |
| 201 |
--no-mycnf Don't read ~/.my.cnf |
| 202 |
--infile FILE Read status values from FILE instead of MySQL |
| 203 |
--outfile FILE Write report to FILE |
| 204 |
--email ADDRESS Email report to ADDRESS (doesn't work on Windows) |
| 205 |
--flush-status Issue FLUSH STATUS; after getting current values |
| 206 |
--relative X Generate relative reports. If X is an integer, |
| 207 |
reports are live from the MySQL server X seconds apart. |
| 208 |
If X is a list of infiles (file1 file2 etc.), |
| 209 |
reports are generated from the infiles in the order |
| 210 |
that they are given. |
| 211 |
--report-count N Collect N number of live relative reports (default 1) |
| 212 |
--detach Fork and detach from terminal (run in background) |
| 213 |
--help Prints this |
| 214 |
--debug Print debugging information |
| 215 |
|
| 216 |
Visit http://hackmysql.com/mysqlreport for more information. |
| 217 |
HELP |
| 218 |
|
| 219 |
exit; |
| 220 |
} |
| 221 |
|
| 222 |
sub get_user_mycnf |
| 223 |
{ |
| 224 |
print "get_user_mycnf\n" if $op{debug}; |
| 225 |
|
| 226 |
return if $WIN; |
| 227 |
open MYCNF, "$ENV{HOME}/.my.cnf" or return; |
| 228 |
while(<MYCNF>) |
| 229 |
{ |
| 230 |
if(/^(.+?)\s*=\s*"?(.+?)"?\s*$/) |
| 231 |
{ |
| 232 |
$mycnf{$1} = $2; |
| 233 |
print "get_user_mycnf: read '$1 = $2'\n" if $op{debug}; |
| 234 |
} |
| 235 |
} |
| 236 |
$mycnf{'pass'} ||= $mycnf{'password'} if exists $mycnf{'password'}; |
| 237 |
close MYCNF; |
| 238 |
} |
| 239 |
|
| 240 |
sub connect_to_MySQL |
| 241 |
{ |
| 242 |
print "connect_to_MySQL\n" if $op{debug}; |
| 243 |
|
| 244 |
if(my @driverList = grep {/mariadb|mysql/i} DBI->available_drivers()) { |
| 245 |
my $dsn; |
| 246 |
my $driver = undef; |
| 247 |
|
| 248 |
if(grep {/mariadb/i} @driverList) |
| 249 |
{ |
| 250 |
$driver = "DBI:MariaDB"; |
| 251 |
} |
| 252 |
elsif(grep {/mysql/i} @driverList) |
| 253 |
{ |
| 254 |
$driver = "DBI:mysql"; |
| 255 |
} |
| 256 |
|
| 257 |
if($mycnf{'socket'} && -S $mycnf{'socket'}) |
| 258 |
{ |
| 259 |
if(grep {/mariadb/i} @driverList) |
| 260 |
{ |
| 261 |
$dsn = $driver . ":mariadb_socket=$mycnf{socket}"; |
| 262 |
} |
| 263 |
elsif(grep {/mysql/i} @driverList) |
| 264 |
{ |
| 265 |
$dsn = $driver . ":mysql_socket=$mycnf{socket}"; |
| 266 |
} |
| 267 |
} |
| 268 |
elsif($mycnf{'host'}) |
| 269 |
{ |
| 270 |
$dsn = $driver . ":host=$mycnf{host}" . ($mycnf{port} ? ";port=$mycnf{port}" : ""); |
| 271 |
} |
| 272 |
else |
| 273 |
{ |
| 274 |
$dsn = $driver . ":host=localhost"; |
| 275 |
} |
| 276 |
|
| 277 |
print "connect_to_MySQL: DBI DSN: " . $dsn . "\n" if $op{debug}; |
| 278 |
|
| 279 |
$dbh = DBI->connect($dsn, $mycnf{'user'}, $mycnf{'pass'}) or die; |
| 280 |
} |
| 281 |
else |
| 282 |
{ |
| 283 |
print STDERR "Install Perl 5.x driver: DBD:mysql or DBD:MariaDB\n"; |
| 284 |
print STDERR "currently installed Perl DBD drivers:\n"; |
| 285 |
foreach my $driver (DBI->available_drivers()) |
| 286 |
{ |
| 287 |
print STDERR " * " . $driver . "\n"; |
| 288 |
} |
| 289 |
print STDERR "\n"; |
| 290 |
die("Exit as no MariaDB DBI driver found!\n"); |
| 291 |
} |
| 292 |
} |
| 293 |
|
| 294 |
sub collect_reports |
| 295 |
{ |
| 296 |
print "collect_reports\n" if $op{debug}; |
| 297 |
|
| 298 |
my $i; |
| 299 |
|
| 300 |
get_vals(); |
| 301 |
get_vars(); |
| 302 |
|
| 303 |
get_Com_values(); |
| 304 |
|
| 305 |
%stats_past = %stats; |
| 306 |
|
| 307 |
set_myisam_vals(); |
| 308 |
set_ib_vals() if $have_innodb_vals; |
| 309 |
set_aria_vals() if $have_aria_vals; |
| 310 |
set_subquerycache_vals() if $have_subquerycache_vals; |
| 311 |
set_binlog_vals() if $have_binlog_vals; |
| 312 |
|
| 313 |
print "#\n# Beginning report, 0 0:0:0\n#\n"; |
| 314 |
|
| 315 |
write_report(); |
| 316 |
|
| 317 |
for($i = 0; $i < $op{'c'}; $i++) |
| 318 |
{ |
| 319 |
$dbh->disconnect(); |
| 320 |
|
| 321 |
sleep($op{'r'}); |
| 322 |
|
| 323 |
connect_to_MySQL(); |
| 324 |
|
| 325 |
print "\n#\n# Interval report " , $i + 1 , ", +", sec_to_dhms(($i + 1) * $op{'r'}), "\n#\n"; |
| 326 |
|
| 327 |
get_vals(); |
| 328 |
|
| 329 |
write_relative_report(); |
| 330 |
} |
| 331 |
} |
| 332 |
|
| 333 |
sub read_relative_infiles |
| 334 |
{ |
| 335 |
print "read_relative_infiles\n" if $op{debug}; |
| 336 |
|
| 337 |
my $slurp; # Used to check infiles for multiple sets of status values |
| 338 |
my $n_stats; # Number of multiple sets of status values in an infile |
| 339 |
my $infile; |
| 340 |
my $report_n; # Report number |
| 341 |
|
| 342 |
$report_n = 1; |
| 343 |
|
| 344 |
foreach $infile (@ARGV) |
| 345 |
{ |
| 346 |
# Read all of infile into $slurp |
| 347 |
open INFILE, "< $infile" or warn and next; |
| 348 |
$slurp = do { local $/; <INFILE> }; |
| 349 |
close INFILE; |
| 350 |
|
| 351 |
$n_stats = 0; |
| 352 |
|
| 353 |
# Count number of status value sets |
| 354 |
$n_stats++ while $slurp =~ /Aborted_clients/g; |
| 355 |
|
| 356 |
print "read_relative_infiles: found $n_stats sets of status values in file '$infile'\n" |
| 357 |
if $op{debug}; |
| 358 |
|
| 359 |
if($n_stats == 1) |
| 360 |
{ |
| 361 |
read_infile($infile); |
| 362 |
relative_infile_report($report_n++); |
| 363 |
} |
| 364 |
|
| 365 |
if($n_stats > 1) |
| 366 |
{ |
| 367 |
my @tmpfile_fh; |
| 368 |
my @tmpfile_name; |
| 369 |
my $i; |
| 370 |
my $stat_n; # Status value set number |
| 371 |
|
| 372 |
# Create a tmp file for each set of status values |
| 373 |
for($i = 0; $i < $n_stats; $i++) |
| 374 |
{ |
| 375 |
my ($fh, $name) = tempfile() |
| 376 |
or die "read_relative_infiles: cannot open temporary file for writing: $!\n"; |
| 377 |
|
| 378 |
push(@tmpfile_fh, $fh); |
| 379 |
push(@tmpfile_name, $name); |
| 380 |
|
| 381 |
print "read_relative_infiles: created tmp file '$name' for set $i\n" if $op{debug}; |
| 382 |
} |
| 383 |
|
| 384 |
$i = 0; |
| 385 |
$stat_n = 0; |
| 386 |
|
| 387 |
select $tmpfile_fh[$i]; |
| 388 |
|
| 389 |
# Read infile again and copy each set of status values to separate tmp files |
| 390 |
open INFILE, "< $infile" or warn and next; |
| 391 |
while(<INFILE>) |
| 392 |
{ |
| 393 |
next if /^\+/; |
| 394 |
next if /^$/; |
| 395 |
|
| 396 |
# The infile must begin with the system variable values. |
| 397 |
# Therefore, the first occurrence of Aborted_clients indicates the beginning |
| 398 |
# of the first set of status values if no sets have occurred yet ($stat_n == 0). |
| 399 |
# In this case, the following status values are printed to the current fh, |
| 400 |
# along with the system variable values read thus far, until Aborted_clients |
| 401 |
# occurs again. Then begins the second and subsequent sets of status values. |
| 402 |
|
| 403 |
if(/Aborted_clients/) |
| 404 |
{ |
| 405 |
print and next if $stat_n++ == 0; |
| 406 |
select $tmpfile_fh[++$i]; |
| 407 |
} |
| 408 |
|
| 409 |
print; |
| 410 |
} |
| 411 |
close INFILE; |
| 412 |
|
| 413 |
# Re-select the main tmp file into which the reports are being written. |
| 414 |
select $tmpfile_fh; |
| 415 |
|
| 416 |
for($i = 0; $i < $n_stats; $i++) |
| 417 |
{ |
| 418 |
close $tmpfile_fh[$i]; |
| 419 |
|
| 420 |
print "read_relative_infiles: reading set $i tmp file '$tmpfile_name[$i]'\n" |
| 421 |
if $op{debug}; |
| 422 |
|
| 423 |
read_infile($tmpfile_name[$i]); |
| 424 |
relative_infile_report($report_n++); |
| 425 |
|
| 426 |
if($WIN) { `del $tmpfile_name[$i]`; } |
| 427 |
else { `rm -f $tmpfile_name[$i]`; } |
| 428 |
|
| 429 |
print "read_relative_infiles: deleted set $i tmp file '$tmpfile_name[$i]'\n" |
| 430 |
if $op{debug}; |
| 431 |
} |
| 432 |
|
| 433 |
} # if($n_stats > 1) |
| 434 |
} # foreach $infile (@files) |
| 435 |
} |
| 436 |
|
| 437 |
sub relative_infile_report |
| 438 |
{ |
| 439 |
print "relative_infile_report\n" if $op{debug}; |
| 440 |
|
| 441 |
my $report_n = shift; |
| 442 |
|
| 443 |
if($report_n == 1) |
| 444 |
{ |
| 445 |
get_Com_values(); |
| 446 |
|
| 447 |
%stats_past = %stats; |
| 448 |
|
| 449 |
set_myisam_vals(); |
| 450 |
set_ib_vals() if $have_innodb_vals; |
| 451 |
set_aria_vals() if $have_aria_vals; |
| 452 |
set_subquerycache_vals() if $have_subquerycache_vals; |
| 453 |
set_binlog_vals() if $have_binlog_vals; |
| 454 |
|
| 455 |
print "#\n# Beginning report, 0 0:0:0\n#\n"; |
| 456 |
|
| 457 |
write_report(); |
| 458 |
} |
| 459 |
else |
| 460 |
{ |
| 461 |
print "\n#\n# Interval report ", $report_n - 1, ", +", |
| 462 |
sec_to_dhms($stats{Uptime} - $stats_past{Uptime}), |
| 463 |
"\n#\n"; |
| 464 |
|
| 465 |
write_relative_report(); |
| 466 |
} |
| 467 |
} |
| 468 |
|
| 469 |
sub get_vals |
| 470 |
{ |
| 471 |
print "get_vals\n" if $op{debug}; |
| 472 |
|
| 473 |
my (@row, $query); |
| 474 |
|
| 475 |
# Get status values |
| 476 |
if($MySQL_version >= 50002) |
| 477 |
{ |
| 478 |
$query = $dbh->prepare("SHOW GLOBAL STATUS;"); |
| 479 |
} |
| 480 |
else |
| 481 |
{ |
| 482 |
$query = $dbh->prepare("SHOW STATUS;"); |
| 483 |
} |
| 484 |
$query->execute(); |
| 485 |
# To avoid problems if the variable capitalization would change (eg. TokuDB on MariaDB 5.5 => 10.0), the $stats index is forced to have its first char uppercase and the rest lowercase |
| 486 |
while(@row = $query->fetchrow_array()) { $stats{ucfirst(lc($row[0]))} = $row[1]; } |
| 487 |
$query->finish(); |
| 488 |
|
| 489 |
$real_uptime = $stats{'Uptime'}; |
| 490 |
} |
| 491 |
|
| 492 |
sub get_vars |
| 493 |
{ |
| 494 |
print "get_vars\n" if $op{debug}; |
| 495 |
|
| 496 |
my (@row, $query); |
| 497 |
|
| 498 |
# Get server system variables |
| 499 |
$query = $dbh->prepare("SHOW VARIABLES;"); |
| 500 |
$query->execute(); |
| 501 |
while(@row = $query->fetchrow_array()) { $vars{$row[0]} = $row[1]; } |
| 502 |
$query->finish(); |
| 503 |
# table_cache was renamed to table_open_cache in MySQL 5.1.3 |
| 504 |
if($MySQL_version >= 50103) |
| 505 |
{ |
| 506 |
$vars{'table_cache'} = $vars{'table_open_cache'}; |
| 507 |
} |
| 508 |
# log_slow_queries was renamed to slow_query_log in MySQL 5.1.29 |
| 509 |
if($MySQL_version >= 50129) |
| 510 |
{ |
| 511 |
$vars{'log_slow_queries'} = $vars{'slow_query_log'}; |
| 512 |
} |
| 513 |
} |
| 514 |
|
| 515 |
sub read_infile |
| 516 |
{ |
| 517 |
print "read_infile\n" if $op{debug}; |
| 518 |
|
| 519 |
my $infile = shift; |
| 520 |
|
| 521 |
# Default required system variable values if not set in INFILE. |
| 522 |
# As of mysqlreport v3.5 the direct output from SHOW VARIABLES; |
| 523 |
# can be put into INFILE instead. See http://hackmysql.com/mysqlreportdoc |
| 524 |
# for details. |
| 525 |
$vars{'version'} = "0.0.0" if !exists $vars{'version'}; |
| 526 |
$vars{'table_cache'} = 64 if !exists $vars{'table_cache'}; |
| 527 |
$vars{'max_connections'} = 100 if !exists $vars{'max_connections'}; |
| 528 |
$vars{'key_buffer_size'} = 8388600 if !exists $vars{'key_buffer_size'}; # 8M |
| 529 |
$vars{'thread_cache_size'} = 0 if !exists $vars{'thread_cache_size'}; |
| 530 |
$vars{'tmp_table_size'} = 0 if !exists $vars{'tmp_table_size'}; |
| 531 |
$vars{'long_query_time'} = '?' if !exists $vars{'long_query_time'}; |
| 532 |
$vars{'log_slow_queries'} = '?' if !exists $vars{'log_slow_queries'}; |
| 533 |
|
| 534 |
# One should also add: |
| 535 |
# key_cache_block_size |
| 536 |
# query_cache_size |
| 537 |
# to INFILE if needed. |
| 538 |
|
| 539 |
open INFILE, "< $infile" or die "Cannot open INFILE '$infile': $!\n"; |
| 540 |
|
| 541 |
while(<INFILE>) |
| 542 |
{ |
| 543 |
last if !defined $_; |
| 544 |
|
| 545 |
next if /^\+/; # skip divider lines |
| 546 |
next if /^$/; # skip blank lines |
| 547 |
|
| 548 |
next until /(Aborted_clients|back_log|=)/; |
| 549 |
|
| 550 |
if($1 eq 'Aborted_clients') # status values |
| 551 |
{ |
| 552 |
print "read_infile: start stats\n" if $op{debug}; |
| 553 |
|
| 554 |
while($_) |
| 555 |
{ |
| 556 |
chomp; |
| 557 |
if(/([A-Za-z_]+)[\s\t|]+(\d+)/) |
| 558 |
{ |
| 559 |
$stats{$1} = $2; |
| 560 |
print "read_infile: save $1 = $2\n" if $op{debug}; |
| 561 |
} |
| 562 |
else { print "read_infile: ignore '$_'\n" if $op{debug}; } |
| 563 |
|
| 564 |
last if $1 eq 'Uptime'; # exit while() if end of status values |
| 565 |
$_ = <INFILE>; # otherwise, read next line of status values |
| 566 |
} |
| 567 |
} |
| 568 |
elsif($1 eq 'back_log') # system variable values |
| 569 |
{ |
| 570 |
print "read_infile: start vars\n" if $op{debug}; |
| 571 |
|
| 572 |
while($_) |
| 573 |
{ |
| 574 |
chomp; |
| 575 |
if(/([A-Za-z_]+)[\s\t|]+([\w\.\-]+)/) # This will exclude some vars |
| 576 |
{ # like pid_file which we don't need |
| 577 |
$vars{$1} = $2; |
| 578 |
print "read_infile: save $1 = $2\n" if $op{debug}; |
| 579 |
} |
| 580 |
else { print "read_infile: ignore '$_'\n" if $op{debug}; } |
| 581 |
|
| 582 |
last if $1 eq 'wait_timeout'; # exit while() if end of vars |
| 583 |
$_ = <INFILE>; # otherwise, read next line of vars |
| 584 |
} |
| 585 |
} |
| 586 |
elsif($1 eq '=') # old style, manually added system variable values |
| 587 |
{ |
| 588 |
print "read_infile: start old vars\n" if $op{debug}; |
| 589 |
|
| 590 |
while($_ && $_ =~ /=/) |
| 591 |
{ |
| 592 |
chomp; |
| 593 |
if(/^\s*(\w+)\s*=\s*([0-9.]+)(M*)\s*$/) # e.g.: key_buffer_size = 128M |
| 594 |
{ |
| 595 |
$vars{$1} = ($3 ? $2 * 1024 * 1024 : $2); |
| 596 |
print "read_infile: read '$_' as $1 = $vars{$1}\n" if $op{debug}; |
| 597 |
} |
| 598 |
else { print "read_infile: ignore '$_'\n" if $op{debug}; } |
| 599 |
|
| 600 |
$_ = <INFILE>; # otherwise, read next line of old vars |
| 601 |
} |
| 602 |
|
| 603 |
redo; |
| 604 |
} |
| 605 |
else |
| 606 |
{ |
| 607 |
print "read_infile: unrecognized line: '$_'\n" if $op{debug}; |
| 608 |
} |
| 609 |
} |
| 610 |
|
| 611 |
close INFILE; |
| 612 |
|
| 613 |
$real_uptime = $stats{'Uptime'}; |
| 614 |
|
| 615 |
$vars{'table_cache'} = $vars{'table_open_cache'} if exists $vars{'table_open_cache'}; |
| 616 |
|
| 617 |
get_MySQL_version(); |
| 618 |
} |
| 619 |
|
| 620 |
sub get_MySQL_version |
| 621 |
{ |
| 622 |
print "get_MySQL_version\n" if $op{debug}; |
| 623 |
|
| 624 |
return if $MySQL_version; |
| 625 |
|
| 626 |
my ($major, $minor, $patch); |
| 627 |
|
| 628 |
if($op{'infile'} || $relative_infiles) |
| 629 |
{ |
| 630 |
($major, $minor, $patch) = ($vars{'version'} =~ /^(\d{1,2})\.(\d{1,2})\.(\d{1,2})/); |
| 631 |
if($vars{'version'} =~ /^\d{1,2}\.\d{1,2}\.\d{1,2}-MariaDB/) { |
| 632 |
print "MariaDB detected\n" if $op{debug}; |
| 633 |
$dbms = "MariaDB"; |
| 634 |
} else { |
| 635 |
$dbms = "MySQL"; |
| 636 |
} |
| 637 |
} |
| 638 |
else |
| 639 |
{ |
| 640 |
my (@row, $query); |
| 641 |
|
| 642 |
$query = $dbh->prepare("SHOW VARIABLES LIKE 'version';"); |
| 643 |
$query->execute(); |
| 644 |
@row = $query->fetchrow_array(); |
| 645 |
$query->finish(); |
| 646 |
($major, $minor, $patch) = ($row[1] =~ /^(\d{1,2})\.(\d{1,2})\.(\d{1,2})/); |
| 647 |
if($row[1] =~ /^\d{1,2}\.\d{1,2}\.\d{1,2}-MariaDB/) |
| 648 |
{ |
| 649 |
print "MariaDB detected\n" if $op{debug}; |
| 650 |
$dbms = "MariaDB"; |
| 651 |
} |
| 652 |
else |
| 653 |
{ |
| 654 |
$dbms = "MySQL"; |
| 655 |
} |
| 656 |
} |
| 657 |
|
| 658 |
# The major version number is kept as is while the minor version and the revision number are forced to 2 digits |
| 659 |
# e.g.: 5.5.9 will be 50509, 10.0.5 will be 100005 and 10.1.23 will be 100123 |
| 660 |
$MySQL_version = sprintf("%d%02d%02d", $major, $minor, $patch); |
| 661 |
print "Version $MySQL_version\n" if $op{debug}; |
| 662 |
|
| 663 |
# Innodb_ status values were added in 5.0.2 |
| 664 |
if($MySQL_version < 50002) |
| 665 |
{ |
| 666 |
$have_innodb_vals = 0; |
| 667 |
print "get_MySQL_version: no InnoDB reports because MySQL version is older than 5.0.2\n" if $op{debug}; |
| 668 |
} else { |
| 669 |
$have_innodb_vals = $dbh->selectall_arrayref("SELECT SUPPORT FROM information_schema.engines WHERE ENGINE = 'InnoDB';", undef)->[0][0]; |
| 670 |
if(defined($have_innodb_vals) && ($have_innodb_vals eq "YES" || $have_innodb_vals eq "DEFAULT")) |
| 671 |
{ |
| 672 |
print "InnoDB detected\n" if $op{debug}; |
| 673 |
$have_innodb_vals = 1; |
| 674 |
} else { |
| 675 |
print "InnoDB is not activated\n" if $op{debug}; |
| 676 |
$have_innodb_vals = 0; |
| 677 |
} |
| 678 |
} |
| 679 |
|
| 680 |
if($dbms eq "MariaDB") { |
| 681 |
$have_aria_vals = $dbh->selectall_arrayref("SELECT SUPPORT FROM information_schema.engines WHERE ENGINE = 'Aria';", undef)->[0][0]; |
| 682 |
if(defined($have_aria_vals) && $have_aria_vals eq "YES") |
| 683 |
{ |
| 684 |
print "Aria engine detected\n" if $op{debug}; |
| 685 |
$have_aria_vals = 1; |
| 686 |
} else { |
| 687 |
$have_aria_vals = 0; |
| 688 |
} |
| 689 |
|
| 690 |
# MariaDB 5.3+, activated by default since 5.3.2 |
| 691 |
$have_subquerycache_vals = $dbh->selectall_arrayref("SELECT VARIABLE_VALUE REGEXP ',subquery_cache=on,|^subquery_cache=on,|,subquery_cache=on\$' AS SUBQUERY_CACHE FROM information_schema.global_variables WHERE VARIABLE_NAME = 'optimizer_switch';", undef)->[0][0]; |
| 692 |
if(defined($have_subquerycache_vals) && $have_subquerycache_vals eq "1") |
| 693 |
{ |
| 694 |
print "Subquery cache is activated\n" if $op{debug}; |
| 695 |
$have_subquerycache_vals = 1; |
| 696 |
} else { |
| 697 |
$have_subquerycache_vals = 0; |
| 698 |
} |
| 699 |
} |
| 700 |
|
| 701 |
if($MySQL_version >= 50000) |
| 702 |
{ |
| 703 |
# These checks use the 'information_schema' virtual database that has been added on MySQL 5.0 |
| 704 |
|
| 705 |
# MariaDB 5.5.21+ and Percona Server 5.5.30+ use the same thread pool implementation |
| 706 |
$use_thread_pool = $dbh->selectall_arrayref("SELECT VARIABLE_VALUE FROM information_schema.global_variables WHERE VARIABLE_NAME = 'thread_handling';", undef)->[0][0]; |
| 707 |
if(defined($use_thread_pool) && $use_thread_pool eq "pool-of-threads") { |
| 708 |
print "Thread pool is used\n" if $op{debug}; |
| 709 |
$use_thread_pool = 1; |
| 710 |
} else { |
| 711 |
$use_thread_pool = 0; |
| 712 |
} |
| 713 |
|
| 714 |
$have_binlog_vals = $dbh->selectall_arrayref("SELECT VARIABLE_VALUE FROM information_schema.global_variables WHERE VARIABLE_NAME = 'log_bin';", undef)->[0][0]; |
| 715 |
if(defined($have_binlog_vals) && $have_binlog_vals eq "ON") |
| 716 |
{ |
| 717 |
print "Binary log is activated\n" if $op{debug}; |
| 718 |
$have_binlog_vals = 1; |
| 719 |
} else { |
| 720 |
$have_binlog_vals = 0; |
| 721 |
} |
| 722 |
} |
| 723 |
} |
| 724 |
|
| 725 |
sub set_myisam_vals |
| 726 |
{ |
| 727 |
print "set_myisam_vals\n" if $op{debug}; |
| 728 |
|
| 729 |
# should be moved elsewhere |
| 730 |
$questions = $stats{'Questions'}; |
| 731 |
|
| 732 |
$key_read_ratio = sprintf "%.2f", |
| 733 |
($stats{'Key_read_requests'} ? |
| 734 |
100 - ($stats{'Key_reads'} / $stats{'Key_read_requests'}) * 100 : |
| 735 |
0); |
| 736 |
|
| 737 |
$key_write_ratio = sprintf "%.2f", |
| 738 |
($stats{'Key_write_requests'} ? |
| 739 |
100 - ($stats{'Key_writes'} / $stats{'Key_write_requests'}) * 100 : |
| 740 |
0); |
| 741 |
|
| 742 |
$key_cache_block_size = (defined $vars{'key_cache_block_size'} ? |
| 743 |
$vars{'key_cache_block_size'} : |
| 744 |
1024); |
| 745 |
|
| 746 |
$key_buffer_used = $stats{'Key_blocks_used'} * $key_cache_block_size; |
| 747 |
|
| 748 |
if(defined $stats{'Key_blocks_unused'}) # MySQL 4.1.2+ |
| 749 |
{ |
| 750 |
$key_buffer_usage = $vars{'key_buffer_size'} - |
| 751 |
($stats{'Key_blocks_unused'} * $key_cache_block_size); |
| 752 |
} |
| 753 |
else { $key_buffer_usage = -1; } |
| 754 |
|
| 755 |
# Data Manipulation Statements: http://dev.mysql.com/doc/refman/5.0/en/data-manipulation.html |
| 756 |
%DMS_vals = |
| 757 |
( |
| 758 |
SELECT => $stats{'Com_select'}, |
| 759 |
INSERT => $stats{'Com_insert'} + $stats{'Com_insert_select'}, |
| 760 |
REPLACE => $stats{'Com_replace'} + $stats{'Com_replace_select'}, |
| 761 |
UPDATE => $stats{'Com_update'} + |
| 762 |
(exists $stats{'Com_update_multi'} ? $stats{'Com_update_multi'} : 0), |
| 763 |
DELETE => $stats{'Com_delete'} + |
| 764 |
(exists $stats{'Com_delete_multi'} ? $stats{'Com_delete_multi'} : 0) |
| 765 |
); |
| 766 |
|
| 767 |
$dms = $DMS_vals{SELECT} + $DMS_vals{INSERT} + $DMS_vals{REPLACE} + $DMS_vals{UPDATE} + $DMS_vals{DELETE}; |
| 768 |
|
| 769 |
$slow_query_t = format_u_time($vars{long_query_time}); |
| 770 |
} |
| 771 |
|
| 772 |
sub set_ib_vals |
| 773 |
{ |
| 774 |
print "set_ib_vals\n" if $op{debug}; |
| 775 |
|
| 776 |
$ib_bp_used = ($stats{'Innodb_buffer_pool_pages_total'} - |
| 777 |
$stats{'Innodb_buffer_pool_pages_free'}) * |
| 778 |
$stats{'Innodb_page_size'}; |
| 779 |
|
| 780 |
$ib_bp_total = $stats{'Innodb_buffer_pool_pages_total'} * $stats{'Innodb_page_size'}; |
| 781 |
|
| 782 |
$ib_bp_read_ratio = sprintf "%.2f", |
| 783 |
($stats{'Innodb_buffer_pool_read_requests'} ? |
| 784 |
100 - ($stats{'Innodb_buffer_pool_reads'} / |
| 785 |
$stats{'Innodb_buffer_pool_read_requests'}) * 100 : |
| 786 |
0); |
| 787 |
} |
| 788 |
|
| 789 |
sub set_aria_vals |
| 790 |
{ |
| 791 |
print "set_aria_vals\n" if $op{debug}; |
| 792 |
|
| 793 |
$pagecache_read_ratio = sprintf "%.2f", |
| 794 |
($stats{'Aria_pagecache_read_requests'} ? |
| 795 |
100 - ($stats{'Aria_pagecache_reads'} / $stats{'Aria_pagecache_read_requests'}) * 100 : |
| 796 |
0); |
| 797 |
|
| 798 |
$pagecache_write_ratio = sprintf "%.2f", |
| 799 |
($stats{'Aria_pagecache_write_requests'} ? |
| 800 |
100 - ($stats{'Aria_pagecache_writes'} / $stats{'Aria_pagecache_write_requests'}) * 100 : |
| 801 |
0); |
| 802 |
|
| 803 |
$pagecache_block_size = (defined $vars{'aria_block_size'} ? |
| 804 |
$vars{'aria_block_size'} : |
| 805 |
1024); |
| 806 |
|
| 807 |
$pagecache_buffer_used = $stats{'Aria_pagecache_blocks_used'} * $pagecache_block_size; |
| 808 |
|
| 809 |
$pagecache_buffer_usage = $vars{'aria_pagecache_buffer_size'} - |
| 810 |
($stats{'Aria_pagecache_blocks_unused'} * $pagecache_block_size); |
| 811 |
} |
| 812 |
|
| 813 |
sub set_subquerycache_vals |
| 814 |
{ |
| 815 |
print "set_subquerycache_vals\n" if $op{debug}; |
| 816 |
} |
| 817 |
|
| 818 |
sub set_binlog_vals |
| 819 |
{ |
| 820 |
print "set_binlog_vals\n" if $op{debug}; |
| 821 |
|
| 822 |
if($stats{'Binlog_cache_use'} gt 0) { $binlog_cache_ratio = $stats{'Binlog_cache_disk_use'} / $stats{'Binlog_cache_use'}; } |
| 823 |
else { $binlog_cache_ratio = 0; } |
| 824 |
|
| 825 |
if(defined($stats{'Binlog_stmt_cache_use'}) && $stats{'Binlog_stmt_cache_use'} gt 0) { $binlog_stmt_cache_ratio = $stats{'Binlog_stmt_cache_disk_use'} / $stats{'Binlog_stmt_cache_use'}; } |
| 826 |
else { $binlog_stmt_cache_ratio = 0; } |
| 827 |
} |
| 828 |
|
| 829 |
sub write_relative_report |
| 830 |
{ |
| 831 |
print "write_relative_report\n" if $op{debug}; |
| 832 |
|
| 833 |
%stats_present = %stats; |
| 834 |
|
| 835 |
for(keys %stats) |
| 836 |
{ |
| 837 |
if($stats_past{$_} =~ /\d+/) |
| 838 |
{ |
| 839 |
if($stats_present{$_} >= $stats_past{$_}) # Avoid negative values |
| 840 |
{ |
| 841 |
$stats{$_} = $stats_present{$_} - $stats_past{$_}; |
| 842 |
} |
| 843 |
} |
| 844 |
} |
| 845 |
|
| 846 |
# These values are either "at present" or "high water marks". |
| 847 |
# Therefore, it is more logical to not relativize these values. |
| 848 |
# Doing otherwise causes strange and misleading values. |
| 849 |
$stats{'Key_blocks_used'} = $stats_present{'Key_blocks_used'}; |
| 850 |
$stats{'Open_tables'} = $stats_present{'Open_tables'}; |
| 851 |
$stats{'Max_used_connections'} = $stats_present{'Max_used_connections'}; |
| 852 |
$stats{'Threads_running'} = $stats_present{'Threads_running'}; |
| 853 |
$stats{'Threads_connected'} = $stats_present{'Threads_connected'}; |
| 854 |
$stats{'Threads_cached'} = $stats_present{'Threads_cached'}; |
| 855 |
$stats{'Qcache_free_blocks'} = $stats_present{'Qcache_free_blocks'}; |
| 856 |
$stats{'Qcache_total_blocks'} = $stats_present{'Qcache_total_blocks'}; |
| 857 |
$stats{'Qcache_free_memory'} = $stats_present{'Qcache_free_memory'}; |
| 858 |
if($have_innodb_vals) |
| 859 |
{ |
| 860 |
$stats{'Innodb_page_size'} = $stats_present{'Innodb_page_size'}; |
| 861 |
$stats{'Innodb_buffer_pool_pages_data'} = $stats_present{'Innodb_buffer_pool_pages_data'}; |
| 862 |
$stats{'Innodb_buffer_pool_pages_dirty'} = $stats_present{'Innodb_buffer_pool_pages_dirty'}; |
| 863 |
$stats{'Innodb_buffer_pool_pages_free'} = $stats_present{'Innodb_buffer_pool_pages_free'}; |
| 864 |
$stats{'Innodb_buffer_pool_pages_latched'} = $stats_present{'Innodb_buffer_pool_pages_latched'}; |
| 865 |
$stats{'Innodb_buffer_pool_pages_misc'} = $stats_present{'Innodb_buffer_pool_pages_misc'}; |
| 866 |
$stats{'Innodb_buffer_pool_pages_total'} = $stats_present{'Innodb_buffer_pool_pages_total'}; |
| 867 |
$stats{'Innodb_data_pending_fsyncs'} = $stats_present{'Innodb_data_pending_fsyncs'}; |
| 868 |
$stats{'Innodb_data_pending_reads'} = $stats_present{'Innodb_data_pending_reads'}; |
| 869 |
$stats{'Innodb_data_pending_writes'} = $stats_present{'Innodb_data_pending_writes'}; |
| 870 |
|
| 871 |
# Innodb_row_lock_ values were added in MySQL 5.0.3 |
| 872 |
if($MySQL_version >= 50003) |
| 873 |
{ |
| 874 |
$stats{'Innodb_row_lock_current_waits'} = $stats_present{'Innodb_row_lock_current_waits'}; |
| 875 |
$stats{'Innodb_row_lock_time_avg'} = $stats_present{'Innodb_row_lock_time_avg'}; |
| 876 |
$stats{'Innodb_row_lock_time_max'} = $stats_present{'Innodb_row_lock_time_max'}; |
| 877 |
} |
| 878 |
} |
| 879 |
if($have_aria_vals) |
| 880 |
{ |
| 881 |
$stats{'Aria_pagecache_blocks_used'} = $stats_present{'Aria_pagecache_blocks_used'}; |
| 882 |
} |
| 883 |
|
| 884 |
get_Com_values(); |
| 885 |
|
| 886 |
%stats_past = %stats_present; |
| 887 |
|
| 888 |
set_myisam_vals(); |
| 889 |
set_ib_vals() if $have_innodb_vals; |
| 890 |
set_aria_vals() if $have_aria_vals; |
| 891 |
set_subquerycache_vals() if $have_subquerycache_vals; |
| 892 |
set_binlog_vals() if $have_binlog_vals; |
| 893 |
|
| 894 |
write_report(); |
| 895 |
} |
| 896 |
|
| 897 |
sub write_report |
| 898 |
{ |
| 899 |
print "write_report\n" if $op{debug}; |
| 900 |
|
| 901 |
$~ = 'MYSQL_TIME', write; |
| 902 |
$~ = 'KEY_BUFF_MAX', write; |
| 903 |
if($key_buffer_usage != -1) { $~ = 'KEY_BUFF_USAGE', write } |
| 904 |
$~ = 'KEY_RATIOS', write; |
| 905 |
write_DTQ(); |
| 906 |
$~ = 'SLOW_DMS', write; |
| 907 |
write_DMS(); |
| 908 |
write_Com(); |
| 909 |
write_Rows(); |
| 910 |
$~ = 'SAS', write; |
| 911 |
write_qcache(); |
| 912 |
$~ = 'REPORT_END', write; |
| 913 |
$~ = 'THREADS', write; |
| 914 |
if($use_thread_pool) |
| 915 |
{ |
| 916 |
$~ = 'THREADPOOL', write; |
| 917 |
} else { |
| 918 |
$~ = 'THREADPERCONNECTION', write; |
| 919 |
} |
| 920 |
$~ = 'TAB', write; |
| 921 |
|
| 922 |
write_InnoDB() if $have_innodb_vals; |
| 923 |
write_Aria() if $have_aria_vals; |
| 924 |
write_Subquerycache() if $have_subquerycache_vals; |
| 925 |
write_Binlog() if $have_binlog_vals; |
| 926 |
} |
| 927 |
|
| 928 |
sub sec_to_dhms # Seconds to days+hours:minutes:seconds |
| 929 |
{ |
| 930 |
my $s = shift; |
| 931 |
my ($d, $h, $m) = (0, 0, 0); |
| 932 |
|
| 933 |
return '0 0:0:0' if $s <= 0; |
| 934 |
|
| 935 |
if($s >= 86400) |
| 936 |
{ |
| 937 |
$d = int $s / 86400; |
| 938 |
$s -= $d * 86400; |
| 939 |
} |
| 940 |
|
| 941 |
if($s >= 3600) |
| 942 |
{ |
| 943 |
$h = int $s / 3600; |
| 944 |
$s -= $h * 3600; |
| 945 |
} |
| 946 |
|
| 947 |
$m = int $s / 60; |
| 948 |
$s -= $m * 60; |
| 949 |
|
| 950 |
return "$d+$h:$m:$s"; |
| 951 |
} |
| 952 |
|
| 953 |
sub make_short |
| 954 |
{ |
| 955 |
my ($number, $kb, $d) = @_; |
| 956 |
my $n = 0; |
| 957 |
my $short; |
| 958 |
|
| 959 |
$d ||= 2; |
| 960 |
|
| 961 |
if($kb) { while ($number > 1023) { $number /= 1024; $n++; }; } |
| 962 |
else { while ($number > 999) { $number /= 1000; $n++; }; } |
| 963 |
|
| 964 |
$short = sprintf "%.${d}f%s", $number, ('','k','M','G','T')[$n]; |
| 965 |
if($short =~ /^(.+)\.(00)$/) { return $1; } # 12.00 -> 12 but not 12.00k -> 12k |
| 966 |
|
| 967 |
return $short; |
| 968 |
} |
| 969 |
|
| 970 |
# What began as a simple but great idea has become the new standard: |
| 971 |
# long_query_time in microseconds. For MySQL 5.1.21+ this is now |
| 972 |
# standard. For 4.1 and 5.0 patches, the architects of this idea |
| 973 |
# provide: http://www.mysqlperformanceblog.com/mysql-patches/ |
| 974 |
# Relevant notes in MySQL manual: |
| 975 |
# http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html |
| 976 |
# |
| 977 |
# The format_u_time sub simply beautifies long_query_time. |
| 978 |
|
| 979 |
sub format_u_time # format microsecond (µ) time value |
| 980 |
{ |
| 981 |
# 0.000000 - 0.000999 = 0 - 999 µ |
| 982 |
# 0.001000 - 0.999999 = 1 ms - 999.999 ms |
| 983 |
# 1.000000 - n.nnnnnn = 1 s - n.nnnnn s |
| 984 |
|
| 985 |
my $t = shift; |
| 986 |
my $f; # formatted µ time |
| 987 |
my $u = chr(($WIN ? 230 : 181)); |
| 988 |
|
| 989 |
$t = 0 if $t < 0; |
| 990 |
|
| 991 |
if($t > 0 && $t <= 0.000999) |
| 992 |
{ |
| 993 |
$f = ($t * 1000000) . " $u"; |
| 994 |
} |
| 995 |
elsif($t >= 0.001000 && $t <= 0.999999) |
| 996 |
{ |
| 997 |
$f = ($t * 1000) . ' ms'; |
| 998 |
} |
| 999 |
elsif($t >= 1) |
| 1000 |
{ |
| 1001 |
$f = ($t * 1) . ' s'; # * 1 to remove insignificant zeros |
| 1002 |
} |
| 1003 |
else |
| 1004 |
{ |
| 1005 |
$f = 0; # $t should = 0 at this point |
| 1006 |
} |
| 1007 |
|
| 1008 |
return $f; |
| 1009 |
} |
| 1010 |
|
| 1011 |
sub perc # Percentage |
| 1012 |
{ |
| 1013 |
my($is, $of) = @_; |
| 1014 |
$is = 0 if (not defined $is); |
| 1015 |
return sprintf "%.2f", ($is * 100) / ($of ||= 1); |
| 1016 |
} |
| 1017 |
|
| 1018 |
sub t # Time average per second |
| 1019 |
{ |
| 1020 |
my $val = shift; |
| 1021 |
return 0 if !$val; |
| 1022 |
return(make_short($val / $stats{'Uptime'}, 0, 1)); |
| 1023 |
} |
| 1024 |
|
| 1025 |
sub email_report # Email given report to $op{'email'} |
| 1026 |
{ |
| 1027 |
print "email_report\n" if $op{debug}; |
| 1028 |
|
| 1029 |
return if $WIN; |
| 1030 |
|
| 1031 |
my $report = shift; |
| 1032 |
|
| 1033 |
open SENDMAIL, "|/usr/sbin/sendmail -t"; |
| 1034 |
print SENDMAIL "From: mariadb-report\n"; |
| 1035 |
print SENDMAIL "To: $op{email}\n"; |
| 1036 |
print SENDMAIL "Subject: $dbms status report on " . ($mycnf{'host'} || 'localhost') . "\n\n"; |
| 1037 |
print SENDMAIL `cat $report`; |
| 1038 |
close SENDMAIL; |
| 1039 |
} |
| 1040 |
|
| 1041 |
sub cat_report # Print given report to screen |
| 1042 |
{ |
| 1043 |
print "cat_report\n" if $op{debug}; |
| 1044 |
|
| 1045 |
my $report = shift; |
| 1046 |
my @report; |
| 1047 |
|
| 1048 |
open REPORT, "< $report"; |
| 1049 |
@report = <REPORT>; |
| 1050 |
close REPORT; |
| 1051 |
print @report; |
| 1052 |
} |
| 1053 |
|
| 1054 |
sub get_Com_values |
| 1055 |
{ |
| 1056 |
print "get_Com_values\n" if $op{debug}; |
| 1057 |
|
| 1058 |
%Com_vals = (); |
| 1059 |
|
| 1060 |
# Make copy of just the Com_ values |
| 1061 |
for(keys %stats) |
| 1062 |
{ |
| 1063 |
if(grep /^Com_/, $_ and $stats{$_} > 0) |
| 1064 |
{ |
| 1065 |
/^Com_(.*)/; |
| 1066 |
$Com_vals{$1} = $stats{$_}; |
| 1067 |
} |
| 1068 |
} |
| 1069 |
|
| 1070 |
# Remove DMS values |
| 1071 |
delete $Com_vals{'select'}; |
| 1072 |
delete $Com_vals{'insert'}; |
| 1073 |
delete $Com_vals{'insert_select'}; |
| 1074 |
delete $Com_vals{'replace'}; |
| 1075 |
delete $Com_vals{'replace_select'}; |
| 1076 |
delete $Com_vals{'update'}; |
| 1077 |
delete $Com_vals{'update_multi'} if exists $Com_vals{'update_multi'}; |
| 1078 |
delete $Com_vals{'delete'}; |
| 1079 |
delete $Com_vals{'delete_multi'} if exists $Com_vals{'delete_multi'}; |
| 1080 |
} |
| 1081 |
|
| 1082 |
sub write_DTQ # Write DTQ report in descending order by values |
| 1083 |
{ |
| 1084 |
print "write_DTQ\n" if $op{debug}; |
| 1085 |
|
| 1086 |
$~ = 'DTQ'; |
| 1087 |
|
| 1088 |
my %DTQ; |
| 1089 |
my $first = 1; |
| 1090 |
|
| 1091 |
# Total Com values |
| 1092 |
$stat_val = 0; |
| 1093 |
for(values %Com_vals) { $stat_val += $_; } |
| 1094 |
$DTQ{'Com_'} = $stat_val; |
| 1095 |
|
| 1096 |
$DTQ{'DMS'} = $dms; |
| 1097 |
$DTQ{'QC Hits'} = $stats{'Qcache_hits'} if $stats{'Qcache_hits'} != 0; |
| 1098 |
$DTQ{'COM_QUIT'} = int (($stats{'Connections'} - 2) - ($stats{'Aborted_clients'} / 2)); |
| 1099 |
|
| 1100 |
$stat_val = 0; |
| 1101 |
for(values %DTQ) { $stat_val += $_; } |
| 1102 |
if($questions != $stat_val) |
| 1103 |
{ |
| 1104 |
$DTQ{($questions > $stat_val ? '+Unknown' : '-Unknown')} = abs $questions - $stat_val; |
| 1105 |
} |
| 1106 |
|
| 1107 |
for(sort { $DTQ{$b} <=> $DTQ{$a} } keys(%DTQ)) |
| 1108 |
{ |
| 1109 |
if($first) { $stat_label = '%Total:'; $first = 0; } |
| 1110 |
else { $stat_label = ''; } |
| 1111 |
|
| 1112 |
$stat_name = $_; |
| 1113 |
$stat_val = $DTQ{$_}; |
| 1114 |
write; |
| 1115 |
} |
| 1116 |
} |
| 1117 |
|
| 1118 |
sub write_DMS # Write DMS report in descending order by values |
| 1119 |
{ |
| 1120 |
print "write_DMS\n" if $op{debug}; |
| 1121 |
|
| 1122 |
$~ = 'DMS'; |
| 1123 |
|
| 1124 |
for(sort { $DMS_vals{$b} <=> $DMS_vals{$a} } keys(%DMS_vals)) |
| 1125 |
{ |
| 1126 |
$stat_name = $_; |
| 1127 |
$stat_val = $DMS_vals{$_}; |
| 1128 |
write; |
| 1129 |
} |
| 1130 |
} |
| 1131 |
|
| 1132 |
sub write_Com # Write COM report in descending order by values |
| 1133 |
{ |
| 1134 |
print "write_Com\n" if $op{debug}; |
| 1135 |
|
| 1136 |
my $i = $op{'com'}; |
| 1137 |
|
| 1138 |
$~ = 'COM_1'; |
| 1139 |
|
| 1140 |
# Total Com values and write first line of COM report |
| 1141 |
$stat_label = '%Total:' unless $op{'dtq'}; |
| 1142 |
$stat_val = 0; |
| 1143 |
for(values %Com_vals) { $stat_val += $_; } |
| 1144 |
write; |
| 1145 |
|
| 1146 |
$~ = 'COM_2'; |
| 1147 |
|
| 1148 |
# Sort remaining Com values, print only the top $op{'com'} number of values |
| 1149 |
for(sort { $Com_vals{$b} <=> $Com_vals{$a} } keys(%Com_vals)) |
| 1150 |
{ |
| 1151 |
$stat_name = $_; |
| 1152 |
$stat_val = $Com_vals{$_}; |
| 1153 |
write; |
| 1154 |
|
| 1155 |
last if !(--$i); |
| 1156 |
} |
| 1157 |
} |
| 1158 |
|
| 1159 |
sub write_qcache |
| 1160 |
{ |
| 1161 |
print "write_qcache\n" if $op{debug}; |
| 1162 |
|
| 1163 |
# Query cache was added in 4.0.1, but have_query_cache was added in 4.0.2, |
| 1164 |
# ergo this method is slightly more reliable |
| 1165 |
return if not exists $vars{'query_cache_size'}; |
| 1166 |
return if $vars{'query_cache_size'} == 0; |
| 1167 |
return if defined($vars{'query_cache_type'}) and $vars{'query_cache_type'} eq 'OFF'; |
| 1168 |
|
| 1169 |
$qc_mem_used = $vars{'query_cache_size'} - $stats{'Qcache_free_memory'}; |
| 1170 |
$qc_hi_r = sprintf "%.2f", $stats{'Qcache_hits'} / ($stats{'Qcache_inserts'} ||= 1); |
| 1171 |
$qc_ip_r = sprintf "%.2f", $stats{'Qcache_inserts'} / ($stats{'Qcache_lowmem_prunes'} ||= 1); |
| 1172 |
|
| 1173 |
$~ = 'QCACHE'; |
| 1174 |
write; |
| 1175 |
} |
| 1176 |
|
| 1177 |
sub write_Subquerycache |
| 1178 |
{ |
| 1179 |
print "write_Subquerycache\n" if $op{debug}; |
| 1180 |
|
| 1181 |
return if not defined $stats{'Subquery_cache_hit'}; |
| 1182 |
return if $stats{'Subquery_cache_hit'} == 0 && $stats{'Subquery_cache_miss'} == 0; |
| 1183 |
|
| 1184 |
$~ = 'SUBQUERYCACHE'; |
| 1185 |
write; |
| 1186 |
} |
| 1187 |
|
| 1188 |
sub write_Binlog |
| 1189 |
{ |
| 1190 |
print "write_Binlog\n" if $op{debug}; |
| 1191 |
|
| 1192 |
return if $binlog_cache_ratio == 0 && $binlog_stmt_cache_ratio == 0; |
| 1193 |
$~ = 'BINLOG'; |
| 1194 |
write; |
| 1195 |
} |
| 1196 |
|
| 1197 |
sub write_InnoDB |
| 1198 |
{ |
| 1199 |
print "write_InnoDB\n" if $op{debug}; |
| 1200 |
|
| 1201 |
return if not defined $stats{'Innodb_page_size'}; |
| 1202 |
|
| 1203 |
$stats{'Innodb_buffer_pool_pages_latched'} = 0 if not defined $stats{'Innodb_buffer_pool_pages_latched'}; |
| 1204 |
|
| 1205 |
$~ = 'IB'; |
| 1206 |
write; |
| 1207 |
|
| 1208 |
# Innodb_row_lock_ values were added in MySQL 5.0.3 |
| 1209 |
if($MySQL_version >= 50003) |
| 1210 |
{ |
| 1211 |
$~ = 'IB_LOCK'; |
| 1212 |
write; |
| 1213 |
} |
| 1214 |
|
| 1215 |
# Data, Pages, Rows |
| 1216 |
$~ = 'IB_DPR'; |
| 1217 |
write; |
| 1218 |
} |
| 1219 |
|
| 1220 |
|
| 1221 |
sub write_Aria |
| 1222 |
{ |
| 1223 |
print "write_Aria\n" if $op{debug}; |
| 1224 |
|
| 1225 |
return if not defined $stats{'Aria_pagecache_blocks_used'}; |
| 1226 |
|
| 1227 |
$~ = 'PAGECACHE_BUFF_MAX'; |
| 1228 |
write; |
| 1229 |
|
| 1230 |
if($pagecache_buffer_usage != -1) { $~ = 'PAGECACHE_BUFF_USAGE', write } |
| 1231 |
|
| 1232 |
$~ = 'PAGECACHE_RATIOS'; |
| 1233 |
write; |
| 1234 |
} |
| 1235 |
|
| 1236 |
sub write_Rows |
| 1237 |
{ |
| 1238 |
print "write_Rows\n" if $op{debug}; |
| 1239 |
|
| 1240 |
$rows_using_indexes = $stats{'Handler_read_first'} + $stats{'Handler_read_key'} + $stats{'Handler_read_next'} + $stats{'Handler_read_prev'}; |
| 1241 |
$rows = $rows_using_indexes + $stats{'Handler_read_rnd'} + $stats{'Handler_read_rnd_next'} + $stats{'Sort_rows'}; |
| 1242 |
|
| 1243 |
$~ = 'ROWS'; |
| 1244 |
write; |
| 1245 |
} |
| 1246 |
|
| 1247 |
sub have_op |
| 1248 |
{ |
| 1249 |
my $key = shift; |
| 1250 |
return 1 if (exists $op{$key} && $op{$key} ne ''); |
| 1251 |
return 0; |
| 1252 |
} |
| 1253 |
|
| 1254 |
sub sig_handler |
| 1255 |
{ |
| 1256 |
print "\nReceived signal at " , scalar localtime , "\n"; |
| 1257 |
exit_tasks_and_cleanup(); |
| 1258 |
exit; |
| 1259 |
} |
| 1260 |
|
| 1261 |
sub exit_tasks_and_cleanup |
| 1262 |
{ |
| 1263 |
print "exit_tasks_and_cleanup\n" if $op{debug}; |
| 1264 |
|
| 1265 |
close $tmpfile_fh; |
| 1266 |
select STDOUT unless $op{'detach'}; |
| 1267 |
|
| 1268 |
email_report($tmpfile) if $op{'email'}; |
| 1269 |
|
| 1270 |
cat_report($tmpfile) unless $op{'detach'}; |
| 1271 |
|
| 1272 |
if($op{'outfile'}) |
| 1273 |
{ |
| 1274 |
if($WIN) { `move $tmpfile $op{outfile}`; } |
| 1275 |
else { `mv $tmpfile $op{outfile}`; } |
| 1276 |
} |
| 1277 |
else |
| 1278 |
{ |
| 1279 |
unlink $tmpfile; |
| 1280 |
} |
| 1281 |
|
| 1282 |
if(!$op{'infile'} && !$relative_infiles) |
| 1283 |
{ |
| 1284 |
if($op{'flush-status'}) |
| 1285 |
{ |
| 1286 |
my $query = $dbh->prepare("FLUSH STATUS;"); |
| 1287 |
$query->execute(); |
| 1288 |
$query->finish(); |
| 1289 |
} |
| 1290 |
$dbh->disconnect(); |
| 1291 |
} |
| 1292 |
} |
| 1293 |
|
| 1294 |
# |
| 1295 |
# Formats |
| 1296 |
# |
| 1297 |
|
| 1298 |
format MYSQL_TIME = |
| 1299 |
@<<<<<< @<<<<<<<<<<<<<<<<<< uptime @<<<<<<<<<<< @<<<<<<<<<<<<<<<<<<<<<<< |
| 1300 |
$dbms, $vars{'version'}, sec_to_dhms($real_uptime), (($op{infile} || $relative_infiles) ? '' : scalar localtime) |
| 1301 |
. |
| 1302 |
|
| 1303 |
format KEY_BUFF_MAX = |
| 1304 |
|
| 1305 |
__ Key _________________________________________________________________ |
| 1306 |
Buffer used @>>>>>> of @>>>>>> %Used: @>>>>> |
| 1307 |
make_short($key_buffer_used, 1), make_short($vars{'key_buffer_size'}, 1), perc($key_buffer_used, $vars{'key_buffer_size'}) |
| 1308 |
. |
| 1309 |
|
| 1310 |
format KEY_BUFF_USAGE = |
| 1311 |
Current @>>>>>> %Usage: @>>>>> |
| 1312 |
make_short($key_buffer_usage, 1), perc($key_buffer_usage, $vars{'key_buffer_size'}) |
| 1313 |
. |
| 1314 |
|
| 1315 |
format KEY_RATIOS = |
| 1316 |
Write hit @>>>>>% |
| 1317 |
$key_write_ratio |
| 1318 |
Read hit @>>>>>% |
| 1319 |
$key_read_ratio |
| 1320 |
|
| 1321 |
__ Questions ___________________________________________________________ |
| 1322 |
Total @>>>>>>>> @>>>>>/s |
| 1323 |
make_short($questions), t($questions) |
| 1324 |
. |
| 1325 |
|
| 1326 |
format DTQ = |
| 1327 |
@<<<<<<< @>>>>>>>> @>>>>>/s @>>>>>> @>>>>> |
| 1328 |
$stat_name, make_short($stat_val), t($stat_val), $stat_label, perc($stat_val, $questions) |
| 1329 |
. |
| 1330 |
|
| 1331 |
format SLOW_DMS = |
| 1332 |
Slow @<<<<<<< @>>>>>> @>>>>>/s @>>>>> %DMS: @>>>>> Log: @>> |
| 1333 |
$slow_query_t, make_short($stats{'Slow_queries'}), t($stats{'Slow_queries'}), perc($stats{'Slow_queries'}, $questions), perc($stats{'Slow_queries'}, $dms), $vars{'log_slow_queries'} |
| 1334 |
DMS @>>>>>>>> @>>>>>/s @>>>>> |
| 1335 |
make_short($dms), t($dms), perc($dms, $questions) |
| 1336 |
. |
| 1337 |
|
| 1338 |
format DMS = |
| 1339 |
@<<<<<<< @>>>>>>>> @>>>>>/s @>>>>> @>>>>> |
| 1340 |
$stat_name, make_short($stat_val), t($stat_val), perc($stat_val, $questions), perc($stat_val, $dms) |
| 1341 |
. |
| 1342 |
|
| 1343 |
format COM_1 = |
| 1344 |
Com_ @>>>>>>>> @>>>>>/s @>>>>> |
| 1345 |
make_short($stat_val), t($stat_val), perc($stat_val, $questions) |
| 1346 |
. |
| 1347 |
|
| 1348 |
format COM_2 = |
| 1349 |
@<<<<<<<<<< @>>>>>> @>>>>>/s @>>>>> |
| 1350 |
$stat_name, make_short($stat_val), t($stat_val), perc($stat_val, $questions) |
| 1351 |
. |
| 1352 |
|
| 1353 |
format SAS = |
| 1354 |
|
| 1355 |
__ SELECT and Sort _____________________________________________________ |
| 1356 |
Scan @>>>>>> @>>>>>/s %SELECT: @>>>>> |
| 1357 |
make_short($stats{'Select_scan'}), t($stats{'Select_scan'}), perc($stats{'Select_scan'}, $stats{'Com_select'}) |
| 1358 |
Range @>>>>>> @>>>>>/s @>>>>> |
| 1359 |
make_short($stats{'Select_range'}), t($stats{'Select_range'}), perc($stats{'Select_range'}, $stats{'Com_select'}) |
| 1360 |
Full join @>>>>>> @>>>>>/s @>>>>> |
| 1361 |
make_short($stats{'Select_full_join'}), t($stats{'Select_full_join'}), perc($stats{'Select_full_join'}, $stats{'Com_select'}) |
| 1362 |
Range check @>>>>>> @>>>>>/s @>>>>> |
| 1363 |
make_short($stats{'Select_range_check'}), t($stats{'Select_range_check'}), perc($stats{'Select_range_check'}, $stats{'Com_select'}) |
| 1364 |
Full rng join @>>>>>> @>>>>>/s @>>>>> |
| 1365 |
make_short($stats{'Select_full_range_join'}), t($stats{'Select_full_range_join'}), perc($stats{'Select_full_range_join'}, $stats{'Com_select'}) |
| 1366 |
Sort scan @>>>>>> @>>>>>/s |
| 1367 |
make_short($stats{'Sort_scan'}), t($stats{'Sort_scan'}) |
| 1368 |
Sort range @>>>>>> @>>>>>/s |
| 1369 |
make_short($stats{'Sort_range'}), t($stats{'Sort_range'}) |
| 1370 |
Sort mrg pass @>>>>>> @>>>>>/s |
| 1371 |
make_short($stats{'Sort_merge_passes'}), t($stats{'Sort_merge_passes'}) |
| 1372 |
. |
| 1373 |
|
| 1374 |
format QCACHE = |
| 1375 |
|
| 1376 |
__ Query Cache _________________________________________________________ |
| 1377 |
Memory usage @>>>>>> of @>>>>>> %Usage: @>>>>> |
| 1378 |
make_short($qc_mem_used, 1), make_short($vars{'query_cache_size'}, 1), perc($qc_mem_used, $vars{'query_cache_size'}) |
| 1379 |
Block Fragment @>>>>>% |
| 1380 |
perc($stats{'Qcache_free_blocks'}, $stats{'Qcache_total_blocks'}) |
| 1381 |
Hits @>>>>>> @>>>>>/s |
| 1382 |
make_short($stats{'Qcache_hits'}), t($stats{'Qcache_hits'}) |
| 1383 |
Inserts @>>>>>> @>>>>>/s |
| 1384 |
make_short($stats{'Qcache_inserts'}), t($stats{'Qcache_inserts'}) |
| 1385 |
Insrt:Prune @>>>>>>:1 @>>>>>/s |
| 1386 |
make_short($qc_ip_r), t($stats{'Qcache_inserts'} - $stats{'Qcache_lowmem_prunes'}) |
| 1387 |
Hit:Insert @>>>>>>:1 |
| 1388 |
$qc_hi_r, t($qc_hi_r) |
| 1389 |
. |
| 1390 |
|
| 1391 |
format SUBQUERYCACHE = |
| 1392 |
|
| 1393 |
__ Subquery Cache ______________________________________________________ |
| 1394 |
Hit ratio @>>>>>% |
| 1395 |
perc($stats{'Subquery_cache_hit'} / ($stats{'Subquery_cache_hit'} + $stats{'Subquery_cache_miss'})) |
| 1396 |
Hits @>>>>>> @>>>>>/s |
| 1397 |
make_short($stats{'Subquery_cache_hit'}), t($stats{'Subquery_cache_hit'}) |
| 1398 |
Miss @>>>>>> @>>>>>/s |
| 1399 |
make_short($stats{'Subquery_cache_miss'}), t($stats{'Subquery_cache_miss'}) |
| 1400 |
. |
| 1401 |
|
| 1402 |
# Not really the end... |
| 1403 |
format REPORT_END = |
| 1404 |
|
| 1405 |
__ Table Locks _________________________________________________________ |
| 1406 |
Waited @>>>>>>>> @>>>>>/s %Total: @>>>>> |
| 1407 |
make_short($stats{'Table_locks_waited'}), t($stats{'Table_locks_waited'}), perc($stats{'Table_locks_waited'}, $stats{'Table_locks_waited'} + $stats{'Table_locks_immediate'}); |
| 1408 |
Immediate @>>>>>>>> @>>>>>/s |
| 1409 |
make_short($stats{'Table_locks_immediate'}), t($stats{'Table_locks_immediate'}) |
| 1410 |
|
| 1411 |
__ Tables ______________________________________________________________ |
| 1412 |
Open @>>>>>>>> of @>>>>> %Cache: @>>>>> |
| 1413 |
$stats{'Open_tables'}, $vars{'table_cache'}, perc($stats{'Open_tables'}, $vars{'table_cache'}) |
| 1414 |
Opened @>>>>>>>> @>>>>>/s |
| 1415 |
make_short($stats{'Opened_tables'}), t($stats{'Opened_tables'}) |
| 1416 |
|
| 1417 |
__ Connections _________________________________________________________ |
| 1418 |
Max used @>>>>>>>> of @>>>>> %Max: @>>>>> |
| 1419 |
$stats{'Max_used_connections'}, $vars{'max_connections'}, perc($stats{'Max_used_connections'}, $vars{'max_connections'}) |
| 1420 |
Total @>>>>>>>> @>>>>>/s |
| 1421 |
make_short($stats{'Connections'}), t($stats{'Connections'}) |
| 1422 |
|
| 1423 |
__ Created Temp ________________________________________________________ |
| 1424 |
Disk table @>>>>>>>> @>>>>>/s %Disk: @>>>>> |
| 1425 |
make_short($stats{'Created_tmp_disk_tables'}), t($stats{'Created_tmp_disk_tables'}), perc($stats{'Created_tmp_disk_tables'}, $stats{'Created_tmp_tables'}) |
| 1426 |
Table @>>>>>>>> @>>>>>/s Size: @>>>>> |
| 1427 |
make_short($stats{'Created_tmp_tables'}), t($stats{'Created_tmp_tables'}), make_short($vars{'tmp_table_size'}, 1, 1) |
| 1428 |
File @>>>>>>>> @>>>>>/s |
| 1429 |
make_short($stats{'Created_tmp_files'}), t($stats{'Created_tmp_files'}) |
| 1430 |
. |
| 1431 |
|
| 1432 |
format THREADS = |
| 1433 |
|
| 1434 |
__ Threads _____________________________________________________________ |
| 1435 |
Running @>>>>>>>> of @>>>>> |
| 1436 |
$stats{'Threads_running'}, $stats{'Threads_connected'} |
| 1437 |
Created @>>>>>>>> @>>>>>/s |
| 1438 |
make_short($stats{'Threads_created'}), t($stats{'Threads_created'}) |
| 1439 |
Slow @>>>>>>>> @>>>>>/s |
| 1440 |
$stats{'Slow_launch_threads'}, t($stats{'Slow_launch_threads'}) |
| 1441 |
. |
| 1442 |
|
| 1443 |
format THREADPERCONNECTION = |
| 1444 |
Cached @>>>>>>>> of @>>>>> %Hit: @>>>>> |
| 1445 |
$stats{'Threads_cached'}, $vars{'thread_cache_size'}, make_short(100 - perc($stats{'Threads_created'}, $stats{'Connections'})) |
| 1446 |
. |
| 1447 |
|
| 1448 |
format THREADPOOL = |
| 1449 |
Threadpool @>>>>>>>> of @>>>>> %Used: @>>>>> |
| 1450 |
$stats{'Threadpool_threads'} + $stats{'Threadpool_idle_threads'}, $vars{'thread_pool_max_threads'}, make_short(perc($stats{'Threadpool_threads'} + $stats{'Threadpool_idle_threads'}, $vars{'thread_pool_max_threads'})) |
| 1451 |
Running @>>>>>>>> of @>>>>> %Running: @>>>>> |
| 1452 |
$stats{'Threadpool_threads'}, $vars{'thread_pool_max_threads'}, make_short(perc($stats{'Threadpool_threads'}, $vars{'thread_pool_max_threads'})) |
| 1453 |
Idle @>>>>>>>> of @>>>>> %Idle: @>>>>> |
| 1454 |
$stats{'Threadpool_idle_threads'}, $vars{'thread_pool_max_threads'}, make_short(perc($stats{'Threadpool_idle_threads'}, $vars{'thread_pool_max_threads'})) |
| 1455 |
. |
| 1456 |
|
| 1457 |
format TAB = |
| 1458 |
|
| 1459 |
__ Aborted _____________________________________________________________ |
| 1460 |
Clients @>>>>>>>> @>>>>>/s |
| 1461 |
make_short($stats{'Aborted_clients'}), t($stats{'Aborted_clients'}) |
| 1462 |
Connects @>>>>>>>> @>>>>>/s |
| 1463 |
make_short($stats{'Aborted_connects'}), t($stats{'Aborted_connects'}) |
| 1464 |
|
| 1465 |
__ Bytes _______________________________________________________________ |
| 1466 |
Sent @>>>>>>>> @>>>>>/s |
| 1467 |
make_short($stats{'Bytes_sent'}), t($stats{'Bytes_sent'}) |
| 1468 |
Received @>>>>>>>> @>>>>>/s |
| 1469 |
make_short($stats{'Bytes_received'}), t($stats{'Bytes_received'}) |
| 1470 |
. |
| 1471 |
|
| 1472 |
format IB = |
| 1473 |
|
| 1474 |
__ InnoDB Buffer Pool __________________________________________________ |
| 1475 |
Usage @>>>>>> of @>>>>>> %Usage: @>>>>> |
| 1476 |
make_short($ib_bp_used, 1), make_short($ib_bp_total, 1), perc($ib_bp_used, $ib_bp_total) |
| 1477 |
Read hit @>>>>>% |
| 1478 |
$ib_bp_read_ratio; |
| 1479 |
Pages |
| 1480 |
Free @>>>>>>>> %Total: @>>>>> |
| 1481 |
make_short($stats{'Innodb_buffer_pool_pages_free'}), perc($stats{'Innodb_buffer_pool_pages_free'}, $stats{'Innodb_buffer_pool_pages_total'}) |
| 1482 |
Data @>>>>>>>> @>>>>> %Drty: @>>>>> |
| 1483 |
make_short($stats{'Innodb_buffer_pool_pages_data'}), perc($stats{'Innodb_buffer_pool_pages_data'}, $stats{'Innodb_buffer_pool_pages_total'}), perc($stats{'Innodb_buffer_pool_pages_dirty'}, $stats{'Innodb_buffer_pool_pages_data'}) |
| 1484 |
Misc @>>>>>>>> @>>>>> |
| 1485 |
$stats{'Innodb_buffer_pool_pages_misc'}, perc($stats{'Innodb_buffer_pool_pages_misc'}, $stats{'Innodb_buffer_pool_pages_total'}) |
| 1486 |
Latched @>>>>>>>> @>>>>> |
| 1487 |
$stats{'Innodb_buffer_pool_pages_latched'}, perc($stats{'Innodb_buffer_pool_pages_latched'}, $stats{'Innodb_buffer_pool_pages_total'}) |
| 1488 |
Reads @>>>>>>>> @>>>>>/s |
| 1489 |
make_short($stats{'Innodb_buffer_pool_read_requests'}), t($stats{'Innodb_buffer_pool_read_requests'}) |
| 1490 |
From disk @>>>>>>>> @>>>>>/s %Disk: @>>>>> |
| 1491 |
make_short($stats{'Innodb_buffer_pool_reads'}), t($stats{'Innodb_buffer_pool_reads'}), perc($stats{'Innodb_buffer_pool_reads'}, $stats{'Innodb_buffer_pool_read_requests'}) |
| 1492 |
Ahead Rnd @>>>>>>>> @>>>>>/s |
| 1493 |
$stats{'Innodb_buffer_pool_read_ahead_rnd'}, t($stats{'Innodb_buffer_pool_read_ahead_rnd'}) |
| 1494 |
# Ahead Sql @>>>>>>>> @>>>>>/s |
| 1495 |
#$stats{'Innodb_buffer_pool_read_ahead_seq'}, t($stats{'Innodb_buffer_pool_read_ahead_seq'}) |
| 1496 |
Writes @>>>>>>>> @>>>>>/s |
| 1497 |
make_short($stats{'Innodb_buffer_pool_write_requests'}), t($stats{'Innodb_buffer_pool_write_requests'}) |
| 1498 |
Wait Free @>>>>>>>> @>>>>>/s %Wait: @>>>>> |
| 1499 |
$stats{'Innodb_buffer_pool_wait_free'}, t($stats{'Innodb_buffer_pool_wait_free'}), perc($stats{'Innodb_buffer_pool_wait_free'}, $stats{'Innodb_buffer_pool_write_requests'}) |
| 1500 |
Flushes @>>>>>>>> @>>>>>/s |
| 1501 |
make_short($stats{'Innodb_buffer_pool_pages_flushed'}), t($stats{'Innodb_buffer_pool_pages_flushed'}) |
| 1502 |
. |
| 1503 |
|
| 1504 |
format IB_LOCK = |
| 1505 |
|
| 1506 |
__ InnoDB Lock _________________________________________________________ |
| 1507 |
Waits @>>>>>>>> @>>>>>/s |
| 1508 |
$stats{'Innodb_row_lock_waits'}, t($stats{'Innodb_row_lock_waits'}) |
| 1509 |
Current @>>>>>>>> |
| 1510 |
$stats{'Innodb_row_lock_current_waits'} |
| 1511 |
Time acquiring |
| 1512 |
Total @>>>>>>>> ms |
| 1513 |
$stats{'Innodb_row_lock_time'} |
| 1514 |
Average @>>>>>>>> ms |
| 1515 |
$stats{'Innodb_row_lock_time_avg'} |
| 1516 |
Max @>>>>>>>> ms |
| 1517 |
$stats{'Innodb_row_lock_time_max'} |
| 1518 |
. |
| 1519 |
|
| 1520 |
format IB_DPR = |
| 1521 |
|
| 1522 |
__ InnoDB Data, Pages, Rows ____________________________________________ |
| 1523 |
Data |
| 1524 |
Reads @>>>>>>>> @>>>>>/s |
| 1525 |
make_short($stats{'Innodb_data_reads'}), t($stats{'Innodb_data_reads'}) |
| 1526 |
Writes @>>>>>>>> @>>>>>/s |
| 1527 |
make_short($stats{'Innodb_data_writes'}), t($stats{'Innodb_data_writes'}) |
| 1528 |
fsync @>>>>>>>> @>>>>>/s |
| 1529 |
make_short($stats{'Innodb_data_fsyncs'}), t($stats{'Innodb_data_fsyncs'}) |
| 1530 |
Pending |
| 1531 |
Reads @>>>>>>>> |
| 1532 |
$stats{'Innodb_data_pending_reads'}, t($stats{'Innodb_data_pending_reads'}) |
| 1533 |
Writes @>>>>>>>> |
| 1534 |
$stats{'Innodb_data_pending_writes'}, t($stats{'Innodb_data_pending_writes'}) |
| 1535 |
fsync @>>>>>>>> |
| 1536 |
$stats{'Innodb_data_pending_fsyncs'}, t($stats{'Innodb_data_pending_fsyncs'}) |
| 1537 |
|
| 1538 |
Pages |
| 1539 |
Created @>>>>>>>> @>>>>>/s |
| 1540 |
make_short($stats{'Innodb_pages_created'}), t($stats{'Innodb_pages_created'}) |
| 1541 |
Read @>>>>>>>> @>>>>>/s |
| 1542 |
make_short($stats{'Innodb_pages_read'}), t($stats{'Innodb_pages_read'}) |
| 1543 |
Written @>>>>>>>> @>>>>>/s |
| 1544 |
make_short($stats{'Innodb_pages_written'}), t($stats{'Innodb_pages_written'}) |
| 1545 |
|
| 1546 |
Rows |
| 1547 |
Deleted @>>>>>>>> @>>>>>/s |
| 1548 |
make_short($stats{'Innodb_rows_deleted'}), t($stats{'Innodb_rows_deleted'}) |
| 1549 |
Inserted @>>>>>>>> @>>>>>/s |
| 1550 |
make_short($stats{'Innodb_rows_inserted'}), t($stats{'Innodb_rows_inserted'}) |
| 1551 |
Read @>>>>>>>> @>>>>>/s |
| 1552 |
make_short($stats{'Innodb_rows_read'}), t($stats{'Innodb_rows_read'}) |
| 1553 |
Updated @>>>>>>>> @>>>>>/s |
| 1554 |
make_short($stats{'Innodb_rows_updated'}), t($stats{'Innodb_rows_updated'}) |
| 1555 |
. |
| 1556 |
|
| 1557 |
format PAGECACHE_BUFF_MAX = |
| 1558 |
|
| 1559 |
__ Aria Pagecache ______________________________________________________ |
| 1560 |
Buffer used @>>>>>> of @>>>>>> %Used: @>>>>> |
| 1561 |
make_short($pagecache_buffer_used, 1), make_short($vars{'aria_pagecache_buffer_size'}, 1), perc($pagecache_buffer_used, $vars{'aria_pagecache_buffer_size'}) |
| 1562 |
. |
| 1563 |
|
| 1564 |
format PAGECACHE_BUFF_USAGE = |
| 1565 |
Current @>>>>>> %Usage: @>>>>> |
| 1566 |
make_short($pagecache_buffer_usage, 1), perc($pagecache_buffer_usage, $vars{'aria_pagecache_buffer_size'}) |
| 1567 |
. |
| 1568 |
|
| 1569 |
format PAGECACHE_RATIOS = |
| 1570 |
Write hit @>>>>>% |
| 1571 |
$pagecache_write_ratio |
| 1572 |
Read hit @>>>>>% |
| 1573 |
$pagecache_read_ratio |
| 1574 |
. |
| 1575 |
|
| 1576 |
format BINLOG = |
| 1577 |
|
| 1578 |
__ Binary Log Cache _____________________________________________________ |
| 1579 |
Disk use |
| 1580 |
Transactional @>>>>>% |
| 1581 |
perc($binlog_cache_ratio) |
| 1582 |
Non transactional @>>>>>% |
| 1583 |
perc($binlog_stmt_cache_ratio) |
| 1584 |
. |
| 1585 |
|
| 1586 |
format ROWS = |
| 1587 |
|
| 1588 |
__ Rows ________________________________________________________________ |
| 1589 |
Rows @>>>>>>>> @>>>>>/s |
| 1590 |
make_short($rows), t($rows) |
| 1591 |
Using idx @>>>>>>>> @>>>>>/s %Index: @>>>>> |
| 1592 |
make_short($rows_using_indexes), t($rows_using_indexes), perc($rows_using_indexes,$rows) |
| 1593 |
Rows/question @>>>>>> |
| 1594 |
make_short($rows/$questions) |
| 1595 |
. |