+++ /dev/null
-// 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 '<schema>.<table>'
-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")
-}
-
-***** */
-// manage the configuration of setup_instruments
+// Manage the configuration of setup_instruments.
package setup_instruments
import (
"database/sql"
+ "fmt"
"log"
"github.com/sjmudd/pstop/lib"
// Error 1142: UPDATE command denied to user 'cacti'@'10.164.132.182' for table 'setup_instruments'
// Error 1290: The MySQL server is running with the --read-only option so it cannot execute this statement
var EXPECTED_UPDATE_ERRORS = []string{
- "Error 1142",
- "Error 1290",
+ "Error 1142:",
+ "Error 1290:",
}
// one row of performance_schema.setup_instruments
// SetupInstruments "object"
type SetupInstruments struct {
+ update_tried bool
update_succeeded bool
rows table_rows
+ dbh *sql.DB
+}
+
+// Return a newly initialised SetupInstruments structure with a handle to the database.
+// Better to return a pointer ?
+func NewSetupInstruments(dbh *sql.DB) SetupInstruments {
+ return SetupInstruments{ dbh: dbh }
+}
+
+// enable mutex and stage monitoring
+func (si *SetupInstruments) EnableMonitoring() {
+ si.EnableMutexMonitoring()
+ si.EnableStageMonitoring()
}
// Change settings to monitor stage/sql/%
-func (si *SetupInstruments) EnableStageMonitoring(dbh *sql.DB) {
+func (si *SetupInstruments) EnableStageMonitoring() {
lib.Logger.Println("EnableStageMonitoring")
sql := "SELECT NAME, ENABLED, TIMED FROM setup_instruments WHERE NAME LIKE 'stage/sql/%' AND ( enabled <> 'YES' OR timed <> 'YES' )"
collecting := "Collecting setup_instruments stage/sql configuration settings"
updating := "Updating setup_instruments configuration for: stage/sql"
- si.ConfigureSetupInstruments(dbh, sql, collecting, updating)
+ si.Configure(sql, collecting, updating)
+ lib.Logger.Println("EnableStageMonitoring finishes")
}
// Change settings to monitor wait/synch/mutex/%
-func (si *SetupInstruments) EnableMutexMonitoring(dbh *sql.DB) {
+func (si *SetupInstruments) EnableMutexMonitoring() {
lib.Logger.Println("EnableMutexMonitoring")
sql := "SELECT NAME, ENABLED, TIMED FROM setup_instruments WHERE NAME LIKE 'wait/synch/mutex/%' AND ( enabled <> 'YES' OR timed <> 'YES' )"
collecting := "Collecting setup_instruments wait/synch/mutex configuration settings"
updating := "Updating setup_instruments configuration for: wait/synch/mutex"
- si.ConfigureSetupInstruments(dbh, sql, collecting, updating)
+ si.Configure(sql, collecting, updating)
+ lib.Logger.Println("EnableMutexMonitoring finishes")
+}
+
+// return true if the error is not in the expected list
+func error_in_expected_list(actual_error string, expected_errors []string) bool {
+ lib.Logger.Println("checking if", actual_error, "is in", expected_errors)
+ e := actual_error[0:11]
+ expected_error := false
+ for i := range expected_errors {
+ if e == expected_errors[i] {
+ lib.Logger.Println("found an expected error", expected_errors[i])
+ expected_error = true
+ break
+ }
+ }
+ lib.Logger.Println("returning", expected_error)
+ return expected_error
}
// generic routine (now) to update some rows in setup instruments
-func (si *SetupInstruments) ConfigureSetupInstruments(dbh *sql.DB, select_sql string, collecting, updating string) {
+func (si *SetupInstruments) Configure(select_sql string, collecting, updating string) {
+ lib.Logger.Println(fmt.Sprintf("Configure(%q,%q,%q)", select_sql, collecting, updating))
+ // skip if we've tried and failed
+ if si.update_tried && !si.update_succeeded {
+ lib.Logger.Println("Configure() - Skipping further configuration")
+ return
+ }
+
// setup the old values in case they're not set
if si.rows == nil {
si.rows = make([]table_row, 0, 500)
lib.Logger.Println(collecting)
- rows, err := dbh.Query(select_sql)
+ lib.Logger.Println("dbh.query", select_sql)
+ rows, err := si.dbh.Query(select_sql)
if err != nil {
log.Fatal(err)
}
- defer rows.Close()
count := 0
for rows.Next() {
&r.TIMED); err != nil {
log.Fatal(err)
}
- // we collect all information even if it's mainly empty as we may reference it later
si.rows = append(si.rows, r)
count++
}
if err := rows.Err(); err != nil {
log.Fatal(err)
}
+ rows.Close()
lib.Logger.Println("- found", count, "rows whose configuration need changing")
// update the rows which need to be set - do multiple updates but I don't care
lib.Logger.Println(updating)
- lib.Logger.Println("- about to try to update", len(si.rows), "row(s)" )
- count = 0
- for i := range si.rows {
- lib.Logger.Println("- changing row:", si.rows[i].NAME )
- sql := "UPDATE setup_instruments SET enabled = 'YES', TIMED = 'YES' WHERE NAME = '" + si.rows[i].NAME + "'"
- lib.Logger.Println("exec statement:", sql )
- if res, err := dbh.Exec(sql); err == nil {
- si.update_succeeded = true
- c, _ := res.RowsAffected()
- count += int(c)
+ const update_sql = "UPDATE setup_instruments SET enabled = ?, TIMED = ? WHERE NAME = ?"
+ lib.Logger.Println("Preparing statement:", update_sql)
+ si.update_tried = true
+ lib.Logger.Println("dbh.Prepare", update_sql)
+ stmt, err := si.dbh.Prepare(update_sql)
+ if err != nil {
+ lib.Logger.Println("- prepare gave error:", err.Error())
+ if !error_in_expected_list(err.Error(), EXPECTED_UPDATE_ERRORS) {
+ log.Fatal("Not expected error so giving up")
} else {
- found_expected := false
- for i := range EXPECTED_UPDATE_ERRORS {
- if err.Error()[0:10] == EXPECTED_UPDATE_ERRORS[i] {
- found_expected = true
- break
+ lib.Logger.Println("- expected error so not running statement")
+ }
+ } else {
+ lib.Logger.Println("Prepare succeeded, trying to update", len(si.rows), "row(s)")
+ count = 0
+ for i := range si.rows {
+ lib.Logger.Println("- changing row:", si.rows[i].NAME)
+ lib.Logger.Println("stmt.Exec", "YES", "YES", si.rows[i].NAME )
+ if res, err := stmt.Exec("YES", "YES", si.rows[i].NAME); err == nil {
+ lib.Logger.Println("update succeeded")
+ si.update_succeeded = true
+ c, _ := res.RowsAffected()
+ count += int(c)
+ } else {
+ si.update_succeeded = false
+ if error_in_expected_list(err.Error(), EXPECTED_UPDATE_ERRORS) {
+ lib.Logger.Println("Insufficient privileges to UPDATE setup_instruments: " + err.Error())
+ lib.Logger.Println("Not attempting further updates")
+ return
+ } else {
+ log.Fatal(err)
}
}
- if !found_expected {
- log.Fatal(err)
- }
- lib.Logger.Println("Insufficient privileges to UPDATE setup_instruments: " + err.Error())
- lib.Logger.Println("Not attempting further updates")
- break
}
+ if si.update_succeeded {
+ lib.Logger.Println(count, "rows changed in p_s.setup_instruments")
+ }
+ stmt.Close()
}
- if si.update_succeeded {
- lib.Logger.Println(count, "rows changed in p_s.setup_instruments")
- }
+ lib.Logger.Println( "Configure() returns update_tried", si.update_tried, ", update_succeeded", si.update_succeeded)
}
// restore setup_instruments rows to their previous settings
-func (si *SetupInstruments) RestoreConfiguration(dbh *sql.DB) {
+func (si *SetupInstruments) RestoreConfiguration() {
+ lib.Logger.Println("RestoreConfiguration()")
// If the previous update didn't work then don't try to restore
if !si.update_succeeded {
- lib.Logger.Println("Not restoring p_s.setup_instruments to its original settings as previous UPDATE had failed")
+ lib.Logger.Println("Not restoring p_s.setup_instruments to original settings as initial configuration attempt failed")
return
} else {
lib.Logger.Println("Restoring p_s.setup_instruments to its original settings")
// update the rows which need to be set - do multiple updates but I don't care
update_sql := "UPDATE setup_instruments SET enabled = ?, TIMED = ? WHERE NAME = ?"
- stmt, err := dbh.Prepare( update_sql )
+ lib.Logger.Println("dbh.Prepare(",update_sql,")")
+ stmt, err := si.dbh.Prepare(update_sql)
if err != nil {
log.Fatal(err)
}
count := 0
for i := range si.rows {
- if _, err := stmt.Exec(si.rows[i].ENABLED, si.rows[i].TIMED, si.rows[i].NAME ); err != nil {
+ lib.Logger.Println("stmt.Exec(",si.rows[i].ENABLED, si.rows[i].TIMED, si.rows[i].NAME,")")
+ if _, err := stmt.Exec(si.rows[i].ENABLED, si.rows[i].TIMED, si.rows[i].NAME); err != nil {
log.Fatal(err)
}
count++
}
+ lib.Logger.Println("stmt.Close()")
stmt.Close()
lib.Logger.Println(count, "rows changed in p_s.setup_instruments")
}
git://git.iain.cx/iain / pstop.git / commitdiff