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_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_rows []table_row
120 // return the table name from the columns as '<schema>.<table>'
121 func (r *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 // Latency %| Read Write|S.Lock High NoIns Normal Extrnl|AlloWr CncIns WrtDly Low Normal Extrnl|
139 // 1234567 100.0%|xxxxx% xxxxx%|xxxxx% xxxxx% xxxxx% xxxxx% xxxxx%|xxxxx% xxxxx% xxxxx% xxxxx% xxxxx% xxxxx%|xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
140 func (r *table_row) headings() string {
141 return fmt.Sprintf("%10s %6s|%6s %6s|%6s %6s %6s %6s %6s|%6s %6s %6s %6s %6s|%-30s",
144 "S.Lock", "High", "NoIns", "Normal", "Extrnl",
145 "AlloWr", "CncIns", "Low", "Normal", "Extrnl",
149 // generate a printable result
150 func (r *table_row) row_content(totals table_row) string {
152 // assume the data is empty so hide it.
154 if r.COUNT_STAR == 0 && name != "Totals" {
158 return fmt.Sprintf("%10s %6s|%6s %6s|%6s %6s %6s %6s %6s|%6s %6s %6s %6s %6s|%s",
159 lib.FormatTime(r.SUM_TIMER_WAIT),
160 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WAIT, totals.SUM_TIMER_WAIT)),
162 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_READ, r.SUM_TIMER_WAIT)),
163 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WRITE, r.SUM_TIMER_WAIT)),
165 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_READ_WITH_SHARED_LOCKS, r.SUM_TIMER_WAIT)),
166 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_READ_HIGH_PRIORITY, r.SUM_TIMER_WAIT)),
167 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_READ_NO_INSERT, r.SUM_TIMER_WAIT)),
168 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_READ_NORMAL, r.SUM_TIMER_WAIT)),
169 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_READ_EXTERNAL, r.SUM_TIMER_WAIT)),
171 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WRITE_ALLOW_WRITE, r.SUM_TIMER_WAIT)),
172 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WRITE_CONCURRENT_INSERT, r.SUM_TIMER_WAIT)),
173 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WRITE_LOW_PRIORITY, r.SUM_TIMER_WAIT)),
174 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WRITE_NORMAL, r.SUM_TIMER_WAIT)),
175 lib.FormatPct(lib.MyDivide(r.SUM_TIMER_WRITE_EXTERNAL, r.SUM_TIMER_WAIT)),
179 func (this *table_row) add(other table_row) {
180 this.COUNT_STAR += other.COUNT_STAR
181 this.SUM_TIMER_WAIT += other.SUM_TIMER_WAIT
182 this.SUM_TIMER_READ += other.SUM_TIMER_READ
183 this.SUM_TIMER_WRITE += other.SUM_TIMER_WRITE
184 this.SUM_TIMER_READ_WITH_SHARED_LOCKS += other.SUM_TIMER_READ_WITH_SHARED_LOCKS
185 this.SUM_TIMER_READ_HIGH_PRIORITY += other.SUM_TIMER_READ_HIGH_PRIORITY
186 this.SUM_TIMER_READ_NO_INSERT += other.SUM_TIMER_READ_NO_INSERT
187 this.SUM_TIMER_READ_NORMAL += other.SUM_TIMER_READ_NORMAL
188 this.SUM_TIMER_READ_EXTERNAL += other.SUM_TIMER_READ_EXTERNAL
189 this.SUM_TIMER_WRITE_CONCURRENT_INSERT += other.SUM_TIMER_WRITE_CONCURRENT_INSERT
190 this.SUM_TIMER_WRITE_LOW_PRIORITY += other.SUM_TIMER_WRITE_LOW_PRIORITY
191 this.SUM_TIMER_WRITE_NORMAL += other.SUM_TIMER_WRITE_NORMAL
192 this.SUM_TIMER_WRITE_EXTERNAL += other.SUM_TIMER_WRITE_EXTERNAL
195 func (this *table_row) subtract(other table_row) {
196 this.COUNT_STAR -= other.COUNT_STAR
197 this.SUM_TIMER_WAIT -= other.SUM_TIMER_WAIT
198 this.SUM_TIMER_READ -= other.SUM_TIMER_READ
199 this.SUM_TIMER_WRITE -= other.SUM_TIMER_WRITE
200 this.SUM_TIMER_READ_WITH_SHARED_LOCKS -= other.SUM_TIMER_READ_WITH_SHARED_LOCKS
201 this.SUM_TIMER_READ_HIGH_PRIORITY -= other.SUM_TIMER_READ_HIGH_PRIORITY
202 this.SUM_TIMER_READ_NO_INSERT -= other.SUM_TIMER_READ_NO_INSERT
203 this.SUM_TIMER_READ_NORMAL -= other.SUM_TIMER_READ_NORMAL
204 this.SUM_TIMER_READ_EXTERNAL -= other.SUM_TIMER_READ_EXTERNAL
205 this.SUM_TIMER_WRITE_CONCURRENT_INSERT -= other.SUM_TIMER_WRITE_CONCURRENT_INSERT
206 this.SUM_TIMER_WRITE_LOW_PRIORITY -= other.SUM_TIMER_WRITE_LOW_PRIORITY
207 this.SUM_TIMER_WRITE_NORMAL -= other.SUM_TIMER_WRITE_NORMAL
208 this.SUM_TIMER_WRITE_EXTERNAL -= other.SUM_TIMER_WRITE_EXTERNAL
211 // return the totals of a slice of rows
212 func (t table_rows) totals() table_row {
214 totals.OBJECT_SCHEMA = "Totals"
223 // Select the raw data from the database into file_summary_by_instance_rows
224 // - filter out empty values
225 // - merge rows with the same name into a single row
226 // - change FILE_NAME into a more descriptive value.
227 func select_rows(dbh *sql.DB) table_rows {
230 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"
232 rows, err := dbh.Query(sql)
248 &r.SUM_TIMER_READ_WITH_SHARED_LOCKS,
249 &r.SUM_TIMER_READ_HIGH_PRIORITY,
250 &r.SUM_TIMER_READ_NO_INSERT,
251 &r.SUM_TIMER_READ_NORMAL,
252 &r.SUM_TIMER_READ_EXTERNAL,
253 &r.SUM_TIMER_WRITE_ALLOW_WRITE,
254 &r.SUM_TIMER_WRITE_CONCURRENT_INSERT,
255 &r.SUM_TIMER_WRITE_LOW_PRIORITY,
256 &r.SUM_TIMER_WRITE_NORMAL,
257 &r.SUM_TIMER_WRITE_EXTERNAL); err != nil {
260 // we collect all data as we may need it later
263 if err := rows.Err(); err != nil {
270 func (t table_rows) Len() int { return len(t) }
271 func (t table_rows) Swap(i, j int) { t[i], t[j] = t[j], t[i] }
272 func (t table_rows) Less(i, j int) bool {
273 return (t[i].SUM_TIMER_WAIT > t[j].SUM_TIMER_WAIT) ||
274 ((t[i].SUM_TIMER_WAIT == t[j].SUM_TIMER_WAIT) &&
275 (t[i].OBJECT_SCHEMA < t[j].OBJECT_SCHEMA) &&
276 (t[i].OBJECT_NAME < t[j].OBJECT_NAME))
281 func (t *table_rows) sort() {
285 // remove the initial values from those rows where there's a match
286 // - if we find a row we can't match ignore it
287 func (this *table_rows) subtract(initial table_rows) {
288 i_by_name := make(map[string]int)
290 // iterate over rows by name
291 for i := range initial {
292 i_by_name[initial[i].name()] = i
295 for i := range *this {
296 if _, ok := i_by_name[(*this)[i].name()]; ok {
297 initial_i := i_by_name[(*this)[i].name()]
298 (*this)[i].subtract(initial[initial_i])
303 // if the data in t2 is "newer", "has more values" than t then it needs refreshing.
304 // check this by comparing totals.
305 func (t table_rows) needs_refresh(t2 table_rows) bool {
306 my_totals := t.totals()
307 t2_totals := t2.totals()
309 return my_totals.SUM_TIMER_WAIT > t2_totals.SUM_TIMER_WAIT
312 // describe a whole row
313 func (r table_row) String() string {
314 return fmt.Sprintf("%10s %10s %10s|%10s %10s %10s %10s %10s|%10s %10s %10s %10s %10s|%s",
315 lib.FormatTime(r.SUM_TIMER_WAIT),
316 lib.FormatTime(r.SUM_TIMER_READ),
317 lib.FormatTime(r.SUM_TIMER_WRITE),
319 lib.FormatTime(r.SUM_TIMER_READ_WITH_SHARED_LOCKS),
320 lib.FormatTime(r.SUM_TIMER_READ_HIGH_PRIORITY),
321 lib.FormatTime(r.SUM_TIMER_READ_NO_INSERT),
322 lib.FormatTime(r.SUM_TIMER_READ_NORMAL),
323 lib.FormatTime(r.SUM_TIMER_READ_EXTERNAL),
325 lib.FormatTime(r.SUM_TIMER_WRITE_ALLOW_WRITE),
326 lib.FormatTime(r.SUM_TIMER_WRITE_CONCURRENT_INSERT),
327 lib.FormatTime(r.SUM_TIMER_WRITE_LOW_PRIORITY),
328 lib.FormatTime(r.SUM_TIMER_WRITE_NORMAL),
329 lib.FormatTime(r.SUM_TIMER_WRITE_EXTERNAL),
333 // describe a whole table
334 func (t table_rows) String() string {
335 s := make([]string, len(t))
338 s = append(s, t[i].String())
341 return strings.Join(s, "\n")