Long-term database connections vs. short-lived connections

[cybervegan]

Hi, I'm a bit new to MySQL; here's a philisophical question.

I'm working on getting my head round apache2+mod_python+mysql for web applications. It all seems pretty straight-forward, and I'm making great progress so far.

However, I'd like to know your opinions on whether it is better to open a database connection once, globally, at the beginning of each module, and keep it open "forever" or to do a "drive by query"; i.e. open/query/close every time.

The way mod_python works, is that a python process is started for each apache server thread, and this is run "forever" - until the server is shut down/restarted. If I choose to open the connection once only at the head of each module, the connections could become broken (due to timeout/loss of network connection/whatever) and cause a crash. On the other hand, if I go the "drive by" route, and the site becomes very busy (theoretically, of course) this could result in a large number of connection opens and closes, which could be computationally expensive.

I suppose a mid-ground, "managed persistent connection" may solve this but the code complexity is probably worse.

Thanks for any input!

regards,
-cybervegan

[Atli]

Hi.

Given the stateless nature of the web, would it really be a good idea to keep the connection open forever?

I'm not really understanding how this would work either. Would this single connection be serving multiple clients, or would a new "eternal" connection be made for each client?

If it is the former, would that not be risking performance hits, as the single connection would most likely have to complete a task for one client before it could start one for a second client. If the server became busy, would it not risk forming huge queues of pending queries?

If it is the latter, would it not still be hurting performance in the long run, as each "eternal" connection would be occupying a connection to the server, even tho the user may be long gone?

In the long run, simply opening a connection on each request and closing it when the request has been served sounds to me a more stable method.

If that is causing problems, a mid-level database connection management layer may be a good idea.
Some software to manage the connections, re-use connections for multiple requests and open new once if the load becomes to heavy.

... which is probably just about the same thing you said... o well :P

[cybervegan]

Hi.

Given the stateless nature of the web, would it really be a good idea to keep the connection open forever?

That's exactly my point. I'm breaking the app down into modules which will provide functions for working with each table within the database - providing centralised queries, updates and deletes, so that the code will remain easy to maintain.

I'm aware that database connections can go stale for many reasons, but also that constantly opening new connections and closing them again after only one operation could be expensive for the db server.

I'm not really understanding how this would work either. Would this single connection be serving multiple clients, or would a new "eternal" connection be made for each client?

Apache starts a new thread for each new concurrent http connection, up to a point, and re-uses these threads as old clients finish and new clients start. With mod_python, each thread gets its own python interpreter, which continues to run indefinitely; any modules loaded stay loaded until web-server shutdown.

If it is the former, would that not be risking performance hits, as the single connection would most likely have to complete a task for one client before it could start one for a second client. If the server became busy, would it not risk forming huge queues of pending queries?

As there's one for each web-server thread, and these are re-used for new connections, only slightly so - queuing shouldn't occur much except under heavy load. At this point i'd have to start thinking about farming the basic components off onto separate, possibly clustered servers for dynamic web, static web and db functions. Whereas this is just an exercise in learning, I want to code it so it *could* cope with, or at least be scalable...

If it is the latter, would it not still be hurting performance in the long run, as each "eternal" connection would be occupying a connection to the server, even tho the user may be long gone?

In the long run, simply opening a connection on each request and closing it when the request has been served sounds to me a more stable method.

If that is causing problems, a mid-level database connection management layer may be a good idea.

I think what I will do is put in a timer or counter from which I will determine if I should open a new connection or not, within a marshalling method wrapping the "connect" function. That way I can avoid the perils of stale connections and also those of thrashing the db server with "meelions" of short-lived connections.

Some software to manage the connections, re-use connections for multiple requests and open new once if the load becomes to heavy.

... which is probably just about the same thing you said... o well :P

Thanx for helping me make up my mind ;-)

regards,
-cybervegan