自动终止长时间运行的查询（MySql），Apache Tomcat DataSource [英] Automatically kill long running queries (MySql), Apache Tomcat DataSource
问题描述
我们的java网络应用程序具有搜索功能，允许用户通过大型数据库搜索记录。
Our java web app has a search functionality that allow users to search record over a big database.
如果用户指定了错误的搜索参数，他们最终会得到一个似乎永远不会结束的查询，因为它需要几个小时才能运行。
If User specify wrong search parameters they end up with a query that doesn't appear to ever end because it need a couple of hours to run.
这是一个Web应用程序，所以他们一次又一次地尝试查询，所有资源都会导致严重的性能问题。
It's a web application so they try again and again and the queries stuck up all resources which cause severe performance issue.
如果查询运行时间过长或使用太多CPU，有没有办法自动终止查询？
Is there any way to automatically kill a query if it runs too long or use too much CPU?
推荐答案
这个答案适用于Apache tomcat-jdbc DataSource提供程序。
This answer is for Apache tomcat-jdbc DataSource provider.
首先，您需要了解PoolProperties
First of all you need to have understanding of PoolProperties
-
setRemoveAbandonedTimeout
setRemoveAbandonedTimeout
setRemoveAbandoned
setRemoveAbandoned
当查询花费的时间超过setRemoveAbandonedTimeout（int）中指定的时间时，执行此查询的连接将被标记为Abandon并调用java.sql.Connection.close（）方法，这将继续等待查询在释放连接之前完成。
When a query took longer than the time specified in setRemoveAbandonedTimeout(int) the connection which executing this query is marked as Abandon and java.sql.Connection.close() method is invoked, which will keep waiting for query to complete before releasing the connection.
我们可以实现自己的处理程序来处理废弃的连接。以下是更改
We can implement our own handler to handle abandoned connection. Below is changes
首先我们需要添加一个界面
First of all we need to add an interface
package org.apache.tomcat.jdbc.pool;
public interface AbandonedConnectionHandler {
public void handleQuery(Long connectionId);
}
tomcat-jdbc文件更改：
tomcat-jdbc files changes:
PoolConfiguration.java （界面）
PoolConfiguration.java (interface)
添加getter和setter方法。
Adding getter and setter method.
public void setAbandonedConnectionHandler(AbandonedConnectionHandler abandonedConnectionHandler);
public AbandonedConnectionHandler getAbandonedConnectionHandler();
将这些方法覆盖到所有实现类
Override these methods to all implementation classes
- DataSourceProxy.java
- PoolProperties.java
- org.apache.tomcat.jdbc.pool.jmx.ConnectionPool。 java
将方法 getConnectionId（）添加到 org.apache.tomcat.jdbc.pool .PooledConnection.java
public Long getConnectionId() {
try {
//jdbc impl has getId()
Method method = this.connection.getClass().getSuperclass().getMethod("getId");
return (Long)method.invoke(this.connection);
} catch (Exception e) {
log.warn(" Abandoned QueryHandler failed to initialize connection id ");
}
return null;
}
上述反射代码可能因mysql驱动程序不同而有所不同。
现在我们需要在调用org.apache.tomcat.jdbc.pool.ConnectionPool中的java.sql.Connection.close（）方法之前放置我们的处理程序。 .java
Now we need to put our handler before calling java.sql.Connection.close() method in org.apache.tomcat.jdbc.pool.ConnectionPool.java
将启动废弃连接清理程序的ConnectionPool.java方法是
The ConnectionPool.java method which will initiate the abandoned connection cleaner is
protected void abandon(PooledConnection con)
在调用
if(getPoolProperties().getAbandonedConnectionHandler() != null)
{
con.lock();
getPoolProperties().getAbandonedConnectionHandler().handleQuery(con.getConnectionId());
}
现在你所要做的就是将你的handerInstance与PoolProperties一起传递创建tomcat-jdbc DataSource。
Now all you have to do is to pass your handerInstance along with the PoolProperties while creating tomcat-jdbc DataSource.
p.setAbandonedConnectionHandler(new ConnectionHandler(true));
这是我的AbandonedConnectionHandler实现。
Here is my AbandonedConnectionHandler implementation.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.juli.logging.Log;
import org.apache.juli.logging.LogFactory;
import org.apache.tomcat.jdbc.pool.AbandonedConnectionHandler;
import org.apache.tomcat.jdbc.pool.PoolConfiguration;
public class ConnectionHandler implements AbandonedConnectionHandler{
private static final Log log = LogFactory.getLog(ConnectionHandler.class);
private Boolean isAllowedToKill;
private PoolConfiguration poolProperties;
public ConnectionHandler(Boolean isAllowedToKill)
{
this.isAllowedToKill = isAllowedToKill;
}
@Override
public void handleQuery(Long connectionId) {
Connection conn = null;
Statement stmt = null;
if(this.isAllowedToKill)
{
try{
Class.forName(poolProperties.getDriverClassName());
conn = DriverManager.getConnection(poolProperties.getUrl(),poolProperties.getUsername(),poolProperties.getPassword());
Statement statement = conn.createStatement();
ResultSet result = statement.executeQuery("SELECT ID, INFO, USER, TIME FROM information_schema.PROCESSLIST WHERE ID=" + connectionId);
if(result.next())
{
if(isFetchQuery(result.getString(2)))
{
statement.execute("Kill "+connectionId);
}
}
statement.close();
conn.close();
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
try {
if(stmt != null && !stmt.isClosed())
stmt.close();
} catch (SQLException e) {
log.warn("Exception while closing Statement ");
}
try {
if(conn != null && !conn.isClosed() )
conn.close();
} catch (SQLException e) {
log.warn("Exception while closing Connection ");
}
}
}
}
private Boolean isFetchQuery(String query)
{
if(query == null)
{
return true;
}
query = query.trim();
return "SELECT".equalsIgnoreCase(query.substring(0, query.indexOf(' ')));
}
public PoolConfiguration getPoolProperties() {
return poolProperties;
}
public void setPoolProperties(PoolConfiguration poolProperties) {
this.poolProperties = poolProperties;
}
}
这篇关于自动终止长时间运行的查询（MySql），Apache Tomcat DataSource的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
