473,847 Members | 1,445 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Performance issues with frequently closing/opening mysql connections?

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
"activiteit en". 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.

Dec 2 '06 #1
7 3262
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**@martienva nwanrooij.nlsch reef in bericht
news:47******** *************** ****@news.chell o.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
"activiteit en". 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.

Dec 2 '06 #2
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
Dec 2 '06 #3

"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

Dec 2 '06 #4
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
Dec 2 '06 #5

"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 :)
Dec 2 '06 #6
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
Dec 2 '06 #7

"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.

Dec 3 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2219
by: Gregory.Spencer | last post by:
Hi There, Been working in a PHP / MySQL project I took over code development for. Today when testing new changes suddenly started getting "Failed to connect to the database" errors. When logged into phpmyadmin it said: Error
16
2678
by: David W. Fenton | last post by:
http://www.granite.ab.ca/access/performancefaq.htm I hope Tony doesn't mind my opening a discussion of some issues on his performance FAQ page here in the newsgroup. This is not meant as criticism, at all, as I am not alleging error. I'm just asking about a couple of things to open up the discussion to see what people have to say about them. 1. BeginTrans/CommitTrans to improve performance: has anyone ever done this? The KB article...
9
2817
by: bluedolphin | last post by:
Hello All: I have been brought onboard to help on a project that had some performance problems last year. I have taken some steps to address the issues in question, but a huge question mark remains. Last year, all of the tables and reports were stored in Access. The database was put online so that end users could access the reports online using Snapshot Viewer. The reports were aggregated on the fly, and the selection criteria...
0
3174
by: Andrew Dowding | last post by:
Hi Everybody, I have been looking at problems with my Windows Forms C# application and it's little Jet 4 (Access) database for the last few days. The Windows Forms app implements a facade and implementation, data abstraction layer. But because each data adapter in the implementation layer has a connection object that opens and closes as needed, I found I got several errors from the Jet engine when there were simultaneous connections to...
4
1309
by: | last post by:
I was just wondering what other people's opinions and experiences were in regards to using Database Connections throughout a website. Supposing that a single webpage accesses a database anywhere from 5-30 times throughout a webpage do you prefer to open the database connection upon say initialization and reuse it elsewhere whenever possible and then dispose of it upon disposing or do you write 5 - 10 lines of code opening and closing it...
7
2440
by: Rich Denis | last post by:
Hello, I have been trying to solve a mysterious memory leak problem and was hoping that you could help me out on my stuck point. First a bit of background. We have two app servers in an app center cluster that are each running a number of web applications. For some reason one of the boxes' asp_wp process keeps recylcing on us. It seems that the memory just grows and grows and then recycles on us. I cam across and have read the...
3
1500
by: KWilliams | last post by:
I'd like to get some good advice about our old ASP site. You can see our home page at: http://www.douglas-county.com/ ....and an example application page at: http://www.douglas-county.com/employment/currentopenings2.asp Our old site uses classic ASP with JavaScript syntax. I'm in the process of developing a new site that uses XML/XSLT/CSS/ASP.NET/VB.NET, but in the meantime, we still have our old site up. It contains a lot of...
1
13693
by: marcfischman | last post by:
Please help. I have a website running on a linux/apache/mysql/php server. I receive about 8,000-10,000 visitors a day with about 200,000 to 300,000 page views. The server is a RedHat Linux server running PHP 5.x, MySQL 5.x, Apache 2.x We have been suffering from a number of performance issues. Our hosting company has set our max connections to 100, and we are using persistent connections in PHP. At times the mysqld process takes 100%...
6
1473
by: Andrea A | last post by:
Hi all, I have a question: what do you think about performance (only performance not style, functionality, and so on) comparing PHP 4 and PHP 5. Which version is better if one want to develop a small website with a lot lot of page views (all pages are dynamic)? thks
0
9892
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9734
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10991
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
7061
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5725
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5915
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4540
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4129
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3168
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.