MySQL Others--优化autocommit会话参数设置请求

问题描述

在排查QPS较高的MySQL集群过程中发现，部分MySQL集群约50%的请求为"SET autocommit=1"，每次业务请求前都会执行1次"SET autocommit=1"操作，虽然单次”SET autocommit=1“操作并不会消耗过多MySQL服务器资源，但高QPS场景下频繁执行"SET autocommit=1"操作，严重浪费应用服务器和MySQL服务器端的服务器资源，同时增加应用服务器MySQL请求延迟。

问题原因

在TCDev提供的MySQL客户端中，每次调用com.qunar.db.resource.RWDelegatorDataSource使用getConnection()方法从连接池获取连接都会先使用setAutoCommit方法来对要返回的连接设置AutoCommit属性：

public Connection getConnection() throws SQLException {
    Connection connection = new IsolateConnection(this);

    try {
        connection.setAutoCommit(this.autoCommit);
        return connection;
    } catch (SQLException var3) {
        this.logger().error("get connection failed:", var3);
        Utils.close(connection);
        throw var3;
    }
}

对于Tomcat连接池，会触发调用org.apache.tomcat.jdbc.pool.DisposableConnectionFacade下面的invoke方法来设置：

public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
    if (this.compare("equals", method)) {
        return this.equals(Proxy.getInvocationHandler(args[0]));
    } else if (this.compare("hashCode", method)) {
        return this.hashCode();
    } else {
        if (this.getNext() == null) {
            if (this.compare("isClosed", method)) {
                return Boolean.TRUE;
            }

            if (this.compare("close", method)) {
                return null;
            }

            if (this.compare("isValid", method)) {
                return Boolean.FALSE;
            }
        }

        String var5;
        try {
            try {
                Object var4 = super.invoke(proxy, method, args);
                return var4;
            } catch (NullPointerException var9) {
                if (this.getNext() != null) {
                    throw var9;
                }
            }

            if (!this.compare("toString", method)) {
                throw new SQLException("PooledConnection has already been closed.");
            }

            var5 = "DisposableConnectionFacade[null]";
        } finally {
            if (this.compare("close", method)) {
                this.setNext((JdbcInterceptor)null);
            }

        }

        return var5;
    }
}

对于Druid连接池，会触发调用com.alibaba.druid.pool.DruidPooledConnection中的setAutoCommit方法：

public void setAutoCommit(boolean autoCommit) throws SQLException {
    this.checkState();
    boolean useLocalSessionState = this.holder.getDataSource().isUseLocalSessionState();
    if (!useLocalSessionState || autoCommit != this.holder.underlyingAutoCommit) {
        try {
            this.conn.setAutoCommit(autoCommit);
            this.holder.setUnderlyingAutoCommit(autoCommit);
            this.holder.setLastExecTimeMillis(System.currentTimeMillis());
        } catch (SQLException var4) {
            this.handleException(var4, (String)null);
        }

    }
}

Tomcat连接池会直接向MySQL服务器发送SET命令，而Druid连接池会根据当前连接在本地缓存的AutoCommit属性值来判断是否需要向MySQL服务器发送SET命令，由于大部分应用都不会显式调整autocommit的属性值，连接池中的MySQL连接会长期保持相同的值，因此Druid连接池能避免重复执行"SET autocommit=1"操作，有效降低"SET autocommit=1"操作的执行频率。

优化建议

• 对于MySQL请求QPS较高的服务，建议将底层MySQL连接池从Tomcat连接池和HikariCP连接池调整为Druid连接池。