Vertica ADO.NET: Degraded performance with connection pooling enabled

viveksviveks Community Edition User
edited January 2022 in General Discussion

While using the Vertica ADO.NET client driver with connection pooling enabled (MinPoolSize of 20 & MaxPoolSize of 100) it was taking ~15 seconds to get results while running 30 queries in parallel. With connection pooling disabled, I was getting results in ~5 seconds for the same 30 queries when called parallelly.

On checking Vertica's query history I could see that the queries themselves are getting completed in <100 ms.

My expectation was that connection pooling should improve the overall performance since the connections don't need to be dropped & recreated. Any reasoning for this degraded performance we are facing when the pooling is on?

We are using Vertica.Data version 9.2.3

Answers

  • HibikiHibiki Vertica Employee Employee
    edited January 2022

    @viveks I would like to know how you got the elapse time to reproduce it.

  • viveksviveks Community Edition User
    @Hibiki We used a stopwatch in our codebase from where we invoke the query execution. Also enabled driver supported logging properties in the ADO.NET.

    What I could make out was that when pooling is enabled, even with a larger min pool size, the library was creating connections very conservatively.
  • HibikiHibiki Vertica Employee Employee

    @viveks Yes, I also can see the client has only the necessary connections even if it sets the large number to MinPoolSize.

    But I cannot see any difference in the performance of both cases. I tried to generate 10 threads and each thread repeated 100 times to open the database connection, run "SELECT 1", and close the connection. As you said, connection pooling should have the advantage but we cannot see it. I guess this is because it needs to execute reset_session() before returning the connection to the pool.

  • HibikiHibiki Vertica Employee Employee

    @viveks I confirmed with the engineering team that it was the expected behavior the connection pool didn't start up with the MinPoolSize database connections. When a connection in the pool goes unused for a certain amount of time, it is automatically closed. What MinPoolSize does is to prevent this from happening if closing the idle connection would cause the number of connections in the pool to be < MinPoolSize. We will improve the description of MinPoolSize property in the product documentation.

  • viveksviveks Community Edition User

    @Hibiki Thank you for clarifying this. So isn't it an issue then that there is no improvment when pooling is enabled or that is the expected behaviour?

  • HibikiHibiki Vertica Employee Employee

    @viveks In the current release, this is expected. Please let me talk with the engineering team we will be able to have a chance to improve this behavior in future release.

  • HibikiHibiki Vertica Employee Employee

    @viveks Let me correct one thing. The whole performance between opening connection and closing it is almost the same, but the performance of opening connection with enabling the connection pooling is better. So the application program can start to execute the queries more quickly.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file