DragonBlade
12-30-2009, 02:47 PM
Lately, after upgrading my server and tweaking it, MySQL's been choking on something I would think is a very simple, straight-forward query. The query comes from includes/functions_newpost.php on line 382:
$getfirstpost = $vbulletin->db->query_first("SELECT postid FROM " . TABLE_PREFIX . "post WHERE threadid=$threadinfo[threadid] ORDER BY dateline LIMIT 1");
For some reason, quite often (but not always) this query is taking a very long time. My users report (with varying accuracy) that this tends to happen in larger threads more often (though I personally think that is only because the larger threads are visited more, so of course people would notice it there more).
My MyISAM Index space is just over 500 MB, and I've got my key_buffer_size set to 640M, so I'm not convinced the answer is "throw more memory at it." My post table itself has 4 million entries and is 1.3GB in size, though, if anyone wants to argue differently. XP
Anyways, has anyone else experienced this? Does anyone know if I should be looking at a particular value? I'm not sure if it's different by default or if the previous Admins set it this way, but I don't have a threadid index by itself--only an index of threadid AND userid. (There is a separate index for just userid.) Could it possibly solve my problem if I put an index on threadid?
Thanks everyone, sorry for being such a newb.
--------------- Added 1262205545 at 1262205545 ---------------
Well, it seems my users were right--it -does- seem to be in the larger threads only. XP
It also seems to happen only when using Quick Replies.
And I -think- (though am not sure) that it only happens to those who are "Mods" of the forum in which they are posting.
If anyone could shed some light for me, I'd be grateful.
--------------- Added 1262231966 at 1262231966 ---------------
A little more light to shed on the situation, if anyone can help...
This seems to also be occurring with "Quick" Edits... What with the Quick Reply and the Quick Edits, could something be screwed up with my AJAX that's causing my MySQL to get ornery? It's vBulletin 3.8.4, Patch Level 2. I do have several plugins, but the queries in my slow logs are from the vBulletin scripts, not any of the plugins. One plugin we use is AJAX-heavy AND out-of-date; it's called iSpy and let's you view new posts on the forum as they are made (we allow only our Donators access to it). I don't know the guts of it, so I don't know if it calls includes like functions_newpost.php, or if it has a very similar query to those in functions_newpost.php and other includes, or if it has nothing to do with the problem at all.
I'd like to get some advice from you guys before I decide to shut it off for a few days, because it's something we like to make sure our donators have, to show our appreciation. (Of course, I'm certain they'd much rather have a stable forum, heh.)
$getfirstpost = $vbulletin->db->query_first("SELECT postid FROM " . TABLE_PREFIX . "post WHERE threadid=$threadinfo[threadid] ORDER BY dateline LIMIT 1");
For some reason, quite often (but not always) this query is taking a very long time. My users report (with varying accuracy) that this tends to happen in larger threads more often (though I personally think that is only because the larger threads are visited more, so of course people would notice it there more).
My MyISAM Index space is just over 500 MB, and I've got my key_buffer_size set to 640M, so I'm not convinced the answer is "throw more memory at it." My post table itself has 4 million entries and is 1.3GB in size, though, if anyone wants to argue differently. XP
Anyways, has anyone else experienced this? Does anyone know if I should be looking at a particular value? I'm not sure if it's different by default or if the previous Admins set it this way, but I don't have a threadid index by itself--only an index of threadid AND userid. (There is a separate index for just userid.) Could it possibly solve my problem if I put an index on threadid?
Thanks everyone, sorry for being such a newb.
--------------- Added 1262205545 at 1262205545 ---------------
Well, it seems my users were right--it -does- seem to be in the larger threads only. XP
It also seems to happen only when using Quick Replies.
And I -think- (though am not sure) that it only happens to those who are "Mods" of the forum in which they are posting.
If anyone could shed some light for me, I'd be grateful.
--------------- Added 1262231966 at 1262231966 ---------------
A little more light to shed on the situation, if anyone can help...
This seems to also be occurring with "Quick" Edits... What with the Quick Reply and the Quick Edits, could something be screwed up with my AJAX that's causing my MySQL to get ornery? It's vBulletin 3.8.4, Patch Level 2. I do have several plugins, but the queries in my slow logs are from the vBulletin scripts, not any of the plugins. One plugin we use is AJAX-heavy AND out-of-date; it's called iSpy and let's you view new posts on the forum as they are made (we allow only our Donators access to it). I don't know the guts of it, so I don't know if it calls includes like functions_newpost.php, or if it has a very similar query to those in functions_newpost.php and other includes, or if it has nothing to do with the problem at all.
I'd like to get some advice from you guys before I decide to shut it off for a few days, because it's something we like to make sure our donators have, to show our appreciation. (Of course, I'm certain they'd much rather have a stable forum, heh.)