Debian Codegraph @

debian/additions/mariadb-report

raw
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 .