Write a simple MySQL load tester with Golang.

2 min readDec 12, 2019

Sometimes you need to do a load test of your DB. Or you need to check an real-life execution time of your query. To do it you have to run your query in parallel and check what is happening with:

execution time;
CPU loading;
RAM loading.

To do it fast i decided to write very simple Golang script using goroutines and WaitGroup. So, let’s start.

Let’s create a main.go text file. Which will contain our peace of code.

Then we need to use Go MySQL driver to have ability to connect to MySQL server and do tests.

package main//all imports we need
import (
   _ "github.com/go-sql-driver/mysql"
   "fmt"
   "io/ioutil"
   "time"
   "os"
   "sync"
   "strconv"
   "database/sql"
)//Define our global variables
var DB *sql.DB
var wg sync.WaitGroup

func main() {
   
}

First we need to do DB connection function.

func InitDBConnection(connection string) {
   var err error
   DB, err = sql.Open("mysql", connection)
   if err != nil {
      panic(err)
   }   //set DB connection settings
   DB.SetMaxOpenConns(100)
   DB.SetMaxIdleConns(0)   //do ping to test connection
   err = DB.Ping()
   if err != nil {
      var output= fmt.Sprintf("|%s| Error Message: %s", "Ping DB", err)
      fmt.Println(output)
   }
}

We need to add reading sql file function. To do it easier let’s set file name as sql.sql

func getQuery() string {
   b, err := ioutil.ReadFile("sql.sql")
   if err != nil {
      panic(err)
   }
   str := string(b)
   return  str
}

Then we need to execute this request

func run(query string) {
   rows, _ := DB.Query(query)
   fmt.Println(time.Now())
   defer wg.Done()
   defer rows.Close()
}

Put it all together in main function

func main() {   //let’s catch connection properties, amount of requests, and kind of way of requesting: parallel or serial.
   connection := os.Args[1]
   amount, _ := strconv.Atoi(os.Args[2])
   serial, _ := strconv.ParseBool(os.Args[3])

   InitDBConnection(connection)
   defer DB.Close()

   query := getQuery()
   wg.Add(amount)

   startTime := time.Now()

   for i := 0; i < amount; i++ {
      if serial {
         run(query)
      } else {
         go run(query)
      }
   }
   wg.Wait()
   upTime := time.Since(startTime)
   fmt.Println(fmt.Sprintf("SQL execution time: %f seconds", upTime.Seconds()))
}

We use WaitGroup mechanism to wait until all goroutines will be done. run function contain defer wg.Done() this important line of code which gives a signal to WaitGroup that is job is done.

Let’s test it by running our code:

go run main.go “username:password@tcp(localhost:3306)/dbName?charset=utf8” 10 true

We do connection and run 10 serial requests to our db.

2019–12–12 11:15:26.19075 +0100 CET m=+3.881882697
2019–12–12 11:15:29.08723 +0100 CET m=+6.778384013
2019–12–12 11:15:29.637349 +0100 CET m=+7.328507009
2019–12–12 11:15:30.238886 +0100 CET m=+7.930048709
2019–12–12 11:15:30.738199 +0100 CET m=+8.429364766
2019–12–12 11:15:33.783201 +0100 CET m=+11.474389130
2019–12–12 11:15:34.293172 +0100 CET m=+11.984363610
2019–12–12 11:15:34.81864 +0100 CET m=+12.509836182
2019–12–12 11:15:35.319327 +0100 CET m=+13.010526090
2019–12–12 11:15:35.820861 +0100 CET m=+13.512064417
SQL execution time: 12.949218 seconds

What’s you learn? We did a simple DB connection and SQL query example. We tried how goroutines taste and how to work with WaitGroup, it’s two important mechanisms in Golang.

Write a simple MySQL load tester with Golang.

Written by Vadym Kozak