LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices



Reply
 
Search this Thread
Old 01-12-2007, 02:41 AM   #1
b0uncer
Guru
 
Registered: Aug 2003
Distribution: CentOS, OS X
Posts: 5,131

Rep: Reputation: Disabled
web page database access per page or per session?


As a "newbie" of one kind to dynamic web page creation and database usage, I'd like to know your opinions about this. Many examples I've seen often consist of a single file (for example php file), and thus a database connection is established and closed within the same file. Now usually dynamic web site has more than one file, but let's say I still needed to access a database from different files; would it be sensible to always, within a file (like list_some_items.php), open a database connection and once needed data is fetched, close it; then with another file (like show_some_information.php) do the same open-fetch-close-thing on the database -- or -- would it be better to establish a database connection right after a session is started for the user and remain the connection open and close it only when the user leaves, and session is destroyed?

Like I said, I'm fairly new to these things, as of now working with php and MySQL, so bear me. I'll possibly try other things in the future, but I guess this question is somewhat "common"; is there a huge difference I don't see right away, security problems having the database connection open all the time the user is in the site, maybe efficiency problems? Or is it even possible, or sane, to maintain a db connection during a session?

Thanks for opinions!
 
Old 01-12-2007, 05:31 AM   #2
vladmihaisima
Member
 
Registered: Oct 2002
Location: Delft, Netherlands
Distribution: Gentoo
Posts: 196

Rep: Reputation: 33
As far as I know the open/close process to a database it is kind of slow. I mean it is slow for a couple connections each second - if your site is not traffic intensive you will probably not notice.

The best is to use connection pooling. I do not know how is this implemented in php as I used just java, but the main idea is that the server or somekind of manager opens a bunch of connections to the database and when a 'page' needs a connections it borrows it from the pool. When it doesn't need it anymore it 'release' it and the pool can reuse it.

A quick search on the web revealed this, maybe it is helpfull to you: http://www.apachetutor.org/dev/reslist
 
Old 01-13-2007, 09:56 AM   #3
b0uncer
Guru
 
Registered: Aug 2003
Distribution: CentOS, OS X
Posts: 5,131

Original Poster
Rep: Reputation: Disabled
Thanks for the information and the link, I'll read it; this is probably what I'm after. The site isn't probably going to be too "traffic-intensive", borrowing your words, but I still somehow dislike the idea of having to open&close a separate connection every time a database-needing page is opened.
 
Old 01-13-2007, 10:56 AM   #4
Guttorm
Senior Member
 
Registered: Dec 2003
Location: Trondheim, Norway
Distribution: Debian and Ubuntu
Posts: 1,159

Rep: Reputation: 258Reputation: 258Reputation: 258
Hi

MySql is very fast on opening and closing connections. On a busy site, the problem is usually the number of database connections, not the time it takes. A typical scripts opens a connection, does some queries, outputs html and then closes. To scale things better, you can open the connection, do queries, close the connection and then output html.

The problem is usually slow clients keeping the connections open while getting the html. I think keeping the connection open in the session would just make things worse, and I've never seen LAMP solutions doing it.
 
Old 01-13-2007, 11:28 AM   #5
b0uncer
Guru
 
Registered: Aug 2003
Distribution: CentOS, OS X
Posts: 5,131

Original Poster
Rep: Reputation: Disabled
Quote:
The problem is usually slow clients keeping the connections open while getting the html. I think keeping the connection open in the session would just make things worse, and I've never seen LAMP solutions doing it.
Now this sounds interesting. So you're saying it's ok to have a connection open&close once per php page (when needed), as far as the html code creation etc. are outside this? (actually this is how I always do things, never even thought the other way around)

Well, maybe there won't be speed problems since the site is and probably stays pretty small, including users that visit it..but anyway I have to do some more searching about the speed thing.
 
Old 01-13-2007, 12:08 PM   #6
Guttorm
Senior Member
 
Registered: Dec 2003
Location: Trondheim, Norway
Distribution: Debian and Ubuntu
Posts: 1,159

Rep: Reputation: 258Reputation: 258Reputation: 258
I think most LAMP solutions don't even consider this, they just open the database connection and it gets closed when the script is finished.

Try benchmarking your site with e.g. "ab" (Apache benchmark). I found out, putting a mysql_close() in the right place can help a lot.
 
Old 01-13-2007, 01:09 PM   #7
vladmihaisima
Member
 
Registered: Oct 2002
Location: Delft, Netherlands
Distribution: Gentoo
Posts: 196

Rep: Reputation: 33
I still think there will be a performance improvement if you use connection pooling (if it is less work for the CPU it should be faster). Most of the application do not need that improvement as they run on fast servers/don't have bilions of request per seconds.

You always can test yourself - and if you do, please post the results. I'm also curious what would be the speed difference, but probably not enough to install apache & php.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Quiery database and show results on a web page jlinkels Linux - Software 5 01-04-2007 08:26 AM
my web browser "mozilla fire fox" isn't rendering the page, rather opening the page amolgupta Linux - Software 2 07-26-2005 01:41 AM
cannot access config web-page for adsl router tireseas Linux - Networking 6 03-19-2005 05:13 PM
Allow this particular web page access on port 443 ONLY lothario Linux - Software 2 01-14-2005 11:14 PM
Web page user access Cristian Negres Linux - Newbie 2 01-05-2002 01:43 PM


All times are GMT -5. The time now is 10:23 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration