X-Git-Url: http://git.iain.cx/?a=blobdiff_plain;f=p_s%2Ftable_io_waits_summary_by_table%2Fprivate.go;fp=p_s%2Ftable_io_waits_summary_by_table%2Fprivate.go;h=92195e154522075001ca1b8d9188d5fa11f08906;hb=7a83b8c7acba48f12c42dfb201bdfe3276173f44;hp=0000000000000000000000000000000000000000;hpb=dde216b3b7ec75702a633abf3c57a408c528a011;p=pstop.git diff --git a/p_s/table_io_waits_summary_by_table/private.go b/p_s/table_io_waits_summary_by_table/private.go new file mode 100644 index 0000000..92195e1 --- /dev/null +++ b/p_s/table_io_waits_summary_by_table/private.go @@ -0,0 +1,302 @@ +// This file contains the library routines for managing the +// table_io_waits_by_table table. +package table_io_waits_summary_by_table + +import ( + "database/sql" + "fmt" + "log" + "sort" + "strings" + + "github.com/sjmudd/pstop/lib" +) + +// a row from performance_schema.table_io_waits_summary_by_table +type table_row struct { + // Note: upper case names to match the performance_schema column names + // This type is _not_ exported. + + OBJECT_TYPE string // in theory redundant but keep anyway + OBJECT_SCHEMA string // in theory redundant but keep anyway + OBJECT_NAME string // in theory redundant but keep anyway + + SUM_TIMER_WAIT uint64 + SUM_TIMER_READ uint64 + SUM_TIMER_WRITE uint64 + SUM_TIMER_FETCH uint64 + SUM_TIMER_INSERT uint64 + SUM_TIMER_UPDATE uint64 + SUM_TIMER_DELETE uint64 + + COUNT_STAR uint64 + COUNT_READ uint64 + COUNT_WRITE uint64 + COUNT_FETCH uint64 + COUNT_INSERT uint64 + COUNT_UPDATE uint64 + COUNT_DELETE uint64 +} +type table_rows []table_row + +// // return the table name from the columns as '.' +func (r *table_row) name() string { + var n string + if len(r.OBJECT_SCHEMA) > 0 { + n += r.OBJECT_SCHEMA + } + if len(n) > 0 { + if len(r.OBJECT_NAME) > 0 { + n += "." + r.OBJECT_NAME + } + } else { + if len(r.OBJECT_NAME) > 0 { + n += r.OBJECT_NAME + } + } + return n +} + +func (r *table_row) pretty_name() string { + s := r.name() + if len(s) > 30 { + s = s[:29] + } + return s +} + +func (r *table_row) latency_headings() string { + return fmt.Sprintf("%-30s %10s %6s|%6s %6s %6s %6s", "Table Name", "Latency", "%", "Fetch", "Insert", "Update", "Delete") +} +func (r *table_row) ops_headings() string { + return fmt.Sprintf("%-30s %10s %6s|%6s %6s %6s %6s", "Table Name", "Ops", "%", "Fetch", "Insert", "Update", "Delete") +} + +// generate a printable result +func (r *table_row) latency_row_content(totals table_row) string { + // assume the data is empty so hide it. + name := r.pretty_name() + if r.COUNT_STAR == 0 && name != "Totals" { + name = "" + } + + return fmt.Sprintf("%-30s %10s %6s|%6s %6s %6s %6s", + name, + lib.FormatTime(r.SUM_TIMER_WAIT), + lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WAIT, totals.SUM_TIMER_WAIT)), + lib.FormatPct(lib.MyDivide(r.SUM_TIMER_FETCH, r.SUM_TIMER_WAIT)), + lib.FormatPct(lib.MyDivide(r.SUM_TIMER_INSERT, r.SUM_TIMER_WAIT)), + lib.FormatPct(lib.MyDivide(r.SUM_TIMER_UPDATE, r.SUM_TIMER_WAIT)), + lib.FormatPct(lib.MyDivide(r.SUM_TIMER_DELETE, r.SUM_TIMER_WAIT))) +} + +// generate a printable result for ops +func (r *table_row) ops_row_content(totals table_row) string { + // assume the data is empty so hide it. + name := r.pretty_name() + if r.COUNT_STAR == 0 && name != "Totals" { + name = "" + } + + return fmt.Sprintf("%-30s %10s %6s|%6s %6s %6s %6s", + name, + lib.FormatAmount(r.COUNT_STAR), + lib.FormatPct(lib.MyDivide(r.COUNT_STAR, totals.COUNT_STAR)), + lib.FormatPct(lib.MyDivide(r.COUNT_FETCH, r.COUNT_STAR)), + lib.FormatPct(lib.MyDivide(r.COUNT_INSERT, r.COUNT_STAR)), + lib.FormatPct(lib.MyDivide(r.COUNT_UPDATE, r.COUNT_STAR)), + lib.FormatPct(lib.MyDivide(r.COUNT_DELETE, r.COUNT_STAR))) +} + +func (this *table_row) add(other table_row) { + this.SUM_TIMER_WAIT += other.SUM_TIMER_WAIT + this.SUM_TIMER_FETCH += other.SUM_TIMER_FETCH + this.SUM_TIMER_INSERT += other.SUM_TIMER_INSERT + this.SUM_TIMER_UPDATE += other.SUM_TIMER_UPDATE + this.SUM_TIMER_DELETE += other.SUM_TIMER_DELETE + this.SUM_TIMER_READ += other.SUM_TIMER_READ + this.SUM_TIMER_WRITE += other.SUM_TIMER_WRITE + + this.COUNT_STAR += other.COUNT_STAR + this.COUNT_FETCH += other.COUNT_FETCH + this.COUNT_INSERT += other.COUNT_INSERT + this.COUNT_UPDATE += other.COUNT_UPDATE + this.COUNT_DELETE += other.COUNT_DELETE + this.COUNT_READ += other.COUNT_READ + this.COUNT_WRITE += other.COUNT_WRITE +} + +// subtract the countable values in one row from another +func (this *table_row) subtract(other table_row) { + // check for issues here (we have a bug) and log it + // - this situation should not happen so there's a logic bug somewhere else + if this.SUM_TIMER_WAIT >= other.SUM_TIMER_WAIT { + this.SUM_TIMER_WAIT -= other.SUM_TIMER_WAIT + this.SUM_TIMER_FETCH -= other.SUM_TIMER_FETCH + this.SUM_TIMER_INSERT -= other.SUM_TIMER_INSERT + this.SUM_TIMER_UPDATE -= other.SUM_TIMER_UPDATE + this.SUM_TIMER_DELETE -= other.SUM_TIMER_DELETE + this.SUM_TIMER_READ -= other.SUM_TIMER_READ + this.SUM_TIMER_WRITE -= other.SUM_TIMER_WRITE + + this.COUNT_STAR -= other.COUNT_STAR + this.COUNT_FETCH -= other.COUNT_FETCH + this.COUNT_INSERT -= other.COUNT_INSERT + this.COUNT_UPDATE -= other.COUNT_UPDATE + this.COUNT_DELETE -= other.COUNT_DELETE + this.COUNT_READ -= other.COUNT_READ + this.COUNT_WRITE -= other.COUNT_WRITE + } else { + lib.Logger.Println("WARNING: table_row.subtract() - subtraction problem! (not subtracting)") + lib.Logger.Println("this=", this) + lib.Logger.Println("other=", other) + } +} + +func (t table_rows) totals() table_row { + var totals table_row + totals.OBJECT_SCHEMA = "Totals" + + for i := range t { + totals.add(t[i]) + } + + return totals +} + +func select_rows(dbh *sql.DB) table_rows { + var t table_rows + + // we collect all information even if it's mainly empty as we may reference it later + sql := "SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, SUM_TIMER_WAIT, COUNT_READ, SUM_TIMER_READ, COUNT_WRITE, SUM_TIMER_WRITE, COUNT_FETCH, SUM_TIMER_FETCH, COUNT_INSERT, SUM_TIMER_INSERT, COUNT_UPDATE, SUM_TIMER_UPDATE, COUNT_DELETE, SUM_TIMER_DELETE FROM table_io_waits_summary_by_table WHERE SUM_TIMER_WAIT > 0" + + rows, err := dbh.Query(sql) + if err != nil { + log.Fatal(err) + } + defer rows.Close() + + for rows.Next() { + var r table_row + if err := rows.Scan( + &r.OBJECT_TYPE, + &r.OBJECT_SCHEMA, + &r.OBJECT_NAME, + &r.COUNT_STAR, + &r.SUM_TIMER_WAIT, + &r.COUNT_READ, + &r.SUM_TIMER_READ, + &r.COUNT_WRITE, + &r.SUM_TIMER_WRITE, + &r.COUNT_FETCH, + &r.SUM_TIMER_FETCH, + &r.COUNT_INSERT, + &r.SUM_TIMER_INSERT, + &r.COUNT_UPDATE, + &r.SUM_TIMER_UPDATE, + &r.COUNT_DELETE, + &r.SUM_TIMER_DELETE); err != nil { + log.Fatal(err) + } + // we collect all information even if it's mainly empty as we may reference it later + t = append(t, r) + } + if err := rows.Err(); err != nil { + log.Fatal(err) + } + + return t +} + +func (t table_rows) Len() int { return len(t) } +func (t table_rows) Swap(i, j int) { t[i], t[j] = t[j], t[i] } + +// sort by value (descending) but also by "name" (ascending) if the values are the same +func (t table_rows) Less(i, j int) bool { + return (t[i].SUM_TIMER_WAIT > t[j].SUM_TIMER_WAIT) || + ((t[i].SUM_TIMER_WAIT == t[j].SUM_TIMER_WAIT) && + (t[i].OBJECT_SCHEMA < t[j].OBJECT_SCHEMA) && + (t[i].OBJECT_NAME < t[j].OBJECT_NAME)) +} + +// for sorting +type ByOps table_rows + +func (t ByOps) Len() int { return len(t) } +func (t ByOps) Swap(i, j int) { t[i], t[j] = t[j], t[i] } +func (t ByOps) Less(i, j int) bool { + return (t[i].COUNT_STAR > t[j].COUNT_STAR) || + ((t[i].SUM_TIMER_WAIT == t[j].SUM_TIMER_WAIT) && + (t[i].OBJECT_SCHEMA < t[j].OBJECT_SCHEMA) && + (t[i].OBJECT_NAME < t[j].OBJECT_NAME)) +} + +func (t table_rows) Sort(want_latency bool) { + if want_latency { + sort.Sort(t) + } else { + sort.Sort(ByOps(t)) + } +} + +// remove the initial values from those rows where there's a match +// - if we find a row we can't match ignore it +func (this *table_rows) subtract(initial table_rows) { + initial_by_name := make(map[string]int) + + // iterate over rows by name + for i := range initial { + initial_by_name[initial[i].name()] = i + } + + for i := range *this { + this_name := (*this)[i].name() + if _, ok := initial_by_name[this_name]; ok { + initial_index := initial_by_name[this_name] + (*this)[i].subtract(initial[initial_index]) + } + } +} + +// if the data in t2 is "newer", "has more values" than t then it needs refreshing. +// check this by comparing totals. +func (t table_rows) needs_refresh(t2 table_rows) bool { + my_totals := t.totals() + t2_totals := t2.totals() + + return my_totals.SUM_TIMER_WAIT > t2_totals.SUM_TIMER_WAIT +} + +// describe a whole row +func (r table_row) String() string { + return fmt.Sprintf("%-30s|%10s %10s %10s %10s %10s|%10s %10s|%10s %10s %10s %10s %10s|%10s %10s", + r.pretty_name(), + lib.FormatTime(r.SUM_TIMER_WAIT), + lib.FormatTime(r.SUM_TIMER_FETCH), + lib.FormatTime(r.SUM_TIMER_INSERT), + lib.FormatTime(r.SUM_TIMER_UPDATE), + lib.FormatTime(r.SUM_TIMER_DELETE), + + lib.FormatTime(r.SUM_TIMER_READ), + lib.FormatTime(r.SUM_TIMER_WRITE), + + lib.FormatAmount(r.COUNT_STAR), + lib.FormatAmount(r.COUNT_FETCH), + lib.FormatAmount(r.COUNT_INSERT), + lib.FormatAmount(r.COUNT_UPDATE), + lib.FormatAmount(r.COUNT_DELETE), + + lib.FormatAmount(r.COUNT_READ), + lib.FormatAmount(r.COUNT_WRITE)) +} + +// describe a whole table +func (t table_rows) String() string { + s := make([]string, len(t)) + + for i := range t { + s = append(s, t[i].String()) + } + + return strings.Join(s, "\n") +}