DB Connection Handles

[boanrb]

Hi Friends,

I am using VFP9 against MySQL database. I use SPT for data processing. I have to settle on one of the options below in managing connections to the database.

1.) Create a connection to the DB and keep it alive till the user logs out, then close it.
2.) Create a connection to the DB only when a transaction is ready for processing, then close it immediately after commit.

Bearing in mind the concurrency issues (around 50 users on DB), and time needed to create a connection, which of the two would be the most practical solution?

What is the practice in the community?

Thanks in advance.

Benson

 

[JRB-Bldr]

Personally, I establish a connection when I need it and then close it as soon as I can when no longer needed.

Any single connection will likely be in effect through multiple transactions, but no longer than is necessary.

I do this in order to minimize the possibility that network 'hiccups', etc. might cause a more persistent connection to be 'broken' and then not be useable later when needed.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Personally, I create a connection to the DB and keep it alive till the user logs out, then close it.

That's because I think the overhead of creating the connection outweighs the cost of keeping it open. But my decision is based on Microsoft SQL Server. The trade-off might be different for MySQL, and only someone who has experience of that DB can give you a good answer.

JRB made a good point about network hiccups. My generic SPT class has the ability to check the connection, to re-connect if the connection fails for any reason (often as the result of time-out), to re-try any failed querie, and so on. These are all useful things to do in any SPT-based app.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

I do as Mike, because the framework I use does so.

If it would be MSSQL, there is a thing called connection pooling, server side, which keeps connections alive for a while after a client disconnects. The next client (or the same reconnecting) then get's this connection handle. This has been a problem for me even way back in the year 2000 in a classic ASP Intranet application, which connected to SQL Server with each page request. There was a connection pool overflow. I think this issue is solved and I think any database server today will have a similar working pooling strategy to allow faster reconnects. It pays even from the point of view of the database server alone. There are some resources reserved with a connection, like a socket, so it pays to keep this alive for a while and reuse it for the next client.

In your situation of 50 concurrent users, I would even more so close connection, at least I'd set up a load test with 50-100 virtual concurrent users and see how much time reconnects really cost. If that is much, you may not disconnect or only when you know a user would become idle for a while, reading/browsing through all the data you just sent/retrieved. MySQL has no license model of CALs, as far as I know, so it may not be a problem from that perspective, to keep connections open. Then all you need is a graceful handling of broken connections with reconnects. You can assume that an idle connection doesn't cost much, not in terms of cpu time ram or other resources, reducing the throughput for active users. If there would be a connection limit, that's another story. If there are too many concurrently active users, that's a problem you'd never solve with disconnects, then that's rather a problem to solve with a cluster of MySQL servers.

Bye, Olaf.

 

[boanrb]

Hi Friends,

I highly appreciate all the comments posted above. I think I now have all the information I need to determine my next course of action. I will do some tests, gather some metrics, then proceed on.

Once again Olaf, Mike and JRB-Bldr, I appreciate your notes on this, THANKS so much.

Benson