mysql crashing during sort (out of memory)

This is an archive of the phpBB 2.0.x support forum. Support for phpBB2 has now ended.
Forum rules
Following phpBB2's EoL, this forum is now archived for reference purposes only.
Please see the following announcement for more information: viewtopic.php?f=14&t=1385785
Locked
Gav
Registered User
Posts: 7
Joined: Tue Apr 09, 2002 6:33 pm
Location: Berkeley, CA
Contact:

mysql crashing during sort (out of memory)

Post by Gav »

Hello,

A few months ago, we upgraded php to 4.3.10. We immediately began seeing problems with mysql crashing, so we upgraded it to the latest version as well (4.0.22, I believe). Looking at the error log when it crashes, I see the following:

Code: Select all

050228  9:12:23 /usr/local/libexec/mysqld: Out of memory. Restart daemon and try again (needed 6291448 bytes)
050228  9:12:23 /usr/local/libexec/mysqld: Sort aborted
050228  9:12:23 /usr/local/libexec/mysqld: Out of memory. Restart daemon and try again (needed 6291448 bytes)
050228  9:12:23 /usr/local/libexec/mysqld: Sort aborted
This will often repeat as many as 20-30 times. Sometimes this results in a crash (mysqld is running with 99% CPU, but can't connect to database), other times not (presumably because mysqld_safe is doing its job). This occurs on a daily basis, and I'm forced to do a "kill -9" on the mysqld process (mysqld_safe then does its job and starts a new process).

We have tried raising the memory limit for sorts, but it just happens again at a higher number (as you can see, we are now at 6M).

I apologize if this is an inappropriate forum to ask this question, as this seems like more of a mysql problem or a php problem than a phpbb problem, but I'm asking here because I know that it's a phpbb sort that is causing the Out of Memory error, because I'm able to recreate it. I simply do a "search" and enter only the * wildcard character as the word I am searching for. Our phpbb_search_wordmatch tables are now about 175 Megs long.

I'm not sure if this is what's causing the problem, or if any number of searches each day could be responsible, but it seems a good start would be to make it so that you can't search for just "*".

I've tried searching for * on other phpbb forums and haven't seen similar problems, though (on this forum, I'm returned a blank screen). I'm not sure, then, what's different about our situation.

Any guidance in how we might fix this problem, or even properly diagnose it (not convinced it's solely the search function responsible) is appreciated. I would prefer to keep as much of our search capabilities intact as possible.

Thanks.
Lates...

Darren "Gav" Bleuel
Know Nukees (an atomic comic)
Keenspot Comics (a Spot that is Keen)
3d0
Registered User
Posts: 56
Joined: Mon Dec 20, 2004 8:14 pm
Location: Greenfield, In
Contact:

Post by 3d0 »

It's mostly likely that the database overloaded from too much info. being put in, from reading the logs.
An Phpbb Support Dude--PM me for help.
Join the Clan:http://www.thedaetclan.tk

Now Testing:Phpbb 3.0 Beta
Gav
Registered User
Posts: 7
Joined: Tue Apr 09, 2002 6:33 pm
Location: Berkeley, CA
Contact:

Post by Gav »

Well, yes, that's somewhat obvious. There are queries going on that use too much memory (searching for "*" for instance, but there may be others). The question then is, how can I fix it?

Or alternatively, does anyone know why mysqld_safe is not always doing its job of restarting mysqld when it crashes and how to fix that?
Lates...

Darren "Gav" Bleuel
Know Nukees (an atomic comic)
Keenspot Comics (a Spot that is Keen)
Locked

Return to “2.0.x Support Forum”