随着数据访问量的增大。终于是出现了多个客户端报这个错:图1
服务端后台报错:
[ERROR]2017-11-03 14:26:32-org.apache.catalina.connector.ClientAbortException:
java.io.IOException: Connection reset by peer
[ERROR]2017-11-03 14:26: 32-java.io.IOException: Connection reset by peer
[WARN]2017-11-03 14:26:32-Failed to invoke @ExceptionHandler method: public tv.huan.ddb.common.model.MyResponse tv.huan.ddb.controller.ExceptionHandlerAdvice.exception(java.lang.Exception,javax.servlet.http.HttpServletRequest,javax.servlet.http.HttpServletResponse)
org.apache.catalina.connector.ClientAbortException: java.io.IOException: 断开的管道
at org.apache.catalina.connector.OutputBuffer.doFlush(OutputBuffer.java:356)
at org.apache.catalina.connector.OutputBuffer.flush(OutputBuffer.java:320)
at org.apache.catalina.connector.CoyoteOutputStream.flush(CoyoteOutputStream.java:110)
at com.fasterxml.jackson.core.json.UTF8JsonGenerator.flush(UTF8JsonGenerator.java:1048)
at com.fasterxml.jackson.databind.ObjectWriter.writeValue(ObjectWriter.java:953)
at org.springframework.http.converter.json.AbstractJackson2HttpMessageConverter.writeInternal(AbstractJackson2HttpMessageConverter.java:285)
at org.springframework.http.converter.AbstractGenericHttpMessageConverter.write(AbstractGenericHttpMessageConverter.java:100)
at org.springframework.web.servlet.mvc.method.annotation.AbstractMessageConverterMethodProcessor.writeWithMessageConverters(AbstractMessageConverterMethodProcessor.java:231)
[ERROR]2017-11-03 14:26: 32-java.io.IOException: Connection reset by peer
[WARN]2017-11-03 14:26:32-Failed to invoke @ExceptionHandler method: public tv.huan.ddb.common.model.MyResponse tv.huan.ddb.controller.ExceptionHandlerAdvice.exception(java.lang.Exception,javax.servlet.http.HttpServletRequest,javax.servlet.http.HttpServletResponse)
org.apache.catalina.connector.ClientAbortException: java.io.IOException: 断开的管道
at org.apache.catalina.connector.OutputBuffer.doFlush(OutputBuffer.java:356)
at org.apache.catalina.connector.OutputBuffer.flush(OutputBuffer.java:320)
at org.apache.catalina.connector.CoyoteOutputStream.flush(CoyoteOutputStream.java:110)
at com.fasterxml.jackson.core.json.UTF8JsonGenerator.flush(UTF8JsonGenerator.java:1048)
at com.fasterxml.jackson.databind.ObjectWriter.writeValue(ObjectWriter.java:953)
at org.springframework.http.converter.json.AbstractJackson2HttpMessageConverter.writeInternal(AbstractJackson2HttpMessageConverter.java:285)
at org.springframework.http.converter.AbstractGenericHttpMessageConverter.write(AbstractGenericHttpMessageConverter.java:100)
at org.springframework.web.servlet.mvc.method.annotation.AbstractMessageConverterMethodProcessor.writeWithMessageConverters(AbstractMessageConverterMethodProcessor.java:231)
开始分析排错:
1. 查看前端设置的 http请求时长 并设置为 20秒。
2.查看tomcat timeout时长,并增大 http请求连接数 。
3.最后 查看 tomcat下的 localhost_access_log.txt 文件 发现 有 两个 程序 执行 时间 分别长达 30s之久
为其中一个 查询语句的 一个字段 添加数据库索引。使其查询时长降到了1s。
另外一个 三表关联查询,并且全表匹配。sql执行 非常耗费资源,
如下所示:
public List<Object> getWinamt() { String sql = "select l.lotteryID,l.winamt,c.phone from lottery l, orderinfo o,customer c " + "where l.orderinfo_id=o.id and o.customer_id=c.id ORDER BY winamt desc limit 10"; return baseDAO.findObject(sql);
修改如下:
public List<Lottery> getWinShow() { DetachedCriteria crit = DetachedCriteria.forClass(Lottery.class); crit.add(Restrictions.eq("status", 4)); crit.addOrder(Order.desc("winamt")); return baseDAO.find(1,10,crit); }
List<Lottery> list = lotteryService.getWinShow(); for (Lottery lottery : list) { Map<String, Object> map = new HashMap<>(); map.put("lotteryID", lottery.getLotteryID()); map.put("winamt", lottery.getWinamt()); map.put("phone", lottery.getOrderInfo().getCustomer_phone()); lis.add(map); }
对于sql的优化有很多种方式,而此次 则采用 单表查询 缩小sql执行 范围,进而使用 数据库之前设计好的表间的关联关系,拿到想要的结果