前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >性能调优之mysql线程池优化

性能调优之mysql线程池优化

作者头像
飞天小子
发布2021-09-26 16:56:17
1.6K0
发布2021-09-26 16:56:17
举报
文章被收录于专栏:公众号-测试驿栈

概述

对Nginx域名转发做了个压测,结果不大理想,jmeter哗哗的报错,nginx连接全是超时,tps波动特别大。如下图

tps在490的时候开始剧烈抖动

大致的错误信息如下 { : "timestamp":"2021-09-22T07:52:22.178+0000", : "status":500, : "error":"Internal Server Error", : "message":"\n### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure\n\nThe last packet successfully received from the server was 10,949 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago.\n### The error may exist in class path resource [mybatis/mapper/UserMapper.xml]\n### The error may involve defaultParameterMap\n### The error occurred while setting parameters\n### 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 = ?\n### Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure\n\nThe last packet successfully received from the server was 10,949 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago.\n; Communications link failure\n\nThe 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\n\nThe 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" }

看错误信息是因为jdbc连接池异常中断导致的,检查一下连接池的配置

考虑到系统使用的人很少,几乎不会有多少jdbc并发,所以连接池的最大连接数有点高了,可能会资源浪费。改小一点再做调试 spring.datasource.druid.initial-size=3 spring.datasource.druid.min-idle=3 spring.datasource.druid.max-active=10 Tps基本保持稳定,jmeter不再报错。但是偶尔还是会有波动。

linux能看到一些错误信息,如下 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] 调用login方法的时候,mysql的IO队列阻塞了,数据包回不来。直接检查login调用的user表,看索引。 这张表里面对username加了索引,但是没有对password加,也没对realname加。实际上登录执行的是realname字段!

下面把索引补上

再次运行,tps稳定不抖动,后端无任何错误信息

应用和数据库分离

原先那个mysql应用和项目应用是在同一台服务器上面。后期调试的时候直接把mysql应用拉到了另一台服务器上。再次执行发现tps能够稳定在到800左右,无任何异常。

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2021-09-23 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 概述
    • 应用和数据库分离
    相关产品与服务
    云数据库 SQL Server
    腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
    领券
    问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档