mysql connections and memory practices

[tpl41803]

Hi all

 

I hope I'm posting this in the right place --

 

I've been looking around on these forms and others and I can't seem to find the answer to my question.

 

I have no real training in php / mysql / etc... Everything I know I have learned through online guides and these forms

 

SO--what, generally, are some good practices for saving memory.

 

I have put together a site that relies heavily on mysql queries -- I may have 10-15 on a single page.

 

I am becoming increasingly worried about the amount of memory and connections I have opened and I am not entirely sure how this works.

 

For example, if I run a query, is it absolutely necessary for me to free the result at the conclusion of the script?

 

I am somewhat under the impression that once the script is executed the result is automatically freed and the connection is closed, but then I am not entirely certain of this.

 

Today I had an issue with my site where every page was loading a "the connection has been reset" error -- I encountered this error when attempting to access the filemanager also.

 

Is this because I have not been freeing my results and closing my connections?

 

Any advice / help greatly appreciated

 

Thnanks

[Mchl]

In general you do not have to close these connections, but it is recommended to do so. Part of the reasoning is that doing so explicitly will allow for faster freeing of resources.

Another thing to watch for, are persistent conncections. Unless you actually do know you want to use them, avoid them.

 

Also try to have only one mysql connection open throught your entire script. There's no point in opening a new one for each query.

 

Last but not least, perhaps some of your queries can be rewritten, so that more of data processing is done on MySQL's side. This may allow for less queries being send to MySQL, less data retrieved from database and overall better performance.

[tpl41803]

thanks for the quick response

 

i've gone through my pages and removed a few extra connections that I didn't realize were unnecessary and although you say it's not entirely necessary I added a mysql_close() at the end of the connections that I am using.

 

I'm not sure what you mean by rewriting the queries so that more of the data processing can be done on mysql's side though.

 

My normal query will look something like this:

 

$result = mysql_query("SELECT * FROM $table $where");
     if (!$result) {
         die("Query to show fields from table failed");
     }

 

I'd appreciate it if someone could provide me with a quick example of how to rewrite this to save memory

 

Thanks again!

[andrewgauger]

well the star is a particularly nasty bugger with memory starvation.  You should explicitly set what rows are to be returned.  I use an object for every type of data interaction so with a call to new DataObjectDefinition(id) it will query the table and create an object.  This is a wonderful way to handle the interaction.  The thing is I don't use a generic "SELECT * FROM $table WHERE $where" function.  You need to generate a "SELECT id, name, location FROM user WHERE $condition" command for every potential.  Also, don't query the location if it isn't needed for what you are doing.  Offload PHP processing into MySQL is best explained with an example:

 

instead of "Select * from myTable desc"

$id=myfetch_assoc ...

 

you just do a "SELECT * FROM mytable desc limit 1"

 

OR instead of collecting a bunch of values from MySQL and adding them together to divide, use the MySQL AVG command.  Generally when you start to get concerned with resource starvation in MySQL you already have a hefty application.  If this is the case, you are just going to have to look at each of the commands you issue to MySQL and make sure that each of them are needed.  There is no magic fix like "SELECT * FROM table REALLY_QUICK"

[Mchl]

As far as query rewriting is concerned take a closer look on JOINs.

 

 

[tpl41803]

well the star is a particularly nasty bugger with memory starvation.  You should explicitly set what rows are to be returned.  I use an object for every type of data interaction so with a call to new DataObjectDefinition(id) it will query the table and create an object.  This is a wonderful way to handle the interaction.  The thing is I don't use a generic "SELECT * FROM $table WHERE $where" function.  You need to generate a "SELECT id, name, location FROM user WHERE $condition" command for every potential.  Also, don't query the location if it isn't needed for what you are doing.  Offload PHP processing into MySQL is best explained with an example:

 

instead of "Select * from myTable desc"

$id=myfetch_assoc ...

 

you just do a "SELECT * FROM mytable desc limit 1"

 

OR instead of collecting a bunch of values from MySQL and adding them together to divide, use the MySQL AVG command.  Generally when you start to get concerned with resource starvation in MySQL you already have a hefty application.  If this is the case, you are just going to have to look at each of the commands you issue to MySQL and make sure that each of them are needed.  There is no magic fix like "SELECT * FROM table REALLY_QUICK"

 

I'm not sure I completely follow you. I follow the * part and I'm going to rewrite the majority of my queries. There are a great many instances where I am using a * and actually only looking for one row of info. So there's some freed memory right there.

 

I'm not sure I'm following the rest of your reply though.

 

By "not necessary to query the location" do you mean the FROM $table isn't necessary?

 

 

And I have looked into joins in the past but never successfully wrote a join that worked. I think the concept of joins as a whole just eludes me.

[andrewgauger]

I was saying if you normally are doing a " SELECT id, name, location " when all you need for a function is an id and a name, go ahead and customize the query for that.  If MySQL is acting really unresponsive there may be a bigger problem too--something just eliminating a few fields wont touch.  Are you running excessively long queries for something simple?

[tpl41803]

I don't believe so--

 

the only issue I can really see is that I am using *'s for nearly all my queries, even if I am only looking for one row.

 

is FROM $table not necessary? I was under the impression that that was necessary on each query or else the script won't know what's going on.

 

 

[andrewgauger]

Table definition is necessary, sorry for confusing you.

[tpl41803]

i think i have most of my issue figured out, thanks so much for your help

 

i'm sure ill be back with a problem sooner or later -- it's all in practice i suppose

 

thanks again