How can i speed up mySQL inserts in Go?

[Sataru (marco)]

As the title states, how can i speed up inserts in a mySQL table?
I made two very simple scripts, one in Go and one in PHP 5.6, where i just connect to my db and insert one row.
Without prepared statements.
In both cases starting with the same empty table.

Now, when i run those scripts in a loop for 100 times, the PHP Script is approximately 5 times faster than the Go one.

package main

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

func main() {
	db, _ := sql.Open("mysql", "dbuser:123@/test")
	defer db.Close()
	
	db.Exec("INSERT INTO test_tb(name) VALUES('Dolly')")
} 

[joncalhoun]

Which part are you looping? Do you run sql.Open inside the loop? What does the PHP code look like?

I’m not certain we have enough info to even start helping you out

[nathankerr]

If you are using go run to run the Go code, then you are compiling the program each time. You may also be compiling the MySQL driver each time (go run does not cache builds). Make sure and go build the Go code first and use the produced executable.

Your benchmark, as designed, is not only measuring db insert times, it is also measuring program launch times. If you move the loop into the programs and set the number of iterations when running the programs you can better figure out the insert times. For example, running the programs with 0 iterations will tell you how much time is spent starting the program and connecting to the db.

Also, please check the errors. My method described at https://pocketgophers.com/error-checking-while-prototyping/ is easy to implement and will let you see what and where something goes wrong.

[Sataru (marco)]

Thx for the replies. Here what i did exactly:
First attempt:

package main

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

func main() {
	db, _ := sql.Open("mysql", "dbuser:123@/test")
	defer db.Close()
	db.Exec("INSERT INTO test_tb(name) VALUES('Dolly')")
}
```

PHP:
```
<?php

    $mysqli = new mysqli("localhost", "dbuser", "123", "test");

    if ($mysqli->connect_errno) {
        printf("Connect failed: %s\n", $mysqli->connect_error);
        exit();
    }

    $stmt = mysqli_prepare("INSERT INTO test_tb(name) VALUES('Dolly)");
    $stmt->execute();
```

I launched the two scripts with a for loop inside a shell script.
On the second attempt i did the looping inside the two scripts.

package main

import (
“database/sql”
_ “github.com/go-sql-driver/mysql”
)

func main() {
db, _ := sql.Open(“mysql”, “dbuser:123@/test”)
defer db.Close()
i := 0
for i < 100 {
db.Exec(“INSERT INTO test_tb(name) VALUES(‘Dolly’)”)
i++
}
}

And the PHP one:

<?php $mysqli = new mysqli("localhost", "dbuser", "123", "test"); if ($mysqli->connect_errno) { printf("Connect failed: %s\n", $mysqli->connect_error); exit(); } $name = "Dolly"; $stmt = $mysqli->prepare("INSERT INTO test_tb(name) VALUES('Dolly')"); for ($i=0; $i<100; $i++) { $stmt->execute(); } ``` I compiled the go app with `nv GOOS=linux GOARCH=386 GOARM=7 go build ./src/hello/mysqltest.go'` on a OSX machine. The tests where executed both on a Virtual Box with Ubuntu. @nathankerr I had a function inside the go app to check for errors. I deleted that part because i just wanted to have the bare inserts for the moment.

[geosoft1 (George Calianu)]

I observed that in your PHP code you prepare the statement then exec but not in the Go code. Try to use the same technique in Go code to properly compare (you also have prepare in Go sql driver)

[Sataru (marco)]

@geosoft1 That’s true. I will try to make some more tests.

[oleg578]

There are a lot of nuances of speed test in mysql between golang and other languages.
At first, the settings of mysql is important. Multithread or singletread, cache etc.
Then we can remember, that the same queries are cached in mysql (we can say - shadow prepare)

Then we can remember, that golang has goroutings. Therefore we can easy to create an overload, because we has limit of tcp stack (and socket also) in system. And limit of database connections also.
Then the great influence has package size - row size.
In general, golang program is faster then php always.

For example php utility

<?php

$dsn = 'mysql:dbname=gotest;host=127.0.0.1';
$user = 'root';
$password = 'corner';

$item = 0;
$value = 'Lorem ipsum dolor';

function microtime_float()
{
    list($usec, $sec) = explode(" ", microtime());
    return ((float)$usec + (float)$sec);
}

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection fault: ' . $e->getMessage();
}

$SQLQuery = "INSERT INTO `foo` (`item`,`itemval`) VALUES(?,?)";

$stmt = $dbh->prepare($SQLQuery);

$time_start = microtime_float();

for ($i=0; $i < 1000000; $i++) {
    $stmt->bindParam(1, $i);
    $stmt->bindParam(2, $value);
   $stmt->execute();
}
$time_end = microtime_float();
$time = $time_end - $time_start;

echo "Exec time $time seconds".PHP_EOL;
$speed = 1000000.00/$time;
echo "Speed $speed per second".PHP_EOL;

has speed on i7-4702MQ 16Gb SSD 850pro 13134 records per second

And golang utiliity

package main

import (
    "database/sql"
    "fmt"
    "log"
    "runtime"
    "sync"
    "time"

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

// Foo test data
type Foo struct {
    Key int
    Val string
}

const (
    VAL = `Lorem ipsum dolor`
)

var (
    DBW *sql.DB

    DSN      = "root:corner@tcp(127.0.0.1:3306)/gotest?charset=utf8"
    SQLQuery = "INSERT INTO `foo` (`item`,`itemval`) VALUES(?,?)"
    StmtMain *sql.Stmt
    wg       sync.WaitGroup
)

func Store(d Foo) {
        defer wg.Done()
    _, err := StmtMain.Exec(d.Key, d.Val)
    if err != nil {
	    log.Fatalln(err)
    }
}

func main() {
var (
	errDbw  error
	errStmt error
)
concurrencyLevel := runtime.NumCPU() * 8
DBW, errDbw = sql.Open("mysql", DSN)
if errDbw != nil {
	log.Fatalln(errDbw)
}
DBW.SetMaxIdleConns(concurrencyLevel)
defer DBW.Close()
StmtMain, errStmt = DBW.Prepare(SQLQuery)
if errStmt != nil {
	log.Fatalln(errStmt)
}
defer StmtMain.Close()
//populate data
dd := Foo{
	Key: 0,
	Val: VAL,
}
t0 := time.Now()
for i := 0; i < 1000000; {
	for k := 0; k < concurrencyLevel; k++ {
		i++
		if i > 1000000 {
			break
		}
		dd.Key = i
		wg.Add(1)
		go Store(dd)
	}
	wg.Wait()
	if i > 1000000 {
		break
	}
}
t1 := time.Now()
fmt.Printf("%v per second.\n", 1000000.0/t1.Sub(t0).Seconds())
}

has speed 26255 records per second

As you see, we prevent shadow prepare (query cache) by set key as number.
Also we can adjust max speed by change concurrency level until our database will work stable.
You can increase this value until database reject connections.

As you can see, a lot of nuances are important in every situation.

Also, you can see that database settings is very important

And in fine you can experiment with PCNTL Functions in PHP