Abstract

Continue from JDBC to Mybatis, introduce the database connection pool technology

Why do YOU need connection pooling

In the previous article, we explained that it takes too long for a client to establish a connection (set up a connection, set up a character set, autocommit, etc.). If you need to establish a connection in every SQL operation, close the connection. Not only is the application slow to respond, but many temporary objects are generated, which puts a lot of pressure on the application server GC. In addition, the database server side also has limitations on connections. For example, MySQL has 151 connections by default (this value will be increased in the actual environment, especially when multiple services are used).

The problem now is how to improve the efficient management of scarce resources. Because the essence of the connection between the client and the database is TCP request, plus mysql request based on TCP protocol encapsulation. So usually to solve this kind of problem, we have two ways, one is pool technology, that is, use a container, create a connection in advance, request directly from the pool, the other is to use IO multiplexing technology. In spring5, mongo, Cassandra and other databases can be accessed by reactive, but not relational databases. The reason is that the access of relational databases is currently based on JDBC, and the process of JDBC operation database. Establish a connection, build a Statement, execute this set of serial, blocking. Multiple operations in a transaction can only be completed in the same connection. So you can’t use IO multiplexing because of JDBC blocking. For other languages, yes, such as NodeJS

So we use pooled call technology to provide database access

Difference between database connection pool and thread pool

Generally, programmers often use thread pool in business development, using CPU multi-core, to process tasks concurrently and improve efficiency. Database connection pool and thread pool belong to the same pooling technology, there is no big difference, both need to manage the pool size, resource control. Different database connection pools hold connections and also need to manage transactions, so this is usually optimized in the database connection pool

Fetching a connection from a connection pool to perform SQL operations requires two more steps to set the Connection autoCOMMIT property operation

Efficiency is provided by splitting connections into two groups

Introduction to open source connection pool technology

A basic database connection pool consists of several major parts

  • Take out the connection

  • Back on the connection

  • Asynchronous/synchronous processing of threads

    Creating and destroying connections for a database connection pool fundamentally depends on the implementation of the concurrent container, and also determines the efficiency of the connection pool. Common connection pool configurations are as follows

InitialSize: indicates the initial number of connections. MaxActive: indicates the maximum number of connections. MinIdle: indicates the minimum number of connections. To obtain maximum wait time ms minEvictableIdleTimeMillis connection: connect to keep free instead of expulsion of minimum time timeBetweenEvictionRunsMillis: destroying threads time detectiontestOnBorrow: Execute when applying for connection, compare performance impact validationQuery:testOnBorrow fortrueCheck for valid join SQLtestWhileIdle: Detection while requesting a connectionCopy the code

The current open source database connection pool mainly includes the following,

C3P0 and DBCP are the first database connections to appear, mainly for Hibernate and Tomcat6.0, relatively stable, low concurrency works fine, but high concurrency performance is poor, so in Tomcat6, we rewrote a JDBC-pool to replace DBCP.

Druid is Alibaba open source high-performance database connection pool, is currently the basic major Internet companies with the standard, plus is domestic, the document is relatively easy to read, so the popularity is relatively high, another is hikariCP, performance is relatively high, the current popularity is not particularly high.

So why is C3P0 and DBCP performing less well? As mentioned earlier, a database connection pool is essentially an implementation of a concurrent container. This is usually done using a List+ lock mechanism. Or use a JDK native structure such as CopyOnWriteList, where there are two types of lock: JVM-level synchronized and JDK provided ReentrantLock, which are semantically identical, mutually exclusive, memory visible, and ReentrantLock. ReentrantLock in JDK5 performed much better than Synchronzied, while in JDK6 there was no significant performance difference after optimization. So ReentrantLock has more advantages

  • The waiting thread that cannot get the lock can be interrupted to avoid deadlock

  • You can combine Condition for more flexible thread control

Look at the com. McHange. V2. C3p0. DriverManagerDataSource implementation

// should NOT be sync'ed -- driver() is sync'ed and that's enough // sync'ing the method creates the danger that one freeze on connect // blocks access to the entire DataSource public Connection  getConnection() throws SQLException { ensureDriverLoaded(); Connection out = driver().connect(jdbcUrl, properties);if (out == null)
            throw new SQLException("Apparently, jdbc URL '" + jdbcUrl + "' is not valid for the underlying " +
                            "driver [" + driver() + "].");
        return out;
    }
Copy the code

The java.sql.Driver is first fetched in a synchonized file when the connection is obtained.

 private synchronized Driver driver() throws SQLException
    {
 	//To simulate an unreliable DataSource...
   	//double d = Math.random() * 10;
   	//if ( d > 1 )
   	//    throw new SQLException(this.getClass().getName() + " TEST of unreliable Connection. If you're not testing, you shouldn't be seeing this!");

        //System.err.println( "driver() <-- " + this );
        if (driver == null)
	{
	    if(driverClass ! = null && forceUseNamedDriverClass) {if ( Debug.DEBUG && logger.isLoggable( MLevel.FINER ) )
		    logger.finer( "Circumventing DriverManager and instantiating driver class '" + driverClass + 
				  "' directly. (forceUseNamedDriverClass = " + forceUseNamedDriverClass + ")" );

		try 
		{ 
		    driver = (Driver) Class.forName( driverClass ).newInstance();
		    this.setDriverClassLoaded( true );
		}
		catch (Exception e)
		    { SqlUtils.toSQLException("Cannot instantiate specified JDBC driver. Exception while initializing named, forced-to-use driver class'" + driverClass +"'", e); }}else
		driver = DriverManager.getDriver( jdbcUrl );
        }
        return driver;
    }
Copy the code

The specific connection pool management is BasicResourcePool, which is filled with synchronized methods. How can concurrency performance be good.

Let’s take a look at the implementation of Druid, Druid datasource

 private DruidPooledConnection getConnectionInternal(long maxWait) throws SQLException {
        DruidConnectionHolder holder;

        for (boolean createDirect = false;;) {// Connection fetch with timeoutif (maxWait > 0) {
                    holder = pollLast(nanos);
                } else{ holder = takeLast(); }}Copy the code

Concurrent environment down take connection, and did not read lock on the operation, higher than the performance of the mutex mutex is a conservative strategy, like synchronized, it avoids the write about conflict, read write conflict, the conflict and read, for database connection pool, application to take, is a read operation more, allowing multiple read operations at the same time, Improves system concurrency.

private DruidConnectionHolder pollLast(long nanos) throws InterruptedException, SQLException { long estimate = nanos; // Queue blocks. When a connection is fetched, there is no connection, and the thread idles, waiting for another thread to create the connectionfor (;;) {
            if(poolingCount == 0) {// Tell the thread to create the connection emptySignal(); } } decrementPoolingCount(); DruidConnectionHolder last = connections[poolingCount]; connections[poolingCount] = null; longwaitNanos = nanos - estimate;
        last.setLastNotEmptyWaitNanos(waitNanos);

        return last; 
    }

Copy the code

Added write locks to create and destroy connection threads

Private Boolean put(DruidConnectionHolder holder) {// Lock lock.lock(); try {if (poolingCount >= maxActive) {
                return false;
            }
            connections[poolingCount] = holder;
            incrementPoolingCount();

            if(poolingCount > poolingPeak) { poolingPeak = poolingCount; poolingPeakTime = System.currentTimeMillis(); } // The waiting thread starts processing notempty.signal (); notEmptySignalCount++;if(createScheduler ! = null) { createTaskCount--;if (poolingCount + createTaskCount < notEmptyWaitThreadCount //
                    && activeCount + poolingCount + createTaskCount < maxActive) {
                    emptySignal();
                }
            }
        } finally {
            lock.unlock();
        }
        return true;
    }
Copy the code

HikariCP is further optimized on the basis of read/write locks github.com/brettwooldr…

Pay attention to [abbot’s temple] and begin the path of technical practice with the abbot

reference

My.oschina.net/javahongxi/…