Mysql: Performance issue - why queries slower in more goroutine

[zhiyongoceanwing]

Issue description

While exec "SELECT 1 as id" in more goroutine and spend more time

AnkerdeMacBook-pro-94% go run sqlasyn.go -n 10 -c 10  
sqlasyn starting...
sqlasyn finished - 7.325ms...

AnkerdeMacBook-pro-94% go run sqlasyn.go -n 100 -c 100
sqlasyn starting...
sqlasyn finished - 106.656303ms...

AnkerdeMacBook-pro-94% go run sqlasyn.go -n 200 -c 200
sqlasyn starting...
sqlasyn finished - 179.160746ms...

Example code

```sqlasyn.go
package main

import (
"database/sql"
"flag"
"log"
"time"
_ "github.com/go-sql-driver/mysql"
"fmt"
)

func main() {

at1 := time.Now()
fmt.Println("sqlasyn starting...")

nPtr := flag.Int("n", 10, "number of goroutines")
cPtr := flag.Int("c", 10, "number of connections")
flag.Parse()
n := *nPtr
db, err := sql.Open("mysql", "root:123456@/mytest?charset=utf8&parseTime=True&loc=Local")
if err != nil {
    log.Fatal(err)
}

err = db.Ping()
if err != nil {
    log.Fatal(err)
}

done := make(chan bool)
db.SetMaxIdleConns(*cPtr)
db.SetMaxOpenConns(*cPtr)
for i := 0; i < n; i++ {
    go func(j int) {
        tn := fmt.Sprintf("thread-%d", j)
        fmt.Println(tn)
        start := time.Now()
        read(db)
        fmt.Println(tn, ":", time.Since(start))
        done <- true
    }(i)
}

for i := 0; i < n; i++ {
    <-done
}

db.Close()

fmt.Printf("sqlasyn finished - %v...\r\n", time.Since(at1))

}

func read(db *sql.DB) {
rows, err := db.Query("SELECT 1 as id")
if err != nil {
log.Fatal(err)
}
defer rows.Close()

users := []user{}
for rows.Next() {
    user := user{}
    err := rows.Scan(
        &user.ID,
    )
    if err != nil {
        log.Fatal(err)
    }
    users = append(users, user)
}

}

type user struct {
ID int64 db:"id"
}
```

Configuration

*Driver version (or git SHA): cd4cb909ce1a31435164be29bf3682031f61539a
*Go version: go version go1.8 darwin/amd64
*Server version: Server version: 5.6.35-log MySQL Community Server (GPL)
*Server OS: macOS Sierra 10.12.6

[methane]

Does your machine have 200 CPU cores?

[zhiyongoceanwing]

@methane just 2 CPU cores
hw.physicalcpu: 2
hw.logicalcpu: 4

[methane]

Unless RTT between your machine and DB is too high, more concurrency means just more context switches. It makes program slower.
Opening new connection has significant cost too.
I feel they are enough reason.

[zhiyongoceanwing]

When I print the time of each goroutine, it goes to

# go run sqlasyn.go -n 10 -c 10
...
thread-3 : 2.707672ms
thread-2 : 2.926755ms
thread-8 : 2.946647ms

# go run sqlasyn.go -n 100 -c 100
...
thread-0 : 66.094596ms
thread-78 : 62.118066ms
thread-86 : 60.793539ms

seems like the queries was blocking, am I do something wrong?

[methane]

It is blocking because creating new connection is heavy job and there are no free CPU cores. Any mystery?

[zhiyongoceanwing]

Also I use mysqlslap to benchmark mysql，and it is faster than my program

# mysqlslap -h localhost -P 123456 --concurrency=100 --iterations=5 --create-schema='mytest' --query='select 1 as `id`;' --number-of-queries=50000 -u root -p
Benchmark
    Average number of seconds to run all queries: 3.879 seconds
    Minimum number of seconds to run all queries: 3.704 seconds
    Maximum number of seconds to run all queries: 4.232 seconds
    Number of clients running queries: 100
    Average number of queries per client: 500

# go run sqlasyn.go -n 50000 -c 100
sqlasyn finished - 6.49145487s

Can I change my code to improve this speed? Thanks for your patience!

[methane]

Doesn't mysqlslap creates all connections before benchmark?

[zhiyongoceanwing]

Doesn't mysqlslap creates all connections before benchmark?

I can not figure it out :(

[methane]

faster version: https://gist.github.com/methane/68f6beda7e6fa650e1df7a3f19a33d63

$ mysqlslap --host=127.0.0.1 -v --create-schema=test  --concurrency=100 --iterations=5 --query='select 1 as `id`;' --number-of-queries=50000 -u root
Benchmark
    Average number of seconds to run all queries: 3.812 seconds
    Minimum number of seconds to run all queries: 3.654 seconds
    Maximum number of seconds to run all queries: 3.946 seconds
    Number of clients running queries: 100
    Average number of queries per client: 500

$ ./sqlasyn -n 50000 -c 100
sqlasyn starting...
sqlasyn finished - 3.18916391s...

[methane]

I'm not free consultant or teacher.
Your question is clearly not an issue of this project.

Read this article: https://medium.com/@methane/why-you-must-not-ask-questions-on-github-issues-51d741d83fde

[zhiyongoceanwing]

Thanks and sorry for this.