Re: [luciddb-users] Problems with Idle Connections

[Nicholas G. <ngo...@dy...>]

Welcome Victor to LucidDB community!

On May 31, 2011, at 2:08 PM, victor.savkin wrote:

> When I don't
> close connections properly I always receive: 'Cache scratch memory
> exhausted'. 
Well, I think you've already identified the key point here.  Database connections should be closed... that's why programmers take such care to make sure it's in the finally() blocks, etc.  However, sometimes, in rare cases they don't which is why we have things like the connectionTimeutMillis param.

> Step to reproduce:
> 1. Write a small java app querying luciddb.
> 2. When the app starts kill it, so the connection won't be closed.
> 3. Repeat steps 1-2 more than expectedConcurrentStatements times.
> 4. Next time you'll get "Cache scratch memory exhausted".

I just verified (since we did have an HTTP regression between 0.9.2 and 0.9.3) that LucidDB does actually shutdown sessions that are idle.  

If a connection is running a long query when client is killed, the connection timeout will only start it's timer once the query has been returned (I didn't test this, but I'm pretty sure).  So... is it possible that there are long running SQL statements are still running on those orphaned connections?  If so, they'll only be marked as available for timing out when that query is done.
Some helpful system views in determining:
http://pub.eigenbase.org/wiki/LucidDbSystemViews#DBA_SQL_STATEMENTS
http://pub.eigenbase.org/wiki/LucidDbSystemViews#DBA_SESSIONS

Also, some helpful statements if you're trying to prune failed clients that can't receive a result when they're ready:
http://pub.eigenbase.org/wiki/LucidDbSysRoot_KILL_SESSION
http://pub.eigenbase.org/wiki/LucidDbSysRoot_KILL_STATEMENT

LucidDB does not kill statements while they're running for connectionTimeoutMillis; it's only for non attached sessions.  If a connection is open, but unused, there's a ping that keeps coming back as well that keeps it available.  If you're killing the application, the ping will be gone so this is probably not an issue for you.

> hoping that luciddb will recreate the connections. But it didn't help. To
> make it work I have to restart the database which is unacceptable in
> production. 
connectionTimeoutMillis only takes effect at startup.  Is it possible that you changed it to 2 minutes but hadn't restarted the server prior to your test?

Nick