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