performance_schema/table_lock_waits_summary_by_table/table_lock_waits_summary_by_table_row.go

   1 // This file contains the library routines for managing the
   2 // table_lock_waits_summary_by_table table.
   3 package table_lock_waits_summary_by_table
   4
   5 import (
   6         "database/sql"
   7         "fmt"
   8         _ "github.com/go-sql-driver/mysql"
   9         "log"
  10         "sort"
  11         "strings"
  12
  13         "github.com/sjmudd/pstop/lib"
  14 )
  15
  16 /*
  17
  18 From 5.7.5
  19
  20 *************************** 1. row ***************************
  21        Table: table_lock_waits_summary_by_table
  22 Create Table: CREATE TABLE `table_lock_waits_summary_by_table` (
  23   `OBJECT_TYPE` varchar(64) DEFAULT NULL,
  24   `OBJECT_SCHEMA` varchar(64) DEFAULT NULL,
  25   `OBJECT_NAME` varchar(64) DEFAULT NULL,
  26   `COUNT_STAR` bigint(20) unsigned NOT NULL,
  27   `SUM_TIMER_WAIT` bigint(20) unsigned NOT NULL,
  28   `MIN_TIMER_WAIT` bigint(20) unsigned NOT NULL,
  29   `AVG_TIMER_WAIT` bigint(20) unsigned NOT NULL,
  30   `MAX_TIMER_WAIT` bigint(20) unsigned NOT NULL,
  31   `COUNT_READ` bigint(20) unsigned NOT NULL,
  32   `SUM_TIMER_READ` bigint(20) unsigned NOT NULL,
  33   `MIN_TIMER_READ` bigint(20) unsigned NOT NULL,
  34   `AVG_TIMER_READ` bigint(20) unsigned NOT NULL,
  35   `MAX_TIMER_READ` bigint(20) unsigned NOT NULL,
  36   `COUNT_WRITE` bigint(20) unsigned NOT NULL,
  37   `SUM_TIMER_WRITE` bigint(20) unsigned NOT NULL,
  38   `MIN_TIMER_WRITE` bigint(20) unsigned NOT NULL,
  39   `AVG_TIMER_WRITE` bigint(20) unsigned NOT NULL,
  40   `MAX_TIMER_WRITE` bigint(20) unsigned NOT NULL,
  41   `COUNT_READ_NORMAL` bigint(20) unsigned NOT NULL,
  42   `SUM_TIMER_READ_NORMAL` bigint(20) unsigned NOT NULL,
  43   `MIN_TIMER_READ_NORMAL` bigint(20) unsigned NOT NULL,
  44   `AVG_TIMER_READ_NORMAL` bigint(20) unsigned NOT NULL,
  45   `MAX_TIMER_READ_NORMAL` bigint(20) unsigned NOT NULL,
  46   `COUNT_READ_WITH_SHARED_LOCKS` bigint(20) unsigned NOT NULL,
  47   `SUM_TIMER_READ_WITH_SHARED_LOCKS` bigint(20) unsigned NOT NULL,
  48   `MIN_TIMER_READ_WITH_SHARED_LOCKS` bigint(20) unsigned NOT NULL,
  49   `AVG_TIMER_READ_WITH_SHARED_LOCKS` bigint(20) unsigned NOT NULL,
  50   `MAX_TIMER_READ_WITH_SHARED_LOCKS` bigint(20) unsigned NOT NULL,
  51   `COUNT_READ_HIGH_PRIORITY` bigint(20) unsigned NOT NULL,
  52   `SUM_TIMER_READ_HIGH_PRIORITY` bigint(20) unsigned NOT NULL,
  53   `MIN_TIMER_READ_HIGH_PRIORITY` bigint(20) unsigned NOT NULL,
  54   `AVG_TIMER_READ_HIGH_PRIORITY` bigint(20) unsigned NOT NULL,
  55   `MAX_TIMER_READ_HIGH_PRIORITY` bigint(20) unsigned NOT NULL,
  56   `COUNT_READ_NO_INSERT` bigint(20) unsigned NOT NULL,
  57   `SUM_TIMER_READ_NO_INSERT` bigint(20) unsigned NOT NULL,
  58   `MIN_TIMER_READ_NO_INSERT` bigint(20) unsigned NOT NULL,
  59   `AVG_TIMER_READ_NO_INSERT` bigint(20) unsigned NOT NULL,
  60   `MAX_TIMER_READ_NO_INSERT` bigint(20) unsigned NOT NULL,
  61   `COUNT_READ_EXTERNAL` bigint(20) unsigned NOT NULL,
  62   `SUM_TIMER_READ_EXTERNAL` bigint(20) unsigned NOT NULL,
  63   `MIN_TIMER_READ_EXTERNAL` bigint(20) unsigned NOT NULL,
  64   `AVG_TIMER_READ_EXTERNAL` bigint(20) unsigned NOT NULL,
  65   `MAX_TIMER_READ_EXTERNAL` bigint(20) unsigned NOT NULL,
  66   `COUNT_WRITE_ALLOW_WRITE` bigint(20) unsigned NOT NULL,
  67   `SUM_TIMER_WRITE_ALLOW_WRITE` bigint(20) unsigned NOT NULL,
  68   `MIN_TIMER_WRITE_ALLOW_WRITE` bigint(20) unsigned NOT NULL,
  69   `AVG_TIMER_WRITE_ALLOW_WRITE` bigint(20) unsigned NOT NULL,
  70   `MAX_TIMER_WRITE_ALLOW_WRITE` bigint(20) unsigned NOT NULL,
  71   `COUNT_WRITE_CONCURRENT_INSERT` bigint(20) unsigned NOT NULL,
  72   `SUM_TIMER_WRITE_CONCURRENT_INSERT` bigint(20) unsigned NOT NULL,
  73   `MIN_TIMER_WRITE_CONCURRENT_INSERT` bigint(20) unsigned NOT NULL,
  74   `AVG_TIMER_WRITE_CONCURRENT_INSERT` bigint(20) unsigned NOT NULL,
  75   `MAX_TIMER_WRITE_CONCURRENT_INSERT` bigint(20) unsigned NOT NULL,
  76   `COUNT_WRITE_LOW_PRIORITY` bigint(20) unsigned NOT NULL,
  77   `SUM_TIMER_WRITE_LOW_PRIORITY` bigint(20) unsigned NOT NULL,
  78   `MIN_TIMER_WRITE_LOW_PRIORITY` bigint(20) unsigned NOT NULL,
  79   `AVG_TIMER_WRITE_LOW_PRIORITY` bigint(20) unsigned NOT NULL,
  80   `MAX_TIMER_WRITE_LOW_PRIORITY` bigint(20) unsigned NOT NULL,
  81   `COUNT_WRITE_NORMAL` bigint(20) unsigned NOT NULL,
  82   `SUM_TIMER_WRITE_NORMAL` bigint(20) unsigned NOT NULL,
  83   `MIN_TIMER_WRITE_NORMAL` bigint(20) unsigned NOT NULL,
  84   `AVG_TIMER_WRITE_NORMAL` bigint(20) unsigned NOT NULL,
  85   `MAX_TIMER_WRITE_NORMAL` bigint(20) unsigned NOT NULL,
  86   `COUNT_WRITE_EXTERNAL` bigint(20) unsigned NOT NULL,
  87   `SUM_TIMER_WRITE_EXTERNAL` bigint(20) unsigned NOT NULL,
  88   `MIN_TIMER_WRITE_EXTERNAL` bigint(20) unsigned NOT NULL,
  89   `AVG_TIMER_WRITE_EXTERNAL` bigint(20) unsigned NOT NULL,
  90   `MAX_TIMER_WRITE_EXTERNAL` bigint(20) unsigned NOT NULL
  91 ) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8
  92
  93 */
  94
  95 type table_lock_waits_summary_by_table_row struct {
  96         OBJECT_TYPE   string // in theory redundant but keep anyway
  97         OBJECT_SCHEMA string // in theory redundant but keep anyway
  98         OBJECT_NAME   string // in theory redundant but keep anyway
  99         COUNT_STAR    int
 100
 101         SUM_TIMER_WAIT  uint64
 102         SUM_TIMER_READ  uint64
 103         SUM_TIMER_WRITE uint64
 104
 105         SUM_TIMER_READ_WITH_SHARED_LOCKS uint64
 106         SUM_TIMER_READ_HIGH_PRIORITY     uint64
 107         SUM_TIMER_READ_NO_INSERT         uint64
 108         SUM_TIMER_READ_NORMAL            uint64
 109         SUM_TIMER_READ_EXTERNAL          uint64
 110
 111         SUM_TIMER_WRITE_ALLOW_WRITE       uint64
 112         SUM_TIMER_WRITE_CONCURRENT_INSERT uint64
 113         SUM_TIMER_WRITE_LOW_PRIORITY      uint64
 114         SUM_TIMER_WRITE_NORMAL            uint64
 115         SUM_TIMER_WRITE_EXTERNAL          uint64
 116 }
 117
 118 type table_lock_waits_summary_by_table_rows []table_lock_waits_summary_by_table_row
 119
 120 // return the table name from the columns as '<schema>.<table>'
 121 func (r *table_lock_waits_summary_by_table_row) name() string {
 122         var n string
 123         if len(r.OBJECT_SCHEMA) > 0 {
 124                 n += r.OBJECT_SCHEMA
 125         }
 126         if len(n) > 0 {
 127                 if len(r.OBJECT_NAME) > 0 {
 128                         n += "." + r.OBJECT_NAME
 129                 }
 130         } else {
 131                 if len(r.OBJECT_NAME) > 0 {
 132                         n += r.OBJECT_NAME
 133                 }
 134         }
 135         return n
 136 }
 137
 138 func (r *table_lock_waits_summary_by_table_row) pretty_name() string {
 139         s := r.name()
 140         if len(s) > 30 {
 141                 s = s[:29]
 142         }
 143         return fmt.Sprintf("%-30s", s)
 144 }
 145
 146 // Table Name                        Latency      %|  Read  Write|S.Lock   High  NoIns Normal Extrnl|AlloWr CncIns WrtDly    Low Normal Extrnl|
 147 // xxxxxxxxxxxxxxxxxxxxxxxxxxxxx  1234567890 100.0%|xxxxx% xxxxx%|xxxxx% xxxxx% xxxxx% xxxxx% xxxxx%|xxxxx% xxxxx% xxxxx% xxxxx% xxxxx% xxxxx%|
 148 func (r *table_lock_waits_summary_by_table_row) headings() string {
 149         return fmt.Sprintf("%-30s %10s %6s|%6s %6s|%6s %6s %6s %6s %6s|%6s %6s %6s %6s %6s",
 150                 "Table Name", "Latency", "%",
 151                 "Read", "Write",
 152                 "S.Lock", "High", "NoIns", "Normal", "Extrnl",
 153                 "AlloWr", "CncIns", "Low", "Normal", "Extrnl")
 154 }
 155
 156 // generate a printable result
 157 func (r *table_lock_waits_summary_by_table_row) row_content(totals table_lock_waits_summary_by_table_row) string {
 158
 159         // assume the data is empty so hide it.
 160         name := r.pretty_name()
 161         if r.COUNT_STAR == 0 {
 162                 name = ""
 163         }
 164
 165         return fmt.Sprintf("%-30s %10s %6s|%6s %6s|%6s %6s %6s %6s %6s|%6s %6s %6s %6s %6s",
 166                 name,
 167                 lib.FormatTime(r.SUM_TIMER_WAIT),
 168                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WAIT, totals.SUM_TIMER_WAIT)),
 169
 170                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_READ, r.SUM_TIMER_WAIT)),
 171                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WRITE, r.SUM_TIMER_WAIT)),
 172
 173                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_READ_WITH_SHARED_LOCKS, r.SUM_TIMER_WAIT)),
 174                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_READ_HIGH_PRIORITY, r.SUM_TIMER_WAIT)),
 175                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_READ_NO_INSERT, r.SUM_TIMER_WAIT)),
 176                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_READ_NORMAL, r.SUM_TIMER_WAIT)),
 177                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_READ_EXTERNAL, r.SUM_TIMER_WAIT)),
 178
 179                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WRITE_ALLOW_WRITE, r.SUM_TIMER_WAIT)),
 180                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WRITE_CONCURRENT_INSERT, r.SUM_TIMER_WAIT)),
 181                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WRITE_LOW_PRIORITY, r.SUM_TIMER_WAIT)),
 182                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WRITE_NORMAL, r.SUM_TIMER_WAIT)),
 183                 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WRITE_EXTERNAL, r.SUM_TIMER_WAIT)))
 184 }
 185
 186 func (this *table_lock_waits_summary_by_table_row) add(other table_lock_waits_summary_by_table_row) {
 187         this.COUNT_STAR += other.COUNT_STAR
 188         this.SUM_TIMER_WAIT += other.SUM_TIMER_WAIT
 189         this.SUM_TIMER_READ += other.SUM_TIMER_READ
 190         this.SUM_TIMER_WRITE += other.SUM_TIMER_WRITE
 191         this.SUM_TIMER_READ_WITH_SHARED_LOCKS += other.SUM_TIMER_READ_WITH_SHARED_LOCKS
 192         this.SUM_TIMER_READ_HIGH_PRIORITY += other.SUM_TIMER_READ_HIGH_PRIORITY
 193         this.SUM_TIMER_READ_NO_INSERT += other.SUM_TIMER_READ_NO_INSERT
 194         this.SUM_TIMER_READ_NORMAL += other.SUM_TIMER_READ_NORMAL
 195         this.SUM_TIMER_READ_EXTERNAL += other.SUM_TIMER_READ_EXTERNAL
 196         this.SUM_TIMER_WRITE_CONCURRENT_INSERT += other.SUM_TIMER_WRITE_CONCURRENT_INSERT
 197         this.SUM_TIMER_WRITE_LOW_PRIORITY += other.SUM_TIMER_WRITE_LOW_PRIORITY
 198         this.SUM_TIMER_WRITE_NORMAL += other.SUM_TIMER_WRITE_NORMAL
 199         this.SUM_TIMER_WRITE_EXTERNAL += other.SUM_TIMER_WRITE_EXTERNAL
 200 }
 201
 202 func (this *table_lock_waits_summary_by_table_row) subtract(other table_lock_waits_summary_by_table_row) {
 203         this.COUNT_STAR -= other.COUNT_STAR
 204         this.SUM_TIMER_WAIT -= other.SUM_TIMER_WAIT
 205         this.SUM_TIMER_READ -= other.SUM_TIMER_READ
 206         this.SUM_TIMER_WRITE -= other.SUM_TIMER_WRITE
 207         this.SUM_TIMER_READ_WITH_SHARED_LOCKS -= other.SUM_TIMER_READ_WITH_SHARED_LOCKS
 208         this.SUM_TIMER_READ_HIGH_PRIORITY -= other.SUM_TIMER_READ_HIGH_PRIORITY
 209         this.SUM_TIMER_READ_NO_INSERT -= other.SUM_TIMER_READ_NO_INSERT
 210         this.SUM_TIMER_READ_NORMAL -= other.SUM_TIMER_READ_NORMAL
 211         this.SUM_TIMER_READ_EXTERNAL -= other.SUM_TIMER_READ_EXTERNAL
 212         this.SUM_TIMER_WRITE_CONCURRENT_INSERT -= other.SUM_TIMER_WRITE_CONCURRENT_INSERT
 213         this.SUM_TIMER_WRITE_LOW_PRIORITY -= other.SUM_TIMER_WRITE_LOW_PRIORITY
 214         this.SUM_TIMER_WRITE_NORMAL -= other.SUM_TIMER_WRITE_NORMAL
 215         this.SUM_TIMER_WRITE_EXTERNAL -= other.SUM_TIMER_WRITE_EXTERNAL
 216 }
 217
 218 // return the totals of a slice of rows
 219 func (t table_lock_waits_summary_by_table_rows) totals() table_lock_waits_summary_by_table_row {
 220         var totals table_lock_waits_summary_by_table_row
 221         totals.OBJECT_SCHEMA = "TOTALS"
 222
 223         for i := range t {
 224                 totals.add(t[i])
 225         }
 226
 227         return totals
 228 }
 229
 230 // Select the raw data from the database into file_summary_by_instance_rows
 231 // - filter out empty values
 232 // - merge rows with the same name into a single row
 233 // - change FILE_NAME into a more descriptive value.
 234 func select_tlwsbt_rows(dbh *sql.DB) table_lock_waits_summary_by_table_rows {
 235         var t table_lock_waits_summary_by_table_rows
 236
 237         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_LOW_PRIORITY, SUM_TIMER_WRITE_NORMAL, SUM_TIMER_WRITE_EXTERNAL FROM table_lock_waits_summary_by_table WHERE COUNT_STAR > 0"
 238
 239         rows, err := dbh.Query(sql)
 240         if err != nil {
 241                 log.Fatal(err)
 242         }
 243         defer rows.Close()
 244
 245         for rows.Next() {
 246                 var r table_lock_waits_summary_by_table_row
 247                 if err := rows.Scan(
 248                         &r.OBJECT_TYPE,
 249                         &r.OBJECT_SCHEMA,
 250                         &r.OBJECT_NAME,
 251                         &r.COUNT_STAR,
 252                         &r.SUM_TIMER_WAIT,
 253                         &r.SUM_TIMER_READ,
 254                         &r.SUM_TIMER_WRITE,
 255                         &r.SUM_TIMER_READ_WITH_SHARED_LOCKS,
 256                         &r.SUM_TIMER_READ_HIGH_PRIORITY,
 257                         &r.SUM_TIMER_READ_NO_INSERT,
 258                         &r.SUM_TIMER_READ_NORMAL,
 259                         &r.SUM_TIMER_READ_EXTERNAL,
 260                         &r.SUM_TIMER_WRITE_ALLOW_WRITE,
 261                         &r.SUM_TIMER_WRITE_CONCURRENT_INSERT,
 262                         &r.SUM_TIMER_WRITE_LOW_PRIORITY,
 263                         &r.SUM_TIMER_WRITE_NORMAL,
 264                         &r.SUM_TIMER_WRITE_EXTERNAL); err != nil {
 265                         log.Fatal(err)
 266                 }
 267                 // we collect all data as we may need it later
 268                 t = append(t, r)
 269         }
 270         if err := rows.Err(); err != nil {
 271                 log.Fatal(err)
 272         }
 273
 274         return t
 275 }
 276
 277 func (t table_lock_waits_summary_by_table_rows) Len() int      { return len(t) }
 278 func (t table_lock_waits_summary_by_table_rows) Swap(i, j int) { t[i], t[j] = t[j], t[i] }
 279 func (t table_lock_waits_summary_by_table_rows) Less(i, j int) bool {
 280         return (t[i].SUM_TIMER_WAIT > t[j].SUM_TIMER_WAIT) ||
 281                 ((t[i].SUM_TIMER_WAIT == t[j].SUM_TIMER_WAIT) &&
 282                         (t[i].OBJECT_SCHEMA < t[j].OBJECT_SCHEMA) &&
 283                         (t[i].OBJECT_NAME < t[j].OBJECT_NAME))
 284
 285 }
 286
 287 // sort the data
 288 func (t *table_lock_waits_summary_by_table_rows) sort() {
 289         sort.Sort(t)
 290 }
 291
 292 // remove the initial values from those rows where there's a match
 293 // - if we find a row we can't match ignore it
 294 func (this *table_lock_waits_summary_by_table_rows) subtract(initial table_lock_waits_summary_by_table_rows) {
 295         i_by_name := make(map[string]int)
 296
 297         // iterate over rows by name
 298         for i := range initial {
 299                 i_by_name[initial[i].name()] = i
 300         }
 301
 302         for i := range *this {
 303                 if _, ok := i_by_name[(*this)[i].name()]; ok {
 304                         initial_i := i_by_name[(*this)[i].name()]
 305                         (*this)[i].subtract(initial[initial_i])
 306                 }
 307         }
 308 }
 309
 310 // if the data in t2 is "newer", "has more values" than t then it needs refreshing.
 311 // check this by comparing totals.
 312 func (t table_lock_waits_summary_by_table_rows) needs_refresh(t2 table_lock_waits_summary_by_table_rows) bool {
 313         my_totals := t.totals()
 314         t2_totals := t2.totals()
 315
 316         return my_totals.SUM_TIMER_WAIT > t2_totals.SUM_TIMER_WAIT
 317 }
 318
 319 // describe a whole row
 320 func (r table_lock_waits_summary_by_table_row) String() string {
 321         return fmt.Sprintf("%-30s|%10s %10s %10s|%10s %10s %10s %10s %10s|%10s %10s %10s %10s %10s",
 322                 r.pretty_name(),
 323                 lib.FormatTime(r.SUM_TIMER_WAIT),
 324                 lib.FormatTime(r.SUM_TIMER_READ),
 325                 lib.FormatTime(r.SUM_TIMER_WRITE),
 326
 327                 lib.FormatTime(r.SUM_TIMER_READ_WITH_SHARED_LOCKS),
 328                 lib.FormatTime(r.SUM_TIMER_READ_HIGH_PRIORITY),
 329                 lib.FormatTime(r.SUM_TIMER_READ_NO_INSERT),
 330                 lib.FormatTime(r.SUM_TIMER_READ_NORMAL),
 331                 lib.FormatTime(r.SUM_TIMER_READ_EXTERNAL),
 332
 333                 lib.FormatTime(r.SUM_TIMER_WRITE_ALLOW_WRITE),
 334                 lib.FormatTime(r.SUM_TIMER_WRITE_CONCURRENT_INSERT),
 335                 lib.FormatTime(r.SUM_TIMER_WRITE_LOW_PRIORITY),
 336                 lib.FormatTime(r.SUM_TIMER_WRITE_NORMAL),
 337                 lib.FormatTime(r.SUM_TIMER_WRITE_EXTERNAL))
 338 }
 339
 340 // describe a whole table
 341 func (t table_lock_waits_summary_by_table_rows) String() string {
 342         s := make([]string, len(t))
 343
 344         for i := range t {
 345                 s = append(s, t[i].String())
 346         }
 347
 348         return strings.Join(s, "\n")
 349 }