Skip to content

Instantly share code, notes, and snippets.

@Martin91
Created November 25, 2022 07:59
Show Gist options
  • Save Martin91/d88288b6ed84060a60a31e6c31ec1c41 to your computer and use it in GitHub Desktop.
Save Martin91/d88288b6ed84060a60a31e6c31ec1c41 to your computer and use it in GitHub Desktop.
Benchmark mysql with simple read, update and insert json by JSON type and text type
package jsonbench
import (
"database/sql"
"fmt"
"math/rand"
)
var (
jsonPattern = "{\"value\": \"%s\"}"
)
type Data struct {
ID int32
Document string
}
const letterBytes = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
func RandString(n int) string {
b := make([]byte, n)
for i := range b {
b[i] = letterBytes[rand.Intn(len(letterBytes))]
}
return string(b)
}
func InsertDataText(db *sql.DB) {
_, err := db.Exec("INSERT INTO data_with_text (document) VALUES (?)", fmt.Sprintf(jsonPattern, RandString(32)))
if err != nil {
panic("InsertDataText error: " + err.Error())
}
}
func InsertDataJson(db *sql.DB) {
_, err := db.Exec("INSERT INTO data_with_json (document) VALUES (?)", fmt.Sprintf(jsonPattern, RandString(32)))
if err != nil {
panic("InsertDataText error: " + err.Error())
}
}
func UpdateDataText(db *sql.DB) {
_, err := db.Exec("UPDATE data_with_text SET document = ? WHERE id=1", fmt.Sprintf(jsonPattern, RandString(32)))
if err != nil {
panic("UpdateDataText error: " + err.Error())
}
}
func UpdateDataJson(db *sql.DB) {
_, err := db.Exec("UPDATE data_with_json SET document = ? WHERE id=1", fmt.Sprintf(jsonPattern, RandString(32)))
if err != nil {
panic("UpdateDataJson error: " + err.Error())
}
}
func ReadDataText(db *sql.DB) {
_, err := db.Query("SELECT * FROM data_with_text LIMIT 10")
if err != nil {
panic("ReadDataText error: " + err.Error())
}
}
func ReadDataJson(db *sql.DB) {
_, err := db.Query("SELECT * FROM data_with_json LIMIT 10")
if err != nil {
panic("ReadDataJson error: " + err.Error())
}
}
package jsonbench
import (
"database/sql"
"testing"
_ "github.com/go-sql-driver/mysql"
)
var db *sql.DB
func init() {
var err error
db, err = sql.Open("mysql", "root@/test_json")
if err != nil {
panic("failed to establish a database connection: " + err.Error())
}
db.SetMaxOpenConns(1000)
db.SetMaxIdleConns(100)
}
func BenchmarkInsertDataText(b *testing.B) {
for i := 0; i < b.N; i++ {
InsertDataText(db)
}
}
func BenchmarkInsertDataJson(b *testing.B) {
for i := 0; i < b.N; i++ {
InsertDataJson(db)
}
}
func BenchmarkUpdateDataText(b *testing.B) {
for i := 0; i < b.N; i++ {
UpdateDataText(db)
}
}
func BenchmarkUpdateDataJson(b *testing.B) {
for i := 0; i < b.N; i++ {
UpdateDataJson(db)
}
}
func BenchmarkReadDataText(b *testing.B) {
for i := 0; i < b.N; i++ {
ReadDataText(db)
}
}
func BenchmarkReadDataJson(b *testing.B) {
for i := 0; i < b.N; i++ {
ReadDataJson(db)
}
}
go test -bench=.
goos: darwin
goarch: amd64
pkg: mysql-json-benchmark
cpu: Intel(R) Core(TM) i7-9750H CPU @ 2.60GHz
BenchmarkInsertDataText-12 1281 938660 ns/op
BenchmarkInsertDataJson-12 1246 936762 ns/op
BenchmarkUpdateDataText-12 1083 1055003 ns/op
BenchmarkUpdateDataJson-12 1164 1025521 ns/op
BenchmarkReadDataText-12 ^Csignal: interrupt
FAIL mysql-json-benchmark 54.450s
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment