X-Git-Url: http://git.iain.cx/?a=blobdiff_plain;f=p_s%2Fps_table%2Fps_table_row.go;fp=p_s%2Fps_table%2Fps_table_row.go;h=5bf1806844c08a158c9df242a4207490b1c70200;hb=52d3c00ce729f908f342e10f48818b112dec0130;hp=0000000000000000000000000000000000000000;hpb=b958730449ac1d927c267373a68b9ae54b72c454;p=pstop.git diff --git a/p_s/ps_table/ps_table_row.go b/p_s/ps_table/ps_table_row.go new file mode 100644 index 0000000..5bf1806 --- /dev/null +++ b/p_s/ps_table/ps_table_row.go @@ -0,0 +1,279 @@ +// This file contains the library routines for managing the +// table_lock_waits_summary_by_table table. +package ps_table + +/* ***** +import ( + "database/sql" + "fmt" + _ "github.com/go-sql-driver/mysql" + "log" + "sort" + "strings" + + "github.com/sjmudd/pstop/lib" +) + +type table_lock_waits_summary_by_table_row struct { + 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 + COUNT_STAR int + + SUM_TIMER_WAIT int + SUM_TIMER_READ int + SUM_TIMER_WRITE int + + SUM_TIMER_READ_WITH_SHARED_LOCKS int + SUM_TIMER_READ_HIGH_PRIORITY int + SUM_TIMER_READ_NO_INSERT int + SUM_TIMER_READ_NORMAL int + SUM_TIMER_READ_EXTERNAL int + + SUM_TIMER_WRITE_ALLOW_WRITE int + SUM_TIMER_WRITE_CONCURRENT_INSERT int + SUM_TIMER_WRITE_DELAYED int + SUM_TIMER_WRITE_LOW_PRIORITY int + SUM_TIMER_WRITE_NORMAL int + SUM_TIMER_WRITE_EXTERNAL int +} + +type table_lock_waits_summary_by_table_rows []table_lock_waits_summary_by_table_row + +// return the table name from the columns as '.' +func (r *table_lock_waits_summary_by_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_lock_waits_summary_by_table_row) pretty_name() string { + s := r.name() + if len(s) > 30 { + s = s[:29] + } + return fmt.Sprintf("%-30s", s) +} + +// Table Name Latency %| Read Write|S.Lock High NoIns Normal Extrnl|AlloWr CncIns WrtDly Low Normal Extrnl| +// xxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1234567890 100.0%|xxxxx% xxxxx%|xxxxx% xxxxx% xxxxx% xxxxx% xxxxx%|xxxxx% xxxxx% xxxxx% xxxxx% xxxxx% xxxxx%| +func (r *table_lock_waits_summary_by_table_row) headings() string { + return fmt.Sprintf("%-30s %10s %6s|%6s %6s|%6s %6s %6s %6s %6s|%6s %6s %6s %6s %6s %6s", + "Table Name", "Latency", "%", + "Read", "Write", + "S.Lock", "High", "NoIns", "Normal", "Extrnl", + "AlloWr", "CncIns", "WrtDly", "Low", "Normal", "Extrnl") +} + +// generate a printable result +func (r *table_lock_waits_summary_by_table_row) row_content(totals table_lock_waits_summary_by_table_row) string { + + // assume the data is empty so hide it. + name := r.pretty_name() + if r.COUNT_STAR == 0 { + name = "" + } + + return fmt.Sprintf("%-30s %10s %6s|%6s %6s|%6s %6s %6s %6s %6s|%6s %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_READ, r.SUM_TIMER_WAIT)), + lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WRITE, r.SUM_TIMER_WAIT)), + + lib.FormatPct(lib.MyDivide(r.SUM_TIMER_READ_WITH_SHARED_LOCKS, r.SUM_TIMER_WAIT)), + lib.FormatPct(lib.MyDivide(r.SUM_TIMER_READ_HIGH_PRIORITY, r.SUM_TIMER_WAIT)), + lib.FormatPct(lib.MyDivide(r.SUM_TIMER_READ_NO_INSERT, r.SUM_TIMER_WAIT)), + lib.FormatPct(lib.MyDivide(r.SUM_TIMER_READ_NORMAL, r.SUM_TIMER_WAIT)), + lib.FormatPct(lib.MyDivide(r.SUM_TIMER_READ_EXTERNAL, r.SUM_TIMER_WAIT)), + + lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WRITE_ALLOW_WRITE, r.SUM_TIMER_WAIT)), + lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WRITE_CONCURRENT_INSERT, r.SUM_TIMER_WAIT)), + lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WRITE_DELAYED, r.SUM_TIMER_WAIT)), + lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WRITE_LOW_PRIORITY, r.SUM_TIMER_WAIT)), + lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WRITE_NORMAL, r.SUM_TIMER_WAIT)), + lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WRITE_EXTERNAL, r.SUM_TIMER_WAIT))) +} + +func (this *table_lock_waits_summary_by_table_row) add(other table_lock_waits_summary_by_table_row) { + this.COUNT_STAR += other.COUNT_STAR + this.SUM_TIMER_WAIT += other.SUM_TIMER_WAIT + this.SUM_TIMER_READ += other.SUM_TIMER_READ + this.SUM_TIMER_WRITE += other.SUM_TIMER_WRITE + this.SUM_TIMER_READ_WITH_SHARED_LOCKS += other.SUM_TIMER_READ_WITH_SHARED_LOCKS + this.SUM_TIMER_READ_HIGH_PRIORITY += other.SUM_TIMER_READ_HIGH_PRIORITY + this.SUM_TIMER_READ_NO_INSERT += other.SUM_TIMER_READ_NO_INSERT + this.SUM_TIMER_READ_NORMAL += other.SUM_TIMER_READ_NORMAL + this.SUM_TIMER_READ_EXTERNAL += other.SUM_TIMER_READ_EXTERNAL + this.SUM_TIMER_WRITE_CONCURRENT_INSERT += other.SUM_TIMER_WRITE_CONCURRENT_INSERT + this.SUM_TIMER_WRITE_DELAYED += other.SUM_TIMER_WRITE_DELAYED + this.SUM_TIMER_WRITE_LOW_PRIORITY += other.SUM_TIMER_WRITE_LOW_PRIORITY + this.SUM_TIMER_WRITE_NORMAL += other.SUM_TIMER_WRITE_NORMAL + this.SUM_TIMER_WRITE_EXTERNAL += other.SUM_TIMER_WRITE_EXTERNAL +} + +func (this *table_lock_waits_summary_by_table_row) subtract(other table_lock_waits_summary_by_table_row) { + this.COUNT_STAR -= other.COUNT_STAR + this.SUM_TIMER_WAIT -= other.SUM_TIMER_WAIT + this.SUM_TIMER_READ -= other.SUM_TIMER_READ + this.SUM_TIMER_WRITE -= other.SUM_TIMER_WRITE + this.SUM_TIMER_READ_WITH_SHARED_LOCKS -= other.SUM_TIMER_READ_WITH_SHARED_LOCKS + this.SUM_TIMER_READ_HIGH_PRIORITY -= other.SUM_TIMER_READ_HIGH_PRIORITY + this.SUM_TIMER_READ_NO_INSERT -= other.SUM_TIMER_READ_NO_INSERT + this.SUM_TIMER_READ_NORMAL -= other.SUM_TIMER_READ_NORMAL + this.SUM_TIMER_READ_EXTERNAL -= other.SUM_TIMER_READ_EXTERNAL + this.SUM_TIMER_WRITE_CONCURRENT_INSERT -= other.SUM_TIMER_WRITE_CONCURRENT_INSERT + this.SUM_TIMER_WRITE_DELAYED -= other.SUM_TIMER_WRITE_DELAYED + this.SUM_TIMER_WRITE_LOW_PRIORITY -= other.SUM_TIMER_WRITE_LOW_PRIORITY + this.SUM_TIMER_WRITE_NORMAL -= other.SUM_TIMER_WRITE_NORMAL + this.SUM_TIMER_WRITE_EXTERNAL -= other.SUM_TIMER_WRITE_EXTERNAL +} + +// return the totals of a slice of rows +func (t table_lock_waits_summary_by_table_rows) totals() table_lock_waits_summary_by_table_row { + var totals table_lock_waits_summary_by_table_row + totals.OBJECT_SCHEMA = "TOTALS" + + for i := range t { + totals.add(t[i]) + } + + return totals +} + +// Select the raw data from the database into file_summary_by_instance_rows +// - filter out empty values +// - merge rows with the same name into a single row +// - change FILE_NAME into a more descriptive value. +func select_tlwsbt_rows(dbh *sql.DB) table_lock_waits_summary_by_table_rows { + var t table_lock_waits_summary_by_table_rows + + 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" + + rows, err := dbh.Query(sql) + if err != nil { + log.Fatal(err) + } + defer rows.Close() + + for rows.Next() { + var r table_lock_waits_summary_by_table_row + if err := rows.Scan( + &r.OBJECT_TYPE, + &r.OBJECT_SCHEMA, + &r.OBJECT_NAME, + &r.COUNT_STAR, + &r.SUM_TIMER_WAIT, + &r.SUM_TIMER_READ, + &r.SUM_TIMER_WRITE, + &r.SUM_TIMER_READ_WITH_SHARED_LOCKS, + &r.SUM_TIMER_READ_HIGH_PRIORITY, + &r.SUM_TIMER_READ_NO_INSERT, + &r.SUM_TIMER_READ_NORMAL, + &r.SUM_TIMER_READ_EXTERNAL, + &r.SUM_TIMER_WRITE_ALLOW_WRITE, + &r.SUM_TIMER_WRITE_CONCURRENT_INSERT, + &r.SUM_TIMER_WRITE_DELAYED, + &r.SUM_TIMER_WRITE_LOW_PRIORITY, + &r.SUM_TIMER_WRITE_NORMAL, + &r.SUM_TIMER_WRITE_EXTERNAL); err != nil { + log.Fatal(err) + } + // we collect all data as we may need it later + t = append(t, r) + } + if err := rows.Err(); err != nil { + log.Fatal(err) + } + + return t +} + +func (t table_lock_waits_summary_by_table_rows) Len() int { return len(t) } +func (t table_lock_waits_summary_by_table_rows) Swap(i, j int) { t[i], t[j] = t[j], t[i] } +func (t table_lock_waits_summary_by_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)) + +} + +// sort the data +func (t *table_lock_waits_summary_by_table_rows) sort() { + sort.Sort(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_lock_waits_summary_by_table_rows) subtract(initial table_lock_waits_summary_by_table_rows) { + i_by_name := make(map[string]int) + + // iterate over rows by name + for i := range initial { + i_by_name[initial[i].name()] = i + } + + for i := range *this { + if _, ok := i_by_name[(*this)[i].name()]; ok { + initial_i := i_by_name[(*this)[i].name()] + (*this)[i].subtract(initial[initial_i]) + } + } +} + +// if the data in t2 is "newer", "has more values" than t then it needs refreshing. +// check this by comparing totals. +func (t table_lock_waits_summary_by_table_rows) needs_refresh(t2 table_lock_waits_summary_by_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_lock_waits_summary_by_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_READ), + lib.FormatTime(r.SUM_TIMER_WRITE), + + lib.FormatTime(r.SUM_TIMER_READ_WITH_SHARED_LOCKS), + lib.FormatTime(r.SUM_TIMER_READ_HIGH_PRIORITY), + lib.FormatTime(r.SUM_TIMER_READ_NO_INSERT), + lib.FormatTime(r.SUM_TIMER_READ_NORMAL), + lib.FormatTime(r.SUM_TIMER_READ_EXTERNAL), + + lib.FormatTime(r.SUM_TIMER_WRITE_ALLOW_WRITE), + lib.FormatTime(r.SUM_TIMER_WRITE_CONCURRENT_INSERT), + lib.FormatTime(r.SUM_TIMER_WRITE_DELAYED), + lib.FormatTime(r.SUM_TIMER_WRITE_LOW_PRIORITY), + lib.FormatTime(r.SUM_TIMER_WRITE_NORMAL), + lib.FormatTime(r.SUM_TIMER_WRITE_EXTERNAL)) +} + +// describe a whole table +func (t table_lock_waits_summary_by_table_rows) String() string { + s := make([]string, len(t)) + + for i := range t { + s = append(s, t[i].String()) + } + + return strings.Join(s, "\n") +} + +***** */