为什么多个SQL查询按顺序而不是同时运行？

Question

I am running mysql “select” queries in seperate go routines at the same time. However the mysql service seems to collect these queries, run them in sequence (not concurrently) and then returns all the resultsets at the same time and only after all the queries have been run.

My questions are:
1. Why are theses queries run in sequence and not concurrently?
2. Why does mysql wait until all queries have been run before returning the resultset for each query at the same time (even though each single resultset belongs to a different go routine and supposedly also uses a seperate connection)?

Another thing: when I set “SetMaxOpenConns(2)” then it returns two resultsets at the same time. If set to 3, 3 resultsets are returned at the same time. However they are still always run in sequence.

Anybody know what is going on here?

package main

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

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

var database *sql.DB

func init() {
    var err error
    databaseURI := "root:toor@tcp(192.168.200.10:3306)/ahc"
    database, err = sql.Open("mysql", databaseURI)
    if err != nil {
        fmt.Println(err)
    } else {
        fmt.Println("DB Connection Established")
        //database.SetMaxIdleConns(0)
        //database.SetMaxOpenConns(2)
    }
}

func test(device string, wg *sync.WaitGroup) {
    fmt.Println("Thread: " + device + " started")
    start := time.Now()

    var count string

    //using go-sql-driver
    sqlStatement, err := database.Prepare("select count(cpeName) from report where errMessage <> \"ok\" and cpeName = ? and jobID = ?")
    if err != nil {
        fmt.Println(err)
    }
    defer sqlStatement.Close()
    err = sqlStatement.QueryRow(device, "11534").Scan(&count)
    sqlStatement.Close()


    duration := time.Since(start)
    fmt.Println("Thread: " + device + " Duration: " + duration.String() + "
")
    wg.Done()
}

func main() {
    var wg sync.WaitGroup
    var deviceList = []string{"xx-swrk-ca-gen-s-002", "xx-leus-ca-ust-ap-068", "xx-sgvn-ca-lug-ap-004", "xx-swrk-ca-vez-s-005", "xx-swrk-ca-vez-ap-006",    "xx-leus-ca-ust-ap-065", "xx-leus-ca-ust-ap-073", "xx-leus-ca-ust-ap-076", "xx-leus-ca-ust-ap-077", "xx-swrk-ca-gen-s-001"}
    total := time.Now()
    for _, device := range deviceList {
        wg.Add(1)
        go test(device, &wg)
    }
    wg.Wait()
    duration := time.Since(total)
    fmt.Println("

Total: Duration: " + duration.String() + "
")
}

THis is the output

DB Connection Established
Thread: xx-leus-ca-ust-ap-068 started
Thread: xx-sgvn-ca-lug-ap-004 started
Thread: xx-leus-ca-ust-ap-065 started
Thread: xx-leus-ca-ust-ap-073 started
Thread: xx-swrk-ca-vez-ap-006 started
Thread: xx-swrk-ca-vez-s-005 started
Thread: xx-leus-ca-ust-ap-076 started
Thread: xx-leus-ca-ust-ap-077 started
Thread: xx-swrk-ca-gen-s-002 started
Thread: xx-swrk-ca-gen-s-001 started
Thread: xx-leus-ca-ust-ap-076 Duration: 7.291656143s

Thread: xx-swrk-ca-gen-s-002 Duration: 7.304134404s

Thread: xx-leus-ca-ust-ap-065 Duration: 7.307958641s

Thread: xx-swrk-ca-vez-s-005 Duration: 7.313591747s

Thread: xx-leus-ca-ust-ap-077 Duration: 7.313992638s

Thread: xx-swrk-ca-vez-ap-006 Duration: 7.314905664s

Thread: xx-swrk-ca-gen-s-001 Duration: 7.320466323s

Thread: xx-leus-ca-ust-ap-073 Duration: 7.322158337s

Thread: xx-leus-ca-ust-ap-068 Duration: 7.324745097s

Thread: xx-sgvn-ca-lug-ap-004 Duration: 7.326001783s



Total: Duration: 7.326096238s

When using database.SetMaxOpenConns(1), this is the output:

DB Connection Established
Thread: xx-leus-ca-ust-ap-068 started
Thread: xx-swrk-ca-gen-s-001 started
Thread: xx-swrk-ca-vez-ap-006 started
Thread: xx-leus-ca-ust-ap-065 started
Thread: xx-leus-ca-ust-ap-073 started
Thread: xx-swrk-ca-gen-s-002 started
Thread: xx-leus-ca-ust-ap-077 started
Thread: xx-sgvn-ca-lug-ap-004 started
Thread: xx-leus-ca-ust-ap-076 started
Thread: xx-swrk-ca-vez-s-005 started
Thread: xx-leus-ca-ust-ap-068 Duration: 1.131790286s

Thread: xx-leus-ca-ust-ap-077 Duration: 2.128919333s

Thread: xx-swrk-ca-gen-s-001 Duration: 3.073559464s

Thread: xx-leus-ca-ust-ap-073 Duration: 4.002964333s

Thread: xx-swrk-ca-vez-s-005 Duration: 4.932256684s

Thread: xx-sgvn-ca-lug-ap-004 Duration: 5.853361245s

Thread: xx-swrk-ca-gen-s-002 Duration: 6.785042625s

Thread: xx-leus-ca-ust-ap-065 Duration: 7.705957815s

Thread: xx-swrk-ca-vez-ap-006 Duration: 8.633000734s

Thread: xx-leus-ca-ust-ap-076 Duration: 9.550948572s



Total: Duration: 9.551103129s

Answer 1

A simple technique to see whether they are run in parallel or serially: Have each connection do

SELECT SLEEP(1);

If parallel, the set won't take much more than 1 second. If serially, then N seconds.

If they are run sequentially, but no output until all are finished, that would be a GO problem.

If your 7.3s is really parallel and 9.5 is serial, then that points out why it is not worth it to run things in parallel -- They will step on each other and not finish much faster. The conflict could be CPU or I/O or mutexes or network or something else; it depends on the query. (My sleep test is very non-invasive, yet takes a predictable amount of time.)