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
8 _ "github.com/go-sql-driver/mysql"
13 "github.com/sjmudd/pstop/lib"
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
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
101 SUM_TIMER_WAIT uint64
102 SUM_TIMER_READ uint64
103 SUM_TIMER_WRITE uint64
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
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
118 type table_lock_waits_summary_by_table_rows []table_lock_waits_summary_by_table_row
120 // return the table name from the columns as '<schema>.<table>'
121 func (r *table_lock_waits_summary_by_table_row) name() string {
123 if len(r.OBJECT_SCHEMA) > 0 {
127 if len(r.OBJECT_NAME) > 0 {
128 n += "." + r.OBJECT_NAME
131 if len(r.OBJECT_NAME) > 0 {
138 func (r *table_lock_waits_summary_by_table_row) pretty_name() string {
143 return fmt.Sprintf("%-30s", s)
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", "%",
152 "S.Lock", "High", "NoIns", "Normal", "Extrnl",
153 "AlloWr", "CncIns", "Low", "Normal", "Extrnl")
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 {
159 // assume the data is empty so hide it.
160 name := r.pretty_name()
161 if r.COUNT_STAR == 0 {
165 return fmt.Sprintf("%-30s %10s %6s|%6s %6s|%6s %6s %6s %6s %6s|%6s %6s %6s %6s %6s",
167 lib.FormatTime(r.SUM_TIMER_WAIT),
168 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WAIT, totals.SUM_TIMER_WAIT)),
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)),
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)),
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)))
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
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
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"
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
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"
239 rows, err := dbh.Query(sql)
246 var r table_lock_waits_summary_by_table_row
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 {
267 // we collect all data as we may need it later
270 if err := rows.Err(); err != nil {
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))
288 func (t *table_lock_waits_summary_by_table_rows) sort() {
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)
297 // iterate over rows by name
298 for i := range initial {
299 i_by_name[initial[i].name()] = i
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])
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()
316 return my_totals.SUM_TIMER_WAIT > t2_totals.SUM_TIMER_WAIT
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",
323 lib.FormatTime(r.SUM_TIMER_WAIT),
324 lib.FormatTime(r.SUM_TIMER_READ),
325 lib.FormatTime(r.SUM_TIMER_WRITE),
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),
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))
340 // describe a whole table
341 func (t table_lock_waits_summary_by_table_rows) String() string {
342 s := make([]string, len(t))
345 s = append(s, t[i].String())
348 return strings.Join(s, "\n")