mysql schema benchmark

97g.go

// import tmp/97g
package main

import (
	"database/sql"
	"fmt"
	"log"
	"math/rand"
	"strconv"
	"time"

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

/*
var migrations = []string{
	`create table application (
		id int unsigned auto_increment not null primary key,
		organization varchar(255) not null,
		name varchar(255) not null,
		version varchar(255) not null,
		platform varchar(255) not null,
		unique index name (organization, name)
	)`,
	`create trigger application_unique before insert on application
	for each row
	begin
		declare found int;
		select count(*) into found from application where organization=new.organization and name=new.name and version=new.version and platform=new.platform;
		if found > 0 then
			signal sqlstate '45000' set MYSQL_ERRNO=1062, MESSAGE_TEXT='Duplicate entry';
		end if;
	end
	`,
}
*/

var migrations = []string{
	`create table application (
		id int unsigned auto_increment not null primary key,
		organization varchar(255) not null,
		name varchar(255) not null,
		version varchar(255) not null,
		platform varchar(255) not null,
		unique index name (organization, name, version, platform)
	)`,
}

type application struct {
	platform     string
	version      string
	organization string
	name         string
}

func main() {
	db0, err := sql.Open("mysql", "root:root@tcp(localhost:3306)/?columnsWithAlias=true&parseTime=true")
	if err != nil {
		log.Fatal(err)
	}

	db0.Exec("drop database if exists 97g")
	_, err = db0.Exec("create database 97g")
	if err != nil {
		log.Fatal(err)
	}

	defer db0.Exec("drop database 97g")

	db, err := sql.Open("mysql", "root:root@tcp(localhost:3306)/97g?columnsWithAlias=true&parseTime=true")
	if err != nil {
		log.Fatal(err)
	}

	for _, migration := range migrations {
		_, err := db.Exec(migration)
		if err != nil {
			log.Fatal(err)
		}
	}

	db.SetMaxOpenConns(30)

	queue := make(chan *application, 100)
	for i := 0; i < 30; i++ {
		go worker(db, queue)
	}

	timeCheckpoint := time.Now()
	i := 0
	for {
		app := &application{
			platform:     strconv.FormatInt(rand.Int63(), 36),
			version:      strconv.FormatInt(rand.Int63(), 36),
			organization: strconv.FormatInt(rand.Int63(), 36),
			name:         strconv.FormatInt(rand.Int63(), 36),
		}

		queue <- app

		i++
		if i%1000 == 0 {
			rows, err := db.Query("select * from application where id=?", rand.Intn(i))
			if err != nil {
				log.Fatal(err)
			}
			rows.Close()

			newTimeCheckpoint := time.Now()
			fmt.Printf("%d: %s\n", i, newTimeCheckpoint.Sub(timeCheckpoint))
			timeCheckpoint = newTimeCheckpoint
		}
	}
}

func worker(db *sql.DB, queue chan *application) {
	for app := range queue {
		_, err := db.Exec("insert into application (platform, version, organization, name) values(?, ?, ?, ?)", app.platform, app.version, app.organization, app.name)
		if err != nil {
			log.Printf("Error inserting %#v:\n\t%s\n", app, err)
		}
	}
}