Vertica ADO.NET: Degraded performance with connection pooling enabled
viveks
Community Edition User ✭
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
0
Answers
@viveks I would like to know how you got the elapse time to reproduce it.
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.
@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.
@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.
@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?
@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.
@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.