1 // This file contains the library routines for managing the
2 // table_lock_waits_summary_by_table table.
9 _ "github.com/go-sql-driver/mysql"
14 "github.com/sjmudd/pstop/lib"
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
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
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
41 type table_lock_waits_summary_by_table_rows []table_lock_waits_summary_by_table_row
43 // return the table name from the columns as '<schema>.<table>'
44 func (r *table_lock_waits_summary_by_table_row) name() string {
46 if len(r.OBJECT_SCHEMA) > 0 {
50 if len(r.OBJECT_NAME) > 0 {
51 n += "." + r.OBJECT_NAME
54 if len(r.OBJECT_NAME) > 0 {
61 func (r *table_lock_waits_summary_by_table_row) pretty_name() string {
66 return fmt.Sprintf("%-30s", s)
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", "%",
75 "S.Lock", "High", "NoIns", "Normal", "Extrnl",
76 "AlloWr", "CncIns", "WrtDly", "Low", "Normal", "Extrnl")
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 {
82 // assume the data is empty so hide it.
83 name := r.pretty_name()
84 if r.COUNT_STAR == 0 {
88 return fmt.Sprintf("%-30s %10s %6s|%6s %6s|%6s %6s %6s %6s %6s|%6s %6s %6s %6s %6s %6s",
90 lib.FormatTime(r.SUM_TIMER_WAIT),
91 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WAIT, totals.SUM_TIMER_WAIT)),
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)),
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)),
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)))
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
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
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"
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
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"
165 rows, err := dbh.Query(sql)
172 var r table_lock_waits_summary_by_table_row
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 {
194 // we collect all data as we may need it later
197 if err := rows.Err(); err != nil {
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))
215 func (t *table_lock_waits_summary_by_table_rows) sort() {
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)
224 // iterate over rows by name
225 for i := range initial {
226 i_by_name[initial[i].name()] = i
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])
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()
243 return my_totals.SUM_TIMER_WAIT > t2_totals.SUM_TIMER_WAIT
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",
250 lib.FormatTime(r.SUM_TIMER_WAIT),
251 lib.FormatTime(r.SUM_TIMER_READ),
252 lib.FormatTime(r.SUM_TIMER_WRITE),
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),
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))
268 // describe a whole table
269 func (t table_lock_waits_summary_by_table_rows) String() string {
270 s := make([]string, len(t))
273 s = append(s, t[i].String())
276 return strings.Join(s, "\n")