- Establish a connection pool through sql
Connection poolUse functions to create a connection pool, but at this time, the connection pool is only initialized and no connection is created. Connection creation is lazy, and the connection pool will only create connections when you are actually using the connection. Connection pooling is important, it directly affects your program behavior.
The connection pooling work turned out to be quite simple. When your function (such as Exec, Query) calls need to access the underlying database, the function first requests a connection from the connection pool. If there is an idle connection on the connection pool, it is returned to the function. Otherwise the connection pool will create a new connection to the function. Once the connection is given to the function, the connection belongs to the function. After the function is executed, either return the connection ownership to the connection pool, or pass it to the next Rows object that needs to be connected. Finally, the object that has been used will release the connection back to the connection pool.
There are several functions that request a connection, and there are slight differences in the way of handling connections after execution, roughly as follows:
- () After the call is completed, the connection will be returned to the connection pool immediately.
- () After the call is completed, the connection will be returned to the connection pool immediately, but the Result object it returns also retains the reference to this connection. When the subsequent code needs to process the result set, the connection will be reused.
- After the call is completed, the connection will be passed to the type. When the subsequent iterates or the displayed call to the .Clonse() method, the connection will be released back to the connection pool.
- After the () call is completed, the connection will be passed to the type. After the .Scan() method is called, the connection will be released back to the connection pool.
- () After the call is completed, the connection is passed to the type object, and the connection is released after the .Commit() or .Rollback() method is called.
Because each connection is created lazy, how do you verify that the object is available after the call? Usually initialized using the () method. After calling Ping, the connection pool will definitely initialize a database connection.
Connection failedAnother knowledge point about connection pooling is that you don't have to check or try to deal with connection failures. When you perform database operations, if the connection fails, database/sql will help you handle it. In fact, when the connection taken from the connection pool is disconnected, database/sql will automatically try to reconnect 10 times. If it still cannot be reconnected, one will be automatically retrieved from the connection pool or another will be created.
Connection pool configurationThere are two ways to configure a connection pool:
- (n int) Sets the maximum number of connections to open the database. Contains the connections and connection pools that are being used. If your function call needs to apply for a connection, and the connection pool has no connections or the number of connections reaches the maximum number of connections. The function call at this time will be blocked and will not return until a connection is available. Setting this value can avoid too many connections errors in connecting mysql. The default setting of this function is 0, indicating no limit.
- (n int) Sets the maximum number of connections to hold connections in the connection pool. The default is also 0, which means that the connection pool will not maintain the connection state of the connection in the connection pool that will be released: that is, when the connection is released back to the connection pool, the connection will be closed. This will cause frequent shutdown and creation of connection reconnection pools.
The use of connection pools depends on how you configure the connection pool. If used improperly, it will cause the following problems:
- A large number of connections are idle, resulting in additional work and delays.
- Too many connections to the database lead to errors.
- Connection blocked.
- The connection pool has more than ten or more dead connections, and the limit is 10 reconnections.
There are 3 methods in the database standard interface to set the properties of the connection pool: SetConnMaxLifetime, SetMaxIdleConns, SetMaxOpenConns
- SetConnMaxLifetime: Set the maximum life cycle of a connection, because the database itself has a timeout time setting for the connection. If the timeout time reaches the database, the connection will be unilaterally disconnected. At this time, an error will occur when accessing the connection in the connection pool will be used. Therefore, this value is often less than the connection timeout time of the database itself.
- SetMaxIdleConns: The maximum number of connections allowed in the connection pool. These connections to Idels are connections that can be obtained simultaneously during concurrency. They are also interoperable connections placed back into the pool after use, thereby improving performance.
- SetMaxOpenConns: Set the maximum number of open connections, the default value is 0 to indicate no limit. Control the number of connections applied to the database to prevent too many connections from overwhelming the database.
Project structure
. +--- +--- +--- +--- pool | +---
Codepool
package pool
import (
"database/sql"
"fmt"
_ "/go-sql-driver/mysql"
"log"
"time"
)
var DB *
func init() {
DB, _ = ("mysql", "root:root@tcp(localhost:3306)/test?charset=utf8&parseTime=True&loc=Local") // Use local time, that is, East Eighth District, Beijing time // set pool params
(2000)
(1000)
( * 60) // mysql default conn timeout=8h, should < mysql_timeout
err := ()
if err != nil {
("database init failed, err: ", err)
}
("mysql conn pool has initiated.")
}
func checkErr(err error) {
if err != nil {
(err)
panic(err)
}
}
func createTable() {
db := DB
table := `CREATE TABLE IF NOT EXISTS (
user_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'User number',
user_name VARCHAR(45) NOT NULL COMMENT 'User Name',
user_age TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'User age',
user_sex TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'User Gender',
PRIMARY KEY (user_id))
ENGINE = InnoDB
AUTO_INCREMENT = 1
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = 'User Table'`
if _, err := (table); err != nil {
checkErr(err)
}
}
func insert() {
db := DB
stmt, err := (`INSERT user (user, age) values (?, ?)`)
checkErr(err)
res, err := ("Elvis", 26)
checkErr(err)
id, err := ()
checkErr(err)
(id)
}
func query() {
db := DB
rows, err := ("SELECT * FROM user")
checkErr(err)
for () {
var userId int
var userName string
var userAge int
var userSex int
()
err = (&userId, &userName, &userAge, &userSex)
checkErr(err)
(userId)
(userName)
(userAge)
(userSex)
}
}
func queryToMap() {
db := DB
rows, err := ("SELECT * FROM user")
checkErr(err)
//Dictionary type //Construct two arrays of scanArgs and values, and each value of scanArgs points to the address of the corresponding value of values columns, _ := ()
scanArgs := make([]interface{}, len(columns))
values := make([]interface{}, len(columns))
for i := range values {
scanArgs[i] = &values[i]
}
for () {
//Save line data to record dictionary err = (scanArgs...)
record := make(map[string]string)
for i, col := range values {
if col != nil {
record[columns[i]] = string(col.([]byte))
}
}
(record)
}
}
func update() {
db := DB
stmt, err := (`UPDATE user SET user_age=?,user_sex=? WHERE user_id=?`)
checkErr(err)
res, err := (21, 2, 1)
checkErr(err)
num, err := ()
checkErr(err)
(num)
}
func remove() {
db := DB
stmt, err := (`DELETE FROM user WHERE user_id=?`)
checkErr(err)
res, err := (1)
checkErr(err)
num, err := ()
checkErr(err)
(num)
}
main
package main
import (
"fmt"
"log"
"net/http"
. "go-mysql-pool-v1/pool"
)
func main() {
("/pool", pool)
("server is up now...")
(":8080", nil)
}
func pool(w , r *) {
rows, err := (`select * from user limit 1`)
defer ()
checkErr(err)
columns, _ := ()
scanArgs := make([]interface{}, len(columns))
values := make([]interface{}, len(columns))
for j := range values {
scanArgs[j] = &values[j]
}
record := make(map[string]string)
for () {
err = (scanArgs...)
for i, col := range values {
if col != nil {
record[columns[i]] = string(col.([]byte))
}
}
}
(record)
(w, "finish")
}
func checkErr(err error) {
if err != nil {
(err)
panic(err)
}
}
Can be done through toolsabTest the performance of the connection pool, see the bet at the bottom.
-gorm Create a connection pool
In fact, the connection pool setting of Gorm is set up using database/sql to set up connection pooling. It is nothing more than adding a layer of Gorm's own settings.
The following example is the Gorm v2 version, and the v1 version is passed /jinzhu/gorm. For example, the driver import of mysql needs to be added._。
Code structure
. +--- config | +--- | +--- +--- +--- +--- +--- pool | +--- +---
{
"database": {
"name": "test",
"type": "mysql",
"host": "localhost",
"port": "3306",
"user": "root",
"password": "root",
"table_prefix": ""
}
}
package config
import (
"encoding/json"
"os"
)
type Database struct {
Type string `json:"type"`
Host string `json:"host"`
Port string `json:"port"`
User string `json:"user"`
Password string `json:"password"`
Name string `json:"name"`
TablePrefix string `json:"table_prefix"`
}
var DatabaseSetting = &Database{}
type Config struct {
Database *Database `json:"database"`
}
var GlobalConfigSetting = &Config{}
func init() {
// win path is abs-path, linux ->
filePtr, err := ("D:\\demo1\\src\\demo\\demo06\\go-mysql-pool-v2\\config\\")
if err != nil {
return
}
defer ()
// json decode
decoder := (filePtr)
err = (GlobalConfigSetting)
DatabaseSetting =
}
Pay attention to setting table prefixes and singular and plural
package pool
import (
"fmt"
"go-mysql-pool-v2/config"
"/driver/mysql"
"/driver/postgres"
"/driver/sqlite"
"/gorm"
"/gorm/schema"
"log"
"time"
)
var db *
// gorm v2
func init() {
var dbURi string
var dialector
if == "mysql" {
dbURi = ("%s:%s@tcp(%s:%s)/%s?charset=utf8&parseTime=true",
,
,
,
,
)
dialector = ({
DSN: dbURi, // data source name
DefaultStringSize: 256, // default size for string fields
DisableDatetimePrecision: true, // disable datetime precision, which not supported before MySQL 5.6
DontSupportRenameIndex: true, // drop & create when rename index, rename index not supported before MySQL 5.7, MariaDB
DontSupportRenameColumn: true, // `change` when rename column, rename column not supported before MySQL 8, MariaDB
SkipInitializeWithVersion: false, // auto configure based on currently MySQL version
})
} else if == "postgres" {
dbURi = ("host=%s port=%s user=%s dbname=%s sslmode=disable password=%s",
,
,
,
,
)
dialector = ({
DSN: "user=gorm password=gorm dbname=gorm port=9920 sslmode=disable TimeZone=Asia/Shanghai",
PreferSimpleProtocol: true, // disables implicit prepared statement usage
})
} else { // sqlite3
dbURi = ("")
dialector = ("")
}
conn, err := (dialector, &{ // If you do not need to set the table prefix and plural numbers, nil NamingStrategy: {
TablePrefix: , // set table prefix
SingularTable: true, // set table singular
},
})
if err != nil {
(())
}
sqlDB, err := ()
if err != nil {
("connect db server failed.")
}
(100)
(10)
(600*)
db = conn
}
// open api
func GetDB() * {
sqlDB, err := ()
if err != nil {
("connect db server failed.")
}
if err = (); err != nil {
()
}
return db
}
main
package main
import (
"go-mysql-pool-v2/pool"
"/gorm"
"log"
)
type Product struct {
// default add id stats time, id as primary key
Code string
Price uint
}
func main() {
("gorm init...")
SetupModel()
}
func SetupModel() {
db := ()
// auto migrate
(&Product{})
// create record
(&Product{Code: "L1212", Price: 1000})
}
module go-mysql-pool-v2 go 1.16 replace go-mysql-pool-v2 => ../go-mysql-pool-v2 require ( /driver/mysql v1.3.4 /driver/postgres v1.3.4 /driver/sqlite v1.3.4 /gorm v1.23.5 )
3. Connection pooling is compared to a single client
4. General connection pool
Note
ubuntuInstallabtool，apt-get install apache2-utils
// get general requestab -n 1000 -c 1000 http://xxx
// post json requestab -n 100000 -c 400 -p -T application/json http://xxx
content：
{"driverId": 17,"pageNo": 1,"pageSize": 20,"status": 1}
refer to
- Apache Bench installation and use
- Use mysql connection pool in golang
- Golang Mysql Notes (I)--- Connection and Connection Pool
- golang database connection pool
This is the article about the specific use of golang mysql connection pool. For more related golang mysql connection pool content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!