1

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.

It's a web application so they try again and again and the queries stuck up all resources which cause severe performance issue.

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

Aftab
  • 938
  • 1
  • 9
  • 20

2 Answers2

1

The best option for you is to catch search conditions that can not be performed.

In MySQL starting from 5.7.8 there is a max_execution_time setting.

Also you can come up with some cron script that checks SHOW PROCESSLIST and handle queries that are being processed more then your time limit.

icoder
  • 145
  • 2
  • 5
0

This answer is for Apache tomcat-jdbc DataSource provider.

First of all you need to have understanding of PoolProperties

  1. setRemoveAbandonedTimeout

  2. setRemoveAbandoned

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 files changes:

PoolConfiguration.java (interface)

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

Add a method getConnectionId() to 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;
}

The above reflection code might differ in case of different mysql driver.

Now we need to put our handler before calling java.sql.Connection.close() method in org.apache.tomcat.jdbc.pool.ConnectionPool.java

The ConnectionPool.java method which will initiate the abandoned connection cleaner is 

protected void abandon(PooledConnection con)

Add below code inside this method before calling release(con);

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

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

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

}
Aftab
  • 938
  • 1
  • 9
  • 20