mysql_ps2tsv.go

package main

// queryが途中で途切れる場合は performance_schema_max_sql_text_length を引き上げる
// https://dev.mysql.com/doc/refman/8.0/en/performance-schema-system-variables.html#sysvar_performance_schema_max_sql_text_length

import (
	"context"
	"database/sql"
	"encoding/csv"
	"flag"
	"fmt"
	"io"
	"log"
	"os"
	"strconv"

	"github.com/go-sql-driver/mysql"
)

const (
	defaultDBName  = "performance_schema"
	defaultExecute = `select
  COUNT_STAR AS cnt,
  SUM_TIMER_WAIT/1e12 AS sum,
  MIN_TIMER_WAIT/1e12 AS min,
  AVG_TIMER_WAIT/1e12 AS avg,
  MAX_TIMER_WAIT/1e12 AS max,
  SUM_LOCK_TIME/1e12 AS sumLock,
  SUM_ROWS_SENT AS sumRows,
  ifnull((SUM_ROWS_SENT / nullif(COUNT_STAR,0)),0) AS avgRows,
  SCHEMA_NAME AS db,
  QUERY_SAMPLE_TEXT AS query
from events_statements_summary_by_digest
where schema_name <> 'performance_schema'
order by SUM_TIMER_WAIT desc`
)

func output(w io.Writer, query, dsn string) error {
	ctx := context.Background()

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return fmt.Errorf("failed to open %s: %v", dsn, err)
	}

	rows, err := db.QueryContext(ctx, query)
	if err != nil {
		return fmt.Errorf("failed to query %s: %v", query, err)
	}
	defer rows.Close()

	cols, err := rows.Columns()
	if err != nil {
		return fmt.Errorf("failed to get columns: %v", err)
	}

	cw := csv.NewWriter(w)
	cw.Comma = '\t'
	defer cw.Flush()

	if err := cw.Write(cols); err != nil {
		return fmt.Errorf("failed to write columns: %v", err)
	}

	for rows.Next() {
		var totalLatency, minLatency, avgLatency, maxLatency, lockLatency, rowsSentAvg float64
		var execCnt, rowsSent int
		var schemaName, querySampleText sql.NullString

		err := rows.Scan(&execCnt, &totalLatency, &minLatency, &avgLatency, &maxLatency, &lockLatency, &rowsSent, &rowsSentAvg, &schemaName, &querySampleText)

		tsv := []string{
			strconv.Itoa(execCnt),
			strconv.FormatFloat(totalLatency, 'f', -1, 64),
			strconv.FormatFloat(minLatency, 'f', -1, 64),
			strconv.FormatFloat(avgLatency, 'f', -1, 64),
			strconv.FormatFloat(maxLatency, 'f', -1, 64),
			strconv.FormatFloat(lockLatency, 'f', -1, 64),
			strconv.Itoa(rowsSent),
			strconv.FormatFloat(rowsSentAvg, 'f', -1, 64),
			schemaName.String,
			querySampleText.String,
		}

		if err = cw.Write(tsv); err != nil {
			return fmt.Errorf("failed to write values: %v", err)
		}
	}
	return nil
}

func main() {
	var host, sock string
	var query string

	mysqlConfig := mysql.NewConfig()

	flag.StringVar(&mysqlConfig.User, "user", "", "Username")
	flag.StringVar(&mysqlConfig.User, "u", "", "Username (shorthand)")
	flag.StringVar(&mysqlConfig.Passwd, "password", "", "Password")
	flag.StringVar(&mysqlConfig.Passwd, "p", "", "Password (shorthand)")
	flag.StringVar(&host, "host", "", "Host")
	flag.StringVar(&host, "h", "", "Host (shorthand)")
	flag.StringVar(&sock, "socket", "", "Socket path")
	flag.StringVar(&sock, "S", "", "Socket path (shorthand)")
	flag.StringVar(&query, "execute", defaultExecute, "execute command")
	flag.StringVar(&query, "e", defaultExecute, "execute command (shorthand)")
	flag.Parse()
	if host != "" {
		mysqlConfig.Net = "tcp"
		mysqlConfig.Addr = host
	} else if sock != "" {
		mysqlConfig.Net = "unix"
		mysqlConfig.Addr = sock
	}

	dbname := flag.Arg(0)
	if dbname == "" {
		dbname = defaultDBName
	}
	mysqlConfig.DBName = dbname

	if err := output(os.Stdout, query, mysqlConfig.FormatDSN()); err != nil {
		log.Fatal(err)
	}
}