Persistent, non-persistent or idle-closing MySQL connection?

[TheBigB]

Hi,

I have this application that can produce up to 100 queries per minute, but most of the times is idle.
It seems to me that reopening the connection for each query is rather inefficient, so I'm inclined to use a persistent connection.

But suppose the application would be used on a bigger scale.
Won't the server at a certain point overflow with all the persistent connections?

Would it be an idea to use some sort of idle monitor that detects inactivity and closes the connection?
What would be the best design for such a thing be?
I was thinking the query handler could set a timestamp for the last execution and a timer or a background thread would check whether a certain time passed every few seconds.

Suggestions?

Thanks

[MarMan]

I like the timer idea. After every query you can start the timer. If the timer is already running, just restart it. If the timer runs then whatever time you decided has passed then close the connection.

[jmcilhinney]

ADO.NET uses connection pooling, so what you're suggesting is basically what happens under the covers anyway. An ADO.NET connection object is very lightweight. The actual database connection is handled at a lower level. That is why, when using ADO.NET, you should open a connection as late as possible and close it as early as possible. Only keep a connection open if you have multiple data access operations to perform immediately. Don't worry about what you might have to do in the future.

[TheBigB]

I like the timer idea. After every query you can start the timer. If the timer is already running, just restart it. If the timer runs then whatever time you decided has passed then close the connection.

That's actually even simpler than what I had in mind

ADO.NET uses connection pooling, so what you're suggesting is basically what happens under the covers anyway. An ADO.NET connection object is very lightweight. The actual database connection is handled at a lower level. That is why, when using ADO.NET, you should open a connection as late as possible and close it as early as possible. Only keep a connection open if you have multiple data access operations to perform immediately. Don't worry about what you might have to do in the future.

Didn't know that. Thanks.

I guess I'll go for the non-persistent then.

[TheBigB]

A completely different application now, working with a SQLite database.
Does that change the situation?

(I'm using the Mono SQLite library here, if it makes any difference)

[jmcilhinney]

I don't know. Never used SQLite, never used Mono. Connection pooling may or may not be used.