31
31
my $WIN = ($^O eq 'MSWin32' ? 1 : 0);
33
33
my %mycnf; # ~/.my.cnf
34
$mycnf{'user'} = $ENV{USER};
36
34
my ($tmpfile_fh, $tmpfile);
37
35
my ($stat_name, $stat_val, $stat_label);
38
36
my ($major, $minor, $patch, $x); # MySQL version
39
37
my (%stats, %vars); # SHOW STATUS, SHOW VARIABLES
40
my (%DMS_vals, %Com_vals);
38
my (%DMS_vals, %Com_vals, %ib_vals);
42
40
my ($questions, $key_read_ratio, $key_write_ratio, $dms);
43
41
my ($key_cache_block_size, $key_buffer_used, $key_buffer_usage);
44
42
my ($qc_mem_used, $qc_hi_r, $qc_ip_r); # Query Cache
43
my ($need_myisam_vals, $need_innodb_vals);
44
my ($ib_bp_used, $ib_bp_total, $ib_bp_read_ratio);
68
71
show_help_and_exit() if $op{'help'};
70
$op{'host'} = 'localhost' if !have_op 'host';
72
$op{'socket'} ||= '/var/run/mysqld/mysqld.sock';
74
73
get_user_mycnf() unless $op{'no-mycnf'};
76
$mycnf{'user'} = $op{'user'} if have_op 'user';
75
# Command line options override ~/.my.cnf
76
$mycnf{'host'} = $op{'host'} if have_op 'host';
77
$mycnf{'port'} = $op{'port'} if have_op 'port';
78
$mycnf{'socket'} = $op{'socket'} if have_op 'socket';
79
$mycnf{'user'} = $op{'user'} if have_op 'user';
81
# Default values if nothing else
82
$mycnf{'host'} ||= 'localhost';
83
$mycnf{'port'} ||= 3306;
84
$mycnf{'socket'} ||= '/var/run/mysqld/mysqld.sock'; # Debian default
85
$mycnf{'user'} ||= $ENV{'USER'};
78
87
if(exists $op{'password'})
87
96
else { $mycnf{'pass'} = $op{'password'}; } # Use password given on command line
90
if($op{'all'} and not defined $op{'com'}) { $op{'com'} = 3; };
99
if($op{'all'} and not defined $op{'com'}) { $op{'com'} = 3; }
91
100
if(defined $op{'com'} and $op{'com'} == 0) { $op{'com'} = 3; }
93
102
# Connect to MySQL
94
if(!have_op 'infile') {
95
$dbh = DBI->connect("dbi:mysql::$op{host};port=$op{port};mysql_socket=$op{socket}", $mycnf{'user'}, $mycnf{'pass'}) or die;
103
if(!have_op 'infile')
107
if(-S $mycnf{'socket'} && !have_op 'host') { $dsn = "DBI:mysql:mysql_socket=$mycnf{socket}"; }
108
else { $dsn = "DBI:mysql:host=$mycnf{host};port=$mycnf{port}"; }
110
$dbh = DBI->connect($dsn, $mycnf{'user'}, $mycnf{'pass'}) or die;
98
113
# The report is written to a tmp file first.
99
114
# Later it will be moved to $op{'outfile'} or emailed $op{'email'} if needed.
100
115
($tmpfile_fh, $tmpfile) = tempfile() or die("Can't open temporary file for writing: $!\n");
102
# Get status values and server system variables from MySQL or -infile
117
# Determine if we need MyISAM, InnoDB, or both vals, then
118
# get vals and system vars from MySQL or infile, then
119
# set vals for our own vars
105
$questions = $stats{'Questions'};
106
$key_read_ratio = sprintf "%.2f", ($stats{'Key_read_requests'} ? $stats{'Key_reads'} / $stats{'Key_read_requests'} : 0);
107
$key_write_ratio = sprintf "%.2f", ($stats{'Key_write_requests'} ? $stats{'Key_writes'} / $stats{'Key_write_requests'} : 0);
109
$key_cache_block_size = (defined $vars{'key_cache_block_size'} ? $vars{'key_cache_block_size'} : 1024);
110
$key_buffer_used = $stats{'Key_blocks_used'} * $key_cache_block_size;
112
if(defined $stats{'Key_blocks_unused'}) { # MySQL 4.1.2+
113
$key_buffer_usage = $vars{'key_buffer_size'} - ($stats{'Key_blocks_unused'} * $key_cache_block_size);
115
else { $key_buffer_usage = -1; }
117
# Data Manipulation Statements: http://dev.mysql.com/doc/mysql/en/Data_Manipulation.html
119
SELECT => $stats{'Com_select'},
120
INSERT => $stats{'Com_insert'} + $stats{'Com_insert_select'},
121
REPLACE => $stats{'Com_replace'} + $stats{'Com_replace_select'},
122
UPDATE => $stats{'Com_update'} + (exists $stats{'Com_update_multi'} ? $stats{'Com_update_multi'} : 0),
123
DELETE => $stats{'Com_delete'} + (exists $stats{'Com_delete_multi'} ? $stats{'Com_delete_multi'} : 0)
126
$dms = $DMS_vals{SELECT} + $DMS_vals{INSERT} + $DMS_vals{REPLACE} + $DMS_vals{UPDATE} + $DMS_vals{DELETE};
122
set_myisam_vals() if $need_myisam_vals;
123
set_ib_vals() if $need_innodb_vals;
129
127
select $tmpfile_fh;
130
$~ = 'KEY_BUFF_MAX', write;
131
if($key_buffer_usage != -1) { $~ = 'KEY_BUFF_USAGE', write }
132
$~ = 'KEY_RATIOS', write;
133
if($op{'dtq'} or $op{'all'}) { write_DTQ(); }
134
$~ = 'SLOW_DMS', write;
135
if($op{'dms'} or $op{'all'}) { write_DMS(); }
136
if($op{'com'} or $op{'all'}) { write_Com(); }
137
if($op{'sas'} or $op{'all'}) { $~ = 'SAS', write; }
138
if($op{'qcache'} or $op{'all'}) { write_qcache(); }
139
$~ = 'REPORT_END', write;
140
$~ = 'TAB', write if $op{'tab'};
128
$~ = 'MYSQL_TIME', write;
129
if($need_myisam_vals)
131
$~ = 'KEY_BUFF_MAX', write;
132
if($key_buffer_usage != -1) { $~ = 'KEY_BUFF_USAGE', write }
133
$~ = 'KEY_RATIOS', write;
134
if($op{'dtq'} or $op{'all'}) { write_DTQ(); }
135
$~ = 'SLOW_DMS', write;
136
if($op{'dms'} or $op{'all'}) { write_DMS(); }
137
if($op{'com'} or $op{'all'}) { write_Com(); }
138
if($op{'sas'} or $op{'all'}) { $~ = 'SAS', write; }
139
if($op{'qcache'} or $op{'all'}) { write_qcache(); }
140
$~ = 'REPORT_END', write;
141
if($op{'tab'} or $op{'all'}) { $~ = 'TAB', write; }
143
write_InnoDB() if $need_innodb_vals;
142
144
close $tmpfile_fh and select STDOUT;
144
146
email_report($tmpfile) if have_op 'email';
192
194
--sas Show SELECT and Sort report
193
195
--tab Show Thread, Aborts, and Bytes reports
194
196
--qcache Show Query Cache report
195
--all Show --dms --com 3 --sas --qcache
197
--innodb Show InnoDB report
198
--innodb-only Show only InnoDB report (hide all other reports)
199
--dpr Show Data, Pages, Rows report in InnoDB report
200
--all Show --dtq --dms --com 3 --sas --tab --qcache --innodb --dpr
196
201
--infile FILE Read status values from FILE instead of MySQL
197
202
--outfile FILE Write report to FILE
198
203
--email ADDRESS Email report to ADDRESS (doesn't work on Windows)
211
216
open MYCNF, "$ENV{HOME}/.my.cnf" or return;
212
217
while(<MYCNF>) { $mycnf{$1} = $2 if /^(.+?)\s*=\s*"?(.+?)"?\s*$/; }
218
$mycnf{'pass'} ||= $mycnf{'password'} if exists $mycnf{'password'};
224
$need_myisam_vals = 1;
225
$need_innodb_vals = 1; # This could be set to 0 later in get_vals_vars()
227
$need_myisam_vals = 0, return if have_op 'id-only';
228
$need_innodb_vals = 0, return if (!$op{'id'} && !$op{'id-only'} && !$op{'all'});
216
231
sub get_vals_vars
218
if(not defined $op{'infile'})
233
if(!have_op 'infile')
222
# Determine MySQL version
223
$query = $dbh->prepare("SHOW VARIABLES LIKE 'version';");
225
@row = $query->fetchrow_array();
227
($major, $minor, $patch) = ($row[1] =~ /(\d{1,2})\.(\d{1,2})\.(\d{1,2})/);
229
if($major == 5 && (($minor == 0 && $patch >= 2) || $minor > 0))
231
$query = $dbh->prepare("SHOW GLOBAL STATUS;");
233
else { $query = $dbh->prepare("SHOW STATUS;"); }
235
239
# Get status values
240
if($major >= 5 && (($minor == 0 && $patch >= 2) || $minor > 0)) {
241
$query = $dbh->prepare("SHOW GLOBAL STATUS;");
244
$query = $dbh->prepare("SHOW STATUS;");
245
$need_innodb_vals = 0;
236
247
$query->execute();
237
248
while(@row = $query->fetchrow_array()) { $stats{$row[0]} = $row[1]; }
268
280
# Explicit var = val (e.g. key_buffer_size = 128M)
269
281
$vars{$1} = ($3 ? $2 * 1024 * 1024 : $2) and next if(/^\s*(\w+)\s*=\s*([0-9.]+)(M*)\s*$/);
271
# print "Unrecognized line in infile: $_\n";
283
# print "Unrecognized line in infile: $_\n";
291
sub get_MySQL_version
295
($major, $minor, $patch) = ($vars{'version'} =~ /(\d{1,2})\.(\d{1,2})\.(\d{1,2})/);
301
$query = $dbh->prepare("SHOW VARIABLES LIKE 'version';");
303
@row = $query->fetchrow_array();
304
($major, $minor, $patch) = ($row[1] =~ /(\d{1,2})\.(\d{1,2})\.(\d{1,2})/);
310
$questions = $stats{'Questions'};
312
$key_read_ratio = sprintf "%.3f",
313
($stats{'Key_read_requests'} ?
314
$stats{'Key_reads'} / $stats{'Key_read_requests'} :
317
$key_write_ratio = sprintf "%.3f",
318
($stats{'Key_write_requests'} ?
319
$stats{'Key_writes'} / $stats{'Key_write_requests'} :
322
$key_cache_block_size = (defined $vars{'key_cache_block_size'} ?
323
$vars{'key_cache_block_size'} :
326
$key_buffer_used = $stats{'Key_blocks_used'} * $key_cache_block_size;
328
if(defined $stats{'Key_blocks_unused'}) { # MySQL 4.1.2+
329
$key_buffer_usage = $vars{'key_buffer_size'} -
330
($stats{'Key_blocks_unused'} * $key_cache_block_size);
332
else { $key_buffer_usage = -1; }
334
# Data Manipulation Statements: http://dev.mysql.com/doc/refman/5.0/en/data-manipulation.html
336
SELECT => $stats{'Com_select'},
337
INSERT => $stats{'Com_insert'} + $stats{'Com_insert_select'},
338
REPLACE => $stats{'Com_replace'} + $stats{'Com_replace_select'},
339
UPDATE => $stats{'Com_update'} +
340
(exists $stats{'Com_update_multi'} ? $stats{'Com_update_multi'} : 0),
341
DELETE => $stats{'Com_delete'} +
342
(exists $stats{'Com_delete_multi'} ? $stats{'Com_delete_multi'} : 0)
345
$dms = $DMS_vals{SELECT} + $DMS_vals{INSERT} + $DMS_vals{REPLACE} + $DMS_vals{UPDATE} + $DMS_vals{DELETE};
350
$ib_bp_used = ($stats{'Innodb_buffer_pool_pages_total'} -
351
$stats{'Innodb_buffer_pool_pages_free'}) *
352
$stats{'Innodb_page_size'};
354
$ib_bp_total = $stats{'Innodb_buffer_pool_pages_total'} * $stats{'Innodb_page_size'};
356
$ib_bp_read_ratio = sprintf "%.3f",
357
($stats{'Innodb_buffer_pool_read_requests'} ?
358
$stats{'Innodb_buffer_pool_reads'} / $stats{'Innodb_buffer_pool_read_requests'} :
277
362
sub sec_to_dhms # Seconds to days hours:minutes:seconds
299
384
return "$d $h:$m:$s";
302
# Copied from mytop (http://jeremy.zawodny.com/mysql/mytop/) and modified a little
305
my ($number, $kb) = @_;
389
my ($number, $kb, $d) = @_;
309
if(defined $kb) { while ($number > 1023) { $number /= 1024; $n++; }; }
395
if($kb) { while ($number > 1023) { $number /= 1024; $n++; }; }
310
396
else { while ($number > 999) { $number /= 1000; $n++; }; }
312
$short = sprintf "%.2f%s", $number, ('','k','M','G','T')[$n];
398
$short = sprintf "%.${d}f%s", $number, ('','k','M','G','T')[$n];
313
399
if($short =~ /^(.+)\.(00)$/) { return $1; } # 12.00 -> 12 but not 12.00k -> 12k
500
615
make_short($questions), t($questions)
503
# Distribution of Total Questions invoked by -dtq or -all
505
619
@<<<<<<< @>>>>>>>> @>>>>>/s @>>>>>> @>>>>>
506
620
$stat_name, make_short($stat_val), t($stat_val), $stat_label, perc($stat_val, $questions)
509
# Second part of main report
510
623
format SLOW_DMS =
511
624
Slow @>>>>>>>> @>>>>>/s @>>>>>> @>>>>> %DMS: @>>>>>
512
625
make_short($stats{'Slow_queries'}), t($stats{'Slow_queries'}), ($op{'dtq'} || $op{'all'} ? '' : '%Total:'), perc($stats{'Slow_queries'}, $questions), perc($stats{'Slow_queries'}, $dms)
514
627
make_short($dms), t($dms), perc($dms, $questions)
517
# Data Manipulation Statements format invoked by -dms or -all
519
631
@<<<<<<< @>>>>>>>> @>>>>>/s @>>>>> @>>>>>
520
632
$stat_name, make_short($stat_val), t($stat_val), perc($stat_val, $questions), perc($stat_val, $dms)
523
# Top Com_ format invoked by -com or -all
525
636
Com_ @>>>>>>>> @>>>>>/s @>>>>>
526
637
make_short($stat_val), t($stat_val), perc($stat_val, $questions)
603
710
make_short($stats{'Created_tmp_files'}), t($stats{'Created_tmp_files'})
606
# Threads, Aborts, and Bytes format invoked by -tab
609
715
__ Threads _____________________________________________________________
610
716
Running @>>>>>>>> of @>>>
611
717
$stats{'Threads_running'}, $stats{'Threads_connected'}
612
Cache @>>>>>>>> %Hit: @>>>>>
613
$stats{'Threads_cached'}, (100 - perc($stats{'Threads_created'}, $stats{'Connections'}))
718
Cached @>>>>>>>> of @>>> %Hit: @>>>>>
719
$stats{'Threads_cached'}, $vars{'thread_cache_size'}, make_short(100 - perc($stats{'Threads_created'}, $stats{'Connections'}))
614
720
Created @>>>>>>>> @>>>>>/s
615
721
make_short($stats{'Threads_created'}), t($stats{'Threads_created'})
616
722
Slow @>>>>>>>> @>>>>>/s
625
731
__ Bytes _______________________________________________________________
626
732
Sent @>>>>>>>> @>>>>>/s
627
make_short($stats{'Bytes_sent'}), make_short(t($stats{'Bytes_sent'}))
733
make_short($stats{'Bytes_sent'}), t($stats{'Bytes_sent'})
628
734
Received @>>>>>>>> @>>>>>/s
629
make_short($stats{'Bytes_received'}), make_short(t($stats{'Bytes_received'}))
735
make_short($stats{'Bytes_received'}), t($stats{'Bytes_received'})
740
__ InnoDB Buffer Pool __________________________________________________
741
Usage @>>>>>> of @>>>>>> %Used: @>>>>>
742
make_short($ib_bp_used, 1), make_short($ib_bp_total, 1), perc($ib_bp_used, $ib_bp_total)
746
Free @>>>>>>>> %Total: @>>>>>
747
make_short($stats{'Innodb_buffer_pool_pages_free'}), perc($stats{'Innodb_buffer_pool_pages_free'}, $stats{'Innodb_buffer_pool_pages_total'})
748
Data @>>>>>>>> @>>>>> %Drty: @>>>>>
749
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'})
750
Misc @>>>>>>>> @>>>>>
751
$stats{'Innodb_buffer_pool_pages_misc'}, perc($stats{'Innodb_buffer_pool_pages_misc'}, $stats{'Innodb_buffer_pool_pages_total'})
752
Latched @>>>>>>>> @>>>>>
753
$stats{'Innodb_buffer_pool_pages_latched'}, perc($stats{'Innodb_buffer_pool_pages_latched'}, $stats{'Innodb_buffer_pool_pages_total'})
754
Reads @>>>>>>>> @>>>>>/s
755
make_short($stats{'Innodb_buffer_pool_read_requests'}), t($stats{'Innodb_buffer_pool_read_requests'})
756
From file @>>>>>>>> @>>>>>/s @>>>>>
757
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'})
758
Ahead Rnd @>>>>>>>> @>>>>>/s
759
$stats{'Innodb_buffer_pool_read_ahead_rnd'}, t($stats{'Innodb_buffer_pool_read_ahead_rnd'})
760
Ahead Sql @>>>>>>>> @>>>>>/s
761
$stats{'Innodb_buffer_pool_read_ahead_seq'}, t($stats{'Innodb_buffer_pool_read_ahead_seq'})
762
Writes @>>>>>>>> @>>>>>/s
763
make_short($stats{'Innodb_buffer_pool_write_requests'}), t($stats{'Innodb_buffer_pool_write_requests'})
764
Flushes @>>>>>>>> @>>>>>/s
765
make_short($stats{'Innodb_buffer_pool_pages_flushed'}), t($stats{'Innodb_buffer_pool_pages_flushed'})
766
Wait Free @>>>>>>>> @>>>>>/s
767
$stats{'Innodb_buffer_pool_wait_free'}, t($stats{'Innodb_buffer_pool_wait_free'})
772
__ InnoDB Lock _________________________________________________________
773
Waits @>>>>>>>> @>>>>>/s
774
$stats{'Innodb_row_lock_waits'}, t($stats{'Innodb_row_lock_waits'})
776
$stats{'Innodb_row_lock_current_waits'}
779
$stats{'Innodb_row_lock_time'}
781
$stats{'Innodb_row_lock_time_avg'}
783
$stats{'Innodb_row_lock_time_max'}
788
__ InnoDB Data, Pages, Rows ____________________________________________
790
Reads @>>>>>>>> @>>>>>/s
791
make_short($stats{'Innodb_data_reads'}), t($stats{'Innodb_data_reads'})
792
Writes @>>>>>>>> @>>>>>/s
793
make_short($stats{'Innodb_data_writes'}), t($stats{'Innodb_data_writes'})
794
fsync @>>>>>>>> @>>>>>/s
795
make_short($stats{'Innodb_data_fsyncs'}), t($stats{'Innodb_data_fsyncs'})
798
$stats{'Innodb_data_pending_reads'}, t($stats{'Innodb_data_pending_reads'})
800
$stats{'Innodb_data_pending_writes'}, t($stats{'Innodb_data_pending_writes'})
802
$stats{'Innodb_data_pending_fsyncs'}, t($stats{'Innodb_data_pending_fysncs'})
805
Created @>>>>>>>> @>>>>>/s
806
make_short($stats{'Innodb_pages_created'}), t($stats{'Innodb_pages_created'})
807
Read @>>>>>>>> @>>>>>/s
808
make_short($stats{'Innodb_pages_read'}), t($stats{'Innodb_pages_read'})
809
Written @>>>>>>>> @>>>>>/s
810
make_short($stats{'Innodb_pages_written'}), t($stats{'Innodb_pages_written'})
813
Deleted @>>>>>>>> @>>>>>/s
814
make_short($stats{'Innodb_rows_deleted'}), t($stats{'Innodb_rows_deleted'})
815
Inserted @>>>>>>>> @>>>>>/s
816
make_short($stats{'Innodb_rows_inserted'}), t($stats{'Innodb_rows_inserted'})
817
Read @>>>>>>>> @>>>>>/s
818
make_short($stats{'Innodb_rows_read'}), t($stats{'Innodb_rows_read'})
819
Updated @>>>>>>>> @>>>>>/s
820
make_short($stats{'Innodb_rows_updated'}), t($stats{'Innodb_rows_updated'})