Home Original page

Improve query building routines of DML event queries, reducing time and allocations by danieljoos · Pull Request #1459 · github/gh-ost

Find below the results of some synthetic benchmarks, using the Go benchmarking capabilities.
The benchmarks have been conducted on Codespace hosts.
Benchmark functions can be found in the details.

Benchmark Build DML Delete Query

Benchmark function 
func BenchmarkApplierBuildDMLEventQuery_Delete(b *testing.B) {
	columns := sql.NewColumnList([]string{"id", "item_id", "name"})

	migrationContext := base.NewMigrationContext()
	migrationContext.DatabaseName = "test"
	migrationContext.OriginalTableName = "test"
	migrationContext.OriginalTableColumns = columns
	migrationContext.SharedColumns = columns
	migrationContext.MappedSharedColumns = columns
	migrationContext.UniqueKey = &sql.UniqueKey{
		Name:    "unique",
		Columns: *columns,
	}

	applier := NewApplier(migrationContext)
	applier.prepareQueries() // comment out in current version

	columnValues := sql.ToColumnValues([]interface{}{123, 456, "abc"})
	binlogEvent := &binlog.BinlogDMLEvent{
		DatabaseName:      "test",
		DML:               binlog.DeleteDML,
		WhereColumnValues: columnValues,
	}
	for range b.N {
		_ = applier.buildDMLEventQuery(binlogEvent)
	}
}

Current version

goos: linux
goarch: amd64
pkg: github.com/github/gh-ost/go/logic
cpu: Intel(R) Xeon(R) Platinum 8370C CPU @ 2.80GHz
BenchmarkApplierBuildDMLEventQuery_Delete-2   	  460914	      2994 ns/op	    1112 B/op	      45 allocs/op
PASS
ok  	github.com/github/gh-ost/go/logic	1.416s

Updated version

goos: linux
goarch: amd64
pkg: github.com/github/gh-ost/go/logic
cpu: Intel(R) Xeon(R) Platinum 8370C CPU @ 2.80GHz
BenchmarkApplierBuildDMLEventQuery_Delete-2   	 5844512	       186.2 ns/op	     128 B/op	       4 allocs/op
PASS
ok  	github.com/github/gh-ost/go/logic	1.306s

Benchmark Build DML Insert Query

Benchmark function 
func BenchmarkApplierBuildDMLEventQuery_Insert(b *testing.B) {
	columns := sql.NewColumnList([]string{"id", "item_id"})

	migrationContext := base.NewMigrationContext()
	migrationContext.DatabaseName = "test"
	migrationContext.OriginalTableName = "test"
	migrationContext.OriginalTableColumns = columns
	migrationContext.SharedColumns = columns
	migrationContext.MappedSharedColumns = columns
	migrationContext.UniqueKey = &sql.UniqueKey{
		Name:    "unique",
		Columns: *columns,
	}

	applier := NewApplier(migrationContext)
	applier.prepareQueries() // comment out in current version
	columnValues := sql.ToColumnValues([]interface{}{1234, 5678})
	binlogEvent := &binlog.BinlogDMLEvent{
		DatabaseName:    "test",
		DML:             binlog.InsertDML,
		NewColumnValues: columnValues,
	}
	for range b.N {
		_ = applier.buildDMLEventQuery(binlogEvent)
	}
}

Current version

goos: linux
goarch: amd64
pkg: github.com/github/gh-ost/go/logic
cpu: Intel(R) Xeon(R) Platinum 8370C CPU @ 2.80GHz
BenchmarkApplierBuildDMLEventQuery_Insert-2   	  774986	      1713 ns/op	     600 B/op	      27 allocs/op
PASS
ok  	github.com/github/gh-ost/go/logic	1.353s

Updated version

goos: linux
goarch: amd64
pkg: github.com/github/gh-ost/go/logic
cpu: Intel(R) Xeon(R) Platinum 8370C CPU @ 2.80GHz
BenchmarkApplierBuildDMLEventQuery_Insert-2   	 9303057	       141.0 ns/op	     104 B/op	       3 allocs/op
PASS
ok  	github.com/github/gh-ost/go/logic	1.453s

Benchmark Build DML Update Query

Benchmark function 
func BenchmarkApplierBuildDMLEventQuery_Update(b *testing.B) {
	columns := sql.NewColumnList([]string{"id", "item_id"})

	migrationContext := base.NewMigrationContext()
	migrationContext.DatabaseName = "test"
	migrationContext.OriginalTableName = "test"
	migrationContext.OriginalTableColumns = columns
	migrationContext.SharedColumns = columns
	migrationContext.MappedSharedColumns = columns
	migrationContext.UniqueKey = &sql.UniqueKey{
		Name:    "unique",
		Columns: *columns,
	}

	applier := NewApplier(migrationContext)
	applier.prepareQueries() // comment out in current version
	columnValues := sql.ToColumnValues([]interface{}{1234, 5678})
	binlogEvent := &binlog.BinlogDMLEvent{
		DatabaseName:      "test",
		DML:               binlog.UpdateDML,
		NewColumnValues:   columnValues,
		WhereColumnValues: columnValues,
	}
	for range b.N {
		_ = applier.buildDMLEventQuery(binlogEvent)
	}
}

Current version

goos: linux
goarch: amd64
pkg: github.com/github/gh-ost/go/logic
cpu: Intel(R) Xeon(R) Platinum 8370C CPU @ 2.80GHz
BenchmarkApplierBuildDMLEventQuery_Update-2   	  400082	      3062 ns/op	    1136 B/op	      52 allocs/op
PASS
ok  	github.com/github/gh-ost/go/logic	1.266s

Updated version

goos: linux
goarch: amd64
pkg: github.com/github/gh-ost/go/logic
cpu: Intel(R) Xeon(R) Platinum 8370C CPU @ 2.80GHz
BenchmarkApplierBuildDMLEventQuery_Update-2   	 3431607	       363.4 ns/op	     232 B/op	       6 allocs/op
PASS
ok  	github.com/github/gh-ost/go/logic	1.611s