How to improve performance of JDBC query execution?

Question

I'm running a bot on Discord that receives a lot of requests to the MySQL database, and recently MySQL has started blocking threads, causing major delays in the program.

After dumping the thread, I've found that the problematic line resides within the PreparedStatement code from JDBC, but I'm really not sure what could be causing this issue.

The code block below is where the error occurs:

public List<HashMap<String, Object>> find(String haystack, Object... needles){
    PreparedStatement prep = null;
    List<HashMap<String, Object>> results = new ArrayList<>();
    ResultSet rs = null;
    try{
        prep = connection.prepareStatement(haystack);
        for(int i = 0; i < needles.length; i++){
            prep.setObject(i+1, needles[i]);
        }
        rs = prep.executeQuery();
        while(rs.next()){
            HashMap<String, Object> result = new HashMap<>();
            for(int i = 1; i < rs.getMetaData().getColumnCount() + 1; i++){
                result.put(rs.getMetaData().getColumnName(i), rs.getObject(i));
            }
            results.add(result);
        }
    }catch(SQLException e){
        System.out.println("MySQL > Unable to execute query: " + e.getMessage());
    }finally{
        try{
            if(rs!=null)rs.close();
            if(prep!=null)prep.close();
        }catch(SQLException e){
            System.out.println("(find) Error closing: " + e.getMessage());
        }
    }
    return results;
}

with rs = prep.executeQuery(); being the problematic line of code.

Is there any way to stop MySQL from blocking threads?

why don't you create a batch in `PreparedStatement` and then execute everything at once? — Vishrant, Apr 29 '18 at 18:12
I see you mentioned that you are getting many request, how you are creating a connection object? Maybe the problem is there. It might be creating lot of connections with MySQL. — Vishrant, Apr 29 '18 at 18:15
The connection is initiated on startup, then referenced throughout the program. — Jake S., Apr 29 '18 at 18:16
So there is only one connection? I would suggest to have a pool of connections. Using connection pooling. — Vishrant, Apr 29 '18 at 18:17
`The connection is initiated on startup, then referenced throughout the program`. What if one connection is simply not enough, have you tried a connection pool for this situation ? — arthur, Apr 29 '18 at 18:17
Are there any resources available to get started with connection pooling in JDBC? — Jake S., Apr 29 '18 at 18:18
@JakeS. please accept the answer or leave your comments if you are still facing the issue. — Vishrant, Apr 29 '18 at 18:36

Vishrant · Accepted Answer · 2018-04-30T00:02:51.350

I see that you are using only one connection throughout the application. You should create a pool of connections if you have a large number of request to handle which can be done using following approaches:

You can create connection pooling on the application side. You can use apache connection pool.
You can create connection pooling on the server end. Read this.
Best, use hibernate, there you have a property called hibernate.connection.pool_size.

If you are using JDBC prepared statement then use batch processing and avoid using Statement (which I see you are doing) because of the reason mentioned in this post.

How to improve performance of JDBC query execution?

1 Answers1