Automatically kill long queries (MySql), Apache Tomcat DataSource

Question

Our java web application has a search function that allows users to search a large database.

If the user sets the wrong search parameters, they end the query, which seems to never end, because he needs a couple of hours to run.

This is a web application, so they are repeated over and over again, and requests delay all resources that cause a serious performance problem.

Is there a way to automatically kill a request if it runs too long or use too much CPU?

Answer 1

DataSource Apache tomcat-jdbc.

, PoolProperties

• setRemoveAbandonedTimeout

• setRemoveAbandoned

, setRemoveAbandonedTimeout (int), , , Abandon java.sql.Connection.close(), .

.

package org.apache.tomcat.jdbc.pool;

public interface AbandonedConnectionHandler {

        public void handleQuery(Long connectionId);

}

tomcat-jdbc:

PoolConfiguration.java ()

getter setter.

public void setAbandonedConnectionHandler(AbandonedConnectionHandler  abandonedConnectionHandler);

public AbandonedConnectionHandler getAbandonedConnectionHandler();

• 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.

java.sql.Connection.close() org.apache.tomcat.jdbc.pool.ConnectionPool.java

ConnectionPool.java, ,

protected void abandon(PooledConnection con) 

, release (con);

if(getPoolProperties().getAbandonedConnectionHandler() != null)
            {
                con.lock();
                getPoolProperties().getAbandonedConnectionHandler().handleQuery(con.getConnectionId());
            }

, , handerInstance PoolProperties tomcat-jdbc DataSource.

p.setAbandonedConnectionHandler(new ConnectionHandler(true));

AbandonedConnectionHandler.

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;
    }

}

Answer 2

, .

MySQL, 5.7.8, max_execution_time.

cron script, SHOW PROCESSLIST , , .