Take you to understand golang mysql database connection pool

The following is an introduction to the golang mysql database connection pool from the golang tutorial column. I hope it will be helpful to friends in need!

Recently, I was using the go language ORM to do some database operations. Finally, I found a bug called invalid connection, so I went to learn about the timeout of the connection pool and mysql. Next, I will use the go ORM. mysql to illustrate (I understand that the languages ​​​​are all connected, and the principles should be the same).

When we want to add, delete, modify and check the database, the first step is to connect to the database

//conn the database
func ConnDb(dbConnString string, dbName string) error {
	maxIdle := 50
	maxConn := 50
	err := orm.RegisterDataBase(dbName, "mysql", dbConnString+"?charset=utf8&loc=Asia%2FShanghai", maxIdle, maxConn)

	if err != nil {
		util.GLogger.Errorw("in Connect DB", "err", err)
	}
	return err
}

The connection here is the mysql database. The maximum connection pool set is 50. The maximum The number of idle connections is 50.

What does this connection pool mainly do? To put it simply, if you want to get data from the database and change the data, you need to establish a pipeline with the database. This is to establish a network connection. We all know that TCP connections are time-consuming, so since it has taken a certain amount of time I've built this pipeline, so how can I take it and use it without throwing it away? Then the connection pool is where these established pipes are stored. The value of 50 can be simply understood as a maximum of 50 pipes. Note that the bigger the better, because if it is too large, it will occupy more memory. Of course, it will take up too much memory. If it is too small, there will be waiting for blocking.

Since these pipes are placed in the connection pool, then the idle connection refers to these idle pipes, then it is obvious that the value setting of the idle connection should not be larger than the size of the connection pool, because the larger the connection The pool will not help you save so many idle connection pipes.

After understanding these simple concepts, what is the workflow like every time you access the database?

#Through this diagram we can clearly see the entire connection access process.

step1 (Get available connections) Go to the connection pool to find available idle connections. If there are no idle connections, then determine whether the connection pool is full. If not, then create a new connection. , if it is full, then wait for the connection to be released; of course, if there is an idle connection, it will directly determine whether the connection has expired. If it has not expired, it will be used directly. If it expires, it will re-judge whether the connection pool is full. No If so, create a new connection and wait when it is full.

step2 (Operation database) After getting this connection, perform addition, deletion, modification and query operations.

step3 (Release connection) After operating the database, you need to release the connection, then the released connection will become idle. If it exceeds the number of idle connections, it will be closed directly. If not, it will be used for waiting.

Then you will notice that this connection will fail:

The timeout of mysql database, when you establish a connection with the database, the database cannot always trust you, then the database There is a timeout, that is, after this period of time, I will not trust your connection. You must connect to me again. Check the various timeout statements set by the database as follows:

show variables like  '%timeout%';

Among them There will be two timeouts: interactive connection timeout (interactive_timeout) and non-interactive connection timeout (wait_timeout)

Interactive connection: you connect to mysql through the command line

Non-interactive connection: It is to connect to mysql in the program

And this non-interactive timeout is when the idle time of the idle connection in the connection pool exceeds the wait_timeout setting. Then at this time, when the program gets the idle connection and makes a query, the original problem will appear, invalid connection.

After understanding the basic principles, the solution to invalid connection is very simple:

1. Extend the wait_timeout time of the database.

2. The program regularly checks these failed connections and discards them in time. Note that the checking time of the program here needs to be less than the wait_timeout value set by mysql.

The above is my understanding of the connection pool. If there are any mistakes, please point them out. Thank you~

The above is the detailed content of Take you to understand golang mysql database connection pool. For more information, please follow other related articles on the PHP Chinese website!