From 19895ca8d31deea9680395e11e889d4b32a58bd4 Mon Sep 17 00:00:00 2001 From: Simon J Mudd Date: Tue, 23 Dec 2014 00:47:54 +0100 Subject: [PATCH] Update to show idle times and adjust formatting --- README.md | 4 +++- i_s/pl_by_user.go | 47 +++++++++++++++++++++++++----------------- i_s/processlist.go | 10 ++++++--- i_s/processlist_row.go | 2 +- lib/common.go | 20 ++++++++++++++++++ screen_samples.txt | 20 ++++++++++++++++++ version/version.go | 2 +- 7 files changed, 80 insertions(+), 25 deletions(-) diff --git a/README.md b/README.md index 880adbb..aa431a8 100644 --- a/README.md +++ b/README.md @@ -50,7 +50,9 @@ queries, or the number of connections they have to MySQL. This is really missing a feature in MySQL (see: http://bugs.mysql.com/75156) to provide higher resolution query times than seconds. It gives some info but if the queries are very short then the integer runtime -in seconds makes the output far less interesting. +in seconds makes the output far less interesting. Total idle time is also +shown as this gives an indication of perhaps overly long idle queries, +and the sum of the values here if there's a pile up may be interesting. You can change the polling interval and switch between modes (see below). diff --git a/i_s/pl_by_user.go b/i_s/pl_by_user.go index f5bd83f..78ddc2a 100644 --- a/i_s/pl_by_user.go +++ b/i_s/pl_by_user.go @@ -24,6 +24,7 @@ CREATE TEMPORARY TABLE `PROCESSLIST` ( type pl_by_user_row struct { username string runtime uint64 + sleeptime uint64 connections uint64 active uint64 hosts uint64 @@ -37,13 +38,13 @@ type pl_by_user_row struct { type pl_by_user_rows []pl_by_user_row /* -username |Run Time %age|Conn Actv|Hosts DBs|Select Insert Update Delete Other| -xxxxxxxxxxxxxx|hh:mm:ss 100.0%|9999 9999|9999 999|100.0% 100.0% 100.0% 100.0% 100.0%| +username |Run Time %age|Sleep |Conn Actv|Hosts DBs|Sel Ins Upd Del Oth| +xxxxxxxxxxxxxx|hh:mm:ss 100.0%|hh:mm:ss|9999 9999|9999 999|999 999 999 999 999| */ func (r *pl_by_user_row) headings() string { - return fmt.Sprintf("%-14s|%10s %6s|%4s %4s|%5s %3s|%6s %6s %6s %6s %6s|", - "User", "Run Time", "%", "Conn", "Actv", "Hosts", "DBs", "Select", "Insert", "Update", "Delete", "Other") + return fmt.Sprintf("%-14s|%8s %6s|%8s %6s|%4s %4s|%5s %3s|%3s %3s %3s %3s %3s|", + "User", "Run Time", "%", "Sleep", "%", "Conn", "Actv", "Hosts", "DBs", "Sel", "Ins", "Upd", "Del", "Oth") } // generate a printable result @@ -56,19 +57,21 @@ func (r *pl_by_user_row) row_content(totals pl_by_user_row) string { } else { u = r.username } - return fmt.Sprintf("%-14s|%10s %6s|%4s %4s|%5s %3s|%6s %6s %6s %6s %6s|", + return fmt.Sprintf("%-14s|%8s %6s|%8s %6s|%4s %4s|%5s %3s|%3s %3s %3s %3s %3s|", u, - lib.FormatTime(r.runtime), + lib.FormatSeconds(r.runtime), lib.FormatPct(lib.MyDivide(r.runtime, totals.runtime)), - lib.FormatAmount(r.connections), - lib.FormatAmount(r.active), - lib.FormatAmount(r.hosts), - lib.FormatAmount(r.dbs), - lib.FormatAmount(r.selects), - lib.FormatAmount(r.inserts), - lib.FormatAmount(r.updates), - lib.FormatAmount(r.deletes), - lib.FormatAmount(r.other)) + lib.FormatSeconds(r.sleeptime), + lib.FormatPct(lib.MyDivide(r.sleeptime, totals.sleeptime)), + lib.FormatCounter(int(r.connections), 4), + lib.FormatCounter(int(r.active), 4), + lib.FormatCounter(int(r.hosts), 5), + lib.FormatCounter(int(r.dbs), 3), + lib.FormatCounter(int(r.selects), 3), + lib.FormatCounter(int(r.inserts), 3), + lib.FormatCounter(int(r.updates), 3), + lib.FormatCounter(int(r.deletes), 3), + lib.FormatCounter(int(r.other), 3)) } // generate a row of totals from a table @@ -78,6 +81,7 @@ func (t pl_by_user_rows) totals() pl_by_user_row { for i := range t { totals.runtime += t[i].runtime + totals.sleeptime += t[i].sleeptime totals.connections += t[i].connections totals.active += t[i].active // totals.hosts += t[i].hosts This needs to be done differently to get the total number of distinct hosts @@ -99,7 +103,12 @@ func (t pl_by_user_rows) Headings() string { // describe a whole row func (r pl_by_user_row) String() string { - return fmt.Sprintf("%v %v %v %v %v %v %v %v %v", r.username, r.runtime, r.connections, r.active, r.hosts, r.dbs, r.selects, r.inserts, r.updates, r.deletes, r.other) + return fmt.Sprintf("%v %v %v %v %v %v %v %v %v %v", r.username, r.runtime, r.connections, r.sleeptime, r.active, r.hosts, r.dbs, r.selects, r.inserts, r.updates, r.deletes, r.other) +} + +// total time is runtime + sleeptime +func (r pl_by_user_row) total_time() uint64 { + return r.runtime + r.sleeptime } // describe a whole table @@ -117,9 +126,9 @@ type ByRunTime pl_by_user_rows func (t ByRunTime) Len() int { return len(t) } func (t ByRunTime) Swap(i, j int) { t[i], t[j] = t[j], t[i] } func (t ByRunTime) Less(i, j int) bool { - return (t[i].runtime > t[j].runtime) || - ((t[i].runtime == t[j].runtime) && (t[i].connections > t[j].connections)) || - ((t[i].runtime == t[j].runtime) && (t[i].connections == t[j].connections) && (t[i].username < t[j].username)) + return (t[i].total_time() > t[j].total_time()) || + ((t[i].total_time() == t[j].total_time()) && (t[i].connections > t[j].connections)) || + ((t[i].total_time() == t[j].total_time()) && (t[i].connections == t[j].connections) && (t[i].username < t[j].username)) } func (t pl_by_user_rows) Sort() { diff --git a/i_s/processlist.go b/i_s/processlist.go index 50d5e92..81178e9 100644 --- a/i_s/processlist.go +++ b/i_s/processlist.go @@ -138,9 +138,13 @@ func (t *Processlist) processlist2by_user() { } row.connections++ // ignore system SQL threads (may be more to filter out) - if username != "system user" && host != "" && command != "Sleep" && command != "Binlog Dump" { - row.runtime += t.current[i].TIME - row.active++ + if username != "system user" && host != "" && command != "Binlog Dump" { + if command == "Sleep" { + row.sleeptime += t.current[i].TIME + } else { + row.runtime += t.current[i].TIME + row.active++ + } } if command == "Binlog Dump" && re_active_repl_master_thread.MatchString(state) { row.active++ diff --git a/i_s/processlist_row.go b/i_s/processlist_row.go index 60aee47..470d545 100644 --- a/i_s/processlist_row.go +++ b/i_s/processlist_row.go @@ -76,7 +76,7 @@ func select_processlist(dbh *sql.DB) processlist_rows { r.DB = db.String } r.COMMAND = command.String - r.TIME = uint64(time.Int64 * 1000000000000) + r.TIME = uint64(time.Int64) if state.Valid { r.STATE = state.String } diff --git a/lib/common.go b/lib/common.go index 9d31075..3cee791 100644 --- a/lib/common.go +++ b/lib/common.go @@ -43,6 +43,15 @@ func sec_to_time(d uint64) string { return fmt.Sprintf("%02d:%02d:%02d", hours, minutes, seconds) } +// similar to sec_to_time() spaces if 0 and takes seconds as input. +func FormatSeconds(seconds uint64) string { + if seconds == 0 { + return " " + } else { + return sec_to_time(seconds) + } +} + // FormatTime is based on sys.format_time. It // formats to 10 characters including space and suffix. // All values have 2 decimal places. Zero is returned as @@ -128,6 +137,17 @@ func FormatAmount(amount uint64) string { return formatted } +// like Amount but tigher in space +func FormatCounter( counter int, width int ) string { + if counter == 0 { + pattern := "%" + fmt.Sprintf("%d", width) + "s" + return fmt.Sprintf( pattern, " " ) + } else { + pattern := "%" + fmt.Sprintf("%d", width) + "d" + return fmt.Sprintf( pattern, counter ) + } +} + // MyDivide() divides a by b except if b is 0 in which case we return 0. func MyDivide(a uint64, b uint64) float64 { if b == 0 { diff --git a/screen_samples.txt b/screen_samples.txt index 6a2b1d0..da58b8f 100644 --- a/screen_samples.txt +++ b/screen_samples.txt @@ -73,3 +73,23 @@ wordpress.wp_usermeta 106.90 us 1.3%| 98.0% 2.0%| wordpress.wp_links 42.85 us 0.5%|100.0% | 24.3% 75.7%| | | | TOTALS 8.24 ms 100.0%| 95.6% 4.4%| 30.8% 64.8%| 1.1% 2.9% + +----------------------------------------------------------------------------- + +pstop 0.1.4 - 0:37:29 xxxxxxxxxxxxxxx / 5.6.xx-log, up 170d 13h 19m 1s [REL] 26 seconds +Activity by Username (processlist) 11 rows +User |Run Time %| Sleep %|Conn Actv|Hosts DBs|Sel Ins Upd Del Oth| +aaaaaaaaa | |00:04:01 72.4%|1146 1| 573 1| 1 | +bbbbbbbbbbbb | |00:01:04 19.2%| 2 | 1 | | +ccccccccc | |00:00:26 7.8%| 5 1| 3 1| | +ddddddd | |00:00:02 0.6%| 9 | 4 1| | +eeeeeeeee | | | 62 7| 62 | | +fffffffff | | | 24 | 17 1| | +gggggggg | | | 2 | 2 1| | +hhhhh | | | 1 1| 1 1| 1 | +iiiiiiiiiiiiii| | | 1 1| 1 | | +jjjjjjjjjjjj | | | 1 1| 1 | | +kkkkkkkkkkkkkk| | | 1 1| 1 | | +... + | | | | | | +Totals | |00:05:33 100.0%|1254 13| | 2 | diff --git a/version/version.go b/version/version.go index 74ffa65..ce76b72 100644 --- a/version/version.go +++ b/version/version.go @@ -2,7 +2,7 @@ package version const ( - version = "0.1.4" + version = "0.1.5" ) // return the current application version -- 2.20.1