I am curious about how applications connect to databases.
For a PHP website, do you write mysql_connect for every page and
apparently request. And then call mysql_close on the same page
after the work is done. But I think this will increase the load
on the server and is less reliable.

Or do you give connection to the user when the session starts
and cleanup the connection when the user session ends. What if the
user does not end the session and just exits the browser? Will this
leave the db connection open?

The other possibilities exist like application crash and powercut.
Does it leave any stray connections to the database?

Any suggestions.

That's the way you typically do it, yes. I've never heard of it causing problems with either load or reliability.

It certainly won't be a problem on a single-server PHP website.

Keep in mind that with PHP, a new instance of the PHP interpreter is launched for each web request, and then closed when the request is served.

For a PHP website? The only to make that work would be to write a Java or Silverlight plugin to connect to the database. Web browsers don't support OBDC.

You'd typically clean up resources (such as open connections) when the application exits. I *assume* both Java and Silverlight allow you to detect if the browser is being closed. If not, then yes, you'd have a resource leak.

Another problem with this approach that it's not scalable. Connections (i.e. sockets) are a finite resource, and they can be used up. FTP servers, which also deal with clients with persistent connections, have the same problem.

(You could use web sockets too, but I don't think that's something you're going to go for. See: http://socketo.me/)

Another possibility is to use the connection pool pattern. Here's the Wikipedia page: http://en.wikipedia.org/wiki/Connection_pooling

SQLAlchemy, an ORM for Python, uses connection pooling. Here's the relevant part of its developer docs:

http://sqlalchemy.readthedocs.org/en...e/pooling.html

Yes, and dealing with that is a database administration issue. Google for, oh, "mysql time out idle connections" without the quotes.

1 members found this post helpful.

The HTTP protocol is stateless. You are given a request for a page, you serve that page up, and then you "fuhgeddaboudit." So, a typical CGI script does, in fact, connect to the database each and every time.

"Sessions" are a concept that is unrelated to database-connections, although a database is typically used to store session data. The objective is to, since HTTP is inherently stateless, provide a way to remember information from one page-request to the next. So, the CGI page connects to the database, retrieves the session data from it, generates the page, closes the database connection again and serves up the page.

1 members found this post helpful.

I realized later what you were asking here. Anyway:

http://stackoverflow.com/q/6078843

Short answer is that it's impossible.

I thought writing mysql_connect and mysql_close on each page would produce load
on the server when around 1000 users accessing the application at the same time.

So I wanted to know it is normally done. Thanks for clearing the things.

It doesn't work exactly one-to-one. Optimization is done in the connection level. So as others said, it's not an issue.

However for large commercial application databases that have to correctly handle "multi statement transactions" it is necessary to keep the connection open across pages since closing the connection does an implied COMMIT WORK.

ON BALANCE, DESPITE THE POSSIBLE NEED FOR MORE MEMORY, I WOULD GO FOR A SINGLE CONNECTION (NO CLOSE IN EVERY PAGE) JUST TO ENSURE DATA ACCURACY. BELIEVE ME, IT'S MORE DIFFICULT TO CORRECT ERRORS ARISING OUT OF INCONSISTENT DATA THAN DOING IT RIGHT FIRST TIME EVEN IF IT SUPPOSEDLY TAKES MORE MEMORY.

OK

1 members found this post helpful.

Wouldn't having a persistent connection for each user be even more expensive, once you take memory usage into account?