Quantcast

Problems with Idle Connections

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Problems with Idle Connections

victor.savkin
Hi Guys,

I really struggle to tackle one problem I have with luciddb. When I don't close connections properly I always receive: 'Cache scratch memory exhausted'.

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 assume luciddb doesn't release memory allocated to those connections. So I tried to fix it by setting connectionTimeoutMillis = 120000 (2 minutes) 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.

Could you help me with this situation?

Thanks,
Victor
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Problems with Idle Connections

ngoodman
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
------------------------------------------------------------------------------
Simplify data backup and recovery for your virtual environment with vRanger.
Installation's a snap, and flexible recovery options mean your data is safe,
secure and there when you need it. Data protection magic?
Nope - It's vRanger. Get your free trial download today.
http://p.sf.net/sfu/quest-sfdev2dev
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Problems with Idle Connections

victor.savkin
Hi Nick,

Thanks for your reply. You are right, last time I didn't restart my server after changing "connectionTimeoutMillis".  After I restarted the database server it started closing idle sessions. However, I was experimenting a bit more and I have one question:

1. I set my connectionTimeoutMillis to 60 seconds. Then I ran my app several times each time leaving an open connection. Each time it took about 90 seconds for luciddb to release my connection.
2. I set my connectionTimeoutMillis to 1 second. Then I ran my app several times each time leaving an open connection. Each time it took about 150 seconds for luciddb to release my connection.

I'm not sure why it's happening. Why setting connectionTimeoutMillis to 1 second makes luciddb to keep an idle connection for more than 2 minutes.  It's not a problem, it's just a bit confusing.

One more time, thank you for your prompt reply.

Victor
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Problems with Idle Connections

ngoodman
My best guess: vJDBC (which is our client server protocol for remote connections) does have a "ping" over HTTP to keep it open.  My best guess (I haven't looked into this code for ages so it's a guess) is that vJDBC might have it's own timer thread that runs to mark connections as stale (and after we get the vJDBC notification is when we mark it as unused).

I'm not sure we've ever noticed this as typically the connection time out for most BI / DW workloads is usually 20, 30, 60 min - ish.  If the vJDBC timer is 30 or 60 seconds itself this could make a big difference (as you noted) in smaller times.

Nick

On Jun 1, 2011, at 1:39 PM, victor.savkin wrote:

> I'm not sure why it's happening. Why setting connectionTimeoutMillis to 1
> second makes luciddb to keep an idle connection for more than 2 minutes.
> It's not a problem, it's just a bit confusing.


------------------------------------------------------------------------------
Simplify data backup and recovery for your virtual environment with vRanger.
Installation's a snap, and flexible recovery options mean your data is safe,
secure and there when you need it. Data protection magic?
Nope - It's vRanger. Get your free trial download today.
http://p.sf.net/sfu/quest-sfdev2dev
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Problems with Idle Connections

victor.savkin
Thanks for your response Nick.

Victor
Loading...