TOMCAT-JDBC source code Analysis and optimization

Database Connection Pool

Connection pooling is a common technique for resource reuse. With connection pooling, it is possible to pool the resources that have been created with greater overhead and to cache them in a pool that needs to be taken out of the connection pool. The process of frequent creation and destruction is omitted in the middle. Database connection pooling is one of the typical applications. Deep Tomcat-jdbc

TOMCAT-JDBC is the highest automatic configuration Priority connection pooling scheme in spring boot, and it appears to replace Apache's earlier connection pool product--DBCP 1.x. In the first place, the principle of connection pooling is much the same, the specific details, such as some early connection pool for concurrency and the use of CPU multi-core considerations are not enough.
Before we introduce TOMCAT-JDBC, we can think about it briefly, assuming that we want to implement a database connection pool, which problems need to be solved. How to guarantee the validity of a cache connection how to maintain the number of connections in a connection pool

Now, we can take a look at the implementation details of TOMCAT-JDBC with the two questions above.Core Action Analysis Connection Pool InitializationCreated with Raphaël 2.1.0 Start Connection pool parameter correction build queue (idle and busy) initialize cleaner task initialization Jdbcinterceptors and invoke Poolstarted method for each interceptor Call the InitialSize secondary Borrowconnection method to create an initialization connection to return all connections back to the connection pool end

the core of database connection pool initialization is to build the idle and busy queues based on the parameters to decide whether to start the cleaning task Jdbcinterceptors initialization based on the parameters initialsize creating the initialization connection

TIPS: The Poolstarted method for each interceptor is called in the "Initialize Jdbcinterceptors" link. However, the Jdbcinterceptor instance here is only temporarily created, not in the subsequent use, so in its own implementation Jdbcinterceptor rewrite poolstarted method, do not inside the operation class member variables, only the operation of static variables is meaningful What is the cleaning job.

The above figure may involve three blocks of clean-up business: A connection that has been removed for a long period of time and has not yet been returned to the connection of the pool idle queue length greater than minidle the validity check of all connections in the idle queue

A few of them may be seen from the above image is not particularly clear again: what is waitcount. When getting a connection from a connection pool, if the number of connections to the connection pool has reached maxactive and is fully occupied, and the maxwait configuration is greater than 0, then the wait state is entered, and the Waitcount is added 1, followed by the core step "get connection". What Connectionversion and Poolversion are. This is the connection pool used to implement the purge function, which means that the current connection in the connection pool can be invalidated. The simplest way to do this is to design a version for pool, such as V1, and the version of the connection created from V1 is also V1. This time you want to fail all connections, only need to lift the poolversion to V2, then the V1 connection will be all invalidated. For the second task, there is an optimized point, you can first determine whether Minevictableidletimemillis is greater than 0, if not greater than 0, then do not need to traverse the idle queue for the third task, how to determine the validity of the connection. This is explained in the next section, "Validation", where the corresponding type is Testwhileidle

which parameters control the start of the cleaning task. Timebetweenevictionrunsmillis, cleaner task frequency removeabandoned/removeabandonedtimeout, whether to release the long time to remove the connection suspecttimeout, Suspected time-out testwhileidle/validatequery, timed test idle connection Minevictableidletimemillis, the connection pool idle for a certain time will be treated as an idle connection

1 is necessary, 2|3|4|5 as long as there is a satisfaction, then will start cleaning Task validation

Remember that one of the cleaners was dedicated to checking the validity of an idle connection, and here's how to determine the validity of a connection:

where all types of validation are maintained here, including when Testonconnect is removed from the connection pool when the connection is created Testonborrow Testonreturn idle connection when it is put back into the connection pool Testwhileidle

The above four types, in the "need to verify" this step will be reflected, respectively, corresponding to the above configuration items.
In addition, for non-testonconnect this type of validation, Validationinterval can be used to avoid frequent connection validation

The parameters related to validation are given below: Testonborrow: Whether the connection validity is verified after the connection is obtained, and the performance Testonconnect: Whether the connection validity is verified after the connection is created, affecting the performance Testonreturn: Whether to verify link validity before returning the connection to the connection pool, affecting performance Testwhileidle: Cleaning timer Task Check validity of idle connection validationquery: Check the connection validity of the query statement, MySQL is basically select 1 Validationquerytimeout: The time-out of a query statement that verifies the validity of the connection Validationinterval: Prevents frequent checks, such as when the last and this check time does not exceed validationinterval, does not perform the checksum, Direct return validation through Logvalidationerrors: Validation failure logging log get connection

About "Get connection", there are two points to note: Perhaps the original maxwait a little misunderstanding, thought to be "get Connected" the maximum waiting time, this understanding is not right, from the flowchart can be seen, this maxwait only when the connection pool full time only useful, and refers to the maximum timeout for waiting for the idle queue to have a new idle connection. For the procedure of connection validity check, there is a special: Get the connection directly from the connection pool, do the Testonborrow check, if the first check fails, will also give a reconnect chance to re-connect the database, and then continue to verify (this check does not pass the error). Other validity checks return the connection if the error is not passed

The core of the return connection is to put the removed connection back into the connection pool, but before putting it back in the pool, a series of checks are made: for example, if the maxage is exceeded, validation is passed, etc. If the pre-checksum is not true, then the connection is released directly and not returned to the pool. connection pool Maintenance related parameters

The following parameters are basically mentioned in the above process, can be combined to review: Maxactive: Depending on the system load, the default value of Maxidle: Depending on the load of the system, only if the cleaning task is not opened in the case will be used, used in Returntopool operation, If the current idle queue size is determined to be greater than or equal to Maxidle, the connection is freed. Default = Maxactive minidle: Number of idle connections in the pool, if the idle queue size exceeds this value, and connections that are idle more than minevictableidletimemillis in the pool will be released (as long as the cleaning task is enabled) InitialSize: Number of connections created at initialization maxwait: When getting a connection, after maxactive, wait for the idle queue to re-establish a new connection, note that this wait time does not consider the time-consuming maxAge of establishing a connection: when you get a connection or when you return a connection, Will judge the current time-the connection creation time is not more than maxAge, if exceeded, then the connection will be freed removeabandoned: whether to remove the long time out and not return the connection, for the application layer does not close the connection is not a case to do a backstop Removeabandonedtimeout: Time threshold Abandonwhenpercentagefull: Removal also has a pre-proportional judgment logabandoned: whether to record connections removed through abandoned Suspecttimeout: There is no substantive function, most of the logging, and will only be in the case of non-abandoned or shut down the removeabandoned, it is possible to function the interceptor

That is, Jdbcinterceptor, the interceptor that inherits it can intercept the invocation of all connection methods.
Here are a few of the interceptors that people find very useful: Querytimeoutinterceptor

Configurable SQL time-out x, SQL over that time will be killed, client error. The simple principle is that the execution of SQL will delay X milliseconds to start a scheduled task, the scheduled task is to send the kill query command to the database, if within X milliseconds, then the scheduled task will be cancel before execution, if the X-millisecond is not finished, then the scheduled task starts, Kill Query, causing a client connection error. This can play a certain role in the protection and monitoring of the system Slowqueryreport

Can configure the threshold of slow check, mainly is the record slow check, but this thing a bit imperfect place, is to play slow check log only will play preparestatement, can't play the parameters inside, this will cause can not take SQL to find the corresponding query in db, An enhanced slow-scan monitor interceptor code is given in the following path to optimization ConnectionState

To cache autocommit, ReadOnly, Transactionisolation, and Catalog properties, cache them locally and avoid roundtrip consumption between the various and the database. For example: If the local cache is Getautocommit, the autocommit value of the local cache is read directly setautocommit if the discovery is consistent with the local cache, there is no need to send the request to the database other parameters Usedisposableconnectionfacade: The default is true, adding a layer of interceptor, preventing the connection from being removed from the same thread, and then executing SQL after close, it doesn't feel like much. some experience in troubleshooting database-related issues There is no slow scan, but the request response is slow, and the processing logic for the request has only one database transaction operation, how to troubleshoot.

To figure this out, we first need to understand the execution of a transaction, and we will have several interactions with the database (MySQL).
We take a simple business example, such as an order-to-store transaction, which involves an update of the order form, and an update to the Order Operation record table, which we simplify into two statements: Update order and update Order_operate_record, in the process of executing the transaction, We'll interact with MySQL several times.
1. Remove the connection from the connection pool, where there may be no idle connection in the connection pool, this time assuming that the maximum active connection is not exceeded, the connection pool will initiate the creation of the connection, this will produce an interaction, and the consumption of establishing a connection is larger than the execution of SQL
2. Depending on the configuration of the connection pool parameters, it may also be necessary to verify the check-out connection, and MySQL usually uses Select to act as a check statement.
3. The following need to open the transaction, SET autocommit = 0;
4. Then send two UPDATE statements
5. Finally, we need to tell MySQL our business is over, COMMIT, of course, there may be some problems in the middle, rollback off
6. Remember when the third step to open a transaction, the execution of SET autocommit = 0. So as a complete transaction operation, and finally a step set autocommit = 1
7. Is it over? Finally, you will put the connection back into the connection pool. Doesn't seem to interact with MySQL. Before putting it back, depending on the connection pool parameter configuration, it may also be necessary to validate the connection that was put back. And so on, in addition to the validation, there may be maxage/maxidle and other checks. But this doesn't have to interact with MySQL. All right, here we go, or there's too much content.
Look at the above, you probably know that the execution of a transaction, not only the two lines of the transaction update statements and database interaction bar, so it is not difficult to understand why the slow check can not be caught, but the actual request processing is very slow. But that's the end of it. Since the slow-check can be monitored, why not all of the MySQL has interacted with the point of monitoring it up. Well, it's good to have ideas, so let's see how we can monitor all the nodes.
TOMCAT-JDBC connection pooling does not seem to provide this functionality. Replace the connection pool. Seems a bit far-fetched. Why don't we turn to MySQL, which is closer to MySQL. Read the official MySQL driver documentation and discover that there is a performance monitoring switch--profilesql
With this switch, we can observe each statement that the application interacts with MySQL, including what initialization was done when the connection was established. When to open a transaction, when to verify the validity of the connection, when to commit the transaction, etc., there will be log printing, including time-consuming.
However, through the above configuration items, or can not be monitored to our 1th on the establishment of the connection time-consuming, this block through the query drive source code discovery is also possible to achieve, and then do their own, a little bit of code to complete this small function. At this point, each of the above nodes have traces to follow, visual can easily find the time-consuming location.the road to optimization 1. Whether the interaction with MySQL in the above question can be reduced

Of course it's possible.
The first thing to think about is the connection validity check, such as the 2nd and 7th, mentioned above, that is, after removing the connection (testonborrow/testonconnect) and putting the connection back into the connection pool (Testonreturn), You may need to do a checksum operation that we can omit. So how is validity guaranteed? In theory, most of the cases are valid, unless the database is hung up and so on, so a separate thread will do just fine (testwhileidle)
The 5th commit can also be omitted, see the official website, should be as long as the set autocommit = 1 when the automatic commit, but this has not been verified 2. How to monitor the time-consuming creation of database connections.

This does not seem to be a good way to do this through the connection pool, we can do it through the connectionlifecycleinterceptor provided by MySQL connector:

/**
 * Created by Zhu on 2017/9/19.
 */Public
class Connectionlifeinteceptor implements connectionlifecycleinterceptor{

    private Connectionimpl Connection;

    public static final Logger Logger = Loggerfactory.getlogger (connectionlifeinteceptor.class);

    This is only concerned with creating connection time
    @Override public void init (Connection conn, Properties props) throws SQLException {
        This.connection = (CONNECTIONIMPL) conn;
        Field field = Reflectionutils.findfield (Conn.getclass (), "Connectioncreationtimemillis", long.type);
        reflectionutils.makeaccessible (field);
        Long Connectioncreationtimemillis = (long) reflectionutils.getfield (field, conn);
        Logger.info ("connection:{} cost:{}", Connection.getid (), System.currenttimemillis ()-Connectioncreationtimemillis) ;
    }
}

3. Whether you can set the timeout to create a connection.

The timeout setting is always a very important node in system optimization, so it's natural to think of a timeout to avoid the risk of potentially creating a connection hang. The answer is certainly yes, but the TOMCAT-JDBC connection pool configuration also does not find a similar configuration. Presumably smart you have thought of MySQL Connector. Yes, or MySQL Connector, which provides a parameter connecttimeout to set the time-out for creating a connection. 4. Age- related reconnection problems

Creating a connection through monitoring helps us finally navigate to the occasional slow phenomenon because the removed connection is aging and dies (more than maxage), triggering the reconnect, causing the connection to be re-established with MySQL, and establishing a connection time of about 1s. What to do then. We can scan the idle team joins will be more than maxage (such as 60s) of the connection, such as the discovery is about to expire, then we take the connection reconnect, reactivate, the task we can directly put in PoolCleaner, with some code:

    Protected static Class PoolCleaner extends TimerTask {//Omit part code @Override public void run () {
                Omit part of the code if (Pool.getpoolproperties (). Istestwhileidle ()) {pool.testallidle ();
            Pool.recoverneardeath (); }//Omit part of code}//Omit part of code} public void Recoverneardeath () {try {i
            F (idle.size () ==0) return;
            iterator<pooledconnection> unlocked = Idle.iterator ();
                while (Unlocked.hasnext ()) {Pooledconnection con = unlocked.next ();
                    try {con.lock ();
                    The connection is removed and does not do the processing if (Busy.contains (con)) continue; if (Con.isneardeath ()) {Log.info ("Connection [" +con+ "] is near death, last connected:" + Con.getla
                        Stconnected ());
                    Con.reconnect (); }
                } finally {Con.unlock ();
        }}//while} catch (Exception e) {log.error ("Recoverneardeath failed", e);
        }} public Boolean Isneardeath () {//Here is temporarily not configured to define a connection that is less than 1min away from MaxAge for dying connections final long val = 60000;
    Return (System.currenttimemillis ()-getlastconnected ()) > (Getpoolproperties (). Getmaxage ()-Val); }

5. Rewrite the slow-scan monitor interceptor to print the SQL that was parsed by the parameters

/** * @author Zhu * @date March 22, 2017 pm 11:00:11 * @description */public class Monitorslowqueryreport extends Slowquer
    Yreport {private String systemcode;

    Logger private static final Logger logger = Loggerfactory.getlogger (Monitorslowqueryreport.class);
         Class Recordparamstatementproxy extends Statementproxy {/** * @param parent * @param query
        */Public Recordparamstatementproxy (Object parent, String query) {Super (parent, query); }/* * (NON-JAVADOC) * * @see Org.apache.tomcat.jdbc.pool.interceptor.AbstractQuery
         Report. * Statementproxy#invoke (Java.lang.Object, Java.lang.reflect.Method, * java.lang.object[]) */@Ov Erride public Object Invoke (object proxy, Method method, object[] args) throws Throwable {if (method).
         GetName (). StartsWith ("set") && args! = null && args.length >= 2) {       ParamHolder.params.get (). Add (Args[1]);
            } Object result = null;
            try {result = Super.invoke (proxy, method, args);
                } finally {if (Isexecute (method, False)) {ParamHolder.params.remove ();
        }} return result; }} @Override public void SetProperties (Map<string, interceptorproperty> properties) {super.se
        Tproperties (properties);
        Final String Systemcode = "Systemcode";
        Interceptorproperty P1 = Properties.get (Systemcode);
        if (P1! = null) {Setsystemcode (P1.getvalue ()); }}/* * (NON-JAVADOC) * * @see * org.apache.tomcat.jdbc.pool.interceptor.slowqueryreport#re Portslowquery (* java.lang.String, java.lang.object[], java.lang.String, long, long) */@Override Protec Ted string Reportslowquery (String query, object[] args, string nAme, long start, long delta) {//Extract the query string string sql = (query = = NULL && args! = Null && args.length > 0)?
        (String) args[0]: query; If we do batch execution, then we name the query ' batch ' if (sql = = null && compare (Execute_batch, name
        ) {sql = "batch";  } if (Islogslow () && SQL! = null) {String beautifulsql = sql.replace ("\ n", ""). ReplaceAll ("['
            ']+", " "); Logger.warn ("Slow Query report sql={};    
        param:[{}], consume={}; ", Beautifulsql, Stringutils.join (ParamHolder.params.get (), ', '), Delta);
    } return SQL; 
        Public String getlocalhostaddress () {try {return inetaddress.getlocalhost (). gethostaddress ();
        } catch (Unknownhostexception e) {logger.error ("Get local IP exception", e);
    } return ""; }/** * To print the full SQL, rewrite */@Override PubLic object Createstatement (Object proxy, method, object[] args, object statement, long time) {try {
            Object result = null;
            String name = Method.getname ();
            String sql = null;
            Constructor<?> Constructor = null; if (compare (create_statement, name)) {//Createstatement constructor = GetConstructor (CRE
            Ate_statement_idx, Statement.class); 
                } else if (compare (prepare_statement, name)) {//Preparestatement sql = (String) args[0];
                constructor = GetConstructor (Prepare_statement_idx, Preparedstatement.class);
                if (sql = null) {preparestatement (SQL, time); }} else if (compare (Prepare_call, name)) {//Preparecall sql = (String) args
                [0];
      constructor = GetConstructor (Prepare_call_idx, Callablestatement.class);          Preparecall (SQL, time); } else {//do nothing, might is a future unsupported method//So we better bail out and
            Let the system continue return statement;
            } result = Constructor.newinstance (new object[] {new Recordparamstatementproxy (statement, SQL)});
        return result;
        } catch (Exception x) {Logger.warn ("Unable to create statement proxy for slow query", x);
    } return statement;
         }/** * @return the Systemcode * */Public String Getsystemcode () {