go连接mysql

如何让golang mysql驱动程序在2秒内超时ping

使用

package db_conn_pool

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

const (
	user     = "root"
	pw       = "root"
	ip       = "127.0.0.1"
	port     = 3306
	database = "test"
)

var (
	ctx context.Context
	db  *sql.DB
)

func init() {
	ctx = context.Background()
	db, _ = sql.Open("mysql", user+":"+pw+"@tcp("+ip+":"+fmt.Sprintf("%d", port)+")/"+database+"?charset=utf8&autocommit=true&timeout=1s")
}

func Test_PingContext(t *testing.T) {

	ctx, cancel := context.WithTimeout(ctx, 1*time.Second)
	defer cancel()

	status := "up"
	if err := db.PingContext(ctx); err != nil {
		status = "down"
	}
	log.Println(status)
}

func Test_Ping(t *testing.T)  {
	status := "up"
	if err := db.Ping(); err != nil {
		status = "down"
	}
	log.Println(status)
}

当数据访问不通时(存在防火墙), Test_PingContext 可以在特定1s超时自动返回错误,但是Test_Ping会等待很久没有返回才超时

go test -v db_pool_test.go             
=== RUN   Test_PingContext
2019/12/27 12:10:48 start
2019/12/27 12:10:49 down
--- PASS: Test_PingContext (1.00s)
=== RUN   Test_Ping
2019/12/27 12:10:49 start
[mysql] 2019/12/27 12:11:04 packets.go:36: unexpected EOF
[mysql] 2019/12/27 12:11:19 packets.go:36: unexpected EOF
[mysql] 2019/12/27 12:11:34 packets.go:36: unexpected EOF
2019/12/27 12:11:34 down
--- PASS: Test_Ping (45.45s)
PASS
ok      command-line-arguments  46.471s

配置 sql.DB 获得更好的性能

package db_conn_pool

import (
	"context"
	"database/sql"
	"fmt"
	"testing"
	"time"
)

const (
	user     = "root"
	pw       = "root"
	ip       = "127.0.0.1"
	port     = 3306
	database = "test"
)

func insertRecord(b *testing.B, db *sql.DB) {
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	_, err := db.ExecContext(ctx, "INSERT INTO isbns(value) VALUES ('978-3-598-21500-1')")
	if err != nil {
		b.Fatal(err)
	}
}

func BenchmarkMaxOpenConns1(b *testing.B) {
	db, err := sql.Open("mysql", user+":"+pw+"@tcp("+ip+":"+fmt.Sprintf("%d", port)+")/"+database+"?charset=utf8&autocommit=true&timeout=1s")
	if err != nil {
		b.Fatal(err)
	}
	db.SetMaxOpenConns(1)
	defer db.Close()

	b.RunParallel(func(pb *testing.PB) {
		for pb.Next() {
			insertRecord(b, db)
		}
	})
}

func BenchmarkMaxOpenConns2(b *testing.B) {
	db, err := sql.Open("mysql", user+":"+pw+"@tcp("+ip+":"+fmt.Sprintf("%d", port)+")/"+database+"?charset=utf8&autocommit=true&timeout=1s")
	if err != nil {
		b.Fatal(err)
	}
	db.SetMaxOpenConns(2)
	defer db.Close()

	b.RunParallel(func(pb *testing.PB) {
		for pb.Next() {
			insertRecord(b, db)
		}
	})
}

func BenchmarkMaxOpenConns5(b *testing.B) {
	db, err := sql.Open("mysql", user+":"+pw+"@tcp("+ip+":"+fmt.Sprintf("%d", port)+")/"+database+"?charset=utf8&autocommit=true&timeout=1s")
	if err != nil {
		b.Fatal(err)
	}
	db.SetMaxOpenConns(5)
	defer db.Close()

	b.RunParallel(func(pb *testing.PB) {
		for pb.Next() {
			insertRecord(b, db)
		}
	})
}

func BenchmarkMaxOpenConns10(b *testing.B) {
	db, err := sql.Open("mysql", user+":"+pw+"@tcp("+ip+":"+fmt.Sprintf("%d", port)+")/"+database+"?charset=utf8&autocommit=true&timeout=1s")
	if err != nil {
		b.Fatal(err)
	}
	db.SetMaxOpenConns(10)
	defer db.Close()

	b.RunParallel(func(pb *testing.PB) {
		for pb.Next() {
			insertRecord(b, db)
		}
	})
}

func BenchmarkMaxOpenConnsUnlimited(b *testing.B) {
	db, err := sql.Open("mysql", user+":"+pw+"@tcp("+ip+":"+fmt.Sprintf("%d", port)+")/"+database+"?charset=utf8&autocommit=true&timeout=1s")
	if err != nil {
		b.Fatal(err)
	}
	defer db.Close()

	b.RunParallel(func(pb *testing.PB) {
		for pb.Next() {
			insertRecord(b, db)
		}
	})
}

func BenchmarkMaxIdleConnsNone(b *testing.B) {
	db, err := sql.Open("mysql", user+":"+pw+"@tcp("+ip+":"+fmt.Sprintf("%d", port)+")/"+database+"?charset=utf8&autocommit=true&timeout=1s")
	if err != nil {
		b.Fatal(err)
	}
	db.SetMaxIdleConns(0)
	defer db.Close()

	b.RunParallel(func(pb *testing.PB) {
		for pb.Next() {
			insertRecord(b, db)
		}
	})
}

func BenchmarkMaxIdleConns1(b *testing.B) {
	db, err := sql.Open("mysql", user+":"+pw+"@tcp("+ip+":"+fmt.Sprintf("%d", port)+")/"+database+"?charset=utf8&autocommit=true&timeout=1s")
	if err != nil {
		b.Fatal(err)
	}
	db.SetMaxIdleConns(1)
	defer db.Close()

	b.RunParallel(func(pb *testing.PB) {
		for pb.Next() {
			insertRecord(b, db)
		}
	})
}

func BenchmarkMaxIdleConns2(b *testing.B) {
	db, err := sql.Open("mysql", user+":"+pw+"@tcp("+ip+":"+fmt.Sprintf("%d", port)+")/"+database+"?charset=utf8&autocommit=true&timeout=1s")
	if err != nil {
		b.Fatal(err)
	}
	db.SetMaxIdleConns(2)
	defer db.Close()

	b.RunParallel(func(pb *testing.PB) {
		for pb.Next() {
			insertRecord(b, db)
		}
	})
}

func BenchmarkMaxIdleConns5(b *testing.B) {
	db, err := sql.Open("mysql", user+":"+pw+"@tcp("+ip+":"+fmt.Sprintf("%d", port)+")/"+database+"?charset=utf8&autocommit=true&timeout=1s")
	if err != nil {
		b.Fatal(err)
	}
	db.SetMaxIdleConns(5)
	defer db.Close()

	b.RunParallel(func(pb *testing.PB) {
		for pb.Next() {
			insertRecord(b, db)
		}
	})
}

func BenchmarkMaxIdleConns10(b *testing.B) {
	db, err := sql.Open("mysql", user+":"+pw+"@tcp("+ip+":"+fmt.Sprintf("%d", port)+")/"+database+"?charset=utf8&autocommit=true&timeout=1s")
	if err != nil {
		b.Fatal(err)
	}
	db.SetMaxIdleConns(10)
	defer db.Close()

	b.RunParallel(func(pb *testing.PB) {
		for pb.Next() {
			insertRecord(b, db)
		}
	})
}

func BenchmarkConnMaxLifetimeUnlimited(b *testing.B) {
	db, err := sql.Open("mysql", user+":"+pw+"@tcp("+ip+":"+fmt.Sprintf("%d", port)+")/"+database+"?charset=utf8&autocommit=true&timeout=1s")
	if err != nil {
		b.Fatal(err)
	}
	db.SetConnMaxLifetime(0)
	defer db.Close()

	b.RunParallel(func(pb *testing.PB) {
		for pb.Next() {
			insertRecord(b, db)
		}
	})
}

func BenchmarkConnMaxLifetime1000(b *testing.B) {
	db, err := sql.Open("mysql", user+":"+pw+"@tcp("+ip+":"+fmt.Sprintf("%d", port)+")/"+database+"?charset=utf8&autocommit=true&timeout=1s")
	if err != nil {
		b.Fatal(err)
	}
	db.SetConnMaxLifetime(1000 * time.Millisecond)
	defer db.Close()

	b.RunParallel(func(pb *testing.PB) {
		for pb.Next() {
			insertRecord(b, db)
		}
	})
}

func BenchmarkConnMaxLifetime500(b *testing.B) {
	db, err := sql.Open("mysql", user+":"+pw+"@tcp("+ip+":"+fmt.Sprintf("%d", port)+")/"+database+"?charset=utf8&autocommit=true&timeout=1s")
	if err != nil {
		b.Fatal(err)
	}
	db.SetConnMaxLifetime(500 * time.Millisecond)
	defer db.Close()

	b.RunParallel(func(pb *testing.PB) {
		for pb.Next() {
			insertRecord(b, db)
		}
	})
}

func BenchmarkConnMaxLifetime200(b *testing.B) {
	db, err := sql.Open("mysql", user+":"+pw+"@tcp("+ip+":"+fmt.Sprintf("%d", port)+")/"+database+"?charset=utf8&autocommit=true&timeout=1s")
	if err != nil {
		b.Fatal(err)
	}
	db.SetConnMaxLifetime(200 * time.Millisecond)
	defer db.Close()

	b.RunParallel(func(pb *testing.PB) {
		for pb.Next() {
			insertRecord(b, db)
		}
	})
}

func BenchmarkConnMaxLifetime100(b *testing.B) {
	db, err := sql.Open("mysql", user+":"+pw+"@tcp("+ip+":"+fmt.Sprintf("%d", port)+")/"+database+"?charset=utf8&autocommit=true&timeout=1s")
	if err != nil {
		b.Fatal(err)
	}
	db.SetConnMaxLifetime(100 * time.Millisecond)
	defer db.Close()

	b.RunParallel(func(pb *testing.PB) {
		for pb.Next() {
			insertRecord(b, db)
		}
	})
}

SetMaxOpenConns 方法

默认情况下，可以同时打开的连接数没有限制。但您可以通过setMaxOpenConns（）方法实现自己的限制

go test -test.bench=”BenchmarkMaxOpenConns*” -benchmem sql_test.go

goos: darwin
goarch: amd64
BenchmarkMaxOpenConns1-4                    1000           1234253 ns/op             574 B/op         14 allocs/op
BenchmarkMaxOpenConns2-4                    2000            635522 ns/op             564 B/op         14 allocs/op
BenchmarkMaxOpenConns5-4                    5000            263964 ns/op             436 B/op         12 allocs/op
BenchmarkMaxOpenConns10-4                   5000            278689 ns/op             444 B/op         12 allocs/op
BenchmarkMaxOpenConnsUnlimited-4            5000            270506 ns/op             441 B/op         12 allocs/op
PASS
ok      command-line-arguments  6.948s

如上基准测试,在设置最大开放连接为1、2、5、10和无限制,插入花费的时间越来越少

SetMaxIdleConns 方法

默认情况下，sql.DB允许在连接池中最多保留2个空闲连接。您可以通过SetMaxIdleConns（）方法进行更改,理论上，在池中允许更多的空闲连接将提高性能，因为这样可以减少从头开始建立新连接的可能性，从而有助于节省资源。

go test -test.bench=”BenchmarkMaxIdleConns*” -benchmem sql_test.go

goos: darwin
goarch: amd64
BenchmarkMaxIdleConnsNone-4         1000           1643194 ns/op            7109 B/op         49 allocs/op
BenchmarkMaxIdleConns1-4            5000            273001 ns/op             505 B/op         12 allocs/op
BenchmarkMaxIdleConns2-4            5000            278861 ns/op             446 B/op         12 allocs/op
BenchmarkMaxIdleConns5-4            5000            293819 ns/op             430 B/op         12 allocs/op
BenchmarkMaxIdleConns10-4           5000            290196 ns/op             430 B/op         12 allocs/op
PASS
ok      command-line-arguments  8.670s

让我们来看看相同的基准，最大空闲连接设置为无、1、2、5和10（并且开放连接的数量是无限的）

go test -test.bench=”BenchmarkConnMaxLifetime*” -benchmem sql_test.go

goos: darwin
goarch: amd64
BenchmarkConnMaxLifetimeUnlimited-4         5000            266796 ns/op             442 B/op         12 allocs/op
BenchmarkConnMaxLifetime1000-4              5000            272057 ns/op             448 B/op         12 allocs/op
BenchmarkConnMaxLifetime500-4               5000            281033 ns/op             460 B/op         12 allocs/op
BenchmarkConnMaxLifetime200-4               5000            271473 ns/op             464 B/op         12 allocs/op
BenchmarkConnMaxLifetime100-4               5000            284376 ns/op             507 B/op         12 allocs/op
PASS
ok      command-line-arguments  8.005s

问题

连接池什么时候销毁IdleConns

比如说maxConns是10,maxIdleConns是5,一次10个并发请求出现,连接池一次性建立了10个请求,当查询

连接池的连接管理

参考文档

配置 sql.DB 获得更好的性能