Using neo4j-jdbc open-connection is bottleneck

Question

I am using neo4-jdbc with pool lib BasicDataSource.

I had huge latency problems so we profiled the app and we found out that opening connection is the cause. I didnt understand why open-connection takes so long we using pool. this is screenshot from our profiles:

This is how the Neo4jDatasourceRemote looks like:

package com.comp.wm.common.repo;

import com.comp.wm.common.utils.Constants;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Value;

import javax.annotation.PostConstruct;
import java.sql.Connection;
import java.sql.SQLException;




    private final Logger logger = Logger.getLogger(Neo4jDataSourceRemote.class);

    private BasicDataSource ds;

    @Value("${neo4j.host:localhost}")
    private String NEO4J_HOST;

    @Value("${neo4j.port:7474}")
    private String NEO4J_PORT;

    @Value("${neo4j.username:nouser}")
    private String NEO4J_USERNAME;

    @Value("${neo4j.password:nopass}")
    private String NEO4J_PASSWORD;

    @Value("${neo4j.pool.size:200}")
    private int NEO4J_POOL_SIZE;

    private String GetUrl() {
        return String.format(Constants.NEO4J_JDBC_CONNECTIVITY_STRING, NEO4J_HOST, NEO4J_PORT);
    }


    @PostConstruct
    public void init(){
        ds = new BasicDataSource();
        ds.setInitialSize(300);
        ds.setDriverClassName("org.neo4j.jdbc.Driver");
        ds.setUrl(GetUrl());
        ds.setUsername(NEO4J_USERNAME);
        ds.setPassword(NEO4J_PASSWORD);
    }


    @Override
    public Connection openConnection() throws SQLException {
        return this.ds.getConnection();
    }

    @Override
    public void closeConnection(Connection conn) {
        try {
            if (conn != null)
                conn.close();
        } catch (SQLException ex) {
            logger.info("error closing connection", ex);
        }
    }
}

and this is a sample of how I execute query against the graph:

public List<NearbyItem> executeQuery(..) {
        conn = neo4jDataSource.openConnection();


        String getUsersStatement = "some query..";

        try (PreparedStatement ps = conn.prepareStatement(getUsersStatement)) {
           ..
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
               ...
            }
        } catch (Exception e) {
            throw new RuntimeException("Error returning userId=" + userIdInput, e);
        } finally {
            neo4jDataSource.closeConnection(conn);
        }
        return distItemDatas;
    }

any ideas?

Answer 1

Based on comments above, I'll add this as a reply.

By default Neo4j runs in the http interface 10 threads for core. You can tweak the total number of threads in neo4j-server.properties

org.neo4j.server.webserver.maxthreads=200

However the more threads you have the more you're suffering from context switches and lock contention. If you increase the number of threads I won't expect a large increase of throughput, you just shift the point where you have to wait. From initialization (openCOnnection) to processing the query.