0

Does anybody know how to avoid the Postgres driver from loading all metadata when connecting to a database?

It takes about a minute to connect with the driver, and from the console it takes like 1ms.

I found this answer but for JPA, I need something like this for JDBC: Hibernate Slow to Acquire Postgres Connection

This are my connection manager methods related to the question. I use a HashMap which holds a BasicDataSource per Database, a method to initialise the BasicDataSource for each DB, and another to get a connection from the corresponding DB datasource.

The delay of over a minute occurs on 'dataSource.getConnection()'

private HashMap<Database, BasicDataSource> dataSources = new HashMap<Database, BasicDataSource>();

private void initializeConnectionPools() throws FileNotFoundException, ReaderException, InterruptedException
    {
        if (dataSources.isEmpty())
        {
            Reader data = ReaderFactory.getReader(Constants.DATA_FILE);
            DatabaseConnectionData connectionData = data.getDatabaseConnectionData();

            String url = Constants.POSTGRESQL_JDBC_PREFIX + connectionData.getHost();

            logger.info("INITIALIZING CONNECTIONS POOL [@" + connectionData.getHost() + "]");

            BasicDataSource dataSource = null;

            for (Database database : Database.values())
            {
                dataSource = new BasicDataSource();
                dataSource.setDriverClassName(Constants.POSTGRESQL_DRIVER_CLASS);
                dataSource.setUrl(url + database.toString().toLowerCase());
                dataSource.setUsername(connectionData.getUser());
                dataSource.setPassword(connectionData.getPassword());
                // Initial size is:
                // The number of browsers multiplied per number of instances
                // allowed for each browser on GRID nodes
                dataSource.setInitialSize(AppnameStation.values().length * 5);
                dataSource.setMaxTotal(-1);

                dataSources.put(database, dataSource);
            }

            logger.info("CONNECTIONS POOL INITIALIZED [@" + connectionData.getHost() + "]");
        }
    }

public Connection getConnection(Database database)
        throws SQLException, FileNotFoundException, ReaderException, ClassNotFoundException
{
    Connection connection = null;
    BasicDataSource dataSource = dataSources.get(database);

    logger.info("Getting " + database + " database connection");

    connection = dataSource.getConnection();

    logger.info("Connected to " + database + " database");

    return connection;
}

Thanks!

Community
  • 1
  • 1
bmartin
  • 11
  • 3
  • You need to provide more context. By default, on connect, no metadata is loaded. You need to show how you connect and what your application does. – Mark Rotteveel Aug 07 '15 at 08:24
  • Thanks for your reply @MarkRotteveel . I just added the class methods. Hope somebody can help – bmartin Aug 12 '15 at 23:38
  • What is the value of `AppnameStation.values().length`? Does connecting take a minute every time, or only the first time? – Mark Rotteveel Aug 13 '15 at 07:00
  • It's the amount of instances that will require a connection to the database, that way I make sure I always have an available connection per instance. It only takes a minute the first time, after that all requests have no delay. – bmartin Aug 13 '15 at 17:32
  • I want to know the number, say it is 100, you are initializing the pool with an initial size of **500**. This is usually populated at first connect, for argument sake, say connecting takes 200 ms, this means that it takes 100 seconds to populate the pool. Note that a pool is usually sized at a **fraction** of the concurrent users, and the initial size is usually a lot lower, sizing it at a multiple is very unusual. – Mark Rotteveel Aug 13 '15 at 19:30
  • I'm aware of that. The size varies from 5 to 8, which are the thread instances that will access concurrently, in that way each can always have an available connection. Still, the issue is not due to the amount of the instances, since even running a single thread, which creates and initialises a single connection, it always takes the same time. – bmartin Aug 15 '15 at 00:53
  • Thanks for the information. If it is only a low number, then I don't know what could be causing this. You could try asking on the DBCP mailinglist, see https://commons.apache.org/proper/commons-dbcp/mail-lists.html – Mark Rotteveel Aug 15 '15 at 07:29
  • Thanks @MarkRotteveel – bmartin Aug 17 '15 at 18:39

0 Answers0