Performance issues with frequently closing/opening mysql connections?

[Martien van Wanrooij]

I have been faced a couple of times with the situation that I wanted to
write a script and was worried about a too frequent opening and closing
mysql connections.

To give some examples:

1)I am trying to develop a photoalbum (also posted a topic called "array /
mysql question"). Of course I can use some wonderful open source albums but
at the same time I want to get more familiar with all the ins and outs so my
idea is a list of filenames, where the database connection should be
opened, a statement like"select filenames from photos" , and, every filename
maybe with a thumbnail is a link to a second page and the whole is followed
by closing the connection. The second page could be something like opening
the database connection, a statement like "select description ( and other
fields) from photos where filename = $filename" , and closing the database
again. Let's say the visitor looks at the picture and its description,
clicks on a link to the first page with the thumbnails and agin the
connection is opened and closed to execute the first select statement, the
visitor chooses another picture etc. etc....

2)On www.tapperijbeekendonk.nl on the left column there is a menu item
"activiteiten". when there are special activities like a music performancein
the near future they are shown below that menu item. A select statement is
done where the first announcement date, and the last announcement date are
checked and the records where the current date is between those values are
selected (and shown in the menu bar of course). Again the connection is
opened and closed again immediately after the select statement. However,
this query is stored in "header.php" so on every page that is visited, the
connection is opened and closed. It would also be nice to make a link on the
"one-photo-page" to the previous and the next photo, which leads again to
the open-select-close "ritual".

I am not really a php guru (but not a newbie either) and unfortunately I
could not find many info on google about the performance issues that can be
involved when I decide to write such scripts. There were some vague
indications that it is better to open and close a connection frequently than
leave it open too long in order to execute more queries but I could not
really find good documentation about it.

I also thought to store the results of the query into a session variable but
I am not sure about the performance issues of sessions either.

Any ideas will be welcome.

Thanks,

Martien.

[Martien van Wanrooij]

Sorry made a weird error when copying and pasting in my first post. The last
sentence in the paragraph about De Tapperij belongs to example 1 :)
"Martien van Wanrooij" <in**@martienvanwanrooij.nlschreef in bericht
news:47***************************@news.chello.nl. ..
I have been faced a couple of times with the situation that I wanted to
write a script and was worried about a too frequent opening and closing
mysql connections.

To give some examples:

1)I am trying to develop a photoalbum (also posted a topic called "array /
mysql question"). Of course I can use some wonderful open source albums but
at the same time I want to get more familiar with all the ins and outs so my
idea is a list of filenames, where the database connection should be
opened, a statement like"select filenames from photos" , and, every filename
maybe with a thumbnail is a link to a second page and the whole is followed
by closing the connection. The second page could be something like opening
the database connection, a statement like "select description ( and other
fields) from photos where filename = $filename" , and closing the database
again. Let's say the visitor looks at the picture and its description,
clicks on a link to the first page with the thumbnails and agin the
connection is opened and closed to execute the first select statement, the
visitor chooses another picture etc. etc....However, this query is stored in
"header.php" so on every page that is visited, the connection is opened and
closed. It would also be nice to make a link on the "one-photo-page" to the
previous and the next photo, which leads again to the open-select-close
"ritual".

2)On www.tapperijbeekendonk.nl on the left column there is a menu item
"activiteiten". when there are special activities like a music performancein
the near future they are shown below that menu item. A select statement is
done where the first announcement date, and the last announcement date are
checked and the records where the current date is between those values are
selected (and shown in the menu bar of course). Again the connection is
opened and closed again immediately after the select statement.

I am not really a php guru (but not a newbie either) and unfortunately I
could not find many info on google about the performance issues that can be
involved when I decide to write such scripts. There were some vague
indications that it is better to open and close a connection frequently than
leave it open too long in order to execute more queries but I could not
really find good documentation about it.

I also thought to store the results of the query into a session variable
but I am not sure about the performance issues of sessions either.

Any ideas will be welcome.

Thanks,

Martien.

[naixn]

Martien van Wanrooij wrote :

[...]

Your problem is quite a common one. In fact, a request that is done on every
page may cause problem, and even more if not needed.

The idea to solve your "problem" would be to use cache. Instead of displaying
the HTML result, you put it in a file that you'll you include when needed.
If you're using a templating system, I'd suggest you to check wether it is able
to manage caching. Otherwise, you'll have to do your own system, that will
update the HTML cache file every time the data updates.
For example : if your page displays the nearest dates to the next performances,
you'll have to update the HTML cache file every time you add a performance
date.

But there's another solution : you can, on the page that displays the dates,
check the last modification time of the HTML cache file, and if it was
modified before one day ago, you update the cache ( by doing the SQL request ),
and then display the cache file ( since it has been updated... :D ).
That would give you : one SQL request a day. Not so many, eh? :)

--
Naixn
http://fma-fr.net

[Martien van Wanrooij]

"naixn" <na***@won-fma.comschreef in bericht
news:45***********************@nan-newsreader-05.noos.net...

But there's another solution : you can, on the page that displays the
dates,
check the last modification time of the HTML cache file, and if it was
modified before one day ago, you update the cache ( by doing the SQL
request ),
and then display the cache file ( since it has been updated... :D ).

Thanks this is a wonderful suggestion. My hosting provider gives me the
right to give writing access to certain folders and I guess that in rough
lines, caching means: writing data to a file on the server when a request is
made from a client?

That would give you : one SQL request a day. Not so many, eh? :)

Enough info to study the topic seriously, one more question. You say, with
a smiley though, one SQL request a day so it could mean that the very first
visitor tonight at 0:00 generates a SQL request and that request writes
"something" to the caching file , when the second visitor shows up at 0:05,
he get his data from that recently update file? Sorry to ask it in such a
simplified way but with all due respect to all these wonderful cms'es I want
to understand the topic as thoroughly as possible.

Martien

[naixn]

Martien van Wanrooij wrote :

>
"naixn" <na***@won-fma.comschreef in bericht
news:45***********************@nan-newsreader-05.noos.net...

>But there's another solution : you can, on the page that displays the
dates,
check the last modification time of the HTML cache file, and if it was
modified before one day ago, you update the cache ( by doing the SQL
request ),
and then display the cache file ( since it has been updated... :D ).

Thanks this is a wonderful suggestion. My hosting provider gives me the
right to give writing access to certain folders and I guess that in
rough lines, caching means: writing data to a file on the server when a
request is made from a client?

>That would give you : one SQL request a day. Not so many, eh? :)

Enough info to study the topic seriously, one more question. You say,
with a smiley though, one SQL request a day so it could mean that the
very first visitor tonight at 0:00 generates a SQL request and that
request writes "something" to the caching file , when the second visitor
shows up at 0:05, he get his data from that recently update file? Sorry
to ask it in such a simplified way but with all due respect to all these
wonderful cms'es I want to understand the topic as thoroughly as possible.

Martien

Yep that's quite it. To simplify :
You get your site working.
Someone arrives on the page at 10 AM.
There is no file where datas where cached
-Your system queries the database, and write the adequate HTML depending on
the result into a file, as it would usually output.
Your system include the cache file, meaning it outputs the db results.
Another ppl arrives on the page at 11.05 AM.
A cache file exists.
It's modification time is less than one day
No update needed
Include the cache file.
[...]
Some guy arrives on the page at 10.30 AM the day after.
A cache file exists.
But it was modified more than 24h ago.
-Your system query the database...
Include cache file.
etc.

A cache file is no more than a text/html file in which you write the desired
HTML output.

--
Naixn
http://fma-fr.net

[Martien van Wanrooij]

"naixn" <na***@won-fma.comschreef in bericht
news:45***********************@nan-newsreader-07.noos.net...

Yep that's quite it. To simplify :

Thank you Naixn that is really the kind of simplification I needed :)

[naixn]

Martien van Wanrooij wrote :

>
"naixn" <na***@won-fma.comschreef in bericht
news:45***********************@nan-newsreader-07.noos.net...

>Yep that's quite it. To simplify :

Thank you Naixn that is really the kind of simplification I needed :)

You're welcome.
Be aware that there are a lot of templating systems that manage caching.
For example : http://smarty.php.net , an official PHP Project :)

--
Naixn
http://fma-fr.net

[Martien van Wanrooij]

"naixn" <na***@won-fma.comschreef in bericht
news:45***********************@nan-newsreader-05.noos.net...

Be aware that there are a lot of templating systems that manage caching.
For example : http://smarty.php.net , an official PHP Project :)

I will certainly use them as I am not up to re-inventing the wheel but
before using them I like to understand how they work :)
Thx again.