benchmark for select from MySQL

main.go

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/google/uuid"
	"os"
	"strings"
)

func WithDB(f func(*sql.DB)) {
	db, err := sql.Open("mysql", "<user>:<pass>@tcp(127.0.0.1:3306)/<db>")
	if err != nil {
		panic(err.Error())
	}
	defer db.Close() // 関数がリターンする直前に呼び出される
	f(db)
}

type Target struct {
	Id         string
	Value      string
}

const (
	FileName = "./ids.txt"
)

func preparation() {
	id := func() string {
		uid, _ := uuid.NewUUID()
		return strings.Replace(uid.String(), "-", "", -1)
	}
	file, err := os.OpenFile(FileName, os.O_CREATE | os.O_WRONLY, os.ModeAppend)
	if err != nil {
		fmt.Printf("failed to open. error = %s", err.Error())
		return
	}
	defer file.Close()

	N := 10000
	var ids []string

	WithDB(func(db *sql.DB) {
		sql := "INSERT INTO target (id, value) VALUES "
		var vals []interface{}
		for i := 0; i < N; i++ {
			sql += "(?, ?),"
			_id := id()
			ids = append(ids, _id)
			vals = append(vals, _id, id())
		}
		sql = strings.TrimSuffix(sql, ",")
		stmt, err := db.Prepare(sql)
		if err != nil {
			fmt.Printf("Error on prepare sql. sql = %s. error: %s", sql, err.Error())
			return
		}
		defer stmt.Close()

		res, err := stmt.Exec(vals...)
		if err != nil {
			fmt.Printf("Error on execute sql. error: %s", err.Error())
			return
		}
		fmt.Printf("result: %s", res)
		return
	})

	fmt.Printf("\n ================ \n")

	n, err := file.WriteString(strings.Join(ids, "\n"))
	if err != nil {
		fmt.Printf("write fail. error = %s", err.Error())
		return
	}
	fmt.Printf("wrote bytes: %d", n)

	file.Sync()
}

func main() {
	preparation()
}

main_test.go

package main

import (
	"bufio"
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"os"
	"sort"
	"testing"
	"time"
)

func readIds() ([]string, error) {
	file, err := os.Open(FileName)
	if err != nil {
		fmt.Printf("error to open file. error = %s", err.Error())
		return nil, err
	}
	defer file.Close()
	scanner := bufio.NewScanner(file)
	var ids []string
	for scanner.Scan() {
		ids = append(ids, scanner.Text())
	}
	if err != nil {
		fmt.Printf("error on scanning file. error = %s", err.Error())
		return nil, err
	}
	return ids, nil
}

func BenchmarkSelectMySQL(b *testing.B) {
	ids, err := readIds()
	if err != nil {
		fmt.Printf("failed to read ids. error = %s", err)
		return
	}

	WithDB(func(db *sql.DB) {
		b.ResetTimer()

		run := func() (results []*Target, timeSpents []int64) {

			query := func(id string) (result *Target, err error) {
				s := "SELECT id, value FROM target WHERE id = ?"
				stmt, err := db.Prepare(s)
				if err != nil {
					fmt.Printf("failed to prepare query. error: %s", err.Error())
					return result, err
				}
				defer stmt.Close()

				rows, err := stmt.Query(id)
				if err != nil {
					fmt.Printf("failed to fetch. error: %s", err.Error())
					return result, err
				}
				defer rows.Close()

				for rows.Next() {
					t := Target{}
					if err = rows.Scan(&t.Id, &t.Value); err != nil {
						fmt.Printf("failed to scan. error: %s", err.Error())
						return result, err
					}
				}
				return result, err
			}

			for _, id := range ids {
				start := time.Now()
				result, err := query(id)
				end := time.Now()
				if err != nil {
					fmt.Printf("cannot query. error = %s", err.Error())
					return results, timeSpents
				}
				timeSpent := end.UnixNano() - start.UnixNano()
				timeSpents = append(timeSpents, timeSpent)
				results = append(results, result)
				// time.Sleep(time.Duration(100) * time.Millisecond)
			}
			return results, timeSpents
		}

		for i := 0; i < b.N; i ++ {
			results, timeSpents := run()
			fmt.Println()
			percentilize(timeSpents)
			fmt.Println()
			fmt.Printf("N = %d, i = %d, result length = %d, \n", b.N, i, len(results))
		}
	})

}

type NanoSeconds []int64

func (p NanoSeconds) Len() int           { return len(p) }
func (p NanoSeconds) Less(i, j int) bool { return p[i] < p[j] }
func (p NanoSeconds) Swap(i, j int)      { p[i], p[j] = p[j], p[i] }

func percentilize(timeSpents NanoSeconds) {

	sort.Sort(timeSpents)
	percentiles := []int{50, 90, 95, 99}
	length := len(timeSpents)

	fmt.Printf("min: %d ms (%d ns)\n", timeSpents[0] / 1000000, timeSpents[0])

	for _, percentile := range percentiles {
		idx := (percentile * length) / 100 - 1
		fmt.Printf("%d percentile: %3d ms (%7d ns)\n", percentile, timeSpents[idx] / 1000000, timeSpents[idx])
	}

	fmt.Printf("max: %d ms (%d ns)\n", timeSpents[length - 1] / 1000000, timeSpents[length - 1])
}