Java、tomcat8:MYSQL查询开始变得非常低，几个小时后，部署了一个新的。战争已经结束了
我有一个java网站（更准确地说，是一个论坛），它由debian VPS托管，多亏了tomcat8
这个网站不是我写的，它是在几天前转让给我的，我开始写我的代码
应用程序的DAO（存储库）基于JOOQ，但我不喜欢它，所以我决定只使用mysql jdbc连接器编写新的查询。 目前，我只写了一个新的查询来获取论坛的所有部分
该查询工作正常，但仅持续几个小时。新WAR部署几个小时后，查询开始变得非常缓慢，无法完成。因此，我必须重新启动web服务器。重新启动后，查询工作正常，但仍然只有几个小时
下面您可以看到MiscDAO类的代码，它包含方法“getForums”。这是包含查询的方法。 数据源（BasicDataSource）被传递给基类，基类将引用保留为类成员（对于jooq变量也是如此）
public class MiscDAO extends BaseDAO {
public MiscDAO(DSLContext jooq, BasicDataSource dataSource) {
super(jooq, dataSource);
}
public List<String> getForums() {
String query = "select forums.id\n" +
"from forums\n" +
"left join messages on forums.id=messages.forum\n" +
"and forums.enabled=1\n" +
"group by forums.id\n" +
"order by count(forums.id) desc;";
Connection conn = null;
ResultSet result = null;
PreparedStatement stat = null;
try {
conn = dataSource.getConnection();
stat = dataSource.getConnection()
.prepareStatement(query);
result = stat.executeQuery();
List<String> res = new ArrayList<String>();
while(result.next()) {
res.add(result.getString("id"));
}
return res;
} catch (SQLException e) {
return null;
}
finally {
CloseDbResources(conn, stat, result);
}
}
}
下面是BaseDAO类中的方法“CloseDbResources”
protected void CloseDbResources(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) { }
}
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) { }
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) { }
}
}
最后是DAO工厂中init方法的代码，在这里创建了类MiscDAO的实例。注意，MiscDAO类是唯一一个接收BasicDataSource作为参数的类，原因是我想开始从jooq到简单sql的转换
void init(Properties databaseConfig) throws ClassNotFoundException {
String driver = databaseConfig.getProperty("driverclass");
Class.forName(driver);
String username = databaseConfig.getProperty("username");
String password = databaseConfig.getProperty("password");
String url = databaseConfig.getProperty("url");
dataSource = new BasicDataSource();
dataSource.setMaxTotal(15);
dataSource.setMaxIdle(10);
dataSource.setMinIdle(3);
dataSource.setMaxWaitMillis(30000);
dataSource.setTestOnBorrow(true);
dataSource.setTestWhileIdle(true);
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
dataSource.setValidationQuery("SELECT 1");
dataSource.setValidationQueryTimeout(30);
DSLContext jooq = DSL.using(dataSource, SQLDialect.MYSQL);
authorsDAO = new AuthorsDAO(jooq);
threadsDAO = new ThreadsDAO(jooq);
messagesDAO = new MessagesDAO(jooq);
pollsDAO = new PollsDAO(jooq);
quotesDAO = new QuotesDAO(jooq);
bookmarksDAO = new BookmarksDAO(jooq);
adminDAO = new AdminDAO(jooq);
miscDAO = new MiscDAO(jooq, dataSource);
privateMsgDAO = new PrivateMsgDAO(jooq);
digestDAO = new DigestDAO(jooq);
loginsDAO = new LoginsDAO(jooq);
}
类DAOFactory是一个单例，因此在初始化该类时，只调用一次init方法
static synchronized DAOFactory getInstance() {
if (instance == null) {
String persistenceName = FdTConfig.getProperty("persistence.name");
try {
instance = new DAOFactory();
instance.init(FdTConfig.getDatabaseConfig(persistenceName));
} catch (Exception e) {
LOG.fatal("Cannot instantiate Persistence '" + persistenceName + "'", e);
return null;
}
}
return instance;
}
在下面，您可以看到旧代码，这是由网站的旧所有者编写的，运行良好。该查询基于jooq。 请注意，该查询用于从messages表中获取论坛部分。原因是“论坛”表不存在。 它是我几天前创建的，这就是我编写新查询的原因
public List<String> getForums() {
Result<Record1<String>> records = jooq.select(MESSAGES.FORUM)
.from(MESSAGES)
.where(MESSAGES.FORUM.isNotNull())
.groupBy(MESSAGES.FORUM)
.orderBy(DSL.count(MESSAGES.ID).desc(), MESSAGES.FORUM.asc())
.fetch();
List<String> res = new ArrayList<String>(records.size());
for (Record1<String> record : records) {
res.add(record.getValue(FORUMS.ID));
}
return res;
}
在DaoFactory中，类MiscDAO的实例过去是以这种方式创建的（唯一的区别是该类没有将BaiscDataSource作为参数，因此BasicCDataSource被封装在jooq中，从未直接使用过）
miscDAO = new MiscDAO(jooq);
在下面，您可以读取日志文件中的错误，该错误在部署新war数小时后发生
2018-04-01 13:46:50,154 ERROR MainServlet:509 -
java.lang.reflect.InvocationTargetException at
sun.reflect.GeneratedMethodAccessor98.invoke(Unknown Source) at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497) at
com.forumdeitroll.servlets.MainServlet.doDo(MainServlet.java:241) at
com.forumdeitroll.servlets.MainServlet.doGetPost(MainServlet.java:120)
at com.forumdeitroll.servlets.MainServlet.doGet(MainServlet.java:110)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:618) at
javax.servlet.http.HttpServlet.service(HttpServlet.java:725) at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.forumdeitroll.filters.MainFilter.doFilter(MainFilter.java:86)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
at
org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:506)
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142)
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at
org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:610)
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
at
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:537)
at
org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1081)
at
org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:658)
at
org.apache.coyote.http11.Http11NioProtocol$Http11ConnectionHandler.process(Http11NioProtocol.java:222)
at
org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1580)
at
org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1537)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at
org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745) Caused by:
org.jooq.exception.DataAccessException: Error getting connection from
data source org.apache.commons.dbcp2.BasicDataSource@41b7afc6 at
org.jooq.impl.DataSourceConnectionProvider.acquire(DataSourceConnectionProvider.java:89)
at
org.jooq.impl.DefaultExecuteContext.connection(DefaultExecuteContext.java:583)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:311) at
org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:319)
at
org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:306)
at
org.jooq.impl.AbstractResultQuery.fetchAny(AbstractResultQuery.java:523)
at org.jooq.impl.SelectImpl.fetchAny(SelectImpl.java:2512) at
com.forumdeitroll.persistence.dao.BaseDAO.getAuthor(BaseDAO.java:51)
at
com.forumdeitroll.persistence.dao.MessagesDAO.recordToDTO(MessagesDAO.java:289)
at
com.forumdeitroll.persistence.dao.MessagesDAO.getMessages(MessagesDAO.java:110)
at com.forumdeitroll.servlets.Messages.getMessages(Messages.java:781)
at com.forumdeitroll.servlets.Messages.getMessages(Messages.java:87)
... 33 more Caused by: java.sql.SQLException: Cannot get a
connection, pool error Timeout waiting for idle object at
org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:142)
at
org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1533)
at
org.jooq.impl.DataSourceConnectionProvider.acquire(DataSourceConnectionProvider.java:86)
... 44 more Caused by: java.util.NoSuchElementException: Timeout
waiting for idle object at
org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:449)
at
org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:363)
at
org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:134)
... 46 more
# 1 楼答案
这真是一件很难察觉的事情。我不能100%确定确切的原因，但以下是您应该考虑的一些提示：-
最大连接数不足。因此，请尝试将最大连接数增加到50
Connection可能未正确关闭。从代码的角度来看，情况看起来不错，但我还是建议使用try with resource，因为它在内部确保关闭连接确保无论何时部署新的war，实际上都完全关闭了tomcat。因为可能存在这样的情况：由于tomcat活动，您的代码从来没有机会释放它获得的连接
尝试了解有多少并发用户正在尝试访问该应用程序。如果这些明显超过最大连接数。您需要增加允许的最大连接数
我正在进一步探索，以找出更多可能的原因