1. This is the 40th article on our official account

2. Even if you don’t know Go, you’ll have no trouble reading this article

3. Cover image from Mael BALLAND on Unsplash

  • What is pooling technology

  • The database/SQL packages

    • The design philosophy

    • The minimalist interface

    • Call relationship

  • Connection pool design

    • Key properties of the SQL. DB object

    • Establish a connection

    • Release the connection

    • Clean up the connection

  • conclusion

What is pooling technology

Pooling is a common programming technique. It can significantly optimize application performance and reduce the resource cost of frequent system connections when there are a large number of requests. Common in our daily work are database connection pool, thread pool, object pool, etc., which are characterized by maintaining “expensive” and “time-consuming” resources in a specific “pool”, specifying the minimum number of connections, maximum number of connections, blocking queue and other configurations, convenient for unified management and reuse. There are usually some supporting functions such as detection mechanism, forced collection, monitoring, etc.

The database/SQL packages

The design philosophy

The database operation in Go language needs to be carried out by the database/ SQL package under the standard library. It provides the standard API operation interface for the upper application, exposes the simple driver interface for the lower driver, and realizes the connection pool management internally. This means that drivers from different databases can easily implement these driver interfaces without worrying about the details of connection pooling, just based on a single connection.

The minimalist interface

Its exposed interface is easy to understand, which is convenient for third-party drivers to implement. The interface functions include Driver registration, Conn, Stmt, Tx, Rows result set, etc. Conn and Stmt are used to illustrate the sophistication of interface design. These two interfaces correspond to Java as Connection and Statement interfaces, but Go is simpler.

I’m sure you can easily understand the above interfaces with your Knowledge of Java, even if you haven’t studied Go. These interfaces are very simple to expose to the driver layer and make it easy for the driver to implement.

Call relationship

The database/ SQL driver interface has a very clear call relationship. In simple terms, the driver uses the Open method to get a new Conn, then calls the Prepare method of Conn, passes the SQL statement to get the Stmt of the statement. Finally, the Exec method of Stmt is called to pass in the parameter and return the result. The query statement does the same, but returns the row data result set.

Connection pool design

Key properties of the SQL. DB object

Open method to return a concrete SQl. DB object, DB in the following code slice:

The SQL. DB object is our entry point to the database, and let’s look at the key properties in it, which are relevant to the connection pool design

Establish a connection

In fact, the connection is not established when SQL.open returns the DB object. This step only opens a channel to receive the connection request. The actual connection step is not established until the execution of the specific SQL statement. Here are some examples of how connections are established and how the connection pooling logic is implemented.

Tell about this part of the principle will not post too much source code, it will become the source code analysis, do not understand the Go language students are not friendly, mainly hope to convey some connection pool design ideas.

Among the operation interfaces exposed by Database/SQL to upper-layer applications, Exec and Query are commonly used. The former is often used to execute WRITE SQL, while the latter can be used to read SQL. However, no matter which method is used, the connection logic db.conn method is called with the connection context and the connection policy parameters.

Connection policies are classified into cachedOrNewConn and alwaysNewConn. The former takes the connection from the freeConn free connection first, otherwise creates a new one; The latter always follows the logic of creating a new connection.

In the cachedOrNewConn policy, the system checks whether there is an idle connection. If the first idle connection is removed, the system checks whether the connection has expired and needs to be reclaimed. If the connection has not expired, the system can use it and proceed to the subsequent logic. If there are no free connections, it determines whether the number of connections has reached the maximum. If there are no new connections, otherwise it blocks the request while it waits for available connections.

If a new connection is required, the Connect interface of the connector implemented by the underlying Driver is called, which is implemented by the individual database drivers themselves.

Release the connection

A connection needs to be returned to the connection pool after it has been used. This is also an important logic in the database connection pool implementation. This is usually accompanied by a connection reliability check.

TestOnReturn or testOnBorrow in Java Druid connection pools have testOnReturn or testOnBorrow options that check the validity of a connection when it is returned or fetched, but enabling these options essentially extends the time a connection is held up and costs performance. The implementation of this function is relatively simple in Go language. There is no specific validity detection mechanism, but directly according to the err information attached to the connection. If it is an ErrBadConn exception, the system closes and sends a signal to create a new one.

Clean up the connection

The database/ SQL package provides three key parameter Settings related to connection pooling: maxIdle, maxOpen, and maxLifeTime.

You can easily understand the three parameters in Configuring SQL.DB for Better Performance

MySQL will kill a connection that has been idle for a long time (8h). Go provides the maxLifeTime option to set the maximum time for a connection to be reused.

The cleanup mechanism is done through an asynchronous task. The key is that the logic checks the free connections in freeConn every second to determine whether the maximum reuse period has been exceeded. The exceeded connections are added to the Closing array and then closed.

conclusion

My recent work is to implement a customized driver that supports read/write separation and high availability based on Go-SQL-Driver. During the investigation and study, I felt the simplicity and clarity of database/ SQL package of GO language, although it is a little simple or even not in the implementation of some functions. But it still covers most of the main functions and features of database connection pooling, so I thought it was a good choice to use it as a starting point.