1
# This program is copyright 2011 Percona Inc.
2
# Feedback and improvements are welcome.
4
# THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
5
# WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
6
# MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.
8
# This program is free software; you can redistribute it and/or modify it under
9
# the terms of the GNU General Public License as published by the Free Software
10
# Foundation, version 2; OR the Perl Artistic License. On UNIX and similar
11
# systems, you can issue `man perlgpl' or `man perlartistic' to read these
14
# You should have received a copy of the GNU General Public License along with
15
# this program; if not, write to the Free Software Foundation, Inc., 59 Temple
16
# Place, Suite 330, Boston, MA 02111-1307 USA.
17
# ###########################################################################
18
# report_mysql_info package
19
# ###########################################################################
21
# Package: report_mysql_info
22
# Report various aspects of MySQL
27
# Accepts a number of seconds, and outputs a d+h:m:s formatted string
29
awk -v sec="$1" 'BEGIN {
30
printf( "%d+%02d:%02d:%02d", sec / 86400, (sec % 86400) / 3600, (sec % 3600) / 60, sec % 60);
34
# Returns "Enabled", "Disabled", or "Not Supported" depending on whether the
35
# variable exists and is ON or enabled. You can pass 2nd and 3rd variables to
36
# control whether the variable should be 'gt' (numeric greater than) or 'eq'
37
# (string equal) to some value.
41
[ -e "$file" ] || return
43
if [ "$( get_var "$varname" "${file}" )" ]; then
44
local var="$(awk "\$1 ~ /^$2$/ { print \$2 }" $file)"
45
if [ "${var}" = "ON" ]; then
47
elif [ "${var}" = "OFF" -o "${var}" = "0" -o -z "${var}" ]; then
49
elif [ "$3" = "ne" ]; then
50
if [ "${var}" != "$4" ]; then
55
elif [ "$3" = "gt" ]; then
56
if [ "${var}" -gt "$4" ]; then
61
elif [ "${var}" ]; then
74
[ -e "$file" ] || return
77
if [ "$( get_var table_open_cache "${file}" )" ]; then
78
table_cache="$(get_var table_open_cache "${file}")"
80
table_cache="$(get_var table_cache "${file}")"
82
echo ${table_cache:-0}
85
# Gets the status of a plugin, or returns "Not found"
86
get_plugin_status () {
90
local status="$(grep -w "$plugin" "$file" | awk '{ print $2 }')"
92
echo ${status:-"Not found"}
95
# ##############################################################################
96
# Functions for parsing specific files and getting desired info from them.
97
# These are called from within main() and are separated so they can be tested
99
# ##############################################################################
101
# Parses the output of 'ps -e -o args | grep mysqld' or 'ps auxww...'
102
_NO_FALSE_NEGATIVES=""
103
parse_mysqld_instances () {
105
local variables_file="$2"
107
local socket=${socket:-""}
108
local port=${port:-""}
109
local datadir="${datadir:-""}"
111
[ -e "$file" ] || return
113
echo " Port Data Directory Nice OOM Socket"
114
echo " ===== ========================== ==== === ======"
116
grep '/mysqld ' "$file" | while read line; do
117
local pid=$(echo "$line" | awk '{print $1;}')
118
for word in ${line}; do
119
# Some grep doesn't have -o, so I have to pull out the words I want by
120
# looking at each word
121
if echo "${word}" | grep -- "--socket=" > /dev/null; then
122
socket="$(echo "${word}" | cut -d= -f2)"
124
if echo "${word}" | grep -- "--port=" > /dev/null; then
125
port="$(echo "${word}" | cut -d= -f2)"
127
if echo "${word}" | grep -- "--datadir=" > /dev/null; then
128
datadir="$(echo "${word}" | cut -d= -f2)"
131
local nice="$(get_var "internal::nice_of_$pid" "$variables_file")"
132
local oom="$(get_var "internal::oom_of_$pid" "$variables_file")"
133
# Only used during testing
134
if [ -n "${_NO_FALSE_NEGATIVES}" ]; then
138
printf " %5s %-26s %-4s %-3s %s\n" "${port}" "${datadir}" "${nice:-"?"}" "${oom:-"?"}" "${socket}"
142
# Gets the MySQL system time. Uses input from $MYSQL_VARIABLES_FILE.
143
get_mysql_timezone () {
146
[ -e "$file" ] || return
148
local tz="$(get_var time_zone "${file}")"
149
if [ "${tz}" = "SYSTEM" ]; then
150
tz="$(get_var system_time_zone "${file}")"
155
# Gets the MySQL system version.
156
get_mysql_version () {
159
name_val Version "$(get_var version "${file}") $(get_var version_comment "${file}")"
160
name_val "Built On" "$(get_var version_compile_os "${file}") $(get_var version_compile_machine "${file}")"
163
# Gets the system start and uptime in human readable format.
164
get_mysql_uptime () {
167
uptime="$(secs_to_time ${uptime})"
168
echo "${restart} (up ${uptime})"
171
# Summarizes the output of SHOW MASTER LOGS.
172
summarize_binlogs () {
175
[ -e "$file" ] || return
177
local size="$(awk '{t += $2} END{printf "%0.f\n", t}' "$file")"
178
name_val "Binlogs" $(wc -l "$file")
179
name_val "Zero-Sized" $(grep -c '\<0$' "$file")
180
name_val "Total Size" $(shorten ${size} 1)
185
[ -e "$file" ] || return
186
awk '{printf "%d users, %d anon, %d w/o pw, %d old pw\n", $1, $2, $3, $4}' "${file}"
189
# Print out binlog_do_db and binlog_ignore_db
190
format_binlog_filters () {
192
[ -e "$file" ] || return
193
name_val "binlog_do_db" "$(cut -f3 "$file")"
194
name_val "binlog_ignore_db" "$(cut -f4 "$file")"
197
# Takes as input a file that has two samples of SHOW STATUS, columnized next to
198
# each other. Outputs fuzzy-ed numbers:
199
# absolute, all-time per second, and per-second over the interval between the
200
# samples. Omits any rows that are all zeroes.
201
format_status_variables () {
203
[ -e "$file" ] || return
205
# First, figure out the intervals.
206
utime1="$(awk '/Uptime /{print $2}' "$file")";
207
utime2="$(awk '/Uptime /{print $3}' "$file")";
212
udays = utime1 / 86400;
213
udiff = utime2 - utime1;
214
format=\"%-35s %11s %11s %11s\\n\";
215
printf(format, \"Variable\", \"Per day\", \"Per second\", udiff \" secs\");
218
if ( \$2 > 0 && \$2 < 18446744073709551615 ) {
220
fuzzy_var=\$2 / udays;
225
fuzzy_var=\$2 / utime1;
230
fuzzy_var=(\$3 - \$2) / udiff;
234
perday = int(perday);
235
persec = int(persec);
236
nowsec = int(nowsec);
237
if ( perday + persec + nowsec > 0 ) {
238
if ( perday == 0 ) { perday = \"\"; }
239
if ( persec == 0 ) { persec = \"\"; }
240
if ( nowsec == 0 ) { nowsec = \"\"; }
241
printf(format, \$1, perday, persec, nowsec);
247
# Slices the processlist a bunch of different ways. The processlist should be
248
# created with the \G flag so it's vertical.
249
# The parsing is a bit awkward because different
250
# versions of awk have limitations like "too many fields on line xyz". So we
251
# use 'cut' to shorten the lines. We count all things into temporary variables
252
# for each process in the processlist, and when we hit the Info: line which
253
# ought to be the last line in the process, we decide what to do with the temp
254
# variables. If we're summarizing Command, we count everything; otherwise, only
255
# non-Sleep processes get counted towards the sum and max of Time.
256
summarize_processlist () {
259
[ -e "$file" ] || return
261
for param in Command User Host db State; do
263
printf ' %-30s %8s %7s %9s %9s\n' \
264
"${param}" "COUNT(*)" Working "SUM(Time)" "MAX(Time)"
265
echo " ------------------------------" \
266
"-------- ------- --------- ---------"
269
\$1 == \"${param}:\" {
270
p = substr(\$0, index(\$0, \":\") + 2);
271
if ( index(p, \":\") > 0 ) {
272
p = substr(p, 1, index(p, \":\") - 1);
274
if ( length(p) > 30 ) {
275
p = substr(p, 1, 30);
281
\$1 == \"Command:\" {
286
if ( c == \"Sleep\" ) {
289
if ( \"${param}\" == \"Command\" || c != \"Sleep\" ) {
291
if ( t > mtime[p] ) { mtime[p] = t; }
296
fuzzy_var=count[p]-sleep[p]; ${fuzzy_formula} fuzzy_work=fuzzy_var;
297
fuzzy_var=count[p]; ${fuzzy_formula} fuzzy_count=fuzzy_var;
298
fuzzy_var=time[p]; ${fuzzy_formula} fuzzy_time=fuzzy_var;
299
fuzzy_var=mtime[p]; ${fuzzy_formula} fuzzy_mtime=fuzzy_var;
300
printf \" %-30s %8d %7d %9d %9d\n\", p, fuzzy_count, fuzzy_work, fuzzy_time, fuzzy_mtime;
308
# Pretty-prints the my.cnf file. It's super annoying, but some *modern*
309
# versions of awk don't support POSIX character sets in regular
310
# expressions, like [[:space:]] (looking at you, Debian). So
311
# the below patterns contain [<space><tab>] and must remain that way.
312
pretty_print_cnf_file () {
315
[ -e "$file" ] || return
323
gsub(/^[ \t]*/, "", $1);
324
gsub(/^[ \t]*/, "", $2);
325
gsub(/[ \t]*$/, "", $1);
326
gsub(/[ \t]*$/, "", $2);
327
printf("%-35s = %s\n", $1, $2);
329
else if ( $0 ~ /\[/ ) {
339
find_checkpoint_age() {
342
/Log sequence number/{
344
lsn = $5 + ($4 * 4294967296);
350
/Last checkpoint at/{
352
print lsn - ($5 + ($4 * 4294967296));
361
find_pending_io_reads() {
364
[ -e "$file" ] || return
367
/Pending normal aio reads/ {
368
normal_aio_reads = substr($5, 1, index($5, ","));
371
ibuf_aio_reads = substr($4, 1, index($4, ","));
380
printf "%d buf pool reads, %d normal AIO", reads, normal_aio_reads;
381
printf ", %d ibuf AIO, %d preads", ibuf_aio_reads, preads;
386
find_pending_io_writes() {
389
[ -e "$file" ] || return
393
aio_writes = substr($NF, 1, index($NF, ","));
396
log_ios = substr($7, 1, index($7, ","));
397
sync_ios = substr($10, 1, index($10, ","));
399
/pending log writes/ {
407
lru = substr($4, 1, index($4, ","));
408
flush_list = substr($7, 1, index($7, ","));
412
printf "%d buf pool (%d LRU, %d flush list, %d page); %d AIO, %d sync, %d log IO (%d log, %d chkp); %d pwrites", lru + flush_list + single_page, lru, flush_list, single_page, aio_writes, sync_ios, log_ios, log_writes, chkp_writes, pwrites;
417
find_pending_io_flushes() {
420
[ -e "$file" ] || return
424
log_flushes = substr($5, 1, index($5, ";"));
428
printf "%d buf pool, %d log", buf_pool, log_flushes;
433
summarize_undo_log_entries() {
436
[ -e "$file" ] || return
438
grep 'undo log entries' "${file}" \
439
| sed -e 's/^.*undo log entries \([0-9]*\)/\1/' \
449
printf "%d transactions, %d total undo, %d max undo\n", count, sum, max;
453
find_max_trx_time() {
456
[ -e "$file" ] || return
462
/^---TRANSACTION.* sec,/ {
463
for ( i = 0; i < 7; ++i ) {
464
if ( $i == "sec," ) {
477
find_transation_states () {
479
local tmpfile="$TMPDIR/find_transation_states.tmp"
481
[ -e "$file" ] || return
483
awk -F, '/^---TRANSACTION/{print $2}' "${file}" \
484
| sed -e 's/ [0-9]* sec.*//' \
486
| uniq -c > "${tmpfile}"
487
group_concat "${tmpfile}"
490
# Summarizes various things about InnoDB status that are not easy to see by eye.
491
format_innodb_status () {
494
[ -e "$file" ] || return
496
name_val "Checkpoint Age" "$(shorten $(find_checkpoint_age "${file}") 0)"
497
name_val "InnoDB Queue" "$(awk '/queries inside/{print}' "${file}")"
498
name_val "Oldest Transaction" "$(find_max_trx_time "${file}") Seconds";
499
name_val "History List Len" "$(awk '/History list length/{print $4}' "${file}")"
500
name_val "Read Views" "$(awk '/read views open inside/{print $1}' "${file}")"
501
name_val "Undo Log Entries" "$(summarize_undo_log_entries "${file}")"
502
name_val "Pending I/O Reads" "$(find_pending_io_reads "${file}")"
503
name_val "Pending I/O Writes" "$(find_pending_io_writes "${file}")"
504
name_val "Pending I/O Flushes" "$(find_pending_io_flushes "${file}")"
505
name_val "Transaction States" "$(find_transation_states "${file}" )"
506
if grep 'TABLE LOCK table' "${file}" >/dev/null ; then
508
awk '/^TABLE LOCK table/{print $4}' "${file}" \
509
| sort | uniq -c | sort -rn
511
if grep 'has waited at' "${file}" > /dev/null ; then
512
echo "Semaphore Waits"
513
grep 'has waited at' "${file}" | cut -d' ' -f6-8 \
514
| sort | uniq -c | sort -rn
516
if grep 'reserved it in mode' "${file}" > /dev/null; then
517
echo "Semaphore Holders"
518
awk '/has reserved it in mode/{
519
print substr($0, 1 + index($0, "("), index($0, ")") - index($0, "(") - 1);
520
}' "${file}" | sort | uniq -c | sort -rn
522
if grep -e 'Mutex at' -e 'lock on' "${file}" >/dev/null 2>&1; then
523
echo "Mutexes/Locks Waited For"
524
grep -e 'Mutex at' -e 'lock on' "${file}" | sed -e 's/^[XS]-//' -e 's/,.*$//' \
525
| sort | uniq -c | sort -rn
529
# Summarizes per-database statistics for a bunch of different things: count of
530
# tables, views, etc. $1 is the file name. $2 is the database name; if none,
531
# then there should be multiple databases.
532
format_overall_db_stats () {
534
local tmpfile="$TMPDIR/format_overall_db_stats.tmp"
536
[ -e "$file" ] || return
539
# We keep counts of everything in an associative array keyed by db name, and
540
# what it is. The num_dbs counter is to ensure sort order is consistent when
541
# we run the awk commands following this one.
544
# In case there is no USE statement in the file.
549
db = substr($2, 2, length($2) - 3);
550
if ( db_seen[db]++ == 0 ) {
556
# Handle single-DB dumps, where there is no USE statement.
562
counts[db ",tables"]++;
564
/CREATE ALGORITHM=/ {
565
counts[db ",views"]++;
567
/03 CREATE.*03 PROCEDURE/ {
570
/03 CREATE.*03 FUNCTION/ {
571
counts[db ",func"]++;
573
/03 CREATE.*03 TRIGGER/ {
580
counts[db ",partn"]++;
583
mdb = length("Database");
584
for ( i = 0; i < num_dbs; i++ ) {
585
if ( length(dbs[i]) > mdb ) {
586
mdb = length(dbs[i]);
589
fmt = " %-" mdb "s %6s %5s %3s %5s %5s %5s %5s\n";
590
printf fmt, "Database", "Tables", "Views", "SPs", "Trigs", "Funcs", "FKs", "Partn";
591
for ( i=0;i<num_dbs;i++ ) {
593
printf fmt, db, counts[db ",tables"], counts[db ",views"], counts[db ",sps"], counts[db ",trg"], counts[db ",func"], counts[db ",fk"], counts[db ",partn"];
596
' "$file" > "$tmpfile"
598
tail -n +3 "$tmpfile" | sort
601
# Now do the summary of engines per DB
604
# In case there is no USE statement in the file.
610
db = substr($2, 2, length($2) - 3);
611
if ( db_seen[db]++ == 0 ) {
617
# Handle single-DB dumps, where there is no USE statement.
623
engine=substr($2, index($2, "=") + 1);
624
if ( engine_seen[engine]++ == 0 ) {
625
engines[num_engines] = engine;
628
counts[db "," engine]++;
631
mdb = length("Database");
632
for ( i=0;i<num_dbs;i++ ) {
634
if ( length(db) > mdb ) {
639
printf fmt, "Database";
640
for ( i=0;i<num_engines;i++ ) {
642
fmts[engine] = " %" length(engine) "s";
643
printf fmts[engine], engine;
646
for ( i=0;i<num_dbs;i++ ) {
649
for ( j=0;j<num_engines;j++ ) {
651
printf fmts[engine], counts[db "," engine];
656
' "$file" > "$tmpfile"
658
tail -n +2 "$tmpfile" | sort
661
# Now do the summary of index types per DB. Careful -- index is a reserved
665
# In case there is no USE statement in the file.
671
db = substr($2, 2, length($2) - 3);
672
if ( db_seen[db]++ == 0 ) {
678
# Handle single-DB dumps, where there is no USE statement.
685
if ( $0 ~ /SPATIAL/ ) {
688
if ( $0 ~ /FULLTEXT/ ) {
691
if ( $0 ~ /USING RTREE/ ) {
694
if ( $0 ~ /USING HASH/ ) {
697
if ( idx_seen[idx]++ == 0 ) {
698
idxes[num_idxes] = idx;
701
counts[db "," idx]++;
704
mdb = length("Database");
705
for ( i=0;i<num_dbs;i++ ) {
707
if ( length(db) > mdb ) {
712
printf fmt, "Database";
713
for ( i=0;i<num_idxes;i++ ) {
715
fmts[idx] = " %" length(idx) "s";
716
printf fmts[idx], idx;
719
for ( i=0;i<num_dbs;i++ ) {
722
for ( j=0;j<num_idxes;j++ ) {
724
printf fmts[idx], counts[db "," idx];
729
' "$file" > "$tmpfile"
731
tail -n +2 "$tmpfile" | sort
734
# Now do the summary of datatypes per DB
737
# In case there is no USE statement in the file.
743
db = substr($2, 2, length($2) - 3);
744
if ( db_seen[db]++ == 0 ) {
750
# Handle single-DB dumps, where there is no USE statement.
757
str = substr(str, index(str, "`") + 1);
758
str = substr(str, index(str, "`") + 2);
759
if ( index(str, " ") > 0 ) {
760
str = substr(str, 1, index(str, " ") - 1);
762
if ( index(str, ",") > 0 ) {
763
str = substr(str, 1, index(str, ",") - 1);
765
if ( index(str, "(") > 0 ) {
766
str = substr(str, 1, index(str, "(") - 1);
769
if ( type_seen[type]++ == 0 ) {
770
types[num_types] = type;
773
counts[db "," type]++;
776
mdb = length("Database");
777
for ( i=0;i<num_dbs;i++ ) {
779
if ( length(db) > mdb ) {
784
mtlen = 0; # max type length
785
for ( i=0;i<num_types;i++ ) {
787
if ( length(type) > mtlen ) {
788
mtlen = length(type);
791
for ( i=1;i<=mtlen;i++ ) {
792
printf " %-" mdb "s", "";
793
for ( j=0;j<num_types;j++ ) {
795
if ( i > length(type) ) {
799
ch = substr(type, i, 1);
805
printf " %-" mdb "s", "Database";
806
for ( i=0;i<num_types;i++ ) {
807
printf " %3s", "===";
810
for ( i=0;i<num_dbs;i++ ) {
813
for ( j=0;j<num_types;j++ ) {
815
printf " %3s", counts[db "," type];
820
' "$file" > "$tmpfile"
821
local hdr=$(grep -n Database "$tmpfile" | cut -d: -f1);
822
head -n${hdr} "$tmpfile"
823
tail -n +$((${hdr} + 1)) "$tmpfile" | sort
827
section_percona_server_features () {
830
[ -e "$file" ] || return
832
name_val "Table & Index Stats" \
833
"$(feat_on "$file" userstat_running)"
834
name_val "Multiple I/O Threads" \
835
"$(feat_on "$file" innodb_read_io_threads gt 1)"
836
name_val "Corruption Resilient" \
837
"$(feat_on "$file" innodb_pass_corrupt_table)"
838
name_val "Durable Replication" \
839
"$(feat_on "$file" innodb_overwrite_relay_log_info)"
840
name_val "Import InnoDB Tables" \
841
"$(feat_on "$file" innodb_expand_import)"
842
name_val "Fast Server Restarts" \
843
"$(feat_on "$file" innodb_auto_lru_dump)"
844
name_val "Enhanced Logging" \
845
"$(feat_on "$file" log_slow_verbosity ne microtime)"
846
name_val "Replica Perf Logging" \
847
"$(feat_on "$file" log_slow_slave_statements)"
848
name_val "Response Time Hist." \
849
"$(feat_on "$file" enable_query_response_time_stats)"
850
name_val "Smooth Flushing" \
851
"$(feat_on "$file" innodb_adaptive_checkpoint ne none)"
852
name_val "HandlerSocket NoSQL" \
853
"$(feat_on "$file" handlersocket_port)"
854
name_val "Fast Hash UDFs" \
855
"$(get_var "pt-summary-internal-FNV_64" "$file")"
859
local variables_file="$1"
860
local status_file="$2"
862
[ -e "$variables_file" -a -e "$status_file" ] || return
864
local buf_size="$(get_var key_buffer_size "$variables_file")"
865
local blk_size="$(get_var key_cache_block_size "$variables_file")"
866
local blk_unus="$(get_var Key_blocks_unused "$status_file")"
867
local blk_unfl="$(get_var Key_blocks_not_flushed "$variables_file")"
868
local unus=$((${blk_unus:-0} * ${blk_size:-0}))
869
local unfl=$((${blk_unfl:-0} * ${blk_size:-0}))
870
local used=$((${buf_size:-0} - ${unus}))
872
name_val "Key Cache" "$(shorten ${buf_size} 1)"
873
name_val "Pct Used" "$(fuzzy_pct ${used} ${buf_size})"
874
name_val "Unflushed" "$(fuzzy_pct ${unfl} ${buf_size})"
878
local variables_file="$1"
879
local status_file="$2"
881
[ -e "$variables_file" -a -e "$status_file" ] || return
883
# XXX TODO I don't think this is working right.
884
# XXX TODO Should it use data from information_schema.plugins too?
885
local version=$(get_var innodb_version "$variables_file")
886
name_val Version ${version:-default}
888
local bp_size="$(get_var innodb_buffer_pool_size "$variables_file")"
889
name_val "Buffer Pool Size" "$(shorten "${bp_size:-0}" 1)"
891
local bp_pags="$(get_var Innodb_buffer_pool_pages_total "$status_file")"
892
local bp_free="$(get_var Innodb_buffer_pool_pages_free "$status_file")"
893
local bp_dirt="$(get_var Innodb_buffer_pool_pages_dirty "$status_file")"
894
local bp_fill=$((${bp_pags} - ${bp_free}))
895
name_val "Buffer Pool Fill" "$(fuzzy_pct ${bp_fill} ${bp_pags})"
896
name_val "Buffer Pool Dirty" "$(fuzzy_pct ${bp_dirt} ${bp_pags})"
898
name_val "File Per Table" $(get_var innodb_file_per_table "$variables_file")
899
name_val "Page Size" $(shorten $(get_var Innodb_page_size "$status_file") 0)
901
local log_size="$(get_var innodb_log_file_size "$variables_file")"
902
local log_file="$(get_var innodb_log_files_in_group "$variables_file")"
903
local log_total=$(awk "BEGIN {printf \"%.2f\n\", ${log_size}*${log_file}}" )
904
name_val "Log File Size" \
905
"${log_file} * $(shorten ${log_size} 1 1000) = $(shorten ${log_total} 1 1000)"
906
name_val "Log Buffer Size" \
907
"$(shorten $(get_var innodb_log_buffer_size "$variables_file") 0)"
908
name_val "Flush Method" \
909
"$(get_var innodb_flush_method "$variables_file")"
910
name_val "Flush Log At Commit" \
911
"$(get_var innodb_flush_log_at_trx_commit "$variables_file")"
912
name_val "XA Support" \
913
"$(get_var innodb_support_xa "$variables_file")"
914
name_val "Checksums" \
915
"$(get_var innodb_checksums "$variables_file")"
916
name_val "Doublewrite" \
917
"$(get_var innodb_doublewrite "$variables_file")"
918
name_val "R/W I/O Threads" \
919
"$(get_var innodb_read_io_threads "$variables_file") $(get_var innodb_write_io_threads "$variables_file")"
920
name_val "I/O Capacity" \
921
"$(get_var innodb_io_capacity "$variables_file")"
922
name_val "Thread Concurrency" \
923
"$(get_var innodb_thread_concurrency "$variables_file")"
924
name_val "Concurrency Tickets" \
925
"$(get_var innodb_concurrency_tickets "$variables_file")"
926
name_val "Commit Concurrency" \
927
"$(get_var innodb_commit_concurrency "$variables_file")"
928
name_val "Txn Isolation Level" \
929
"$(get_var tx_isolation "$variables_file")"
930
name_val "Adaptive Flushing" \
931
"$(get_var innodb_adaptive_flushing "$variables_file")"
932
name_val "Adaptive Checkpoint" \
933
"$(get_var innodb_adaptive_checkpoint "$variables_file")"
937
section_noteworthy_variables () {
940
[ -e "$file" ] || return
942
name_val "Auto-Inc Incr/Offset" "$(get_var auto_increment_increment "$file")/$(get_var auto_increment_offset "$file")"
944
default_storage_engine flush_time init_connect init_file sql_mode;
946
name_val "${v}" "$(get_var ${v} "$file")"
949
join_buffer_size sort_buffer_size read_buffer_size read_rnd_buffer_size \
950
bulk_insert_buffer max_heap_table_size tmp_table_size \
951
max_allowed_packet thread_stack;
953
name_val "${v}" "$(shorten $(get_var ${v} "$file") 0)"
955
for v in log log_error log_warnings log_slow_queries \
956
log_queries_not_using_indexes log_slave_updates;
958
name_val "${v}" "$(get_var ${v} "$file")"
963
# Formats and outputs the semisyncronious replication-related variables
965
_semi_sync_stats_for () {
969
[ -e "$file" ] || return
971
local semisync_status="$(get_var "Rpl_semi_sync_${target}_status" "${file}" )"
972
local semisync_trace="$(get_var "rpl_semi_sync_${target}_trace_level" "${file}")"
975
if [ -n "${semisync_trace}" ]; then
976
if [ $semisync_trace -eq 1 ]; then
977
trace_extra="general (for example, time function failures) "
978
elif [ $semisync_trace -eq 16 ]; then
979
trace_extra="detail (more verbose information) "
980
elif [ $semisync_trace -eq 32 ]; then
981
trace_extra="net wait (more information about network waits)"
982
elif [ $semisync_trace -eq 64 ]; then
983
trace_extra="function (information about function entry and exit)"
985
trace_extra="Unknown setting"
989
name_val "${target} semisync status" "${semisync_status}"
990
name_val "${target} trace level" "${semisync_trace}, ${trace_extra}"
992
if [ "${target}" = "master" ]; then
993
name_val "${target} timeout in milliseconds" \
994
"$(get_var "rpl_semi_sync_${target}_timeout" "${file}")"
995
name_val "${target} waits for slaves" \
996
"$(get_var "rpl_semi_sync_${target}_wait_no_slave" "${file}")"
998
_d "Prepend Rpl_semi_sync_master_ to the following"
1000
clients net_avg_wait_time net_wait_time net_waits \
1001
no_times no_tx timefunc_failures tx_avg_wait_time \
1002
tx_wait_time tx_waits wait_pos_backtraverse \
1003
wait_sessions yes_tx;
1005
name_val "${target} ${v}" \
1006
"$( get_var "Rpl_semi_sync_master_${v}" "${file}" )"
1011
# Make a pattern of things we want to omit because they aren't
1012
# counters, they are gauges (in RRDTool terminology). Gauges are shown
1013
# elsewhere in the output.
1014
noncounters_pattern () {
1015
local noncounters_pattern=""
1017
for var in Compression Delayed_insert_threads Innodb_buffer_pool_pages_data \
1018
Innodb_buffer_pool_pages_dirty Innodb_buffer_pool_pages_free \
1019
Innodb_buffer_pool_pages_latched Innodb_buffer_pool_pages_misc \
1020
Innodb_buffer_pool_pages_total Innodb_data_pending_fsyncs \
1021
Innodb_data_pending_reads Innodb_data_pending_writes \
1022
Innodb_os_log_pending_fsyncs Innodb_os_log_pending_writes \
1023
Innodb_page_size Innodb_row_lock_current_waits Innodb_row_lock_time_avg \
1024
Innodb_row_lock_time_max Key_blocks_not_flushed Key_blocks_unused \
1025
Key_blocks_used Last_query_cost Max_used_connections Ndb_cluster_node_id \
1026
Ndb_config_from_host Ndb_config_from_port Ndb_number_of_data_nodes \
1027
Not_flushed_delayed_rows Open_files Open_streams Open_tables \
1028
Prepared_stmt_count Qcache_free_blocks Qcache_free_memory \
1029
Qcache_queries_in_cache Qcache_total_blocks Rpl_status \
1030
Slave_open_temp_tables Slave_running Ssl_cipher Ssl_cipher_list \
1031
Ssl_ctx_verify_depth Ssl_ctx_verify_mode Ssl_default_timeout \
1032
Ssl_session_cache_mode Ssl_session_cache_size Ssl_verify_depth \
1033
Ssl_verify_mode Ssl_version Tc_log_max_pages_used Tc_log_page_size \
1034
Threads_cached Threads_connected Threads_running \
1035
Uptime_since_flush_status;
1037
if [ -z "${noncounters_pattern}" ]; then
1038
noncounters_pattern="${var}"
1040
noncounters_pattern="${noncounters_pattern}\|${var}"
1043
echo $noncounters_pattern
1047
local executables_file="$1"
1048
local variables_file="$2"
1050
[ -e "$executables_file" -a -e "$variables_file" ] || return
1052
section "MySQL Executable"
1054
while read executable; do
1055
name_val "Path to executable" "$executable"
1056
name_val "Has symbols" "$( get_var "pt-summary-internal-mysqld_executable_${i}" "$variables_file" )"
1058
done < "$executables_file"
1061
section_mysql_files () {
1062
local variables_file="$1"
1064
section "MySQL Files"
1065
for file_name in pid_file slow_query_log_file general_log_file log_error; do
1066
local file="$(get_var "${file_name}" "$variables_file")"
1067
local name_out="$(echo "$file_name" | sed 'y/[a-z]/[A-Z]/')"
1068
if [ -e "${file}" ]; then
1069
name_val "$name_out" "$file"
1070
name_val "${name_out} Size" "$(du "$file" | awk '{print $1}')"
1072
name_val "$name_out" "(does not exist)"
1077
report_mysql_summary () {
1080
# Field width for name_val
1081
local NAME_VAL_LEN=25
1083
# ########################################################################
1084
# Header for the whole thing, table of discovered instances
1085
# ########################################################################
1087
section "Percona Toolkit MySQL Summary Report"
1088
name_val "System time" "`date -u +'%F %T UTC'` (local TZ: `date +'%Z %z'`)"
1090
parse_mysqld_instances "$dir/mysqld-instances" "$dir/mysql-variables"
1092
section_mysqld "$dir/mysqld-executables" "$dir/mysql-variables"
1094
# ########################################################################
1095
# General date, hostname, etc
1096
# ########################################################################
1097
local user="$(get_var "pt-summary-internal-user" "$dir/mysql-variables")"
1098
local port="$(get_var port "$dir/mysql-variables")"
1099
local now="$(get_var "pt-summary-internal-now" "$dir/mysql-variables")"
1100
section "Report On Port ${port}"
1101
name_val User "${user}"
1102
name_val Time "${now} ($(get_mysql_timezone "$dir/mysql-variables"))"
1103
name_val Hostname "$(get_var hostname "$dir/mysql-variables")"
1104
get_mysql_version "$dir/mysql-variables"
1106
local uptime="$(get_var Uptime "$dir/mysql-status")"
1107
local current_time="$(get_var "pt-summary-internal-current_time" "$dir/mysql-variables")"
1108
name_val Started "$(get_mysql_uptime "${uptime}" "${current_time}")"
1110
local num_dbs="$(grep -c . "$dir/mysql-databases")"
1111
name_val Databases "${num_dbs}"
1112
name_val Datadir "$(get_var datadir "$dir/mysql-variables")"
1114
local fuzz_procs=$(fuzz $(get_var Threads_connected "$dir/mysql-status"))
1115
local fuzz_procr=$(fuzz $(get_var Threads_running "$dir/mysql-status"))
1116
name_val Processes "${fuzz_procs} connected, ${fuzz_procr} running"
1119
if [ -s "$dir/mysql-slave" ]; then slave=""; else slave="not "; fi
1120
local slavecount=$(grep -c 'Binlog Dump' "$dir/mysql-processlist")
1121
name_val Replication "Is ${slave}a slave, has ${slavecount} slaves connected"
1124
# TODO move this into a section with other files: error log, slow log and
1126
# section_mysql_files "$dir/mysql-variables"
1127
local pid_file="$(get_var "pid_file" "$dir/mysql-variables")"
1129
if [ "$( get_var "pt-summary-internal-pid_file_exists" "$dir/mysql-variables" )" ]; then
1130
PID_EXISTS="(exists)"
1132
PID_EXISTS="(does not exist)"
1134
name_val Pidfile "${pid_file} ${PID_EXISTS}"
1136
# ########################################################################
1137
# Processlist, sliced several different ways
1138
# ########################################################################
1139
section "Processlist"
1140
summarize_processlist "$dir/mysql-processlist"
1142
# ########################################################################
1143
# Queries and query plans
1144
# ########################################################################
1145
section "Status Counters (Wait ${OPT_SLEEP} Seconds)"
1146
# Wait for the child that was forked during collection.
1148
local noncounters_pattern="$(noncounters_pattern)"
1149
format_status_variables "$dir/mysql-status-defer" | grep -v "${noncounters_pattern}"
1151
# ########################################################################
1153
# ########################################################################
1154
section "Table cache"
1155
local open_tables=$(get_var "Open_tables" "$dir/mysql-status")
1156
local table_cache=$(get_table_cache "$dir/mysql-variables")
1157
name_val Size $table_cache
1158
name_val Usage "$(fuzzy_pct ${open_tables} ${table_cache})"
1160
# ########################################################################
1161
# Percona Server features
1162
# ########################################################################
1163
section "Key Percona Server features"
1164
section_percona_server_features "$dir/mysql-variables"
1166
# ########################################################################
1168
# ########################################################################
1169
# TODO: what would be good is to show nonstandard plugins here.
1171
name_val "InnoDB compression" "$(get_plugin_status "$dir/mysql-plugins" "INNODB_CMP")"
1173
# ########################################################################
1175
# ########################################################################
1176
if [ "$(get_var have_query_cache "$dir/mysql-variables")" ]; then
1177
section "Query cache"
1178
local query_cache_size=$(get_var query_cache_size "$dir/mysql-variables")
1179
local used=$(( ${query_cache_size} - $(get_var Qcache_free_memory "$dir/mysql-status") ))
1180
local hrat=$(fuzzy_pct $(get_var Qcache_hits "$dir/mysql-status") $(get_var Qcache_inserts "$dir/mysql-status"))
1181
name_val query_cache_type $(get_var query_cache_type "$dir/mysql-variables")
1182
name_val Size "$(shorten ${query_cache_size} 1)"
1183
name_val Usage "$(fuzzy_pct ${used} ${query_cache_size})"
1184
name_val HitToInsertRatio "${hrat}"
1187
local semisync_enabled_master="$(get_var "rpl_semi_sync_master_enabled" "$dir/mysql-variables")"
1188
if [ -n "${semisync_enabled_master}" ]; then
1189
section "Semisynchronous Replication"
1190
if [ "$semisync_enabled_master" = "OFF" -o "$semisync_enabled_master" = "0" -o -z "$semisync_enabled_master" ]; then
1191
name_val "Master" "Disabled"
1193
_semi_sync_stats_for "master" "$dir/mysql-variables"
1195
local semisync_enabled_slave="$(get_var rpl_semi_sync_slave_enabled "$dir/mysql-variables")"
1196
if [ "$semisync_enabled_slave" = "OFF" -o "$semisync_enabled_slave" = "0" -o -z "$semisync_enabled_slave" ]; then
1197
name_val "Slave" "Disabled"
1199
_semi_sync_stats_for "slave" "$dir/mysql-variables"
1203
# ########################################################################
1204
# Schema, databases, data type, other analysis.
1205
# ########################################################################
1207
# Assume "no" if stdin or stdout is not a terminal, so this can be run and
1208
# put into a file, or piped into a pager, or something else like that.
1210
# But dump no matter what if they passed in something through --databases,
1211
# OR if --read-samples was set
1212
if [ "${OPT_DATABASES}" ] || [ "${OPT_READ_SAMPLES}" ] \
1213
|| [ -e "$dir/mysqldump" -a -s "$dir/mysqldump" ]; then
1215
elif [ -t 0 -a -t 1 ]; then
1216
echo -n "Would you like to mysqldump -d the schema and analyze it? y/n "
1220
if echo "${reply:-n}" | grep -i '^y' > /dev/null ; then
1221
if [ -z "${OPT_DATABASES}" ] && [ -z "$OPT_READ_SAMPLES" ] \
1222
&& [ ! -e "$dir/mysqldump" ]; then
1223
# If --dump-schemas wasn't used, ask what they want to dump
1224
echo "There are ${num_dbs} databases. Would you like to dump all, or just one?"
1225
echo -n "Type the name of the database, or press Enter to dump all of them. "
1228
local trg_arg="$( get_mysqldump_args "$dir/mysql-variables" )"
1229
get_mysqldump_for "${trg_arg}" "${dbtodump}" > "$dir/mysqldump"
1232
# Test the result by checking the file, not by the exit status, because we
1233
# might get partway through and then die, and the info is worth analyzing
1235
if [ -e "$dir/mysqldump" -a -s "$dir/mysqldump" ] \
1236
&& grep 'CREATE TABLE' "$dir/mysqldump" >/dev/null 2>&1; then
1237
format_overall_db_stats "$dir/mysqldump"
1238
elif [ ! -e "$dir/mysqldump" -a "$OPT_READ_SAMPLES" ]; then
1239
echo "Skipping schema analysis as the directory passed in" \
1240
"doesn't have a dump file"
1242
echo "Skipping schema analysis due to apparent error in dump file"
1245
echo "Skipping schema analysis"
1248
# ########################################################################
1249
# Noteworthy Technologies
1250
# ########################################################################
1251
section "Noteworthy Technologies"
1252
if [ -s "$dir/mysqldump" ]; then
1253
if grep FULLTEXT "$dir/mysqldump" > /dev/null; then
1254
name_val "Full Text Indexing" "Yes"
1256
name_val "Full Text Indexing" "No"
1258
if grep 'GEOMETRY\|POINT\|LINESTRING\|POLYGON' "$dir/mysqldump" > /dev/null; then
1259
name_val "Geospatial Types" "Yes"
1261
name_val "Geospatial Types" "No"
1263
if grep 'FOREIGN KEY' "$dir/mysqldump" > /dev/null; then
1264
name_val "Foreign Keys" "Yes"
1266
name_val "Foreign Keys" "No"
1268
if grep 'PARTITION BY' "$dir/mysqldump" > /dev/null; then
1269
name_val "Partitioning" "Yes"
1271
name_val "Partitioning" "No"
1273
if grep -e 'ENGINE=InnoDB.*ROW_FORMAT' \
1274
-e 'ENGINE=InnoDB.*KEY_BLOCK_SIZE' "$dir/mysqldump" > /dev/null; then
1275
name_val "InnoDB Compression" "Yes"
1277
name_val "InnoDB Compression" "No"
1280
local ssl="$(get_var Ssl_accepts "$dir/mysql-status")"
1281
if [ -n "$ssl" -a "${ssl:-0}" -gt 0 ]; then
1282
name_val "SSL" "Yes"
1286
local lock_tables="$(get_var Com_lock_tables "$dir/mysql-status")"
1287
if [ -n "$lock_tables" -a "${lock_tables:-0}" -gt 0 ]; then
1288
name_val "Explicit LOCK TABLES" "Yes"
1290
name_val "Explicit LOCK TABLES" "No"
1292
local delayed_insert="$(get_var Delayed_writes "$dir/mysql-status")"
1293
if [ -n "$delayed_insert" -a "${delayed_insert:-0}" -gt 0 ]; then
1294
name_val "Delayed Insert" "Yes"
1296
name_val "Delayed Insert" "No"
1298
local xat="$(get_var Com_xa_start "$dir/mysql-status")"
1299
if [ -n "$xat" -a "${xat:-0}" -gt 0 ]; then
1300
name_val "XA Transactions" "Yes"
1302
name_val "XA Transactions" "No"
1304
local ndb_cluster="$(get_var "Ndb_cluster_node_id" "$dir/mysql-status")"
1305
if [ -n "$ndb_cluster" -a "${ndb_cluster:-0}" -gt 0 ]; then
1306
name_val "NDB Cluster" "Yes"
1308
name_val "NDB Cluster" "No"
1310
local prep=$(( $(get_var "Com_stmt_prepare" "$dir/mysql-status") + $(get_var "Com_prepare_sql" "$dir/mysql-status") ))
1311
if [ "${prep}" -gt 0 ]; then
1312
name_val "Prepared Statements" "Yes"
1314
name_val "Prepared Statements" "No"
1316
local prep_count="$(get_var Prepared_stmt_count "$dir/mysql-status")"
1317
if [ "${prep_count}" ]; then
1318
name_val "Prepared statement count" "${prep_count}"
1321
# ########################################################################
1323
# ########################################################################
1325
local have_innodb="$(get_var "have_innodb" "$dir/mysql-variables")"
1326
if [ "${have_innodb}" = "YES" ]; then
1327
section_innodb "$dir/mysql-variables" "$dir/mysql-status"
1329
if [ -s "$dir/innodb-status" ]; then
1330
format_innodb_status "$dir/innodb-status"
1334
# ########################################################################
1336
# ########################################################################
1338
section_myisam "$dir/mysql-variables" "$dir/mysql-status"
1340
# ########################################################################
1342
# ########################################################################
1344
local users="$( format_users "$dir/mysql-users" )"
1345
name_val "Users" "${users}"
1346
name_val "Old Passwords" "$(get_var old_passwords "$dir/mysql-variables")"
1348
# ########################################################################
1350
# ########################################################################
1351
section "Binary Logging"
1353
if [ -s "$dir/mysql-master-logs" ] \
1354
|| [ -s "$dir/mysql-master-status" ]; then
1355
summarize_binlogs "$dir/mysql-master-logs"
1356
local format="$(get_var binlog_format "$dir/mysql-variables")"
1357
name_val binlog_format "${format:-STATEMENT}"
1358
name_val expire_logs_days "$(get_var expire_logs_days "$dir/mysql-variables")"
1359
name_val sync_binlog "$(get_var sync_binlog "$dir/mysql-variables")"
1360
name_val server_id "$(get_var server_id "$dir/mysql-variables")"
1361
format_binlog_filters "$dir/mysql-master-status"
1364
# Replication: seconds behind, running, filters, skip_slave_start, skip_errors,
1365
# read_only, temp tables open, slave_net_timeout, slave_exec_mode
1367
# ########################################################################
1368
# Interesting things that you just ought to know about.
1369
# ########################################################################
1370
section "Noteworthy Variables"
1371
section_noteworthy_variables "$dir/mysql-variables"
1373
# ########################################################################
1374
# If there is a my.cnf in a standard location, see if we can pretty-print it.
1375
# ########################################################################
1376
section "Configuration File"
1377
local cnf_file="$(get_var "pt-summary-internal-Config_File_path" "$dir/mysql-variables")"
1378
if [ -n "${cnf_file}" ]; then
1379
name_val "Config File" "${cnf_file}"
1380
pretty_print_cnf_file "$dir/mysql-config-file"
1382
name_val "Config File" "Cannot autodetect or find, giving up"
1385
# Make sure that we signal the end of the tool's output.
1389
# ###########################################################################
1390
# End report_mysql_info package
1391
# ###########################################################################
added
removed
lib/bash/report_mysql_info.sh
