Page 1 of 1

MySQL memory usage

Posted: Thu Jan 04, 2007 12:41 pm
by dirkvu01
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hello,

Is there a way to find out how much memory an sql query uses?

example php code:

Code: Select all

$query1 = "SELECT * FROM $table WHERE field = '$var' ORDER BY f1, f2 LIMIT 0, $n";
 $sql1 = mysql_query($query1, $handledb);
 while($row1 = mysql_fetch_array($sql1)){
 ...
 }
 mysql_free_result($sql1);

Queries do use a lot of memory and can cause the server to crash:-(
Are there other ways to get/insert/update data from a database?

Any other tips to reduce memory usage are welcome;-)

Thanks,
Dirk


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Fri Jan 05, 2007 11:23 am
by aaronhall
I'm not sure how you would find the memory usage per query, but this article covers many query and database optimization techniques.

In your example, selecting only the columns you need instead of (*) will cut down the seek/response time and size of the result.

Posted: Fri Jan 05, 2007 10:45 pm
by volka
mysql_query() copies the whole resultset into the memory space of the php process at once. If the result contains many records you might want to consider using mysql_unbuffered_query

There should be indices on field, f1 and f2 so mysql does not have to scan all the records. see http://dev.mysql.com/doc/refman/5.0/en/explain.html

Posted: Mon Jan 15, 2007 11:04 am
by dirkvu01
Hi again,

Thanks for the tips. We could use them :lol:

I've also found the following. If you have root access to your server you can try the command: cat /proc/user_beancounters
This gives you some list with lots of things I don't understand :? but I do know that if the column failcnt is giving non zero values, it's not good.

Who knows where to find some more info about this?

Thanks,
Dirk

Posted: Mon Jan 15, 2007 11:06 am
by dirkvu01
Sorry about my previous message. It's not about databases anymore :oops: