Expensive query?

[@rjen]

Working on a mod for SMF and I have it working locally, but on another server the mod results in this php error

PHP Fatal error:  Allowed memory size of 100663296 bytes exhausted (tried to allocate 71093376 bytes)

I think this query is a bit heavy. Perhaps it can be written better not to be such a resource hog , but I do not know how to improve this.

Anyone have any tips to improve/ optimize this?

Code Select Expand

$topics_to_purge = array();
if(count($boards_to_purge)) {
foreach($boards_to_purge as $board_id) {
$setting = 'autoPurge_' . $board_id;
if($modSettings[$setting] > 9999)
$modSettings[$setting] = 9999;
$timestamp = time() - ($modSettings[$setting] * 86400);
$topicquery = 'SELECT t.id_topic
FROM {db_prefix}topics t
INNER JOIN {db_prefix}messages m ON t.id_last_msg = m.id_msg
WHERE t.id_board = {int:id_board}
AND m.poster_time <= {int:timestamp}';
$topicquery .= (!empty($modSettings['auto_purge_sticky']) && $modSettings['auto_purge_sticky']) ? '' : ' AND t.is_sticky = 0';
$topicquery .= (!empty($modSettings['auto_purge_locked']) && $modSettings['auto_purge_locked']) ? '' : ' AND t.locked = 1';

$result = $smcFunc['db_query']('',
$topicquery,
array(
'id_board' => $board_id,
'timestamp' => $timestamp,
)
);

if($smcFunc['db_num_rows']($result) > 0) {
while($row = $smcFunc['db_fetch_row']($result)) {
$topics_to_purge[] = (int) $row[0];
}
}

$smcFunc['db_free_result']($result);
}
}

I suspect this join may be the cause of the issue...

         $topicquery = 'SELECT t.id_topic
            FROM {db_prefix}topics t
            INNER JOIN {db_prefix}messages m ON t.id_last_msg = m.id_msg
            WHERE t.id_board = {int:id_board}
            AND m.poster_time <= {int:timestamp}';

[Arantor]

So you're doing it a board at a time, and getting a list of topics per board - you're pulling a single column back and feeding that into an array.

How many boards are we talking here? How many potential topics?

This shouldn't be an expensive query in general, it's not returning lots of columns. A million topic IDs coming back might consume a decent chunk of memory at a time (my back-of-the-envelope calculations suggest that you're throwing maybe 700,000 topics at it in one go)

[@rjen]

Well, it is actually failing on a small board, no more then 30-40 topics, so perhaps it is something else that is choking it, but I am not sure what.

I see the memory error but not exactly what causes it...

[Arantor]

Yeah, there's absolutely no way 40 topics should consume 70MB of RAM like that. It's literally querying the IDs, not the topic content, and while there might be some memory usage while it's figuring that out, that doesn't come out of PHP's budget, only the copy of the results when it comes back from MySQL.

Would be interesting to have a call to memory_get_usage() before that loop runs to see how much memory is already in use (not sure if you'll need to force it into a log somewhere with error_log or go hardcore on it with a fatal_error so it can be logged)

[@rjen]

Just took out the join on the messages table, and it finishes in an instant...

Code Select Expand

$topics_to_recycle = array();
if(count($boards_to_recycle)) {
foreach($boards_to_recycle as $board_id) {
$setting = 'autoRecycle_' . $board_id;
$topicquery = 'SELECT t.id_topic
FROM {db_prefix}topics t
WHERE t.id_board = {int:id_board}
AND t.locked = 1';
$topicquery .= (!empty($modSettings['auto_recycle_sticky']) && $modSettings['auto_recycle_sticky']) ? '' : ' AND t.is_sticky = 0';

$result = $smcFunc['db_query']('',
$topicquery,
array(
'id_board' => $board_id,
)
);

if($smcFunc['db_num_rows']($result) > 0) {
while($row = $smcFunc['db_fetch_row']($result)) {
$topics_to_recycle[] = (int) $row[0];
}
}

$smcFunc['db_free_result']($result);
}
}

this is what I set out to do at the start, but I decided to see if I could combine both functions (so also including the last edit) and the error is what i got...

[@rjen]

Whoops...

just ran the query on a board with around 2000 topics, and also this chokes...

PHP Fatal error:  Allowed memory size of 100663296 bytes exhausted (tried to allocate 71093376 bytes) in /home/deb77453/domains/fjr-club.nl/public_html/test/Sources/Subs-Db-mysql.php on line 489\n,

So maybe it is something else that is doing it?

Perhaps it is the next action that is causing it?
(not suitable for large numbers of topics?)

Code Select Expand

if(count($topics_to_recycle)) {
require_once($sourcedir . '/RemoveTopic.php');

// let's do this: remove topics, don't remove post count, do NOT ignore recycling
removeTopics($topics_to_recycle, false, false);
}

[@rjen]

I think that's it: the removeTopics function seems not to like it.

I changed it to remove one by one and now it works...

Code Select Expand

if(count($topics_to_recycle)) {
require_once($sourcedir . '/RemoveTopic.php');

// let's do this: remove topics, don't remove post count, do NOT ignore recycling
foreach($topics_to_recycle as $recycle_id) {
removeTopics($recycle_id, false, false);
}
}

[Arantor]

Yeah that makes a bit more sense.

[dodos26]

Code Select Expand

INNER JOIN {db_prefix}messages m ON t.id_last_msg = m.id_msgTest query times in php my admin. Also, why are you mixing where with inner join? It the same I could be wrong because I learned the secrets of databases recently.

[@rjen]

Not sure what you mean with that. This query is functional, the performance issue was actually after the query and has been resolved..

[Arantor]

Well, the inner join is there to find topics based on the last message in the topic - to find which topics haven't been posted in recently. This is the primary way to find this answer.

The issue isn't even one of query *time*, but memory use and in this case, not the problem at hand.