FWD » Core Development » Database

Is it a top-priority task?
Thank you.

TempTableDataSourceProvider was introduced in branch 4011a to provide database connections to the in-memory H2 database used for temp-table support. It currently uses DriverManager.getConnection to establish a new connection. This is fine for very long-running temp-table database sessions. However, I suspect we can get into some use cases where we are "thrashing" the creation and closing of database sessions, along with their backing JDBC connections.

In #4702, I have requested some test cases be written that expose this "thrashing" use case, so we can see whether this is a bottleneck. Perhaps it is best that the assignee of this issue write those test cases and provide them back for use with #4702.

If this proves to be a bottleneck, we would want to back TempTableDataSourceProvider with a c3p0 pool instead of using DriverManager.getConnection.

FYI, 4011b is the branch for this work. 4011a and 4011a_rebase are now frozen.

I've tested calling one of Stanislav's tests (with minor changes to suppress output) as an external program in loop:

DO i = 1 TO 1000000:
    RUN h2_performance/perf-create1.p.
END.

This is good news, but it suggests that the test did not recreate the condition I thought it would, either because I am misunderstanding the way the data source provider is implemented, or because I misjudged the conditions needed to allow a temp-table related Session object to be closed.

I'm not sure what you mean by the connections are created at server startup. My understanding is that the first time TempTableDataSourceProvider needs to create a connection, it does so, and this connection is stored in a context-local instance of the inner Context class. When the Session for that connection is closed, the connection is released if that context's count of active temp-tables is 0. A new connection would be retrieved the next time a Session object is opened for that context.

I thought I had witnessed situations (like the looping test case above) where we would have short-lived temp-tables, and thus the associated session and connection would be closed and opened again in quick succession. It seems that scenario was not recreated by that test.

Eric Faulhaber wrote:

This is good news, but it suggests that the test did not recreate the condition I thought it would, either because I am misunderstanding the way the data source provider is implemented, or because I misjudged the conditions needed to allow a temp-table related Session object to be closed.

I'm not sure what you mean by the connections are created at server startup. My understanding is that the first time TempTableDataSourceProvider needs to create a connection, it does so, and this connection is stored in a context-local instance of the inner Context class. When the Session for that connection is closed, the connection is released if that context's count of active temp-tables is 0. A new connection would be retrieved the next time a Session object is opened for that context.

Well, maybe I have missed something, but the debugger stops at the corresponding call only at the server startup.

I thought I had witnessed situations (like the looping test case above) where we would have short-lived temp-tables, and thus the associated session and connection would be closed and opened again in quick succession. It seems that scenario was not recreated by that test.

Indeed. I was trying to figure out in which situations the converted code will close the connection, but w/o success so far. Maybe it makes sense to use pure Java test? Unfortunately, the way the 1TempTableDataSourceProvider is implemented makes this a little bit tricky.

I don't think it is really worth it to contrive something in hand-written Java to recreate a problem that we haven't actually seen coming from converted code (at least not that I can recall or pinpoint now). I opened this task based on a perceived problem from my reading of the source for the data source provider. I recalled seeing behavior which I attributed to this potential problem, but through assumption, not debugging. Until we hit something that actually realizes this potential in real converted code, let's put this task on hold.

Eric Faulhaber wrote:

I don't think it is really worth it to contrive something in hand-written Java to recreate a problem that we haven't actually seen coming from converted code (at least not that I can recall or pinpoint now). I opened this task based on a perceived problem from my reading of the source for the data source provider. I recalled seeing behavior which I attributed to this potential problem, but through assumption, not debugging. Until we hit something that actually realizes this potential in real converted code, let's put this task on hold.

ОК, thank you. Please note that I have not seen a single call to the TempTableDataSourceProvider during profiling the server with YourKit during the client session run.