Slow query for PM deletion on large boards

[Brain Sparks]

If you have a board that is storing a large number of personal messages (mine has over 300k messages) then deleting a PM will often take a surprisingly long time for what would apparently be a very simple action.

On my site, which is a pretty decent dedicated Linux box (4 GB RAM, 2 x 2.2 GHz Opteron 175 CPU), the deletion of a single PM can often take 15 seconds or more.

This problem was mentioned previously on invisionpower.com here: Slow Query on IPB 2.2.1 (topic closed without discussion)

It has also been encounted by another large forum and their solution was to delete all old PMs: All PMs older than December 2006 deleted

Of course deleting all old PMs, or limiting the number of PMs available to members, is not really a solution to the problem. In software you don't fix a problem by disabling the feature that causes it.

The problem query is this one:



Any thoughts?

select msg_id from ipb_message_text where msg_deleted_count >= msg_sent_to_count

11 seconds, rows sent 1, rows examined: 339984

This is located in sources/lib/func_msg.php at line 660 (in IPB v. 2.3.5) within the delete_messages() function:

$this->ipsclass->DB->simple_construct( array( 'select' => 'msg_id', 'from' => 'message_text', 'where' => 'msg_deleted_count >= msg_sent_to_count' ) );

The problem appears to be that the code is examining the entire message text table.


This could possibly be speeded up by modifying the code to add a second where clause as follows:

$this->ipsclass->DB->simple_construct( array( 'select' => 'msg_id', 'from' => 'message_text', 'where' => 'msg_deleted_count >= msg_sent_to_count AND msg_id IN (' . implode(',',$final_ids) . ')' ) );

[bfarber]

I know exactly which query you are referring to (we see it pop up in slow query logs even on moderate-sized boards). I don't think your fix includes the overall exact functionality, but is something I'll look into. :)

In all honesty, a huge bulk of the messenger was already rewritten, so the query may not even be there anymore...

[Brandon D]

Because you changed it into private topics?? o:)

[Luke]

This could possibly be speeded up by modifying the code to add a second where clause as follows:

$this->ipsclass->DB->simple_construct( array( 'select' => 'msg_id', 'from' => 'message_text', 'where' => 'msg_deleted_count >= msg_sent_to_count AND msg_id IN (' . implode(',',$final_ids) . ')' ) );

Any thoughts?

Your solution is acceptable, however, the purpose behind the routine under:

//-----------------------------------------
// Run through and delete dead msgs
//-----------------------------------------

.. is to flush out all message_text entires after they no longer have a linking message_topic. If this is causing a problem, realistically you could take out that entire block and move it into it's own function and run it on a daily task rather than having it occur on every delete action. The reason being that after a topic is deleted, it can't be seen anyway and it doesnt have to be deleted immediately.

Then again with the way msg_deleted_count works, if you deleted a topic manually through phpMyAdmin, that part of the code wouldn't pick it up anyway, thus defeating the purpose of it grabbing "dead" PM's. If put on a task, you'd have to check the topics table instead.

[Brain Sparks]

Regarding the problem likely disappearing altogether in version 3.0, yes, I thought that might be likely.

In the meantime, I think Luke's suggestion is a good one. I'll look into moving the dead msg deletion to a separate daily task.

Thanks guys.

[Brain Sparks]

[quote name='bfarber' post='1741069' date='Jul 25 2008, 04:48 PM']we see it pop up in slow query logs
The other slow query I get is search_get_all_user() for getting all posts from a user. This can be even slower than the PM deletion. But that's a different topic I guess. Plus there's no easy way to fix a search through well over a million posts, as far as I know.

[wickedfpoop]

That's actually why the About Me page was introduced in 2.3.5. Basically for when someone went to another user's profile, that was being executed and would cause massive slow downs. With the About Me page, of course, that gets diverted but with a function that calls members posts/topics still in there, well yea, over a million posts, there's always that. :)

[TCWT]

No wonder deleting PMs take a long time on my large board.

[James Ford]

Despite the different username, I'm actually the same person as "Brain Sparks" above (access to my original account somehow got lost).

Anyway, two and a half years later, a new version of the software (IPB 3) and upgraded hardware, and unfortunately we're still having problems with this issue.

Deletion of PMs always locks up our server badly.

Anyone else suffering this? Anyone have a fix?