Debian Codegraph @

scripts/mytop.sh

raw ◀ ▶
Source reconstructed at patch state 0 of 2 — not the installed (fully patched) source.
2 Debian patches touch this file
1 #!/usr/bin/env perl
2 #
3 # $Id: mytop,v 1.99-maria8 2025/07/16 17:59:26 jweisbuch Exp $
4
5 =pod
6
7 =head1 NAME
8
9 mytop - display MariaDB/MySQL server performance info like `top'
10
11 =cut
12
13 ## most of the POD is at the bottom of the file
14
15 use 5.005;
16 use strict;
17 use warnings;
18 use DBI;
19 use Getopt::Long;
20 use Socket;
21 use List::Util qw(min max);
22 use File::Basename;
23
24 $main::VERSION = "1.99-maria8";
25 my $path_for_script = dirname($0);
26
27 $| = 1;
28 $0 = 'mytop';
29
30 my $WIN = ($^O eq 'MSWin32') ? 1 : 0;
31
32 ## Test for color support.
33
34 eval { require Term::ANSIColor; };
35
36 my $HAS_COLOR = $@ ? 0 : 1;
37
38 $HAS_COLOR = 0 if $WIN;
39
40 ## Test of Time::HiRes support
41
42 eval { require Time::HiRes };
43
44 my $HAS_TIME = $@ ? 0 : 1;
45
46 my $debug = 0;
47
48 ## Try to lower our priority (which, who, pri)
49
50 setpriority(0,0,10) unless $WIN;
51
52 ## Prototypes
53
54 sub Clear();
55 sub GetData();
56 sub GetQPS();
57 sub FullQueryInfo($);
58 sub Explain($);
59 sub PrintTable(@);
60 sub PrintHelp();
61 sub Sum(@);
62 sub commify($);
63 sub make_short($);
64 sub Hashes($);
65 sub Execute($);
66 sub StringOrRegex($);
67 sub GetInnoDBStatus();
68 sub GetCmdSummary();
69 sub GetShowVariables();
70 sub GetShowStatus();
71 sub cmd_s;
72 sub cmd_S;
73 sub cmd_q;
74
75 ## Default Config Values
76
77 my %config = (
78 batchmode => 0,
79 color => 1,
80 db => '',
81 database => '',
82 delay => 5,
83 filter_user => qr/.?/,
84 filter_db => qr/.?/,
85 filter_host => qr/.?/,
86 filter_state => qr/.?/,
87 header => 1,
88 help => 0,
89 host => 'localhost',
90 idle => 1,
91 long => 120,
92 long_nums => 0,
93 mode => 'top',
94 prompt => 0,
95 pass => '',
96 password => '',
97 port => 3306,
98 resolve => 0,
99 slow => 10, ## slow query time
100 socket => '',
101 sort => 1, ## default or reverse sort ("s")
102 user => 'root',
103 fullqueries => 0, ## shows untruncated queries
104 usercol_width => 8, ## User column width
105 dbcol_width => 9, ## DB column width
106 hide_progress => 0 ## hide the "%" column when available
107 );
108
109 my %qcache = (); ## The query cache--used for full query info support.
110 my %ucache = (); ## The user cache--used for full killing by user
111 my %dbcache = (); ## The db cache. This should be merged at some point.
112 my %statcache = (); ## The show status cache for GetShowStatus()
113
114 my (%STATUS, %OLD_STATUS); # header stuff.
115
116 my $CLEAR = $WIN ? '': `clear`;
117
118 ## Term::ReadKey values
119
120 my $RM_RESET = 0;
121 my $RM_NOBLKRD = 3; ## using 4 traps Ctrl-C :-(
122
123 ## Add options from .my.cnf first
124
125 my $my_print_defaults;
126 if (!defined($my_print_defaults=my_which("my_print_defaults")))
127 {
128 print "Warning: Can't find my_print_defaults. Please add it to your PATH!\n";
129 exit(1);
130 }
131
132 unshift @ARGV, split "\n", `$my_print_defaults client mytop`;
133
134 ## Read the user's config file, if it exists.
135
136 my $config = "$ENV{HOME}/.mytop";
137
138 if (-e $config)
139 {
140 if (open CFG, "<$config")
141 {
142 while (<CFG>)
143 {
144 next if /^\s*($|#)/; ## skip blanks and comments
145
146 chomp;
147
148 if (/(\S+)\s*=\s*(.*\S)/)
149 {
150 my ($k, $v) = ($1, $2);
151 if ($k =~ /^filter_/i) {
152 $config{lc $k} = StringOrRegex($v) if exists $config{lc $k};
153 } else {
154 $config{lc $k} = $v if exists $config{lc $k};
155 }
156 }
157 }
158 close CFG;
159 }
160 ## map database/password onto db/pass
161 ## short version gets precedence for historical reasons
162 $config{'db'} = $config{'database'} unless $config{'db'};
163 $config{'pass'} = $config{'password'} unless $config{'pass'};
164 }
165
166 ## Command-line args.
167
168 use vars qw($opt_foo);
169
170 Getopt::Long::Configure('no_ignore_case', 'bundling');
171
172 GetOptions(
173 "color!" => \$config{color},
174 "user|u=s" => \$config{user},
175 "pass|password|p=s" => \$config{pass},
176 "database|db|d=s" => \$config{db},
177 "host|h=s" => \$config{host},
178 "port|P=i" => \$config{port},
179 "socket|S=s" => \$config{socket},
180 "delay|s=i" => \$config{delay},
181 "batch|batchmode|b" => \$config{batchmode},
182 "header!" => \$config{header},
183 "idle|i!" => \$config{idle},
184 "resolve|r!" => \$config{resolve},
185 "prompt!" => \$config{prompt},
186 "long=i" => \$config{long},
187 "long_nums!" => \$config{long_nums},
188 "mode|m=s" => \$config{mode},
189 "slow=i" => \$config{slow},
190 "sort=s" => \$config{sort},
191 "fullqueries|L!" => \$config{fullqueries},
192 "usercol_width=i" => \$config{usercol_width},
193 "dbcol_width=i" => \$config{dbcol_width},
194 "hide_progress|a!" => \$config{hide_progress}
195 );
196
197 ## User may have put the port with the host.
198
199 if ($config{host} =~ s/:(\d+)$//)
200 {
201 $config{port} = $1;
202 }
203
204 ## Don't use Term::ReadKey unless running interactively.
205
206 if (not $config{batchmode})
207 {
208 require Term::ReadKey;
209 Term::ReadKey->import();
210 }
211
212 ## User may want to disable color.
213
214 if ($HAS_COLOR and not $config{color})
215 {
216 $HAS_COLOR = 0;
217 }
218
219 if ($HAS_COLOR)
220 {
221 import Term::ANSIColor ':constants';
222 }
223 else
224 {
225 *RESET = sub { };
226 *YELLOW = sub { };
227 *RED = sub { };
228 *MAGENTA = sub { };
229 *GREEN = sub { };
230 *BLUE = sub { };
231 *WHITE = sub { };
232 *BOLD = sub { };
233 }
234
235 my $RESET = RESET() || '';
236 my $YELLOW = YELLOW() || '';
237 my $RED = RED() || '';
238 my $MAGENTA = MAGENTA() || '';
239 my $GREEN = GREEN() || '';
240 my $BLUE = BLUE() || '';
241 my $WHITE = WHITE() || '';
242 my $BOLD = BOLD() || '';
243
244 ## Connect
245
246 my $dsn;
247
248 ## Socket takes precedence.
249 my $prefix= 'mysql';
250 if (eval {DBI->install_driver("MariaDB")}) {
251 $dsn = "DBI:MariaDB:database=$config{db};mariadb_read_default_group=mytop;";
252 $prefix= 'mariadb'
253 } else {
254 $dsn = "DBI:mysql:database=$config{db};mysql_read_default_group=mytop;";
255 }
256
257 if ($config{socket} and -S $config{socket})
258 {
259 $dsn .= "${prefix}_socket=$config{socket}";
260 }
261 elsif($config{host} eq "localhost")
262 {
263 $dsn .= "host=$config{host}";
264 }
265 else
266 {
267 $dsn .= "host=$config{host};port=$config{port}";
268 }
269
270 if ($config{prompt})
271 {
272 print "Password: ";
273 ReadMode(2);
274 chomp($config{pass} = <STDIN>);
275 ReadMode(0);
276 print "\n";
277 }
278
279 my $dbh = DBI->connect($dsn, $config{user}, $config{pass},
280 { PrintError => 0 });
281
282 if (not ref $dbh)
283 {
284 my $Error = <<EODIE
285 Cannot connect to MariaDB/MySQL server. Please check the:
286
287 * database you specified "$config{db}" (default is "")
288 * username you specified "$config{user}" (default is "root")
289 * password you specified "$config{pass}" (default is "")
290 * hostname you specified "$config{host}" (default is "localhost")
291 * port you specified "$config{port}" (default is 3306)
292 * socket you specified "$config{socket}" (default is "")
293
294 The options my be specified on the command-line or in a ~/.mytop or
295 ~/.my.cnf config file. See the manual (perldoc mytop) for details.
296
297 Here's the exact error from DBI. It might help you debug:
298
299 $DBI::errstr
300
301 EODIE
302 ;
303
304 die $Error;
305
306 }
307
308 ReadMode($RM_RESET) unless $config{batchmode};
309
310 ## Get static data
311
312 my $db_version;
313 my $db_release;
314 my $server = "MySQL";
315 my $have_query_cache;
316
317 my @variables = Hashes("SHOW VARIABLES");
318
319 foreach (@variables)
320 {
321 if ($_->{Variable_name} eq "version")
322 {
323 $db_version = $_->{Value};
324 $db_version =~ /^(\d+)/;
325 $db_release = $1;
326 $server = "MariaDB" if ($db_version =~ /maria/i);
327 # Get the version number only
328 $db_version = $1 if($db_version =~ m/(.*?)-/);
329 next;
330 }
331 if ($_->{Variable_name} eq "have_query_cache")
332 {
333 if ($_->{Value} ne 'NO')
334 {
335 $have_query_cache = 1;
336 }
337 else
338 {
339 $have_query_cache = 0;
340 }
341 next;
342 }
343 }
344
345 my ($has_is_processlist, $has_time_ms, $has_progress);
346 $has_is_processlist = $has_time_ms = $has_progress = 0;
347
348 ## Check if the server has the INFORMATION_SCHEMA.PROCESSLIST table
349 ## for backward compatibility
350 $has_is_processlist = Execute("SELECT /*mytop*/ 1 FROM INFORMATION_SCHEMA.TABLES
351 WHERE TABLE_SCHEMA = 'information_schema' AND
352 TABLE_NAME = 'PROCESSLIST';")->rows;
353 if ($has_is_processlist == 1)
354 {
355 ## Check if the server has the TIME_MS column on the I_S.PROCESSLIST table
356 ## If it is the case, it will fetch the query time with decimal precision
357 ## for queries that has been running for less than 10k seconds
358 $has_time_ms = Execute("SELECT /*mytop*/ 1 FROM INFORMATION_SCHEMA.COLUMNS
359 WHERE TABLE_SCHEMA = 'information_schema' AND
360 TABLE_NAME = 'PROCESSLIST' AND
361 COLUMN_NAME = 'TIME_MS';")->rows;
362 if ($has_time_ms == 1)
363 {
364 ## Check if the server has the STAGE column on the I_S.PROCESSLIST
365 ## table (MariaDB) to retreive query completion information
366 $has_progress = Execute("SELECT /*mytop*/ 1 FROM INFORMATION_SCHEMA.COLUMNS
367 WHERE TABLE_SCHEMA = 'information_schema' AND
368 TABLE_NAME = 'PROCESSLIST' AND
369 COLUMN_NAME = 'STAGE';")->rows;
370 }
371 }
372
373 #########################################################################
374 ##
375 ## The main loop
376 ##
377 #########################################################################
378
379 ReadMode($RM_NOBLKRD) unless $config{batchmode};
380
381 while (1)
382 {
383 my $key;
384
385 if ($config{mode} eq 'qps')
386 {
387 GetQPS();
388 $key = ReadKey(1);
389
390 next unless $key;
391
392 if ($key =~ /t/i)
393 {
394 $config{mode} = 'top';
395 }
396 if ($key =~ /q/)
397 {
398 cmd_q();
399 }
400 next;
401 }
402 if ($config{mode} eq 'top')
403 {
404 GetData();
405 last if $config{batchmode};
406 $key = ReadKey($config{delay});
407 next unless $key;
408 }
409 elsif ($config{mode} eq 'cmd')
410 {
411 GetCmdSummary();
412 last if $config{batchmode};
413 $key = ReadKey($config{delay});
414 next unless $key;
415 }
416 elsif ($config{mode} eq 'innodb')
417 {
418 GetInnoDBStatus();
419 last if $config{batchmode};
420 print "InnoDB Status [hit t to exit this mode or q to exit the application]\n";
421 $key = ReadKey($config{delay});
422 next unless $key;
423 }
424 elsif ($config{mode} eq 'status')
425 {
426 GetShowStatus();
427 last if $config{batchmode};
428 $key = ReadKey($config{delay});
429 next unless $key;
430 }
431
432 ##
433 ## keystroke command processing (if we get this far)
434 ##
435
436 if ($key eq '!')
437 {
438 Execute("STOP /*mytop*/ SLAVE;");
439 Execute("SET /*mytop*/ GLOBAL sql_slave_skip_counter=1");
440 Execute("START /*mytop*/ SLAVE");
441 }
442
443 # t - top
444
445 if ($key =~ /t/i)
446 {
447 $config{mode} = 'top';
448 }
449
450 ## q - quit
451
452 if ($key eq 'q')
453 {
454 cmd_q();
455 }
456
457 if ($key eq 'D')
458 {
459 require Data::Dumper;
460 print Data::Dumper::Dumper([\%config]);
461 ReadKey(0);
462 }
463
464 ## l - change long running hightling
465
466 if ($key eq 'l')
467 {
468 cmd_l();
469 next;
470 }
471
472 ## m - mode switch to qps
473
474 if ($key eq 'm')
475 {
476 $config{mode} = 'qps';
477 Clear() unless $config{batchmode};
478 print "Queries Per Second [hit t to exit this mode or q to exit the application]\n";
479 next;
480 }
481
482 ## c - mode switch to command summary
483
484 if ($key eq 'c')
485 {
486 $config{mode} = 'cmd';
487 Clear() unless $config{batchmode};
488 print "Command Summary\n";
489 next;
490 }
491
492 ## C - change Color on and off
493
494 if ($key eq 'C')
495 {
496 if ($HAS_COLOR)
497 {
498 $HAS_COLOR = 0;
499 }
500 else
501 {
502 $HAS_COLOR = 1;
503 }
504 }
505
506 ## s - seconds of delay
507
508 if ($key eq 's')
509 {
510 cmd_s();
511 next;
512 }
513
514 if ($key eq 'S')
515 {
516 cmd_S();
517 next;
518 }
519
520 ## R - resolve hostnames
521 if ($key eq 'R')
522 {
523 if ($config{resolve})
524 {
525 $config{resolve} = 0;
526 }
527 else
528 {
529 $config{resolve} = 1;
530 }
531 }
532
533 ## t - username based filter
534
535 if ($key eq 't')
536 {
537 ReadMode($RM_RESET);
538 print RED(), "Which state (blank for all, /.../ for regex): ", RESET();
539 $config{filter_state} = StringOrRegex(ReadLine(0));
540 ReadMode($RM_NOBLKRD);
541 next;
542 }
543
544 ## u - username based filter
545
546 if ($key eq 'u')
547 {
548 ReadMode($RM_RESET);
549 print RED(), "Which user (blank for all, /.../ for regex): ", RESET();
550 $config{filter_user} = StringOrRegex(ReadLine(0));
551 ReadMode($RM_NOBLKRD);
552 next;
553 }
554
555 ## d - database name based filter
556
557 if ($key eq 'd')
558 {
559 ReadMode($RM_RESET);
560 print RED(), "Which database (blank for all, /.../ for regex): ",
561 RESET();
562 $config{filter_db} = StringOrRegex(ReadLine(0));
563 ReadMode($RM_NOBLKRD);
564 next;
565 }
566
567 ## h - hostname based filter
568
569 if ($key eq 'h')
570 {
571 ReadMode($RM_RESET);
572 print RED(), "Which hostname (blank for all, /.../ for regex): ",
573 RESET();
574 $config{filter_host} = StringOrRegex(ReadLine(0));
575 ReadMode($RM_NOBLKRD);
576 next;
577 }
578
579 ## E - Show full Replication Error
580
581 if ($key eq 'E')
582 {
583 my ($data) = Hashes('SHOW /*mytop*/ SLAVE STATUS');
584 Clear();
585 print "Error is: $data->{Last_Error}\n";
586 print RED(), "-- paused. press any key to resume --", RESET();
587 ReadKey(0);
588 next;
589 }
590 ## F - remove all filters
591
592 if ($key eq 'F')
593 {
594 $config{filter_host} = qr/.?/;
595 $config{filter_db} = qr/.?/;
596 $config{filter_user} = qr/.?/;
597 $config{filter_state} = qr/.?/;
598 print RED(), "-- display unfiltered --", RESET();
599 sleep 1;
600 next;
601 }
602
603 ## p - pause
604
605 if ($key eq 'p')
606 {
607 print RED(), "-- paused. press any key to resume --", RESET();
608 ReadKey(0);
609 next;
610 }
611
612 ## i - idle toggle
613
614 if ($key =~ /i/)
615 {
616 if ($config{idle})
617 {
618 $config{idle} = 0;
619 $config{sort} = 1;
620 print RED(), "-- idle (sleeping) processed filtered --", RESET();
621 sleep 1;
622 }
623 else
624 {
625 $config{idle} = 1;
626 $config{sort} = 0;
627 print RED(), "-- idle (sleeping) processed unfiltered --", RESET();
628 sleep 1;
629 }
630 }
631
632 ## I - InnoDB status
633
634 if ($key =~ 'I')
635 {
636 $config{mode} = 'innodb';
637 Clear() unless $config{batchmode};
638 print "InnoDB Status\n";
639 next;
640 }
641
642 ## o - sort order
643
644 if ($key =~ /o/)
645 {
646 if ($config{sort})
647 {
648 $config{sort} = 0;
649 print RED(), "-- sort order reversed --", RESET();
650 sleep 1;
651 }
652 else
653 {
654 $config{sort} = 1;
655 print RED(), "-- sort order reversed --", RESET();
656 sleep 1;
657 }
658 }
659
660 ## ? - help
661
662 if ($key eq '?')
663 {
664 Clear();
665 PrintHelp();
666 ReadKey(0);
667 next;
668 }
669
670 ## k - kill
671
672 if ($key eq 'k')
673 {
674 ReadMode($RM_RESET);
675
676 print RED(), "Thread id to kill: ", RESET();
677 my $id = ReadLine(0);
678
679 $id =~ s/\s//g;
680
681 if ($id =~ /^\d+$/)
682 {
683 Execute("KILL /*mytop*/ $id");
684 }
685 else
686 {
687 print RED(), "-- invalid thread id --", RESET();
688 sleep 1;
689 }
690
691 ReadMode($RM_NOBLKRD);
692 next;
693 }
694
695 ## K - kill based on a username
696 if ($key =~ /K/)
697 {
698 ReadMode($RM_RESET);
699
700 print RED(), "User to kill: ", RESET();
701 my $user = ReadLine(0);
702
703 $user =~ s/\s//g;
704
705 if ($user =~ /^\S+$/)
706 {
707 for my $pid (keys %ucache)
708 {
709 next unless $ucache{$pid} eq $user;
710 Execute("KILL /*mytop*/ $pid");
711 select(undef, undef, undef, 0.2);
712 }
713 }
714 else
715 {
716 print RED(), "-- invalid thread id --", RESET();
717 sleep 1;
718 }
719
720 ReadMode($RM_NOBLKRD);
721 }
722
723 ## f - full info
724
725 if ($key =~ /f/)
726 {
727 ReadMode($RM_RESET);
728 print RED(), "Full query for which thread id: ", RESET();
729 my $id = ReadLine(0);
730 chomp $id;
731 FullQueryInfo($id);
732 ReadMode($RM_NOBLKRD);
733 print RED(), "-- paused. press any key to resume or (e) to explain --",
734 RESET();
735 my $key = ReadKey(0);
736
737 if ($key eq 'e')
738 {
739 Explain($id);
740 print RED(), "-- paused. press any key to resume --", RESET();
741 ReadKey(0);
742 }
743
744 next;
745 }
746
747 ## e - explain
748
749 if ($key =~ /e/)
750 {
751 ReadMode($RM_RESET);
752 print RED(), "Explain which query (id): ", RESET();
753 my $id = ReadLine(0);
754 chomp $id;
755 Explain($id);
756 ReadMode($RM_NOBLKRD);
757 print RED(), "-- paused. press any key to resume --", RESET();
758 ReadKey(0);
759 next;
760 }
761
762 ## r - reset status counters
763
764 if ($key =~ /r/)
765 {
766 Execute("FLUSH /*mytop*/ STATUS");
767 print RED(), "-- counters reset --", RESET();
768 sleep 1;
769 next;
770 }
771
772 ## H - header toggle
773
774 if ($key eq 'H')
775 {
776 if ($config{header})
777 {
778 $config{header} = 0;
779 }
780 else
781 {
782 $config{header}++;
783 }
784 }
785
786 ## # - magic debug key
787
788 if ($key eq '#')
789 {
790 $debug = 1;
791 }
792
793 if ($key eq 'V')
794 {
795 GetShowVariables();
796 print RED(), "-- paused. press any key to resume --", RESET();
797 ReadKey(0);
798 }
799
800 ## M - switch to SHOW STATUS mode
801
802 if ($key eq 'M')
803 {
804 $config{mode} = 'status';
805 }
806
807 ## L - full queries toggle
808
809 if ($key eq 'L')
810 {
811 if ($config{fullqueries})
812 {
813 $config{fullqueries} = 0;
814 print RED(), "-- full queries OFF --", RESET();
815 sleep 1;
816 }
817 else
818 {
819 $config{fullqueries} = 1;
820 print RED(), "-- full queries ON --", RESET();
821 sleep 1;
822 }
823 }
824
825 ## w - change columns width for the "User" and "Database" columns
826
827 if ($key eq 'w')
828 {
829 ReadMode($RM_RESET);
830 print RED(), "Width for the 'User' column (the actual value is ".
831 $config{usercol_width}."): ";
832 my $readWidth = ReadLine(0);
833 chomp($readWidth);
834 if (defined($readWidth) && $readWidth ne "")
835 {
836 if ($readWidth > 4 && $readWidth < 60)
837 {
838 $config{usercol_width} = $readWidth;
839 }
840 else
841 {
842 print RED(), "-- Invalid value ($readWidth), the previous value".
843 "has been kept, press a key to resume --";
844 ReadKey(0);
845 }
846 }
847 print RESET(), RED(), "Width for the 'DB' column (the actual value is ".
848 $config{dbcol_width}."): ", RESET();
849 $readWidth = ReadLine(0);
850 chomp($readWidth);
851 if (defined($readWidth) && $readWidth ne "")
852 {
853 if ($readWidth > 2 && $readWidth < 60)
854 {
855 $config{dbcol_width} = $readWidth
856 }
857 else
858 {
859 print RED(), "-- Invalid value ($readWidth), the previous value".
860 "has been kept, press a key to resume --", RESET();
861 ReadKey(0);
862 }
863 }
864 undef $readWidth;
865 ReadMode($RM_NOBLKRD);
866 next;
867 }
868
869 ## a - progress column toggle (the column is only displayed
870 ## if progress information are available from the processlist)
871
872 if ($key eq 'a')
873 {
874 if ($config{hide_progress})
875 {
876 $config{hide_progress} = 0;
877 print RED(), "-- progress display ON --", RESET();
878 sleep 1;
879 }
880 else
881 {
882 $config{hide_progress} = 1;
883 print RED(), "-- progress display OFF --", RESET();
884 sleep 1;
885 }
886 }
887
888 }
889
890 ReadMode($RM_RESET) unless $config{batchmode};
891
892 exit;
893
894 #######################################################################
895
896 sub Clear()
897 {
898 if (not $WIN)
899 {
900 print "$CLEAR"
901 }
902 else
903 {
904 print "\n" x 90; ## dumb hack for now. Anyone know how to
905 ## clear the screen in dos window on a Win32
906 ## system??
907 }
908 }
909
910 my $last_time;
911
912 sub GetData()
913 {
914 ## Get terminal info
915 my $now_time;
916 %qcache = (); ## recycle memory
917 %dbcache = ();
918
919 my ($width, $height, $wpx, $hpx, $lines_left);
920
921 if (not $config{batchmode})
922 {
923 ($width, $height, $wpx, $hpx) = GetTerminalSize();
924 $lines_left = $height - 2;
925 }
926 else
927 {
928 $height = 999_999; ## I hope you don't have more than that!
929 $lines_left = 999_999;
930 $width = 80;
931 }
932
933 ##
934 ## Header stuff.
935 ##
936 if ($config{header})
937 {
938 my @recs = "";
939 if ($db_release > 4)
940 {
941 @recs = Hashes("SHOW /*mytop*/ GLOBAL STATUS");
942 }
943 else
944 {
945 @recs = Hashes("SHOW /*mytop*/ STATUS");
946 }
947
948 ## if the server died or we lost connectivity
949 if (not @recs)
950 {
951 ReadMode($RM_RESET);
952 exit 1;
953 }
954
955 ## get high-res or low-res time
956 my ($t_delta);
957
958 if ($HAS_TIME)
959 {
960 $now_time = Time::HiRes::gettimeofday();
961 }
962 else
963 {
964 $now_time = time;
965 }
966
967 if ($last_time and $last_time != $now_time)
968 {
969 $t_delta = $now_time - $last_time;
970 }
971
972 %OLD_STATUS = %STATUS;
973 # Set some status that may not exist in all versions
974 $STATUS{Handler_tmp_write} = 0;
975 $STATUS{Handler_tmp_update} = 0;
976 $STATUS{Rows_tmp_read} = 0;
977
978 foreach my $ref (@recs)
979 {
980 my $key = $ref->{Variable_name};
981 my $val = $ref->{Value};
982
983 $STATUS{$key} = $val;
984 }
985
986 ## Compute Key Cache Hit Stats
987
988 $STATUS{Key_read_requests} ||= 1; ## can't divide by zero next
989
990 my $cache_hits_percent = (100-($STATUS{Key_reads}/$STATUS{Key_read_requests}) * 100);
991 $cache_hits_percent = sprintf("%2.2f",$cache_hits_percent);
992
993 ## Query Cache info for <= Ver. 4.1
994 ##
995 ## mysql> show status like 'qcache%';
996 ## +-------------------------+----------+
997 ## | Variable_name | Value |
998 ## +-------------------------+----------+
999 ## | Qcache_queries_in_cache | 81 |
1000 ## | Qcache_inserts | 4961668 |
1001 ## | Qcache_hits | 1374170 |
1002 ## | Qcache_not_cached | 5656249 |
1003 ## | Qcache_free_memory | 33164800 |
1004 ## | Qcache_free_blocks | 2 |
1005 ## | Qcache_total_blocks | 168 |
1006 ## +-------------------------+----------+
1007 ##
1008 ## Query Cache info for => Ver. 5.0
1009 ##
1010 ## mysql> show status like 'qcache%';
1011 ## +-------------------------+------------+
1012 ## | Variable_name | Value |
1013 ## +-------------------------+------------+
1014 ## | Qcache_free_blocks | 37652 |
1015 ## | Qcache_free_memory | 110289712 |
1016 ## | Qcache_hits | 1460617356 |
1017 ## | Qcache_inserts | 390563495 |
1018 ## | Qcache_lowmem_prunes | 6414172 |
1019 ## | Qcache_not_cached | 93002420 |
1020 ## | Qcache_queries_in_cache | 66558 |
1021 ## | Qcache_total_blocks | 192031 |
1022 ## +-------------------------+------------+
1023
1024 my $query_cache_hits = 0;
1025 my $query_cache_hits_per_sec = 0;
1026 my $now_query_cache_hits_per_sec = 0;
1027
1028 if ($have_query_cache)
1029 {
1030 $query_cache_hits = $STATUS{Qcache_hits};
1031 $query_cache_hits_per_sec = $STATUS{Qcache_hits} / $STATUS{Uptime};
1032
1033 if (defined $last_time and $last_time != $now_time)
1034 {
1035 my $q_delta = $STATUS{Qcache_hits} - $OLD_STATUS{Qcache_hits};
1036 $now_query_cache_hits_per_sec = sprintf "%.2f", $q_delta / $t_delta;
1037 }
1038 }
1039
1040 my $l;
1041 if (-e "/proc/loadavg")
1042 {
1043 ## To avoid warnings if the OS is not Linux
1044 open (my $fh, "<", "/proc/loadavg");
1045 ## Only the first 3 values are interresting
1046 $l = join(" ", (split /\s+/, <$fh>)[0..2]);
1047 close $fh;
1048 }
1049
1050 $last_time = $now_time;
1051
1052 ## Server Uptime in meaningful terms...
1053
1054 my $time = $STATUS{Uptime};
1055 my ($d,$h,$m,$s) = (0, 0, 0, 0);
1056
1057 $d += int($time / (60*60*24)); $time -= $d * (60*60*24);
1058 $h += int($time / (60*60)); $time -= $h * (60*60);
1059 $m += int($time / (60)); $time -= $m * (60);
1060 $s += int($time);
1061
1062 my $uptime = sprintf("%d+%02d:%02d:%02d", $d, $h, $m, $s);
1063
1064 ## Queries per second...
1065
1066 my $avg_queries_per_sec = sprintf("%.2f", $STATUS{Questions} / $STATUS{Uptime});
1067 my $num_queries = $STATUS{Questions};
1068
1069 my @t = localtime(time);
1070
1071 my $current_time = sprintf "[%02d:%02d:%02d]", $t[2], $t[1], $t[0];
1072
1073 my $host_width = length("$server $db_version on $config{host}");
1074 my $up_width = $width - $host_width - 1;
1075 Clear() unless $config{batchmode};
1076 print RESET();
1077
1078 printf "%-.${host_width}s %${up_width}s\n",
1079 "$server $db_version on $config{host}",
1080 defined($l) ? "load ($l) up $uptime $current_time" : "up $uptime $current_time";
1081 $lines_left--;
1082
1083
1084 printf " Queries: %-7s qps: %4.0f Slow: %7s Se/In/Up/De(%%): %02.0f/%02.0f/%02.0f/%02.0f\n",
1085 make_short( $STATUS{Questions} ), # q total
1086 $STATUS{Questions} / $STATUS{Uptime}, # qps, average
1087 make_short( $STATUS{Slow_queries} ), # slow
1088
1089 # hmm. a Qcache hit is really a select and should be counted.
1090 100 * ($STATUS{Com_select} + ($STATUS{Qcache_hits}||0) ) / $STATUS{Questions},
1091 100 * ($STATUS{Com_insert} + $STATUS{Com_replace} ) / $STATUS{Questions},
1092 100 * ($STATUS{Com_update} ) / $STATUS{Questions},
1093 100 * $STATUS{Com_delete} / $STATUS{Questions};
1094
1095 $lines_left--;
1096
1097 if ($t_delta)
1098 {
1099 my $q_diff = ( $STATUS{Questions} - $OLD_STATUS{Questions} );
1100 # print("q_diff: $STATUS{Questions} - $OLD_STATUS{Questions} / $t_delta = $q_diff\n");
1101
1102 printf(" Sorts: %6.0f qps now: %4.0f Slow qps: %3.1f Threads: %4.0f (%4.0f/%4.0f) %02.0f/%02.0f/%02.0f/%02.0f\n",
1103 ( $STATUS{Sort_rows} - $OLD_STATUS{Sort_rows} ) / $t_delta,
1104
1105 ( $STATUS{Questions} - $OLD_STATUS{Questions} ) / $t_delta,
1106 ( # slow now (qps)
1107 ($STATUS{Slow_queries} ) ?
1108 ( $STATUS{Slow_queries} - $OLD_STATUS{Slow_queries} ) / $t_delta :
1109 0
1110 ),
1111 $STATUS{Threads_connected},
1112 $STATUS{Threads_running},
1113 $STATUS{Threads_cached},
1114
1115 (100 * ($STATUS{Com_select} - $OLD_STATUS{Com_select} +
1116 ($STATUS{Qcache_hits}||0) - ($OLD_STATUS{Qcache_hits}||0)
1117 ) ) / ($q_diff ),
1118 (100 * ($STATUS{Com_insert} - $OLD_STATUS{Com_insert} +
1119 $STATUS{Com_replace} - $OLD_STATUS{Com_replace}
1120 ) ) / ($q_diff ),
1121 (100 * ($STATUS{Com_update} - $OLD_STATUS{Com_update}) ) / ($q_diff ),
1122 (100 * ($STATUS{Com_delete} - $OLD_STATUS{Com_delete}) ) / ($q_diff ),
1123 );
1124 }
1125 else
1126 {
1127 print "\n";
1128 }
1129 $lines_left--;
1130
1131 if ($have_query_cache and $STATUS{Com_select} and $query_cache_hits)
1132 {
1133 printf(" Cache Hits: %-5s Hits/s: %4.1f Hits now: %5.1f Ratio: ",
1134 make_short($STATUS{Qcache_hits}), # cache hits
1135 $STATUS{Qcache_hits} / $STATUS{Uptime}, # hits / sec
1136 ($t_delta) ? ($STATUS{Qcache_hits} - $OLD_STATUS{Qcache_hits}) / $t_delta : 0, # Hits Now
1137 );
1138
1139 my ($Ratio) = 100 * ($STATUS{Qcache_hits}) / ($STATUS{Qcache_hits} + $STATUS{Com_select} );
1140 if ($HAS_COLOR)
1141 {
1142 print YELLOW() if ($Ratio < 80.0);
1143 print RED() if ($Ratio < 50.0);
1144 print MAGENTA() if ($Ratio < 20.0);
1145 }
1146 printf("%4.1f%% ",$Ratio);
1147 if ($HAS_COLOR)
1148 {
1149 print RESET();
1150 }
1151
1152 print " Ratio now: ";
1153 my ($Ratio_now) = ($t_delta) ? # ratio now
1154 100 * ($STATUS{Qcache_hits} - $OLD_STATUS{Qcache_hits} ) /
1155 ( ($STATUS{Com_select} + $STATUS{Qcache_hits} -
1156 ($OLD_STATUS{Qcache_hits} + $OLD_STATUS{Com_select})
1157 ) || 1) : 0;
1158 if ($HAS_COLOR)
1159 {
1160 print GREEN() if ($Ratio_now >= 80.0);
1161 print YELLOW() if ($Ratio_now < 80.0);
1162 print RED() if ($Ratio_now < 50.0);
1163 print MAGENTA() if ($Ratio_now < 20.0);
1164 }
1165 printf("%4.1f%% \n",$Ratio_now);
1166 if ($HAS_COLOR)
1167 {
1168 print RESET();
1169 }
1170 $lines_left--;
1171 }
1172
1173 if ($t_delta)
1174 {
1175 my $rows_read;
1176 if (defined($STATUS{Rows_read}))
1177 {
1178 $rows_read= $STATUS{Rows_read} - $OLD_STATUS{Rows_read};
1179 }
1180 else
1181 {
1182 $rows_read=
1183 ($STATUS{Handler_read_first} + $STATUS{Handler_read_key} +
1184 $STATUS{Handler_read_next} + $STATUS{Handler_read_prev} +
1185 $STATUS{Handler_read_rnd} + $STATUS{Handler_read_rnd_next} -
1186 $OLD_STATUS{Handler_read_first} - $OLD_STATUS{Handler_read_key} -
1187 $OLD_STATUS{Handler_read_next} - $OLD_STATUS{Handler_read_prev} -
1188 $OLD_STATUS{Handler_read_rnd} - $OLD_STATUS{Handler_read_rnd_next});
1189 }
1190 printf(" Handler: (R/W/U/D) %5d/%5d/%5d/%5d Tmp: R/W/U: %5d/%5d/%5d\n",
1191 $rows_read/$t_delta,
1192 ($STATUS{Handler_write} - $OLD_STATUS{Handler_write}) /
1193 $t_delta,
1194 ($STATUS{Handler_update} - $OLD_STATUS{Handler_update}) /
1195 $t_delta,
1196 ($STATUS{Handler_delete} - $OLD_STATUS{Handler_delete}) /
1197 $t_delta,
1198 ($STATUS{Rows_tmp_read} - $OLD_STATUS{Rows_tmp_read}) /
1199 $t_delta,
1200 ($STATUS{Handler_tmp_write}
1201 -$OLD_STATUS{Handler_tmp_write})/$t_delta,
1202 ($STATUS{Handler_tmp_update} -
1203 $OLD_STATUS{Handler_tmp_update})/$t_delta);
1204 }
1205 else
1206 {
1207 print "\n";
1208 }
1209
1210 $lines_left--;
1211
1212 printf(" MyISAM Key Cache Efficiency: %2.1f%% Bps in/out: %5s/%5s ",
1213 $cache_hits_percent,
1214 make_short($STATUS{Bytes_received} / $STATUS{Uptime} ),
1215 make_short($STATUS{Bytes_sent} / $STATUS{Uptime}));
1216 printf("Now in/out: %5s/%5s",
1217 make_short(($STATUS{Bytes_received} - $OLD_STATUS{Bytes_received}) / $t_delta ),
1218 make_short(($STATUS{Bytes_sent} - $OLD_STATUS{Bytes_sent}) / $t_delta ))
1219 if ($t_delta);
1220 print "\n";
1221
1222 $lines_left--;
1223
1224 my ($data) = Hashes('SHOW /*mytop*/ GLOBAL VARIABLES LIKE "read_only"');
1225 if ($data->{Value} ne "OFF")
1226 {
1227 print RED() if ($HAS_COLOR) ;
1228 print " ReadOnly";
1229 RESET() if ($HAS_COLOR);
1230 }
1231
1232 ($data) = Hashes('SHOW /*mytop*/ SLAVE STATUS');
1233 if (defined($data->{Master_Host}))
1234 {
1235 if (defined($data->{Seconds_Behind_Master}))
1236 {
1237 if ($HAS_COLOR)
1238 {
1239 print GREEN();
1240 print YELLOW() if ($data->{Seconds_Behind_Master} > 60);
1241 print MAGENTA() if ($data->{Seconds_Behind_Master} > 360);
1242 }
1243 }
1244 print " Replication ";
1245 print "IO:$data->{Slave_IO_Running} ";
1246 print "SQL:$data->{Slave_SQL_Running} ";
1247 print RESET() if ($HAS_COLOR);
1248
1249 if (defined($data->{Seconds_Behind_Master}))
1250 {
1251 if ($HAS_COLOR)
1252 {
1253 print GREEN();
1254 print YELLOW() if ($data->{Seconds_Behind_Master} > 60);
1255 print MAGENTA() if ($data->{Seconds_Behind_Master} > 360);
1256 }
1257 print "Delay: $data->{Seconds_Behind_Master} sec.";
1258 }
1259 else
1260 {
1261 my $free = $width - 45;
1262 my $Err = substr $data->{Last_Error},0 ,$free;
1263 printf(" ERR: %-${free}s", $Err) if ($Err ne "");
1264 }
1265 print WHITE() if ($HAS_COLOR);
1266 print "\n";
1267 $lines_left--;
1268 }
1269 print "\n";
1270 }
1271
1272 if (not $config{batchmode} and not $config{header})
1273 {
1274 Clear();
1275 print RESET();
1276 }
1277
1278 ##
1279 ## Threads
1280 ##
1281
1282 my $proc_cmd; ## Query used to fetch the processlist
1283 my $time_format = "6d";
1284
1285 if ($has_is_processlist == 1)
1286 {
1287 if ($has_time_ms == 1)
1288 {
1289 $time_format = "6.6s";
1290 if ($has_progress == 1)
1291 {
1292 ## To have a computed value of "Progress" like the
1293 ## "SHOW PROCESSLIST" one, the Progress column of the query
1294 ## must be replaced by :
1295 ## "CASE WHEN Max_Stage < 2 THEN Progress ELSE
1296 ## (Stage-1)/Max_Stage*100+Progress/Max_Stage END AS Progress"
1297 $proc_cmd = "SELECT /*mytop*/ Id, User, Host, db, Command,
1298 CASE WHEN TIME > 10000 THEN Time ELSE
1299 ROUND(TIME_MS/1000, 1) END AS Time,
1300 State, Info, Progress, Stage, Max_Stage
1301 FROM INFORMATION_SCHEMA.PROCESSLIST
1302 WHERE ID != CONNECTION_ID();";
1303 }
1304 else
1305 {
1306 $proc_cmd = "SELECT /*mytop*/ Id, User, Host, db, Command,
1307 CASE WHEN TIME > 10000 THEN Time ELSE
1308 ROUND(TIME_MS/1000, 1) END AS Time,
1309 State, Info FROM INFORMATION_SCHEMA.PROCESSLIST
1310 WHERE ID != CONNECTION_ID();";
1311 }
1312 }
1313 else
1314 {
1315 $proc_cmd = "SELECT /*mytop*/ Id, User, Host, db, Command, Time,
1316 State, Info FROM INFORMATION_SCHEMA.PROCESSLIST
1317 WHERE ID != CONNECTION_ID();";
1318 }
1319 }
1320 else
1321 {
1322 $proc_cmd = "SHOW /*mytop*/ FULL PROCESSLIST;";
1323 }
1324
1325 ## Minimal width values for columns with a dynamic width
1326 if ($config{usercol_width} < 4) { $config{usercol_width} = 4; }
1327 if ($config{dbcol_width} < 2) { $config{dbcol_width} = 2; }
1328
1329 my @sz = (9, $config{usercol_width}, 15, $config{dbcol_width}, 6, 6, 8);
1330 if ($has_progress == 1 && !$config{hide_progress}) { push @sz, 5; };
1331 my $used = scalar(@sz) + Sum(@sz);
1332 undef(@sz);
1333
1334 ## If the terminal width <= 80, the state column will have a width of 6
1335 ## chars else it will be between 6 and 15 depending on the terminal width
1336 my $state = $width <= 80 ? 6 : int(min(6+($width-80)/3, 15));
1337 ## $free = The number of chars between the beginning of the "Query"
1338 ## column and the end of the line
1339 my $free = $width - $used - ($state - 6);
1340 my $format= "%9s %$config{usercol_width}s %15s %$config{dbcol_width}s %6s ";
1341 if ($has_progress == 1 && !$config{hide_progress}) { $format .= "%5s "; }
1342 $format .= "%6s %${state}s %-.${free}s\n";
1343
1344 my $format2 = "%9d %$config{usercol_width}.$config{usercol_width}s %15.15s %$config{dbcol_width}.$config{dbcol_width}s %${time_format} ";
1345 if ($has_progress == 1 && !$config{hide_progress}) { $format2 .= "%5.1f "; }
1346 $format2 .= "%6.6s %${state}.${state}s ";
1347 if ($config{fullqueries})
1348 {
1349 $format2 .= "%-${free}s\n";
1350 }
1351 else
1352 {
1353 $format2 .= "%-${free}.${free}s\n";
1354 }
1355
1356 print BOLD() if ($HAS_COLOR);
1357
1358 if ($has_progress == 1 && !$config{hide_progress})
1359 {
1360 printf $format,
1361 'Id','User','Host/IP','DB','Time', '%', 'Cmd', 'State', 'Query';
1362 }
1363 else
1364 {
1365 printf $format,
1366 'Id','User','Host/IP','DB','Time', 'Cmd', 'State', 'Query';
1367 }
1368
1369 print RESET() if ($HAS_COLOR);
1370
1371 ## Id User Host DB
1372 if ($has_progress == 1 && !$config{hide_progress})
1373 {
1374 printf $format,
1375 '--','----','-------','--','----', '-', '---', '-----', '----------';
1376 }
1377 else
1378 {
1379 printf $format,
1380 '--','----','-------','--','----', '---', '-----', '----------';
1381 }
1382
1383 $lines_left -= 2;
1384
1385 my @data = Hashes($proc_cmd);
1386
1387 foreach my $thread (@data)
1388 {
1389 last if not $lines_left;
1390
1391 ## Drop Domain Name, unless it looks like an IP address. If
1392 ## it's an IP, we'll strip the port number because it's rarely
1393 ## interesting.
1394
1395 my $is_ip = 0;
1396
1397 if ($thread->{Host} =~ /^(\d{1,3}\.){3}(\d{1,3})(:\d+)?$/)
1398 {
1399 $thread->{Host} =~ s/:.*$//;
1400 $is_ip = 1;
1401 }
1402 else
1403 {
1404 $thread->{Host} =~ s/^([^.]+).*/$1/;
1405 }
1406
1407 ## Otherwise, look up the IP (if resolve is set) and strip the
1408 ## name
1409 if ($is_ip and $config{resolve})
1410 {
1411 $thread->{Host} =~ s/:\d+$//;
1412 my $host = gethostbyaddr(inet_aton($thread->{Host}), AF_INET);
1413 if ($host)
1414 {
1415 ## Only the hostname part of the DNS is kept
1416 $host =~ s/^([^.]+).*/$1/;
1417 }
1418 $thread->{Host} = $host;
1419 }
1420
1421 ## Fix possible undefs
1422
1423 $thread->{db} ||= '';
1424 $thread->{Info} ||= '';
1425 $thread->{Time} ||= 0 ;
1426 $thread->{Id} ||= 0 ;
1427 $thread->{User} ||= '';
1428 $thread->{Command} ||= '';
1429 $thread->{Host} ||= '';
1430 $thread->{State} ||= "";
1431 $thread->{Progress} ||= 0;
1432
1433 ## Alter double hyphen comments so they don't break
1434 ## the query when newlines are removed - http://freshmeat.net/users/jerjones
1435 $thread->{Info} =~ s~\s--(.*)$~ /* $1 */ ~mg;
1436
1437 ## Normalize spaces -- mostly disabled for now. This can
1438 ## break EXPLAIN if you try to explain a mangled query. It
1439 ## may be re-enabled later as an option.
1440
1441 ## Replace newlines and carriage returns with a space
1442 $thread->{Info} =~ tr/\n\r/ /;
1443
1444 ## Leading space removal
1445 $thread->{Info} =~ s/^\s*//;
1446
1447 ## Strip non printing control symbols
1448 $thread->{Info} =~ tr/[[:cntrl:]]//;
1449
1450 ## Collpase whitespace
1451 $thread->{Info} =~ s/\s+/ /g;
1452
1453 ## Trailing space removal
1454 $thread->{Info} =~ s/\s$//;
1455
1456 ## Put the first letter of the query uppercase for a better readability
1457 ## with long State strings
1458 $thread->{Info} = ucfirst $thread->{Info};
1459
1460 ## Stow it in the cache
1461 $qcache{$thread->{Id}} = $thread->{Info};
1462 $dbcache{$thread->{Id}} = $thread->{db};
1463 $ucache{$thread->{Id}} = $thread->{User};
1464
1465 ## If Progress information is available and a multi-stage query is
1466 ## running, the actual stage and the total number of stages of the
1467 ## thread are shown at the beginning of the State column
1468 if ($has_progress == 1 && $thread->{Max_Stage} && $thread->{Max_Stage} > 1)
1469 {
1470 $thread->{State} = $thread->{Stage}."/".
1471 $thread->{Max_Stage}." ".$thread->{State};
1472 }
1473 }
1474
1475 ## Sort by idle time (closest thing to CPU usage I can think of).
1476
1477 my @sorted;
1478
1479 if (not $config{sort})
1480 {
1481 @sorted = sort { $a->{Time} <=> $b->{Time} } @data
1482 }
1483 else
1484 {
1485 @sorted = sort { $b->{Time} <=> $a->{Time} } @data
1486 }
1487
1488 foreach my $thread (@sorted)
1489 {
1490 ## Check to see if we can skip out.
1491 ## We skip out if we know the given line doesn't match.
1492
1493 next if (($thread->{Command} eq "Sleep")
1494 and
1495 (not $config{idle}));
1496
1497 next if (($thread->{Command} eq "Binlog Dump")
1498 and
1499 (not $config{idle}));
1500
1501 next if (($thread->{Command} eq "Daemon")
1502 and
1503 (not $config{idle}));
1504
1505 next if ($thread->{User} !~ $config{filter_user});
1506 next if ($thread->{db} !~ $config{filter_db});
1507 next if ($thread->{Host} !~ $config{filter_host});
1508 next if ($thread->{State} !~ $config{filter_state});
1509
1510 ## Otherwise, print.
1511
1512 my $smInfo;
1513
1514 if ($thread->{Info})
1515 {
1516 if ($config{fullqueries})
1517 {
1518 $smInfo = $thread->{Info};
1519 if (length($smInfo) > $free)
1520 {
1521 $lines_left -= int((length($smInfo) - $free)/$width) + 1;
1522 }
1523 }
1524 else
1525 {
1526 $smInfo = substr $thread->{Info}, 0, $free;
1527 }
1528 }
1529 # if ($thread->{State})
1530 # {
1531 # $smInfo = substr $thread->{State}, 0, $free;
1532 # }
1533 else
1534 {
1535 $smInfo = "";
1536 }
1537
1538 $lines_left--;
1539 if ($lines_left < 0)
1540 {
1541 print WHITE(), "-- Truncated query list -- ";
1542 last;
1543 }
1544
1545 if ($HAS_COLOR)
1546 {
1547 print YELLOW() if $thread->{Command} eq 'Query';
1548 print WHITE() if $thread->{Command} eq 'Sleep';
1549 print GREEN() if $thread->{Command} eq 'Connect';
1550 print BOLD() if $thread->{Time} > $config{slow};
1551 print MAGENTA() if $thread->{Time} > $config{long};
1552 }
1553
1554 if ($has_progress == 1 && !$config{hide_progress})
1555 {
1556 printf $format2,
1557 $thread->{Id}, $thread->{User}, $thread->{Host}, $thread->{db},
1558 $thread->{Time}, $thread->{Progress}, $thread->{Command},
1559 $thread->{State}, $smInfo;
1560 }
1561 else
1562 {
1563 printf $format2,
1564 $thread->{Id}, $thread->{User}, $thread->{Host}, $thread->{db},
1565 $thread->{Time}, $thread->{Command}, $thread->{State}, $smInfo;
1566 }
1567
1568 print RESET() if $HAS_COLOR;
1569 }
1570
1571 }
1572
1573 ###########################################################################
1574
1575 my $questions;
1576
1577 sub GetQPS()
1578 {
1579 my ($data) = Hashes('SHOW /*mytop*/ STATUS LIKE "Questions"');
1580 my $num = $data->{Value};
1581
1582 if (not defined $questions) ## first time?
1583 {
1584 $questions = $num;
1585 return;
1586 }
1587
1588 my $qps = $num - $questions;
1589 $questions = $num;
1590 print "$qps\n";
1591 }
1592
1593 ###########################################################################
1594
1595 sub GetInnoDBStatus()
1596 {
1597 if (not $config{pager})
1598 {
1599 if (not $config{pager} = my_which('less'))
1600 {
1601 $config{pager} = my_which('more');
1602 }
1603 }
1604
1605 my @data = Hashes("SHOW /*mytop*/ ENGINE INNODB STATUS");
1606
1607 open P, "|$config{pager}" or die "$!";
1608 print keys %{$data[0]};
1609 print $data[0]->{Status},"\n";
1610 close P;
1611 }
1612
1613 ###########################################################################
1614
1615 my %prev_data;
1616
1617 sub GetCmdSummary()
1618 {
1619 my ($width, $height, $wpx, $hpx, $lines_left);
1620
1621 if (not $config{batchmode})
1622 {
1623 ($width, $height, $wpx, $hpx) = GetTerminalSize();
1624
1625 $lines_left = $height - 2;
1626 }
1627 else
1628 {
1629 $height = 999_999; ## I hope you don't have more than that!
1630 $lines_left = 999_999;
1631 $width = 80;
1632 }
1633
1634 ## Variable_name and Value pairs come back...
1635 my @data = Hashes("SHOW /*mytop*/ STATUS LIKE 'Com\\_%'");
1636 my %cmd_data;
1637 my %cmd_delta;
1638 my %cmd_pct;
1639 my %cmd_delta_pct;
1640 my $total;
1641 my $delta_total;
1642
1643 for my $item (@data)
1644 {
1645 next unless $item->{Value};
1646 $item->{Variable_name} =~ s/^Com_//;
1647 $item->{Variable_name} =~ s/_/ /g;
1648 $cmd_data{$item->{Variable_name}} = $item->{Value};
1649 $total += $item->{Value};
1650 }
1651
1652 ## Populate other stats
1653
1654 for my $item (keys %cmd_data)
1655 {
1656 $cmd_delta{$item} = $cmd_data{$item} -
1657 ($prev_data{$item} || $cmd_data{$item} - 1);
1658
1659 $delta_total += $cmd_delta{$item};
1660
1661 $cmd_pct{$item} = int(($cmd_data{$item} / $total) * 100);
1662 }
1663
1664 for my $item (keys %cmd_data)
1665 {
1666 $cmd_delta_pct{$item} = int(($cmd_delta{$item} / $delta_total) * 100);
1667 }
1668
1669
1670 ## Display
1671
1672 Clear() unless $config{batchmode};
1673 print RESET();
1674 printf "%18s %10s %4s | %5s %4s\n", 'Command', 'Total', 'Pct', 'Last', 'Pct';
1675 printf "%18s %10s %4s | %5s %4s\n", '-------', '-----', '---', '----', '---';
1676 $lines_left -= 2;
1677
1678 for my $item (sort { $cmd_data{$b} <=> $cmd_data{$a} } keys %cmd_data)
1679 {
1680 printf "%18s %10d %4s | %5d %4s\n",
1681 $item,
1682 $cmd_data{$item},
1683 $cmd_pct{$item} . "%",
1684 $cmd_delta{$item},
1685 $cmd_delta_pct{$item} . "%";
1686
1687 last if not $lines_left;
1688 $lines_left -= 1;
1689 }
1690
1691 %prev_data = %cmd_data;
1692 }
1693
1694 ###########################################################################
1695
1696 sub GetShowVariables()
1697 {
1698 if (not $config{pager})
1699 {
1700 if (not $config{pager} = my_which('less'))
1701 {
1702 $config{pager} = my_which('more');
1703 }
1704 }
1705
1706 my @rows = Hashes("SHOW /*mytop*/ VARIABLES");
1707
1708 open P, "|$config{pager}" or die "$!";
1709
1710 for my $row (@rows)
1711 {
1712 my $name = $row->{Variable_name};
1713 my $value = $row->{Value};
1714 printf P "%32s: %s\n", $name, $value;
1715 }
1716
1717 close P;
1718 }
1719
1720 ###########################################################################
1721
1722 sub GetShowStatus()
1723 {
1724 Clear() unless $config{batchmode};
1725 my @rows = Hashes("SHOW /*mytop*/ STATUS");
1726
1727 printf "%32s %10s %10s Toggle idle with 'i'\n", 'Counter', 'Total', 'Change';
1728 printf "%32s %10s %10s\n", '-------', '-----', '------';
1729
1730 for my $row (@rows)
1731 {
1732 my $name = $row->{Variable_name};
1733 my $value = $row->{Value};
1734 my $old = $statcache{$name};
1735 my $delta = 0;
1736
1737 next if $name =~ m/^Com_/; ## skip Com_ stats
1738 next if $value =~ m/^[^0-9]*$/; ## skip non-numeric
1739
1740 ## TODO: if Qcache is off, we should skip Qcache_ values
1741
1742 if ($HAS_COLOR and defined $old and $old =~ /^\d/)
1743 {
1744 if ($value > $old)
1745 {
1746 print YELLOW();
1747 $delta = $value - $old;
1748 }
1749 elsif ($value < $old)
1750 {
1751 print RED();
1752 $delta = $value - $old;
1753 }
1754
1755 if (not $config{idle} and $value == $old)
1756 {
1757 ## filter unchanging stats, maybe
1758 print RESET();
1759 next;
1760 }
1761 }
1762
1763 printf "%32s: %10s %10s\n", $name, $value, $delta;
1764 print RESET() if $HAS_COLOR;
1765
1766 $statcache{$name} = $value;
1767 }
1768
1769 }
1770
1771 ###########################################################################
1772
1773 sub FullQueryInfo($)
1774 {
1775 my $id = shift;
1776
1777 if (not exists $qcache{$id} or not defined $qcache{$id})
1778 {
1779 print "*** Invalid id. ***\n";
1780 return;
1781 }
1782
1783 my $sql = $qcache{$id};
1784 print $CLEAR;
1785 print "Thread $id was executing following query:\n\n";
1786 print YELLOW(), $sql,"\n\n", RESET();
1787 }
1788
1789 ###########################################################################
1790
1791 sub Explain($)
1792 {
1793 my $id = shift;
1794
1795 if (not exists $qcache{$id} or not defined $qcache{$id})
1796 {
1797 print "*** Invalid id. ***\n";
1798 return;
1799 }
1800
1801 my $sql = $qcache{$id};
1802 my $db = $dbcache{$id};
1803
1804 Execute("USE /*mytop*/ $db");
1805 my @info = Hashes("EXPLAIN $sql");
1806 print $CLEAR;
1807 print "EXPLAIN $sql:\n\n";
1808 PrintTable(@info);
1809 }
1810
1811 ###########################################################################
1812
1813 sub PrintTable(@)
1814 {
1815 my $cnt = 1;
1816 my @cols = qw(table type possible_keys key key_len ref rows Extra);
1817
1818 for my $row (@_)
1819 {
1820 print "*** row $cnt ***\n";
1821 for my $key (@cols)
1822 {
1823 my $val = $row->{$key} || 'NULL';
1824 printf "%15s: %s\n", $key, $val;
1825 }
1826 $cnt++;
1827 }
1828 }
1829
1830 ###########################################################################
1831
1832 sub StringOrRegex($)
1833 {
1834 my $input = shift;
1835 chomp $input;
1836 if (defined $input)
1837 {
1838 ## regex, strip /.../ and use via qr//
1839 if ($input =~ m{^/} and $input =~ m{/$})
1840 {
1841 $input =~ s{^/}{} if $config{filter_user};
1842 $input =~ s{/$}{} if $config{filter_user};
1843 $input = qr/$input/;
1844 }
1845
1846
1847 ## reset to match anything
1848 elsif ($input eq '')
1849 {
1850 $input = qr/.*/;
1851 }
1852
1853 ## string, build a simple regex
1854 else
1855 {
1856 $input = '^' . $input . '$';
1857 $input = qr/$input/;
1858 }
1859 }
1860
1861 ## reset to match anything
1862 else
1863 {
1864 $input = qr/.*/;
1865 }
1866 return $input;
1867 }
1868
1869 ###########################################################################
1870
1871 sub cmd_l
1872 {
1873 ReadMode($RM_RESET);
1874
1875 print RED(), "Seconds for long queries: ", RESET();
1876 my $secs = ReadLine(0);
1877
1878 if ($secs =~ /^\s*(\d+)/)
1879 {
1880 $config{long} = $1;
1881 if ($config{long} < 1)
1882 {
1883 $config{long} = 1;
1884 }
1885 }
1886 ReadMode($RM_NOBLKRD);
1887 }
1888
1889 sub cmd_s
1890 {
1891 ReadMode($RM_RESET);
1892
1893 print RED(), "Seconds of Delay: ", RESET();
1894 my $secs = ReadLine(0);
1895
1896 if ($secs =~ /^\s*(\d+)/)
1897 {
1898 $config{delay} = $1;
1899 if ($config{delay} < 1)
1900 {
1901 $config{delay} = 1;
1902 }
1903 }
1904 ReadMode($RM_NOBLKRD);
1905 }
1906
1907 sub cmd_S
1908 {
1909 ReadMode($RM_RESET);
1910
1911 print RED(), "Seconds for Slow queries: ", RESET();
1912 my $secs = ReadLine(0);
1913
1914 if ($secs =~ /^\s*(\d+)/)
1915 {
1916 $config{slow} = $1;
1917 if ($config{slow} < 1)
1918 {
1919 $config{slow} = 1;
1920 }
1921 }
1922 ReadMode($RM_NOBLKRD);
1923 }
1924
1925 sub cmd_q
1926 {
1927 ReadMode($RM_RESET);
1928 print "\n";
1929 exit;
1930 }
1931
1932 sub trim($)
1933 {
1934 my $string = shift;
1935 $string =~ s/^\s+//;
1936 $string =~ s/\s+$//;
1937 return $string;
1938 }
1939
1940 ###########################################################################
1941
1942 sub PrintHelp()
1943 {
1944 my $help = qq[Help for mytop version $main::VERSION by Jeremy D. Zawodny <${YELLOW}Jeremy\@Zawodny.com${RESET}>
1945 with updates by Mark Grennan <${YELLOW}mark\@grennan.com${RESET}> and Jean Weisbuch <${YELLOW}jean\@phpnet.org${RESET}>
1946
1947 ? - display this screen
1948 # - debug mode (toggle)
1949 c - command summary view (based on Com_* counters)
1950 C - turn color on and off
1951 d - show only a specific database
1952 e - explain the query that a thread is running
1953 E - display current replication error
1954 f - show full query info for a given thread
1955 F - unfilter the display
1956 h - show only a specifc host's connections
1957 H - toggle the mytop header
1958 i - toggle the display of idle (sleeping) threads
1959 I - show innodb status
1960 k - kill a thread
1961 p - pause the display
1962 l - change long running queries hightlighing
1963 m - switch [mode] to qps (queries/sec) scrolling view
1964 M - switch [mode] to status
1965 o - reverse the sort order (toggle)
1966 q - quit
1967 r - reset the status counters (via FLUSH STATUS on your server)
1968 R - change reverse IP lookup
1969 s - change the delay between screen updates
1970 S - change slow query hightlighting
1971 t - switch to thread view (default)
1972 u - show only a specific user
1973 V - show variables
1974 : - enter a command (not yet implemented)
1975 ! - Skip an error that has stopped replications (at your own risk)
1976 L - show full queries (do not strip to terminal width)
1977 w - adjust the User and DB columns width
1978 a - toggle the progress column
1979
1980 Base version from ${GREEN}http://www.mysqlfanboy.com/mytop-3${RESET}
1981 This version comes as part of the ${GREEN}MariaDB${RESET} distribution.
1982 ];
1983
1984 print $help;
1985 }
1986
1987 sub Sum(@)
1988 {
1989 my $sum;
1990 while (my $val = shift @_) { $sum += $val; }
1991 return $sum;
1992 }
1993
1994 ## A useful routine from perlfaq
1995
1996 sub commify($)
1997 {
1998 local $_ = shift;
1999 return 0 unless defined $_;
2000 1 while s/^([-+]?\d+)(\d{3})/$1,$2/;
2001 return $_;
2002 }
2003
2004 ## Compact numeric representation (10,000 -> 10.0k)
2005
2006 sub make_short($)
2007 {
2008 my $number = shift;
2009 return commify($number) if $config{long_nums};
2010 my $n = 0;
2011 while ($number > 1_025) { $number /= 1024; $n++; };
2012 return sprintf "%.1f%s", $number, ('','k','M','G', 'T')[$n];
2013 }
2014
2015
2016 ## Run a query and return the records has an array of hashes.
2017
2018 sub Hashes($)
2019 {
2020 my $sql = shift;
2021 my @records;
2022
2023 if (my $sth = Execute($sql))
2024 {
2025 while (my $ref = $sth->fetchrow_hashref)
2026 {
2027 print "record\n" if $debug;
2028 push @records, $ref;
2029 }
2030 }
2031 return @records;
2032 }
2033
2034 ## Execute an SQL query and return the statement handle.
2035
2036 sub Execute($)
2037 {
2038 my $sql = shift;
2039
2040 $sql =~ s/\n/ /sg;
2041
2042 my $sth = $dbh->prepare($sql);
2043
2044 if (not $sth) { ReadMode($RM_RESET); die $DBI::errstr; }
2045
2046 my $ReturnCode = $sth->execute;
2047
2048 if (not $ReturnCode)
2049 {
2050 if ($debug)
2051 {
2052 print "query failed\n";
2053 sleep 10;
2054 }
2055 return undef;
2056 }
2057
2058 return $sth;
2059 }
2060
2061 ####
2062 #### my_which is used, because we can't assume that every system has the
2063 #### which command. my_which can take only one argument at a time.
2064 #### Return values: requested system command with the first found path,
2065 #### or undefined, if not found.
2066 ####
2067
2068 sub my_which
2069 {
2070 my ($command) = @_;
2071 my (@paths, $path);
2072
2073 return $command if (-f $command && -x $command);
2074
2075 ## Check first if this is a source distribution, then if this binary distribution and last in the path
2076
2077 push @paths, "./extra";
2078 push @paths, $path_for_script;
2079 push @paths, split(':', $ENV{'PATH'});
2080
2081 foreach $path (@paths)
2082 {
2083 $path .= "/$command";
2084 return $path if (-f $path && -x $path);
2085 }
2086 return undef();
2087 }
2088
2089 =pod
2090
2091 =head1 SYNOPSIS
2092
2093 B<mytop> [options]
2094
2095 =head1 AVAILABILITY
2096
2097 Base version from B<http://www.mysqlfanboy.com/mytop-3>.
2098
2099 This version comes as part of the B<MariaDB> distribution. See B<https://mariadb.org/>.
2100
2101 And older (the original) version B<mytop> is available from
2102 http://www.mysqlfanboy.com/mytop-3/ it B<might> also be on CPAN as
2103 well.
2104
2105 =head1 REQUIREMENTS
2106
2107 In order for B<mytop> to function properly, you must have the
2108 following:
2109
2110 * Perl 5.005 or newer
2111 * Getopt::Long
2112 * DBI and DBD::MariaDB or DBD::mysql
2113 * Term::ReadKey from CPAN
2114
2115 Most systems are likely to have all of those installed--except for
2116 Term::ReadKey. You will need to pick that up from the CPAN. You can
2117 pick up Term::ReadKey here:
2118
2119 http://search.cpan.org/search?dist=TermReadKey
2120
2121 And you obviously need access to a MariaDB server with the necessary
2122 security to run the I<SHOW PROCESSLIST> and I<SHOW STATUS> commands.
2123
2124 If you are a Windows user, using ActiveState's Perl, you can use PPM
2125 (the Perl Package Manager) to install the MariaDB/MySQL and Term::ReadKey
2126 modules.
2127
2128 =head2 Optional Color Support
2129
2130 In additon, if you want a color B<mytop> (recommended), install
2131 Term::ANSIColor from the CPAN:
2132
2133 http://search.cpan.org/search?dist=ANSIColor
2134
2135 Once you do, B<mytop> will automatically use it. However, color is not
2136 yet working on Windows. Patches welcome. :-)
2137
2138 =head2 Optional Hi-Res Timing
2139
2140 If you want B<mytop> to provide more accurate real-time
2141 queries-per-second statistics, install the Time::HiRes module from
2142 CPAN. B<mytop> will automatically notice that you have it and use it
2143 rather than the standard timing mechanism.
2144
2145 =head2 Platforms
2146
2147 B<mytop> is known to work on:
2148
2149 * Linux (2.2.x, 2.4.x)
2150 * FreeBSD (2.2, 3.x, 4.x)
2151 * Mac OS X
2152 * BSDI 4.x
2153 * Solaris 2.x
2154 * Windows NT 4.x (ActivePerl)
2155
2156 If you find that it works on another platform, please let me
2157 know. Given that it is all Perl code, I expect it to be rather
2158 portable to Unix and Unix-like systems. Heck, it I<might> even work on
2159 Win32 systems.
2160
2161 =head1 DESCRIPTION
2162
2163 Help is always welcome in improving this software. Feel free to
2164 contact the author (see L<"AUTHOR"> below) with bug reports, fixes,
2165 suggestions, and comments. Additionally L<"BUGS"> will provide a list
2166 of things this software is not able to do yet.
2167
2168 Having said that, here are the details on how it works and what you can
2169 do with it.
2170
2171 =head2 The Basics
2172
2173 B<mytop> was inspired by the system monitoring tool B<top>. I
2174 routinely use B<top> on Linux, FreeBSD, and Solaris. You are likely to
2175 notice features from each of them here.
2176
2177 B<mytop> will connect to a MariaDB server and periodically run the
2178 I<SHOW PROCESSLIST> and I<SHOW STATUS> commands and attempt to
2179 summarize the information from them in a useful format.
2180
2181 =head2 The Display
2182
2183 The B<mytop> display screen is really broken into two parts. The top 4
2184 lines (header) contain summary information about your MariaDB
2185 server. For example, you might see something like:
2186
2187 MariaDB 10.5.0 on localhost load (3.89 3.86 3.91) up 7+23:56:31 [16:33:01]
2188 Queries: 353.4M qps: 531 Slow: 4.5k Se/In/Up/De(%): 87/02/02/00
2189 Sorts: 2390 qps now: 651 Slow qps: 0.0 Threads: 11 ( 1/ 13) 88/01/03/00
2190 Handler: (R/W/U/D) 82138/ 5884/ 20/ 1 Tmp: R/W/U: 13623/29501/ 79
2191 MyISAM Key Cache Efficiency: 99.9% Bps in/out: 157.4k/ 2.2M Now in/out: 554.8k/ 2.6M
2192
2193 The first line identifies the hostname of the server (localhost) and
2194 the version of MariaDB it is running. The right hand side shows the
2195 uptime of the MariaDB server process in days+hours:minutes:seconds
2196 format (much like FreeBSD's top) as well as the current time.
2197
2198 The second line displays the total number of queries the server has
2199 processed, the average number of queries per second, the number of
2200 slow queries, and the percentage of Select, Insert, Update, and Delete
2201 queries.
2202
2203 The third real-time values. First is the number of queries per second,
2204 then the number of slow queries, followed by query precentages (like
2205 on the previous line).
2206
2207 And the fourth line displays key buffer efficiency (how often keys are
2208 read from the buffer rather than disk) and the number of bytes that
2209 MariaDB has sent and received, both over all and in the last cycle.
2210
2211 You can toggle the header by hitting B<H> when running B<mytop>.
2212
2213 The second part of the display lists as many threads as can fit on
2214 screen. By default they are sorted according to their idle time (least
2215 idle first). The display looks like:
2216
2217 Id User Host Dbase Time Cmd Query or State
2218 -- ---- ---- ----- ---- --- --------------
2219 61 jzawodn localhost music 0 Query show processlist
2220
2221 As you can see, the thread id, username, host from which the user is
2222 connecting, database to which the user is connected, number of seconds
2223 of idle time, the command the thread is executing, and the query info
2224 are all displayed.
2225
2226 Often times the query info is what you are really interested in, so it
2227 is good to run B<mytop> in an xterm that is wider than the normal 80
2228 columns if possible.
2229
2230 The thread display color-codes the threads if you have installed color
2231 support. The current color scheme only works well in a window with a
2232 dark (like black) background. The colors are selected according to the
2233 C<Command> column of the display:
2234
2235 Query - Yellow
2236 Sleep - White
2237 Connect - Green
2238 Slow - Bright
2239 Long - Magenta
2240
2241 Those are purely arbitrary and will be customizable in a future
2242 release. If they annoy you just start B<mytop> with the B<--nocolor>
2243 flag or adjust your config file appropriately.
2244
2245 =head2 Arguments
2246
2247 B<mytop> handles long and short command-line arguments. Not all
2248 options have both long and short formats, however. The long arguments
2249 have two dashes `--'. Short arguments only have one '-'.
2250
2251 =over
2252
2253 =item B<-u> or B<--user> username
2254
2255 Username to use when logging in to the MariaDB server. Default: ``B<root>''.
2256
2257 =item B<-p> or B<--pass> or B<--password> I<password>
2258
2259 Password to use when logging in to the MariaDB server. Default: none.
2260
2261 WARNING: This is insecure as the password is visible for anyone.
2262 See B<--prompt> instead!
2263
2264 =item B<-h> or B<--host> I<hostname>[B<:>I<port>]
2265
2266 Hostname of the MariaDB server. The hostname may be followed by an
2267 option port number. Note that the port is specified separate from the
2268 host when using a config file. Default: ``B<localhost>''.
2269
2270 =item B<--port> or B<-P> I<port>
2271
2272 If you're running MariaDB on a non-standard port, use this to specify
2273 the port number. Default: B<3306>.
2274
2275 =item B<-s> or B<--delay> I<seconds>
2276
2277 How long between display refreshes. Default: B<5>
2278
2279 =item B<-d> or B<--db> or B<--database> I<database>
2280
2281 Use if you'd like B<mytop> to connect to a specific database by
2282 default. Default: none.
2283
2284 =item B<-b> or B<--batch> or B<--batchmode>
2285
2286 In batch mode, mytop runs only once, does not clear the screen, and
2287 places no limit on the number of lines it will print. This is suitable
2288 for running periodically (perhaps from B<cron>) to capture the
2289 information into a file for later viewing. You might use batch mode in
2290 a CGI script to occasionally display your MariaDB server status on the
2291 web.
2292
2293 Default: unset.
2294
2295 =item B<-S> or B<--socket> I</path/to/socket>
2296
2297 If you're running B<mytop> on the same host as MariaDB/MySQL, you may wish to
2298 have it use the MariaDB/MySQL socket directly rather than a standard TCP/IP
2299 connection. If you do,just specify one.
2300
2301 Note that specifying a socket will make B<mytop> ignore any host
2302 and/or port that you might have specified. If the socket does not
2303 exist (or the file specified is not a socket), this option will be
2304 ignored and B<mytop> will use the hostname and port number instead.
2305
2306 Default: none.
2307
2308 =item B<--header> or B<--noheader>
2309
2310 Specify if you want the header to display or not. You can toggle this
2311 with the B<h> key while B<mytop> is running.
2312
2313 Default: header.
2314
2315 =item B<--color> or B<--nocolor>
2316
2317 Specify if you want a color display. This has no effect if you don't
2318 have color support available.
2319
2320 Default: If you have color support, B<mytop> will try color unless you
2321 tell it not to.
2322
2323 =item B<-i> or B<--idle> or B<--noi> or B<--noidle>
2324
2325 Specify if you want idle (sleeping) threads to appear in the list. If
2326 sleeping threads are omitted, the default sorting order is reversed so
2327 that the longest running queries appear at the top of the list.
2328
2329 Default: idle.
2330
2331 =item B<--prompt> or B<--noprompt>
2332
2333 Specify if you want to be prompted to type in your database password.
2334 This provides a little bit more security since it not only prevents
2335 the password from viewable in a process list, but also doesn't require
2336 the password to be stored in plain text in your C<~/.mytop> config file.
2337 You will B<only> be prompted if a password has not been specified in
2338 your config file or through another command line option.
2339
2340 Default: noprompt.
2341
2342 =item B<--resolve>
2343
2344 If you have skip-resolve set on MariaDB (to keep it from doing a reverse
2345 DNS lookup on each inbound connection), mytop can replace IP addresses
2346 with hostnames but toggling this option.
2347
2348 Default: noresolve
2349
2350 =item B<--long> or B<--nolong>
2351
2352 For large numbers print all digits (e.g. 10.000) instead of using a more
2353 compact approximation (e.g. 10.0k).
2354
2355 Default: nolong.
2356
2357 =item B<-m> or B<--mode> I<mode>
2358
2359 Specify initial mode B<qps>(queries/second), B<top>(overview),
2360 B<cmd>(command summary), B<innodb>(InnoDB status) or B<status>().
2361
2362 Default: B<top>
2363
2364 =item B<--sort> or B<--nosort>
2365
2366 Reverse sort order from ascending to descending using Idle time.
2367
2368 Default: nosort.
2369
2370
2371 =back
2372
2373 Command-line arguments will always take precedence over config file
2374 options. That happens because the config file is read I<BEFORE> the
2375 command-line arguments are applied.
2376
2377 =head2 Config File
2378
2379 Instead of always using bulky command-line parameters, you can also
2380 use a config files for the default value of your options.
2381
2382 mytop will first read the [client] and [mytop] sections from your
2383 my.cnf files. After that it will read the (C<~/.mytop>) file from your
2384 home directory (if present). These are read I<before> any of your
2385 command-line arguments are processed, so your command-line arguments
2386 will override directives in the config file.
2387
2388
2389 Here is a sample config file C<~/.mytop> which implements the defaults
2390 described above.
2391
2392 user=root
2393 pass=
2394 host=localhost
2395 db=test
2396 delay=5
2397 port=3306
2398 slow=10
2399 socket=
2400 batchmode=0
2401 header=1
2402 color=1
2403 idle=1
2404 long=120
2405
2406 Using a config file will help to ensure that your database password
2407 isn't visible to users on the command-line. Just make sure that the
2408 permissions on C<~/.mytop> are such that others cannot read it (unless
2409 you want them to, of course).
2410
2411 You may have white space on either side of the C<=> in lines of the
2412 config file.
2413
2414 =head2 Shortcut Keys
2415
2416 The following keys perform various actions while B<mytop> is
2417 running. Those which have not been implemented are listed as
2418 such. They are included to give the user idea of what is coming.
2419
2420 =over
2421
2422 =item B<?>
2423
2424 Display help.
2425
2426 =item B<c>
2427
2428 Show "command counters" based on the Com_* values in SHOW STATUS.
2429 This is a new feature. Feedback welcome.
2430
2431 =item B<C>
2432
2433 Turn display color on and off. Default is on.
2434
2435 =item B<d>
2436
2437 Show only threads connected to a particular database.
2438
2439 =item B<f>
2440
2441 Given a thread id, display the entire query that thread was (and still
2442 may be) running.
2443
2444 =item B<F>
2445
2446 Disable all filtering (host, user, and db).
2447
2448 =item B<h>
2449
2450 Only show queries from a particular host.
2451
2452 =item B<H>
2453
2454 Toggle the header display. You can also specify either C<header=0> or
2455 C<header=1> in your config file to set the default behavior.
2456
2457 =item B<i>
2458
2459 Toggle the display of idle (sleeping) threads. If sleeping threads are
2460 filtered, the default sorting order is reversed so that the longest
2461 running queries appear at the top of the list.
2462
2463 =item B<I>
2464
2465 Switch to InnoDB Status mode. The output of "SHOW ENGINE INNODB STATUS" will
2466 be displayed every cycle. In a future version, this may actually
2467 summarize that data rather than producing raw output.
2468
2469 =item B<k>
2470
2471 Kill a thread.
2472
2473 =item B<m>
2474
2475 Toggle modes. Currently this switches from `top' mode to `qps'
2476 (Queries Per Second Mode). In this mode, mytop will write out one
2477 integer per second. The number written reflects the number of queries
2478 executed by the server in the previous one second interval.
2479
2480 More modes may be added in the future.
2481
2482 =item B<o>
2483
2484 Reverse the default sort order.
2485
2486 =item B<p>
2487
2488 Pause display.
2489
2490 =item B<q>
2491
2492 Quit B<mytop>
2493
2494 =item B<r>
2495
2496 Reset the server's status counters via a I<FLUSH STATUS> command.
2497
2498 =item B<R>
2499
2500 Togle IP reverse lookup. Default is on.
2501
2502 =item B<s>
2503
2504 Change the sleep time (number of seconds between display refreshes).
2505
2506 =item B<S>
2507
2508 Set the number of seconds a query will need to run before it is
2509 considered old and will be highlighted.
2510
2511 =item B<u>
2512
2513 Show only threads owned by a giver user.
2514
2515 =back
2516
2517 The B<s> key has a command-line counterpart: B<-s>.
2518
2519 The B<h> key has two command-line counterparts: B<--header> and
2520 B<--noheader>.
2521
2522 =head1 BUGS
2523
2524 This is more of a BUGS + WishList.
2525
2526 Some performance information is not available when talking to a
2527 version 3.22.x MySQL server. Additional information (about threads
2528 mostly) was added to the output of I<SHOW STATUS> in MySQL 3.23.x and
2529 B<mytop> makes use of it. If the information is not available, you
2530 will simply see zeros where the real numbers should be.
2531
2532 Simply running this program will increase your overall counters (such
2533 as the number of queries run). But you may or may not view that as a
2534 bug.
2535
2536 B<mytop> consumes too much CPU time when running (verified on older
2537 versions of Linux and FreeBSD). It's likely a problem related to
2538 Term::ReadKey. I haven't had time to investigate yet, so B<mytop> now
2539 automatically lowers its priority when you run it. You may also think
2540 about running B<mytop> on another workstation instead of your database
2541 server. However, C<mytop> on Solaris does B<not> have this problem.
2542 Newer versions of Linux and FreeBSD seem to have fixed this.
2543
2544 You can't specify the maximum number of threads to list. If you have
2545 many threads and a tall xterm, B<mytop> will always try to display as
2546 many as it can fit.
2547
2548 The size of most of the columns in the display has a small maximum
2549 width. If you have fairly long database/user/host names the display
2550 may appear odd. I have no good idea as to how best to deal with that
2551 yet. Suggestions are welcome.
2552
2553 It'd be nice if you could just add B<mytop> configuration directives
2554 in your C<my.cnf> file instead of having a separate config file.
2555
2556 You should be able to specify the columns you'd like to see in the
2557 display and the order in which they appear. If you only have one
2558 username that connects to your database, it's probably not worth
2559 having the User column appear, for example.
2560
2561 =head1 AUTHOR
2562
2563 mytop was developed and is maintained by Jeremy D. Zawodny
2564 ([email protected]).
2565
2566 If you wish to e-mail me regarding this software, B<PLEASE> subscribe
2567 to the B<mytop> mailing list. See the B<mytop> homepage for details.
2568
2569 =head1 DISCLAIMER
2570
2571 While I use this software in my job at Yahoo!, I am solely responsible
2572 for it. Yahoo! does not necessarily support this software in any
2573 way. It is merely a personal idea which happened to be very useful in
2574 my job.
2575
2576 =head1 SEE ALSO
2577
2578 Please check the MariaDB manual if you're not sure where some of the
2579 output of B<mytop> is coming from.
2580
2581 =head1 COPYRIGHT
2582
2583 Copyright (C) 2000-2010, Jeremy D. Zawodny.
2584
2585 =head1 CREDITS
2586
2587 Fix a bug. Add a feature. See your name here!
2588
2589 Many thanks go to these fine folks:
2590
2591 =over
2592
2593 =item Jean Weisbuch ([email protected])
2594
2595 Added --fullqueries and --sort options, dynamic user and database columns
2596 width, reading of .my.cnf, state/progress column that can be disabled
2597 dynamically (when available) and various small fixes.
2598
2599 =item Michael "Monty" Widenius <[email protected]>
2600
2601 Fixed a couple of minor bugs that gave warnings on startup.
2602 Added support for MariaDB (show MariaDB at top and % done).
2603 Cut long server version names to display width.
2604 Made 'State' length dynamic.
2605
2606 =item Mark Grennan ([email protected]) www.linuxfangoy.com
2607
2608 Added updates for MySQL 5.x. Added 'S' (slow) highlighting.
2609 Added 'C' to turn on and off Color. Added 'l' command to change
2610 color for long running queries. Fixed a few documentation issues.
2611 Monitors Slave status. Added color to Queue hit ratio.
2612 Added number of rows sorted per second.
2613 Created release 1.7.
2614
2615 =item Sami Ahlroos ([email protected])
2616
2617 Suggested the idle/noidle stuff.
2618
2619 =item Jan Willamowius ([email protected])
2620
2621 Minor bug report. Documentation fixes.
2622
2623 =item Alex Osipov ([email protected])
2624
2625 Long command-line options, Unix socket support.
2626
2627 =item Stephane Enten ([email protected])
2628
2629 Suggested batch mode.
2630
2631 =item Richard Ellerbrock ([email protected])
2632
2633 Bug reports and usability suggestions.
2634
2635 =item William R. Mattil ([email protected])
2636
2637 Bug report about empty passwords not working.
2638
2639 =item Benjamin Pflugmann ([email protected])
2640
2641 Suggested -P command-line flag as well as other changes.
2642
2643 =item Justin Mecham <[email protected]>
2644
2645 Suggested setting $0 to `mytop'.
2646
2647 =item Thorsten Kunz <[email protected]>
2648
2649 Provided a fix for cases when we try remove the domain name from the
2650 display even if it is actually an IP address.
2651
2652 =item Sasha Pachev <[email protected]>
2653
2654 Provided the idea of real-time queries per second in the main display.
2655
2656 =item Paul DuBois <[email protected]>
2657
2658 Pointed out some option-handling bugs.
2659
2660 =item Mike Wexler <[email protected]>
2661
2662 Suggested that we don't mangle (normalize) whitespace in query info by
2663 default.
2664
2665 =item Mark Zweifel <[email protected]>
2666
2667 Make the --idle command-line argument negatable.
2668
2669 =item Axel Schwenke <[email protected]>
2670
2671 Noticed the incorrect formula for query cache hit percentages in
2672 version 1.2.
2673
2674 =item Steven Roussey <[email protected]>
2675
2676 Supplied a patch to help filter binary junk in queries so that
2677 terminals don't freak out.
2678
2679 =item Jon R. Luini <[email protected]>
2680
2681 Supplied a patch that formed the basis for C<--prompt> support.
2682 Sean Leach <[email protected]> submitted a similar patch.
2683
2684 =item Yogish Baliga <[email protected]>
2685
2686 Supplied a patch that formed the basis for C<--resolve> support.
2687
2688 =item Per Andreas Buer <[email protected]>
2689
2690 Supplied an excellent patch to tidy up the top display. This includes
2691 showing most values in short form, such as 10k rather than 10000.
2692
2693 =back
2694
2695 See the Changes file on the B<mytop> distribution page for more
2696 details on what has changed.
2697
2698 =head1 LICENSE
2699
2700 B<mytop> is licensed under the GNU General Public License version
2701 2. For the full license information, please visit
2702 http://www.gnu.org/copyleft/gpl.html
2703
2704 =cut
2705
2706 __END__