MySQL memory usage

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
dirkvu01
Forum Newbie
Posts: 3
Joined: Thu Jan 04, 2007 10:50 am

MySQL memory usage

Post 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]
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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
dirkvu01
Forum Newbie
Posts: 3
Joined: Thu Jan 04, 2007 10:50 am

Post 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
dirkvu01
Forum Newbie
Posts: 3
Joined: Thu Jan 04, 2007 10:50 am

Post by dirkvu01 »

Sorry about my previous message. It's not about databases anymore :oops:
Post Reply