性能調(diào)優(yōu)之mysql線程池優(yōu)化
2021-09-23 13:58:02

概述

對(duì)Nginx域名轉(zhuǎn)發(fā)做了個(gè)壓測(cè)，結(jié)果不大理想，jmeter嘩嘩的報(bào)錯(cuò)，nginx連接全是超時(shí)，tps波動(dòng)特別大。如下圖

tps在490的時(shí)候開(kāi)始劇烈抖動(dòng)

大致的錯(cuò)誤信息如下
{
: "timestamp":"2021-09-22T07:52:22.178+0000",
: "status":500,
: "error":"Internal Server Error",
: "message":" ### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure The last packet successfully received from the server was 10,949 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago. ### The error may exist in class path resource [mybatis/mapper/UserMapper.xml] ### The error may involve defaultParameterMap ### The error occurred while setting parameters ### SQL: SELECT user_id,user_name,user_nickname,user_password,user_realname,user_gender,user_birthday,user_profile_picture_src,user_address,user_homeplace FROM user WHERE user_name = ? and user_password = ? ### Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure The last packet successfully received from the server was 10,949 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago. ; Communications link failure The last packet successfully received from the server was 10,949 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago.; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure The last packet successfully received from the server was 10,949 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago.",
: "path":"/tmall/login/doLogin"
}

看錯(cuò)誤信息是因?yàn)閖dbc連接池異常中斷導(dǎo)致的，檢查一下連接池的配置

考慮到系統(tǒng)使用的人很少，幾乎不會(huì)有多少jdbc并發(fā)，所以連接池的最大連接數(shù)有點(diǎn)高了，可能會(huì)資源浪費(fèi)。改小一點(diǎn)再做調(diào)試
spring.datasource.druid.initial-size=3
spring.datasource.druid.min-idle=3
spring.datasource.druid.max-active=10
Tps基本保持穩(wěn)定，jmeter不再報(bào)錯(cuò)。但是偶爾還是會(huì)有波動(dòng)。

linux能看到一些錯(cuò)誤信息，如下
The last packet successfully received from the server was 11,938 milliseconds ago. The last packet sent successfully to the server was 1 milliseconds ago.; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 11,938 milliseconds ago. The last packet sent successfully to the server was 1 milliseconds ago.] with root cause java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3014) ~[mysql-connector-java-5.1.47.jar:5.1.47]
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3472) ~[mysql-connector-java-5.1.47.jar:5.1.47]
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3462) ~[mysql-connector-java-5.1.47.jar:5.1.47]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3905) ~[mysql-connector-java-5.1.47.jar:5.1.47]
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530) ~[mysql-connector-java-5.1.47.jar:5.1.47]
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683) ~[mysql-connector-java-5.1.47.jar:5.1.47]
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2495) ~[mysql-connector-java-5.1.47.jar:5.1.47]
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1903) ~[mysql-connector-java-5.1.47.jar:5.1.47]
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1242) ~[mysql-connector-java-5.1.47.jar:5.1.47]
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3461) ~[druid-1.1.19.jar:1.1.19]
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440) ~[druid-1.1.19.jar:1.1.19]
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3459) ~[druid-1.1.19.jar:1.1.19]
at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167) ~[druid-1.1.19.jar:1.1.19]
at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497) ~[druid-1.1.19.jar:1.1.19]
at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64) ~[mybatis-3.5.1.jar:3.5.1]
at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) ~[mybatis-3.5.1.jar:3.5.1]
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63) ~[mybatis-3.5.1.jar:3.5.1]
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324) ~[mybatis-3.5.1.jar:3.5.1]
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) ~[mybatis-3.5.1.jar:3.5.1]
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) ~[mybatis-3.5.1.jar:3.5.1]
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83) ~[mybatis-3.5.1.jar:3.5.1]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147) ~[mybatis-3.5.1.jar:3.5.1]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140) ~[mybatis-3.5.1.jar:3.5.1]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:76) ~[mybatis-3.5.1.jar:3.5.1]
at sun.reflect.GeneratedMethodAccessor82.invoke(Unknown Source) ~[?:?]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_241]
at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_241]
at org.mybatis.spring.SqlSessionTemplate(SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426) ~[mybatis-spring-2.0.2.jar:2.0.2] at com.sun.proxy.)Proxy96.selectOne(Unknown Source) ~[?:?]
at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:159) ~[mybatis-spring-2.0.2.jar:2.0.2]
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:87) ~[mybatis-3.5.1.jar:3.5.1]
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:58) ~[mybatis-3.5.1.jar:3.5.1]
at com.sun.proxy.$Proxy104.selectByLogin(Unknown Source) ~[?:?]
at com.xq.tmall.service.impl.UserServiceImpl.login(UserServiceImpl.java:47) ~[tmall.jar:?]
at sun.reflect.GeneratedMethodAccessor81.invoke(Unknown Source) ~[?:?]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_241]
at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_241]
調(diào)用login方法的時(shí)候，mysql的IO隊(duì)列阻塞了，數(shù)據(jù)包回不來(lái)。直接檢查login調(diào)用的user表，看索引。
這張表里面對(duì)username加了索引，但是沒(méi)有對(duì)password加，也沒(méi)對(duì)realname加。實(shí)際上登錄執(zhí)行的是realname字段！


下面把索引補(bǔ)上

再次運(yùn)行，tps穩(wěn)定不抖動(dòng)，后端無(wú)任何錯(cuò)誤信息

應(yīng)用和數(shù)據(jù)庫(kù)分離

原先那個(gè)mysql應(yīng)用和項(xiàng)目應(yīng)用是在同一臺(tái)服務(wù)器上面。后期調(diào)試的時(shí)候直接把mysql應(yīng)用拉到了另一臺(tái)服務(wù)器上。再次執(zhí)行發(fā)現(xiàn)tps能夠穩(wěn)定在到800左右，無(wú)任何異常。