1 // This file contains the library routines for managing the
2 // file_summary_by_instance table.
3 package file_summary_by_instance
13 "github.com/sjmudd/pstop/lib"
17 CREATE TABLE `file_summary_by_instance` (
18 `FILE_NAME` varchar(512) NOT NULL,
19 `EVENT_NAME` varchar(128) NOT NULL, // not collected
20 `OBJECT_INSTANCE_BEGIN` bigint(20) unsigned NOT NULL, // not collected
21 `COUNT_STAR` bigint(20) unsigned NOT NULL,
22 `SUM_TIMER_WAIT` bigint(20) unsigned NOT NULL,
23 `MIN_TIMER_WAIT` bigint(20) unsigned NOT NULL,
24 `AVG_TIMER_WAIT` bigint(20) unsigned NOT NULL,
25 `MAX_TIMER_WAIT` bigint(20) unsigned NOT NULL,
26 `COUNT_READ` bigint(20) unsigned NOT NULL,
27 `SUM_TIMER_READ` bigint(20) unsigned NOT NULL,
28 `MIN_TIMER_READ` bigint(20) unsigned NOT NULL,
29 `AVG_TIMER_READ` bigint(20) unsigned NOT NULL,
30 `MAX_TIMER_READ` bigint(20) unsigned NOT NULL,
31 `SUM_NUMBER_OF_BYTES_READ` bigint(20) NOT NULL,
32 `COUNT_WRITE` bigint(20) unsigned NOT NULL,
33 `SUM_TIMER_WRITE` bigint(20) unsigned NOT NULL,
34 `MIN_TIMER_WRITE` bigint(20) unsigned NOT NULL,
35 `AVG_TIMER_WRITE` bigint(20) unsigned NOT NULL,
36 `MAX_TIMER_WRITE` bigint(20) unsigned NOT NULL,
37 `SUM_NUMBER_OF_BYTES_WRITE` bigint(20) NOT NULL,
38 `COUNT_MISC` bigint(20) unsigned NOT NULL,
39 `SUM_TIMER_MISC` bigint(20) unsigned NOT NULL,
40 `MIN_TIMER_MISC` bigint(20) unsigned NOT NULL,
41 `AVG_TIMER_MISC` bigint(20) unsigned NOT NULL,
42 `MAX_TIMER_MISC` bigint(20) unsigned NOT NULL
43 ) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8
44 1 row in set (0.00 sec)
47 type file_summary_by_instance_row struct {
57 SUM_TIMER_WRITE uint64
60 SUM_NUMBER_OF_BYTES_READ uint64
61 SUM_NUMBER_OF_BYTES_WRITE uint64
64 // represents a table or set of rows
65 type file_summary_by_instance_rows []file_summary_by_instance_row
67 // Return the name using the FILE_NAME attribute.
68 func (r *file_summary_by_instance_row) name() string {
72 // Return a formatted pretty name for the row.
73 func (r *file_summary_by_instance_row) pretty_name() string {
78 return fmt.Sprintf("%-30s", s)
81 func (r *file_summary_by_instance_row) headings() string {
82 return fmt.Sprintf("%-30s %10s %6s|%6s %6s %6s|%8s %8s|%8s %6s %6s %6s",
97 // generate a printable result
98 func (row *file_summary_by_instance_row) row_content(totals file_summary_by_instance_row) string {
101 // We assume that if COUNT_STAR = 0 then there's no data at all...
102 // when we have no data we really don't want to show the name either.
103 if row.COUNT_STAR == 0 {
106 name = row.pretty_name()
109 return fmt.Sprintf("%-30s %10s %6s|%6s %6s %6s|%8s %8s|%8s %6s %6s %6s",
111 lib.FormatTime(row.SUM_TIMER_WAIT),
112 lib.FormatPct(lib.MyDivide(row.SUM_TIMER_WAIT, totals.SUM_TIMER_WAIT)),
113 lib.FormatPct(lib.MyDivide(row.SUM_TIMER_READ, row.SUM_TIMER_WAIT)),
114 lib.FormatPct(lib.MyDivide(row.SUM_TIMER_WRITE, row.SUM_TIMER_WAIT)),
115 lib.FormatPct(lib.MyDivide(row.SUM_TIMER_MISC, row.SUM_TIMER_WAIT)),
116 lib.FormatAmount(row.SUM_NUMBER_OF_BYTES_READ),
117 lib.FormatAmount(row.SUM_NUMBER_OF_BYTES_WRITE),
118 lib.FormatAmount(row.COUNT_STAR),
119 lib.FormatPct(lib.MyDivide(row.COUNT_READ, row.COUNT_STAR)),
120 lib.FormatPct(lib.MyDivide(row.COUNT_WRITE, row.COUNT_STAR)),
121 lib.FormatPct(lib.MyDivide(row.COUNT_MISC, row.COUNT_STAR)))
124 func (this *file_summary_by_instance_row) add(other file_summary_by_instance_row) {
125 this.COUNT_STAR += other.COUNT_STAR
126 this.COUNT_READ += other.COUNT_READ
127 this.COUNT_WRITE += other.COUNT_WRITE
128 this.COUNT_MISC += other.COUNT_MISC
130 this.SUM_TIMER_WAIT += other.SUM_TIMER_WAIT
131 this.SUM_TIMER_READ += other.SUM_TIMER_READ
132 this.SUM_TIMER_WRITE += other.SUM_TIMER_WRITE
133 this.SUM_TIMER_MISC += other.SUM_TIMER_MISC
135 this.SUM_NUMBER_OF_BYTES_READ += other.SUM_NUMBER_OF_BYTES_READ
136 this.SUM_NUMBER_OF_BYTES_WRITE += other.SUM_NUMBER_OF_BYTES_WRITE
139 func (this *file_summary_by_instance_row) subtract(other file_summary_by_instance_row) {
140 this.COUNT_STAR -= other.COUNT_STAR
141 this.COUNT_READ -= other.COUNT_READ
142 this.COUNT_WRITE -= other.COUNT_WRITE
143 this.COUNT_MISC -= other.COUNT_MISC
145 this.SUM_TIMER_WAIT -= other.SUM_TIMER_WAIT
146 this.SUM_TIMER_READ -= other.SUM_TIMER_READ
147 this.SUM_TIMER_WRITE -= other.SUM_TIMER_WRITE
148 this.SUM_TIMER_MISC -= other.SUM_TIMER_MISC
150 this.SUM_NUMBER_OF_BYTES_READ -= other.SUM_NUMBER_OF_BYTES_READ
151 this.SUM_NUMBER_OF_BYTES_WRITE -= other.SUM_NUMBER_OF_BYTES_WRITE
154 // return the totals of a slice of rows
155 func (t file_summary_by_instance_rows) totals() file_summary_by_instance_row {
156 var totals file_summary_by_instance_row
157 totals.FILE_NAME = "TOTALS"
166 // clean up the given path reducing redundant stuff and return the clean path
167 func cleanup_path(path string) string {
168 // foo/../bar --> bar perl: $new =~ s{[^/]+/\.\./}{/};
169 // foo/./bar --> foo/bar perl: $new =~ s{/\./}{};
170 // // --> / perl: $new =~ s{//}{/};
172 double_slash_re = `//`
173 slash_dot_slash_re = `/\./`
174 slash_dot_dot_slash_re = `[^/]+/\.\./`
179 r := regexp.MustCompile(double_slash_re)
180 path = r.ReplaceAllString(path, "")
181 r = regexp.MustCompile(slash_dot_slash_re)
182 path = r.ReplaceAllString(path, "")
183 r = regexp.MustCompile(slash_dot_dot_slash_re)
184 path = r.ReplaceAllString(path, "")
185 if orig_path == path { // no change so give up
193 // From the original FILE_NAME we want to generate a simpler name to use.
194 // This simpler name may also merge several different filenames into one.
195 func (t file_summary_by_instance_row) simple_name(global_variables map[string]string) string {
197 auto_cnf_re = `/auto\.cnf$`
198 binlog_re = `/binlog\.(\d{6}|index)$`
199 charset_re = `/share/charsets/Index\.xml$`
200 db_opt_re = `/db\.opt$`
201 error_msg_re = `/share/[^/]+/errmsg\.sys$`
202 ibdata_re = `/ibdata\d+$`
203 redo_log_re = `/ib_logfile\d+$`
204 pid_file_re = `/[^/]+\.pid$`
205 // relay_log_re = `/mysql-relay-bin.(\d{6}|index)$`
206 relative_path_re = `^\.\./`
207 current_dir_re = `^\./`
208 slowlog_re = `/slowlog$`
209 table_file_re = `/([^/]+)/([^/]+)\.(frm|ibd|MYD|MYI|CSM|CSV|par)$`
210 temp_table_re = `#sql-[0-9_]+`
211 part_table_re = `(.+)#P#p\d+`
216 // this should probably be ordered from most expected regexp to least
217 re := regexp.MustCompile(table_file_re)
218 if m1 := re.FindStringSubmatch(path); m1 != nil {
219 // we may match temporary tables so check for them
220 re2 := regexp.MustCompile(temp_table_re)
221 if m2 := re2.FindStringSubmatch(m1[2]); m2 != nil {
222 return "<temp_table>"
225 // we may match partitioned tables so check for them
226 re3 := regexp.MustCompile(part_table_re)
227 if m3 := re3.FindStringSubmatch(m1[2]); m3 != nil {
228 return m1[1] + "." + m3[1] // <schema>.<table> (less partition info)
231 return m1[1] + "." + m1[2] // <schema>.<table>
233 if regexp.MustCompile(ibdata_re).MatchString(path) == true {
236 if regexp.MustCompile(redo_log_re).MatchString(path) == true {
239 if regexp.MustCompile(binlog_re).MatchString(path) == true {
242 if regexp.MustCompile(db_opt_re).MatchString(path) == true {
245 if regexp.MustCompile(slowlog_re).MatchString(path) == true {
248 if regexp.MustCompile(auto_cnf_re).MatchString(path) == true {
251 // relay logs are a bit complicated. If a full path then easy to
252 // identify,but if a relative path we may need to add $datadir,
253 // but also if as I do we have a ../blah/somewhere/path then we
254 // need to make it match too.
255 if len(global_variables["relay_log"]) > 0 {
256 relay_log := global_variables["relay_log"]
257 if relay_log[0] != '/' { // relative path
258 relay_log = cleanup_path(global_variables["datadir"] + relay_log) // datadir always ends in /
260 relay_log_re := relay_log + `\.(\d{6}|index)$`
261 if regexp.MustCompile(relay_log_re).MatchString(path) == true {
265 if regexp.MustCompile(pid_file_re).MatchString(path) == true {
268 if regexp.MustCompile(error_msg_re).MatchString(path) == true {
271 if regexp.MustCompile(charset_re).MatchString(path) == true {
277 // Convert the imported "table" to a merged one with merged data.
278 // Combine all entries with the same "FILE_NAME" by adding their values.
279 func merge_by_table_name(orig file_summary_by_instance_rows, global_variables map[string]string) file_summary_by_instance_rows {
281 t := make(file_summary_by_instance_rows, 0, len(orig))
283 m := make(map[string]file_summary_by_instance_row)
285 // iterate over source table
286 for i := range orig {
288 var new_row file_summary_by_instance_row
291 if orig_row.COUNT_STAR > 0 {
292 file_name = orig_row.simple_name(global_variables)
294 // check if we have an entry in the map
295 if _, found := m[file_name]; found {
296 new_row = m[file_name]
298 new_row.FILE_NAME = file_name
300 new_row.add(orig_row)
301 m[file_name] = new_row // update the map with the new value
305 // add the map contents back into the table
306 for _, row := range m {
310 lib.Logger.Println("merge_by_table_name() took:", time.Duration(time.Since(start)).String())
314 // Select the raw data from the database into file_summary_by_instance_rows
315 // - filter out empty values
316 // - merge rows with the same name into a single row
317 // - change FILE_NAME into a more descriptive value.
318 func select_fsbi_rows(dbh *sql.DB) file_summary_by_instance_rows {
319 var t file_summary_by_instance_rows
322 sql := "SELECT FILE_NAME, COUNT_STAR, SUM_TIMER_WAIT, COUNT_READ, SUM_TIMER_READ, SUM_NUMBER_OF_BYTES_READ, COUNT_WRITE, SUM_TIMER_WRITE, SUM_NUMBER_OF_BYTES_WRITE, COUNT_MISC, SUM_TIMER_MISC FROM file_summary_by_instance"
324 rows, err := dbh.Query(sql)
331 var r file_summary_by_instance_row
333 if err := rows.Scan(&r.FILE_NAME, &r.COUNT_STAR, &r.SUM_TIMER_WAIT, &r.COUNT_READ, &r.SUM_TIMER_READ, &r.SUM_NUMBER_OF_BYTES_READ, &r.COUNT_WRITE, &r.SUM_TIMER_WRITE, &r.SUM_NUMBER_OF_BYTES_WRITE, &r.COUNT_MISC, &r.SUM_TIMER_MISC); err != nil {
338 if err := rows.Err(); err != nil {
341 lib.Logger.Println("select_fsbi_rows() took:", time.Duration(time.Since(start)).String())
346 // remove the initial values from those rows where there's a match
347 // - if we find a row we can't match ignore it
348 func (this *file_summary_by_instance_rows) subtract(initial file_summary_by_instance_rows) {
349 i_by_name := make(map[string]int)
351 // iterate over rows by name
352 for i := range initial {
353 i_by_name[initial[i].name()] = i
356 for i := range *this {
357 if _, ok := i_by_name[(*this)[i].name()]; ok {
358 initial_i := i_by_name[(*this)[i].name()]
359 (*this)[i].subtract(initial[initial_i])
364 func (t file_summary_by_instance_rows) Len() int { return len(t) }
365 func (t file_summary_by_instance_rows) Swap(i, j int) { t[i], t[j] = t[j], t[i] }
366 func (t file_summary_by_instance_rows) Less(i, j int) bool {
367 return (t[i].SUM_TIMER_WAIT > t[j].SUM_TIMER_WAIT) ||
368 ((t[i].SUM_TIMER_WAIT == t[j].SUM_TIMER_WAIT) && (t[i].FILE_NAME < t[j].FILE_NAME))
371 func (t *file_summary_by_instance_rows) sort() {
375 // if the data in t2 is "newer", "has more values" than t then it needs refreshing.
376 // check this by comparing totals.
377 func (t file_summary_by_instance_rows) needs_refresh(t2 file_summary_by_instance_rows) bool {
378 my_totals := t.totals()
379 t2_totals := t2.totals()
381 return my_totals.SUM_TIMER_WAIT > t2_totals.SUM_TIMER_WAIT