p_s/table_io_waits_summary_by_table/table_io_waits_summary_by_table_row.go

   1 // This file contains the library routines for managing the
   2 // table_io_waits_by_table table.
   3 package table_io_waits_summary_by_table
   4
   5 import (
   6         "database/sql"
   7         "fmt"
   8         "log"
   9         "sort"
  10         "strings"
  11
  12         "github.com/sjmudd/pstop/lib"
  13 )
  14
  15 // a row from performance_schema.table_io_waits_summary_by_table
  16 type table_io_waits_summary_by_table_row struct {
  17         // Note: upper case names to match the performance_schema column names
  18         // This type is _not_ exported.
  19
  20         OBJECT_TYPE   string // in theory redundant but keep anyway
  21         OBJECT_SCHEMA string // in theory redundant but keep anyway
  22         OBJECT_NAME   string // in theory redundant but keep anyway
  23
  24         SUM_TIMER_WAIT   uint64
  25         SUM_TIMER_READ   uint64
  26         SUM_TIMER_WRITE  uint64
  27         SUM_TIMER_FETCH  uint64
  28         SUM_TIMER_INSERT uint64
  29         SUM_TIMER_UPDATE uint64
  30         SUM_TIMER_DELETE uint64
  31
  32         COUNT_STAR   uint64
  33         COUNT_READ   uint64
  34         COUNT_WRITE  uint64
  35         COUNT_FETCH  uint64
  36         COUNT_INSERT uint64
  37         COUNT_UPDATE uint64
  38         COUNT_DELETE uint64
  39 }
  40 type table_io_waits_summary_by_table_rows []table_io_waits_summary_by_table_row
  41
  42 // // return the table name from the columns as '<schema>.<table>'
  43 func (r *table_io_waits_summary_by_table_row) name() string {
  44         var n string
  45         if len(r.OBJECT_SCHEMA) > 0 {
  46                 n += r.OBJECT_SCHEMA
  47         }
  48         if len(n) > 0 {
  49                 if len(r.OBJECT_NAME) > 0 {
  50                         n += "." + r.OBJECT_NAME
  51                 }
  52         } else {
  53                 if len(r.OBJECT_NAME) > 0 {
  54                         n += r.OBJECT_NAME
  55                 }
  56         }
  57         return n
  58 }
  59
  60 func (r *table_io_waits_summary_by_table_row) pretty_name() string {
  61         s := r.name()
  62         if len(s) > 30 {
  63                 s = s[:29]
  64         }
  65         return fmt.Sprintf("%-30s", s)
  66 }
  67
  68 func (r *table_io_waits_summary_by_table_row) latency_headings() string {
  69         return fmt.Sprintf("%-30s %10s %6s|%6s %6s %6s %6s", "Table Name", "Latency", "%", "Fetch", "Insert", "Update", "Delete")
  70 }
  71 func (r *table_io_waits_summary_by_table_row) ops_headings() string {
  72         return fmt.Sprintf("%-30s %10s %6s|%6s %6s %6s %6s", "Table Name", "Ops", "%", "Fetch", "Insert", "Update", "Delete")
  73 }
  74
  75 // generate a printable result
  76 func (r *table_io_waits_summary_by_table_row) latency_row_content(totals table_io_waits_summary_by_table_row) string {
  77         // assume the data is empty so hide it.
  78         name := r.pretty_name()
  79         if r.COUNT_STAR == 0 {
  80                 name = ""
  81         }
  82
  83         return fmt.Sprintf("%-30s %10s %6s|%6s %6s %6s %6s",
  84                 name,
  85                 lib.FormatTime(r.SUM_TIMER_WAIT),
  86                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WAIT, totals.SUM_TIMER_WAIT)),
  87                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_FETCH, r.SUM_TIMER_WAIT)),
  88                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_INSERT, r.SUM_TIMER_WAIT)),
  89                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_UPDATE, r.SUM_TIMER_WAIT)),
  90                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_DELETE, r.SUM_TIMER_WAIT)))
  91 }
  92
  93 // generate a printable result for ops
  94 func (r *table_io_waits_summary_by_table_row) ops_row_content(totals table_io_waits_summary_by_table_row) string {
  95         // assume the data is empty so hide it.
  96         name := r.pretty_name()
  97         if r.COUNT_STAR == 0 {
  98                 name = ""
  99         }
 100
 101         return fmt.Sprintf("%-30s %10s %6s|%6s %6s %6s %6s",
 102                 name,
 103                 lib.FormatAmount(r.COUNT_STAR),
 104                 lib.FormatPct(lib.MyDivide(r.COUNT_STAR, totals.COUNT_STAR)),
 105                 lib.FormatPct(lib.MyDivide(r.COUNT_FETCH, r.COUNT_STAR)),
 106                 lib.FormatPct(lib.MyDivide(r.COUNT_INSERT, r.COUNT_STAR)),
 107                 lib.FormatPct(lib.MyDivide(r.COUNT_UPDATE, r.COUNT_STAR)),
 108                 lib.FormatPct(lib.MyDivide(r.COUNT_DELETE, r.COUNT_STAR)))
 109 }
 110
 111 func (this *table_io_waits_summary_by_table_row) add(other table_io_waits_summary_by_table_row) {
 112         this.SUM_TIMER_WAIT += other.SUM_TIMER_WAIT
 113         this.SUM_TIMER_FETCH += other.SUM_TIMER_FETCH
 114         this.SUM_TIMER_INSERT += other.SUM_TIMER_INSERT
 115         this.SUM_TIMER_UPDATE += other.SUM_TIMER_UPDATE
 116         this.SUM_TIMER_DELETE += other.SUM_TIMER_DELETE
 117         this.SUM_TIMER_READ += other.SUM_TIMER_READ
 118         this.SUM_TIMER_WRITE += other.SUM_TIMER_WRITE
 119
 120         this.COUNT_STAR += other.COUNT_STAR
 121         this.COUNT_FETCH += other.COUNT_FETCH
 122         this.COUNT_INSERT += other.COUNT_INSERT
 123         this.COUNT_UPDATE += other.COUNT_UPDATE
 124         this.COUNT_DELETE += other.COUNT_DELETE
 125         this.COUNT_READ += other.COUNT_READ
 126         this.COUNT_WRITE += other.COUNT_WRITE
 127 }
 128
 129 // subtract the countable values in one row from another
 130 func (this *table_io_waits_summary_by_table_row) subtract(other table_io_waits_summary_by_table_row) {
 131         // check for issues here (we have a bug) and log it
 132         // - this situation should not happen so there's a logic bug somewhere else
 133         if this.SUM_TIMER_WAIT >= other.SUM_TIMER_WAIT {
 134                 this.SUM_TIMER_WAIT -= other.SUM_TIMER_WAIT
 135                 this.SUM_TIMER_FETCH -= other.SUM_TIMER_FETCH
 136                 this.SUM_TIMER_INSERT -= other.SUM_TIMER_INSERT
 137                 this.SUM_TIMER_UPDATE -= other.SUM_TIMER_UPDATE
 138                 this.SUM_TIMER_DELETE -= other.SUM_TIMER_DELETE
 139                 this.SUM_TIMER_READ -= other.SUM_TIMER_READ
 140                 this.SUM_TIMER_WRITE -= other.SUM_TIMER_WRITE
 141
 142                 this.COUNT_STAR -= other.COUNT_STAR
 143                 this.COUNT_FETCH -= other.COUNT_FETCH
 144                 this.COUNT_INSERT -= other.COUNT_INSERT
 145                 this.COUNT_UPDATE -= other.COUNT_UPDATE
 146                 this.COUNT_DELETE -= other.COUNT_DELETE
 147                 this.COUNT_READ -= other.COUNT_READ
 148                 this.COUNT_WRITE -= other.COUNT_WRITE
 149         } else {
 150                 lib.Logger.Println("WARNING: table_io_waits_summary_by_table_row.subtract() - subtraction problem! (not subtracting)")
 151                 lib.Logger.Println("this=", this)
 152                 lib.Logger.Println("other=", other)
 153         }
 154 }
 155
 156 func (t table_io_waits_summary_by_table_rows) totals() table_io_waits_summary_by_table_row {
 157         var totals table_io_waits_summary_by_table_row
 158         totals.OBJECT_SCHEMA = "TOTALS"
 159
 160         for i := range t {
 161                 totals.add(t[i])
 162         }
 163
 164         return totals
 165 }
 166
 167 func select_tiwsbt_rows(dbh *sql.DB) table_io_waits_summary_by_table_rows {
 168         var t table_io_waits_summary_by_table_rows
 169
 170         // we collect all information even if it's mainly empty as we may reference it later
 171         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"
 172
 173         rows, err := dbh.Query(sql)
 174         if err != nil {
 175                 log.Fatal(err)
 176         }
 177         defer rows.Close()
 178
 179         for rows.Next() {
 180                 var r table_io_waits_summary_by_table_row
 181                 if err := rows.Scan(
 182                         &r.OBJECT_TYPE,
 183                         &r.OBJECT_SCHEMA,
 184                         &r.OBJECT_NAME,
 185                         &r.COUNT_STAR,
 186                         &r.SUM_TIMER_WAIT,
 187                         &r.COUNT_READ,
 188                         &r.SUM_TIMER_READ,
 189                         &r.COUNT_WRITE,
 190                         &r.SUM_TIMER_WRITE,
 191                         &r.COUNT_FETCH,
 192                         &r.SUM_TIMER_FETCH,
 193                         &r.COUNT_INSERT,
 194                         &r.SUM_TIMER_INSERT,
 195                         &r.COUNT_UPDATE,
 196                         &r.SUM_TIMER_UPDATE,
 197                         &r.COUNT_DELETE,
 198                         &r.SUM_TIMER_DELETE); err != nil {
 199                         log.Fatal(err)
 200                 }
 201                 // we collect all information even if it's mainly empty as we may reference it later
 202                 t = append(t, r)
 203         }
 204         if err := rows.Err(); err != nil {
 205                 log.Fatal(err)
 206         }
 207
 208         return t
 209 }
 210
 211 func (t table_io_waits_summary_by_table_rows) Len() int      { return len(t) }
 212 func (t table_io_waits_summary_by_table_rows) Swap(i, j int) { t[i], t[j] = t[j], t[i] }
 213
 214 // sort by value (descending) but also by "name" (ascending) if the values are the same
 215 func (t table_io_waits_summary_by_table_rows) Less(i, j int) bool {
 216         return (t[i].SUM_TIMER_WAIT > t[j].SUM_TIMER_WAIT) ||
 217                 ((t[i].SUM_TIMER_WAIT == t[j].SUM_TIMER_WAIT) &&
 218                         (t[i].OBJECT_SCHEMA < t[j].OBJECT_SCHEMA) &&
 219                         (t[i].OBJECT_NAME < t[j].OBJECT_NAME))
 220 }
 221
 222 // for sorting
 223 type ByOps table_io_waits_summary_by_table_rows
 224
 225 func (t ByOps) Len() int      { return len(t) }
 226 func (t ByOps) Swap(i, j int) { t[i], t[j] = t[j], t[i] }
 227 func (t ByOps) Less(i, j int) bool {
 228         return (t[i].COUNT_STAR > t[j].COUNT_STAR) ||
 229                 ((t[i].SUM_TIMER_WAIT == t[j].SUM_TIMER_WAIT) &&
 230                         (t[i].OBJECT_SCHEMA < t[j].OBJECT_SCHEMA) &&
 231                         (t[i].OBJECT_NAME < t[j].OBJECT_NAME))
 232 }
 233
 234 func (t table_io_waits_summary_by_table_rows) Sort(want_latency bool) {
 235         if want_latency {
 236                 sort.Sort(t)
 237         } else {
 238                 sort.Sort(ByOps(t))
 239         }
 240 }
 241
 242 // remove the initial values from those rows where there's a match
 243 // - if we find a row we can't match ignore it
 244 func (this *table_io_waits_summary_by_table_rows) subtract(initial table_io_waits_summary_by_table_rows) {
 245         initial_by_name := make(map[string]int)
 246
 247         // iterate over rows by name
 248         for i := range initial {
 249                 initial_by_name[initial[i].name()] = i
 250         }
 251
 252         for i := range *this {
 253                 this_name := (*this)[i].name()
 254                 if _, ok := initial_by_name[this_name]; ok {
 255                         initial_index := initial_by_name[this_name]
 256                         (*this)[i].subtract(initial[initial_index])
 257                 }
 258         }
 259 }
 260
 261 // if the data in t2 is "newer", "has more values" than t then it needs refreshing.
 262 // check this by comparing totals.
 263 func (t table_io_waits_summary_by_table_rows) needs_refresh(t2 table_io_waits_summary_by_table_rows) bool {
 264         my_totals := t.totals()
 265         t2_totals := t2.totals()
 266
 267         return my_totals.SUM_TIMER_WAIT > t2_totals.SUM_TIMER_WAIT
 268 }
 269
 270 // describe a whole row
 271 func (r table_io_waits_summary_by_table_row) String() string {
 272         return fmt.Sprintf("%-30s|%10s %10s %10s %10s %10s|%10s %10s|%10s %10s %10s %10s %10s|%10s %10s",
 273                 r.pretty_name(),
 274                 lib.FormatTime(r.SUM_TIMER_WAIT),
 275                 lib.FormatTime(r.SUM_TIMER_FETCH),
 276                 lib.FormatTime(r.SUM_TIMER_INSERT),
 277                 lib.FormatTime(r.SUM_TIMER_UPDATE),
 278                 lib.FormatTime(r.SUM_TIMER_DELETE),
 279
 280                 lib.FormatTime(r.SUM_TIMER_READ),
 281                 lib.FormatTime(r.SUM_TIMER_WRITE),
 282
 283                 lib.FormatAmount(r.COUNT_STAR),
 284                 lib.FormatAmount(r.COUNT_FETCH),
 285                 lib.FormatAmount(r.COUNT_INSERT),
 286                 lib.FormatAmount(r.COUNT_UPDATE),
 287                 lib.FormatAmount(r.COUNT_DELETE),
 288
 289                 lib.FormatAmount(r.COUNT_READ),
 290                 lib.FormatAmount(r.COUNT_WRITE))
 291 }
 292
 293 // describe a whole table
 294 func (t table_io_waits_summary_by_table_rows) String() string {
 295         s := make([]string, len(t))
 296
 297         for i := range t {
 298                 s = append(s, t[i].String())
 299         }
 300
 301         return strings.Join(s, "\n")
 302 }