July 25, 2008 in Feedback
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:
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) . ')' ) );
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...
Because you changed it into private topics?? o:)
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.
[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.
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. :)
No wonder deleting PMs take a long time on my large board.
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?
This topic is now archived and is closed to further replies.
403 - Forbiddeen
Started 2 hours ago
Started 2 hours ago
Started Thursday at 01:08 PM