p_s/ps_table/ps_table_row.go

   1 // This file contains the library routines for managing the
   2 // table_lock_waits_summary_by_table table.
   3 package ps_table
   4
   5 /* *****
   6 import (
   7         "database/sql"
   8         "fmt"
   9         _ "github.com/go-sql-driver/mysql"
  10         "log"
  11         "sort"
  12         "strings"
  13
  14         "github.com/sjmudd/pstop/lib"
  15 )
  16
  17 type table_lock_waits_summary_by_table_row struct {
  18         OBJECT_TYPE   string // in theory redundant but keep anyway
  19         OBJECT_SCHEMA string // in theory redundant but keep anyway
  20         OBJECT_NAME   string // in theory redundant but keep anyway
  21         COUNT_STAR    int
  22
  23         SUM_TIMER_WAIT  int
  24         SUM_TIMER_READ  int
  25         SUM_TIMER_WRITE int
  26
  27         SUM_TIMER_READ_WITH_SHARED_LOCKS int
  28         SUM_TIMER_READ_HIGH_PRIORITY     int
  29         SUM_TIMER_READ_NO_INSERT         int
  30         SUM_TIMER_READ_NORMAL            int
  31         SUM_TIMER_READ_EXTERNAL          int
  32
  33         SUM_TIMER_WRITE_ALLOW_WRITE       int
  34         SUM_TIMER_WRITE_CONCURRENT_INSERT int
  35         SUM_TIMER_WRITE_DELAYED           int
  36         SUM_TIMER_WRITE_LOW_PRIORITY      int
  37         SUM_TIMER_WRITE_NORMAL            int
  38         SUM_TIMER_WRITE_EXTERNAL          int
  39 }
  40
  41 type table_lock_waits_summary_by_table_rows []table_lock_waits_summary_by_table_row
  42
  43 // return the table name from the columns as '<schema>.<table>'
  44 func (r *table_lock_waits_summary_by_table_row) name() string {
  45         var n string
  46         if len(r.OBJECT_SCHEMA) > 0 {
  47                 n += r.OBJECT_SCHEMA
  48         }
  49         if len(n) > 0 {
  50                 if len(r.OBJECT_NAME) > 0 {
  51                         n += "." + r.OBJECT_NAME
  52                 }
  53         } else {
  54                 if len(r.OBJECT_NAME) > 0 {
  55                         n += r.OBJECT_NAME
  56                 }
  57         }
  58         return n
  59 }
  60
  61 func (r *table_lock_waits_summary_by_table_row) pretty_name() string {
  62         s := r.name()
  63         if len(s) > 30 {
  64                 s = s[:29]
  65         }
  66         return fmt.Sprintf("%-30s", s)
  67 }
  68
  69 // Table Name                        Latency      %|  Read  Write|S.Lock   High  NoIns Normal Extrnl|AlloWr CncIns WrtDly    Low Normal Extrnl|
  70 // xxxxxxxxxxxxxxxxxxxxxxxxxxxxx  1234567890 100.0%|xxxxx% xxxxx%|xxxxx% xxxxx% xxxxx% xxxxx% xxxxx%|xxxxx% xxxxx% xxxxx% xxxxx% xxxxx% xxxxx%|
  71 func (r *table_lock_waits_summary_by_table_row) headings() string {
  72         return fmt.Sprintf("%-30s %10s %6s|%6s %6s|%6s %6s %6s %6s %6s|%6s %6s %6s %6s %6s %6s",
  73                 "Table Name", "Latency", "%",
  74                 "Read", "Write",
  75                 "S.Lock", "High", "NoIns", "Normal", "Extrnl",
  76                 "AlloWr", "CncIns", "WrtDly", "Low", "Normal", "Extrnl")
  77 }
  78
  79 // generate a printable result
  80 func (r *table_lock_waits_summary_by_table_row) row_content(totals table_lock_waits_summary_by_table_row) string {
  81
  82         // assume the data is empty so hide it.
  83         name := r.pretty_name()
  84         if r.COUNT_STAR == 0 {
  85                 name = ""
  86         }
  87
  88         return fmt.Sprintf("%-30s %10s %6s|%6s %6s|%6s %6s %6s %6s %6s|%6s %6s %6s %6s %6s %6s",
  89                 name,
  90                 lib.FormatTime(r.SUM_TIMER_WAIT),
  91                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WAIT, totals.SUM_TIMER_WAIT)),
  92
  93                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_READ, r.SUM_TIMER_WAIT)),
  94                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WRITE, r.SUM_TIMER_WAIT)),
  95
  96                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_READ_WITH_SHARED_LOCKS, r.SUM_TIMER_WAIT)),
  97                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_READ_HIGH_PRIORITY, r.SUM_TIMER_WAIT)),
  98                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_READ_NO_INSERT, r.SUM_TIMER_WAIT)),
  99                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_READ_NORMAL, r.SUM_TIMER_WAIT)),
 100                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_READ_EXTERNAL, r.SUM_TIMER_WAIT)),
 101
 102                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WRITE_ALLOW_WRITE, r.SUM_TIMER_WAIT)),
 103                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WRITE_CONCURRENT_INSERT, r.SUM_TIMER_WAIT)),
 104                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WRITE_DELAYED, r.SUM_TIMER_WAIT)),
 105                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WRITE_LOW_PRIORITY, r.SUM_TIMER_WAIT)),
 106                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WRITE_NORMAL, r.SUM_TIMER_WAIT)),
 107                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WRITE_EXTERNAL, r.SUM_TIMER_WAIT)))
 108 }
 109
 110 func (this *table_lock_waits_summary_by_table_row) add(other table_lock_waits_summary_by_table_row) {
 111         this.COUNT_STAR += other.COUNT_STAR
 112         this.SUM_TIMER_WAIT += other.SUM_TIMER_WAIT
 113         this.SUM_TIMER_READ += other.SUM_TIMER_READ
 114         this.SUM_TIMER_WRITE += other.SUM_TIMER_WRITE
 115         this.SUM_TIMER_READ_WITH_SHARED_LOCKS += other.SUM_TIMER_READ_WITH_SHARED_LOCKS
 116         this.SUM_TIMER_READ_HIGH_PRIORITY += other.SUM_TIMER_READ_HIGH_PRIORITY
 117         this.SUM_TIMER_READ_NO_INSERT += other.SUM_TIMER_READ_NO_INSERT
 118         this.SUM_TIMER_READ_NORMAL += other.SUM_TIMER_READ_NORMAL
 119         this.SUM_TIMER_READ_EXTERNAL += other.SUM_TIMER_READ_EXTERNAL
 120         this.SUM_TIMER_WRITE_CONCURRENT_INSERT += other.SUM_TIMER_WRITE_CONCURRENT_INSERT
 121         this.SUM_TIMER_WRITE_DELAYED += other.SUM_TIMER_WRITE_DELAYED
 122         this.SUM_TIMER_WRITE_LOW_PRIORITY += other.SUM_TIMER_WRITE_LOW_PRIORITY
 123         this.SUM_TIMER_WRITE_NORMAL += other.SUM_TIMER_WRITE_NORMAL
 124         this.SUM_TIMER_WRITE_EXTERNAL += other.SUM_TIMER_WRITE_EXTERNAL
 125 }
 126
 127 func (this *table_lock_waits_summary_by_table_row) subtract(other table_lock_waits_summary_by_table_row) {
 128         this.COUNT_STAR -= other.COUNT_STAR
 129         this.SUM_TIMER_WAIT -= other.SUM_TIMER_WAIT
 130         this.SUM_TIMER_READ -= other.SUM_TIMER_READ
 131         this.SUM_TIMER_WRITE -= other.SUM_TIMER_WRITE
 132         this.SUM_TIMER_READ_WITH_SHARED_LOCKS -= other.SUM_TIMER_READ_WITH_SHARED_LOCKS
 133         this.SUM_TIMER_READ_HIGH_PRIORITY -= other.SUM_TIMER_READ_HIGH_PRIORITY
 134         this.SUM_TIMER_READ_NO_INSERT -= other.SUM_TIMER_READ_NO_INSERT
 135         this.SUM_TIMER_READ_NORMAL -= other.SUM_TIMER_READ_NORMAL
 136         this.SUM_TIMER_READ_EXTERNAL -= other.SUM_TIMER_READ_EXTERNAL
 137         this.SUM_TIMER_WRITE_CONCURRENT_INSERT -= other.SUM_TIMER_WRITE_CONCURRENT_INSERT
 138         this.SUM_TIMER_WRITE_DELAYED -= other.SUM_TIMER_WRITE_DELAYED
 139         this.SUM_TIMER_WRITE_LOW_PRIORITY -= other.SUM_TIMER_WRITE_LOW_PRIORITY
 140         this.SUM_TIMER_WRITE_NORMAL -= other.SUM_TIMER_WRITE_NORMAL
 141         this.SUM_TIMER_WRITE_EXTERNAL -= other.SUM_TIMER_WRITE_EXTERNAL
 142 }
 143
 144 // return the totals of a slice of rows
 145 func (t table_lock_waits_summary_by_table_rows) totals() table_lock_waits_summary_by_table_row {
 146         var totals table_lock_waits_summary_by_table_row
 147         totals.OBJECT_SCHEMA = "TOTALS"
 148
 149         for i := range t {
 150                 totals.add(t[i])
 151         }
 152
 153         return totals
 154 }
 155
 156 // Select the raw data from the database into file_summary_by_instance_rows
 157 // - filter out empty values
 158 // - merge rows with the same name into a single row
 159 // - change FILE_NAME into a more descriptive value.
 160 func select_tlwsbt_rows(dbh *sql.DB) table_lock_waits_summary_by_table_rows {
 161         var t table_lock_waits_summary_by_table_rows
 162
 163         sql := "SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, SUM_TIMER_WAIT, SUM_TIMER_READ, SUM_TIMER_WRITE, SUM_TIMER_READ_WITH_SHARED_LOCKS, SUM_TIMER_READ_HIGH_PRIORITY, SUM_TIMER_READ_NO_INSERT, SUM_TIMER_READ_NORMAL, SUM_TIMER_READ_EXTERNAL, SUM_TIMER_WRITE_ALLOW_WRITE, SUM_TIMER_WRITE_CONCURRENT_INSERT, SUM_TIMER_WRITE_DELAYED, SUM_TIMER_WRITE_LOW_PRIORITY, SUM_TIMER_WRITE_NORMAL, SUM_TIMER_WRITE_EXTERNAL FROM table_lock_waits_summary_by_table WHERE COUNT_STAR > 0"
 164
 165         rows, err := dbh.Query(sql)
 166         if err != nil {
 167                 log.Fatal(err)
 168         }
 169         defer rows.Close()
 170
 171         for rows.Next() {
 172                 var r table_lock_waits_summary_by_table_row
 173                 if err := rows.Scan(
 174                         &r.OBJECT_TYPE,
 175                         &r.OBJECT_SCHEMA,
 176                         &r.OBJECT_NAME,
 177                         &r.COUNT_STAR,
 178                         &r.SUM_TIMER_WAIT,
 179                         &r.SUM_TIMER_READ,
 180                         &r.SUM_TIMER_WRITE,
 181                         &r.SUM_TIMER_READ_WITH_SHARED_LOCKS,
 182                         &r.SUM_TIMER_READ_HIGH_PRIORITY,
 183                         &r.SUM_TIMER_READ_NO_INSERT,
 184                         &r.SUM_TIMER_READ_NORMAL,
 185                         &r.SUM_TIMER_READ_EXTERNAL,
 186                         &r.SUM_TIMER_WRITE_ALLOW_WRITE,
 187                         &r.SUM_TIMER_WRITE_CONCURRENT_INSERT,
 188                         &r.SUM_TIMER_WRITE_DELAYED,
 189                         &r.SUM_TIMER_WRITE_LOW_PRIORITY,
 190                         &r.SUM_TIMER_WRITE_NORMAL,
 191                         &r.SUM_TIMER_WRITE_EXTERNAL); err != nil {
 192                         log.Fatal(err)
 193                 }
 194                 // we collect all data as we may need it later
 195                 t = append(t, r)
 196         }
 197         if err := rows.Err(); err != nil {
 198                 log.Fatal(err)
 199         }
 200
 201         return t
 202 }
 203
 204 func (t table_lock_waits_summary_by_table_rows) Len() int      { return len(t) }
 205 func (t table_lock_waits_summary_by_table_rows) Swap(i, j int) { t[i], t[j] = t[j], t[i] }
 206 func (t table_lock_waits_summary_by_table_rows) Less(i, j int) bool {
 207         return (t[i].SUM_TIMER_WAIT > t[j].SUM_TIMER_WAIT) ||
 208                 ((t[i].SUM_TIMER_WAIT == t[j].SUM_TIMER_WAIT) &&
 209                         (t[i].OBJECT_SCHEMA < t[j].OBJECT_SCHEMA) &&
 210                         (t[i].OBJECT_NAME < t[j].OBJECT_NAME))
 211
 212 }
 213
 214 // sort the data
 215 func (t *table_lock_waits_summary_by_table_rows) sort() {
 216         sort.Sort(t)
 217 }
 218
 219 // remove the initial values from those rows where there's a match
 220 // - if we find a row we can't match ignore it
 221 func (this *table_lock_waits_summary_by_table_rows) subtract(initial table_lock_waits_summary_by_table_rows) {
 222         i_by_name := make(map[string]int)
 223
 224         // iterate over rows by name
 225         for i := range initial {
 226                 i_by_name[initial[i].name()] = i
 227         }
 228
 229         for i := range *this {
 230                 if _, ok := i_by_name[(*this)[i].name()]; ok {
 231                         initial_i := i_by_name[(*this)[i].name()]
 232                         (*this)[i].subtract(initial[initial_i])
 233                 }
 234         }
 235 }
 236
 237 // if the data in t2 is "newer", "has more values" than t then it needs refreshing.
 238 // check this by comparing totals.
 239 func (t table_lock_waits_summary_by_table_rows) needs_refresh(t2 table_lock_waits_summary_by_table_rows) bool {
 240         my_totals := t.totals()
 241         t2_totals := t2.totals()
 242
 243         return my_totals.SUM_TIMER_WAIT > t2_totals.SUM_TIMER_WAIT
 244 }
 245
 246 // describe a whole row
 247 func (r table_lock_waits_summary_by_table_row) String() string {
 248         return fmt.Sprintf("%-30s|%10s %10s %10s|%10s %10s %10s %10s %10s|%10s %10s %10s %10s %10s %10s",
 249                 r.pretty_name(),
 250                 lib.FormatTime(r.SUM_TIMER_WAIT),
 251                 lib.FormatTime(r.SUM_TIMER_READ),
 252                 lib.FormatTime(r.SUM_TIMER_WRITE),
 253
 254                 lib.FormatTime(r.SUM_TIMER_READ_WITH_SHARED_LOCKS),
 255                 lib.FormatTime(r.SUM_TIMER_READ_HIGH_PRIORITY),
 256                 lib.FormatTime(r.SUM_TIMER_READ_NO_INSERT),
 257                 lib.FormatTime(r.SUM_TIMER_READ_NORMAL),
 258                 lib.FormatTime(r.SUM_TIMER_READ_EXTERNAL),
 259
 260                 lib.FormatTime(r.SUM_TIMER_WRITE_ALLOW_WRITE),
 261                 lib.FormatTime(r.SUM_TIMER_WRITE_CONCURRENT_INSERT),
 262                 lib.FormatTime(r.SUM_TIMER_WRITE_DELAYED),
 263                 lib.FormatTime(r.SUM_TIMER_WRITE_LOW_PRIORITY),
 264                 lib.FormatTime(r.SUM_TIMER_WRITE_NORMAL),
 265                 lib.FormatTime(r.SUM_TIMER_WRITE_EXTERNAL))
 266 }
 267
 268 // describe a whole table
 269 func (t table_lock_waits_summary_by_table_rows) String() string {
 270         s := make([]string, len(t))
 271
 272         for i := range t {
 273                 s = append(s, t[i].String())
 274         }
 275
 276         return strings.Join(s, "\n")
 277 }
 278
 279 ***** */