davio33
02-20-2012, 12:54 PM
I have a dedicated server. The memory is constantly crashing. I thought it was from adding too many child forums at once, which would cuase a spike, but even when i stopped the spike would continue. I asked the host to look into it. Here is what they wrote back. Any advice is greatly appreciated. I dont know what he says means. ( too many selects) or what the cure is
I tried adding the child forums a few times, and I didn't see a significant increase in the memory usage while adding the child forums -- that is, until I visited the main page. This leads me to believe that vBulletin does some sort of database caching, and it isn't until a user hits the main page that it performs all these slow queries that consume considerable amounts of memory. Right now I see that the database has tons of SELECTs in it.
As this is third party software, we can provide very little assistance in optimising the application. However, I did find this article with a few things that we can try in it:
http://articles.digitalpoint.com/content.php?r=7-Optimize-vBulletin-4
If you'd like to try any of these (only the ones that apply to your version of vBulletin), then let us know. Otherwise, you may want to contact vBulletin support. Below is a very long paste that I'm going to give you, for your review and for anyone at vBulletin who may help you to review. Let us know if you need any more information. It's just the queries that are currently running in MySQL.
root@ori [/home/orinje/public_html]# mysqladmin pr
+------+-----------------+-----------+--------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-----------------+-----------+--------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 3087 | orinje_sunshine | localhost | orinje_vb4ad | Query | 145 | Locked | SELECT moderator.*, user.username,
IF(user.displaygroupid = 0, user.usergroupid, user.displaygroup |
| 3096 | orinje_sunshine | localhost | orinje_vb4ad | Query | 141 | Locked | SELECT moderator.*, user.username,
IF(user.displaygroupid = 0, user.usergroupid, user.displaygroup |
| 3102 | orinje_sunshine | localhost | orinje_vb4ad | Query | 145 | Locked | SELECT moderator.*, user.username,
IF(user.displaygroupid = 0, user.usergroupid, user.displaygroup |
| 3103 | orinje_sunshine | localhost | orinje_vb4ad | Query | 139 | Locked | SELECT
post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS |
| 3110 | orinje_sunshine | localhost | orinje_vb4ad | Query | 394 | Copying to tmp table | SELECT thread.threadid, thread.title, thread.prefixid, post.attach,
thread.postusername, thread |
| 3112 | orinje_sunshine | localhost | orinje_vb4ad | Query | 377 | Copying to tmp table | SELECT thread.threadid, thread.title, thread.prefixid, post.attach,
thread.postusername, thread |
| 3114 | orinje_sunshine | localhost | orinje_vb4ad | Query | 373 | Copying to tmp table | SELECT thread.threadid, thread.title, thread.prefixid, post.attach,
thread.postusername, thread |
| 3115 | orinje_sunshine | localhost | orinje_vb4ad | Query | 368 | Copying to tmp table | SELECT thread.threadid, thread.title, thread.prefixid, post.attach,
thread.postusername, thread |
| 3116 | orinje_sunshine | localhost | orinje_vb4ad | Query | 224 | Locked | SELECT thread.threadid, thread.title, thread.prefixid, post.attach,
thread.postusername, thread |
| 3131 | orinje_sunshine | localhost | orinje_vb4ad | Query | 225 | Locked | UPDATE orinje_user SET
posts = 0
WHERE userid = 19304 |
| 3132 | orinje_sunshine | localhost | orinje_vb4ad | Query | 325 | Copying to tmp table | SELECT thread.threadid, thread.title, thread.prefixid, post.attach,
thread.postusername, thread |
| 3133 | orinje_sunshine | localhost | orinje_vb4ad | Query | 322 | Copying to tmp table | SELECT thread.threadid, thread.title, thread.prefixid, post.attach,
thread.postusername, thread |
| 3134 | orinje_sunshine | localhost | orinje_vb4ad | Query | 224 | Locked | SELECT thread.threadid, thread.title, thread.prefixid, post.attach,
thread.postusername, thread |
| 3135 | orinje_sunshine | localhost | orinje_vb4ad | Query | 304 | Copying to tmp table | SELECT thread.threadid, thread.title, thread.prefixid, post.attach,
thread.postusername, thread |
| 3139 | orinje_sunshine | localhost | orinje_vb4ad | Query | 272 | Copying to tmp table | SELECT thread.threadid, thread.title, thread.prefixid, post.attach,
thread.postusername, thread |
| 3141 | orinje_sunshine | localhost | orinje_vb4ad | Query | 224 | Locked | SELECT thread.threadid, thread.title, thread.prefixid, post.attach,
thread.postusername, thread |
| 3143 | orinje_sunshine | localhost | orinje_vb4ad | Query | 175 | Locked | UPDATE orinje_thread AS thread,orinje_taggregate_temp_1329742560 AS aggregate
SET thread.views = th |
| 3144 | orinje_sunshine | localhost | orinje_vb4ad | Query | 181 | Locked | SELECT forum.forumid, forum.lastpost, forum.lastposter, forum.lastposterid, forum.lastthread, forum. |
| 3145 | orinje_sunshine | localhost | orinje_vb4ad | Query | 183 | Locked | SELECT moderator.*, user.username,
IF(user.displaygroupid = 0, user.usergroupid, user.displaygroup |
| 3149 | orinje_sunshine | localhost | orinje_vb4ad | Query | 182 | Locked | SELECT moderator.*, user.username,
IF(user.displaygroupid = 0, user.usergroupid, user.displaygroup |
| 3150 | orinje_sunshine | localhost | orinje_vb4ad | Query | 185 | Locked | SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate, user. |
| 3152 | orinje_sunshine | localhost | orinje_vb4ad | Query | 181 | Locked | SELECT moderator.*, user.username,
IF(user.displaygroupid = 0, user.usergroupid, user.displaygroup |
| 3157 | orinje_sunshine | localhost | orinje_vb4ad | Query | 118 | Locked | SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate, user. |
| 3158 | orinje_sunshine | localhost | orinje_vb4ad | Query | 95 | Locked | SELECT moderator.*, user.username,
IF(user.displaygroupid = 0, user.usergroupid, user.displaygroup |
| 3160 | orinje_sunshine | localhost | orinje_vb4ad | Query | 119 | Locked | SELECT moderator.*, user.username,
IF(user.displaygroupid = 0, user.usergroupid, user.displaygroup |
| 3161 | orinje_sunshine | localhost | orinje_vb4ad | Query | 118 | Locked | SELECT moderator.*, user.username,
IF(user.displaygroupid = 0, user.usergroupid, user.displaygroup |
| 3162 | orinje_sunshine | localhost | orinje_vb4ad | Query | 95 | Locked | SELECT moderator.*, user.username,
IF(user.displaygroupid = 0, user.usergroupid, user.displaygroup |
| 3165 | orinje_sunshine | localhost | orinje_vb4ad | Query | 115 | Locked | SELECT SQL_CALC_FOUND_ROWS node.nodeid AS itemid ,(node.nodeleft = 1) AS isroot, node.nodeid, node |
| 3169 | orinje_sunshine | localhost | orinje_vb4ad | Query | 107 | Locked | SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate, user. |
| 3171 | orinje_sunshine | localhost | orinje_vb4ad | Query | 102 | Locked | SELECT userid, username FROM orinje_user
WHERE userid != 0
AND
(
username = 'Gineereelt |
| 3172 | orinje_sunshine | localhost | orinje_vb4ad | Query | 101 | Locked | SELECT SQL_CALC_FOUND_ROWS node.nodeid AS itemid ,(node.nodeleft = 1) AS isroot, node.nodeid, node |
| 3174 | orinje_sunshine | localhost | orinje_vb4ad | Query | 98 | Locked | SELECT SQL_CALC_FOUND_ROWS node.nodeid AS itemid ,(node.nodeleft = 1) AS isroot, node.nodeid, node |
| 3175 | orinje_sunshine | localhost | orinje_vb4ad | Query | 82 | Locked | SELECT moderator.*, user.username,
IF(user.displaygroupid = 0, user.usergroupid, user.displaygroup |
| 3176 | orinje_sunshine | localhost | orinje_vb4ad | Query | 63 | Locked | SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate, user. |
| 3177 | orinje_sunshine | localhost | orinje_vb4ad | Query | 57 | Locked | SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate, user. |
| 3178 | orinje_sunshine | localhost | orinje_vb4ad | Query | 56 | Locked | SELECT
post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS |
| 3181 | leechprotect | localhost | leechprotect | Sleep | 22 | | |
| 3182 | orinje_sunshine | localhost | orinje_vb4ad | Query | 17 | Locked | SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate, user. |
| 3183 | orinje_sunshine | localhost | orinje_vb4ad | Query | 3 | Locked | SELECT
post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS |
| 3184 | orinje_sunshine | localhost | orinje_vb4ad | Query | 4 | Locked | SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate, user. |
| 3185 | orinje_sunshine | localhost | orinje_vb4ad | Query | 0 | Locked | SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate, user. |
| 3186 | root | localhost | | Query | 0 | | show processlist |
I tried adding the child forums a few times, and I didn't see a significant increase in the memory usage while adding the child forums -- that is, until I visited the main page. This leads me to believe that vBulletin does some sort of database caching, and it isn't until a user hits the main page that it performs all these slow queries that consume considerable amounts of memory. Right now I see that the database has tons of SELECTs in it.
As this is third party software, we can provide very little assistance in optimising the application. However, I did find this article with a few things that we can try in it:
http://articles.digitalpoint.com/content.php?r=7-Optimize-vBulletin-4
If you'd like to try any of these (only the ones that apply to your version of vBulletin), then let us know. Otherwise, you may want to contact vBulletin support. Below is a very long paste that I'm going to give you, for your review and for anyone at vBulletin who may help you to review. Let us know if you need any more information. It's just the queries that are currently running in MySQL.
root@ori [/home/orinje/public_html]# mysqladmin pr
+------+-----------------+-----------+--------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-----------------+-----------+--------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 3087 | orinje_sunshine | localhost | orinje_vb4ad | Query | 145 | Locked | SELECT moderator.*, user.username,
IF(user.displaygroupid = 0, user.usergroupid, user.displaygroup |
| 3096 | orinje_sunshine | localhost | orinje_vb4ad | Query | 141 | Locked | SELECT moderator.*, user.username,
IF(user.displaygroupid = 0, user.usergroupid, user.displaygroup |
| 3102 | orinje_sunshine | localhost | orinje_vb4ad | Query | 145 | Locked | SELECT moderator.*, user.username,
IF(user.displaygroupid = 0, user.usergroupid, user.displaygroup |
| 3103 | orinje_sunshine | localhost | orinje_vb4ad | Query | 139 | Locked | SELECT
post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS |
| 3110 | orinje_sunshine | localhost | orinje_vb4ad | Query | 394 | Copying to tmp table | SELECT thread.threadid, thread.title, thread.prefixid, post.attach,
thread.postusername, thread |
| 3112 | orinje_sunshine | localhost | orinje_vb4ad | Query | 377 | Copying to tmp table | SELECT thread.threadid, thread.title, thread.prefixid, post.attach,
thread.postusername, thread |
| 3114 | orinje_sunshine | localhost | orinje_vb4ad | Query | 373 | Copying to tmp table | SELECT thread.threadid, thread.title, thread.prefixid, post.attach,
thread.postusername, thread |
| 3115 | orinje_sunshine | localhost | orinje_vb4ad | Query | 368 | Copying to tmp table | SELECT thread.threadid, thread.title, thread.prefixid, post.attach,
thread.postusername, thread |
| 3116 | orinje_sunshine | localhost | orinje_vb4ad | Query | 224 | Locked | SELECT thread.threadid, thread.title, thread.prefixid, post.attach,
thread.postusername, thread |
| 3131 | orinje_sunshine | localhost | orinje_vb4ad | Query | 225 | Locked | UPDATE orinje_user SET
posts = 0
WHERE userid = 19304 |
| 3132 | orinje_sunshine | localhost | orinje_vb4ad | Query | 325 | Copying to tmp table | SELECT thread.threadid, thread.title, thread.prefixid, post.attach,
thread.postusername, thread |
| 3133 | orinje_sunshine | localhost | orinje_vb4ad | Query | 322 | Copying to tmp table | SELECT thread.threadid, thread.title, thread.prefixid, post.attach,
thread.postusername, thread |
| 3134 | orinje_sunshine | localhost | orinje_vb4ad | Query | 224 | Locked | SELECT thread.threadid, thread.title, thread.prefixid, post.attach,
thread.postusername, thread |
| 3135 | orinje_sunshine | localhost | orinje_vb4ad | Query | 304 | Copying to tmp table | SELECT thread.threadid, thread.title, thread.prefixid, post.attach,
thread.postusername, thread |
| 3139 | orinje_sunshine | localhost | orinje_vb4ad | Query | 272 | Copying to tmp table | SELECT thread.threadid, thread.title, thread.prefixid, post.attach,
thread.postusername, thread |
| 3141 | orinje_sunshine | localhost | orinje_vb4ad | Query | 224 | Locked | SELECT thread.threadid, thread.title, thread.prefixid, post.attach,
thread.postusername, thread |
| 3143 | orinje_sunshine | localhost | orinje_vb4ad | Query | 175 | Locked | UPDATE orinje_thread AS thread,orinje_taggregate_temp_1329742560 AS aggregate
SET thread.views = th |
| 3144 | orinje_sunshine | localhost | orinje_vb4ad | Query | 181 | Locked | SELECT forum.forumid, forum.lastpost, forum.lastposter, forum.lastposterid, forum.lastthread, forum. |
| 3145 | orinje_sunshine | localhost | orinje_vb4ad | Query | 183 | Locked | SELECT moderator.*, user.username,
IF(user.displaygroupid = 0, user.usergroupid, user.displaygroup |
| 3149 | orinje_sunshine | localhost | orinje_vb4ad | Query | 182 | Locked | SELECT moderator.*, user.username,
IF(user.displaygroupid = 0, user.usergroupid, user.displaygroup |
| 3150 | orinje_sunshine | localhost | orinje_vb4ad | Query | 185 | Locked | SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate, user. |
| 3152 | orinje_sunshine | localhost | orinje_vb4ad | Query | 181 | Locked | SELECT moderator.*, user.username,
IF(user.displaygroupid = 0, user.usergroupid, user.displaygroup |
| 3157 | orinje_sunshine | localhost | orinje_vb4ad | Query | 118 | Locked | SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate, user. |
| 3158 | orinje_sunshine | localhost | orinje_vb4ad | Query | 95 | Locked | SELECT moderator.*, user.username,
IF(user.displaygroupid = 0, user.usergroupid, user.displaygroup |
| 3160 | orinje_sunshine | localhost | orinje_vb4ad | Query | 119 | Locked | SELECT moderator.*, user.username,
IF(user.displaygroupid = 0, user.usergroupid, user.displaygroup |
| 3161 | orinje_sunshine | localhost | orinje_vb4ad | Query | 118 | Locked | SELECT moderator.*, user.username,
IF(user.displaygroupid = 0, user.usergroupid, user.displaygroup |
| 3162 | orinje_sunshine | localhost | orinje_vb4ad | Query | 95 | Locked | SELECT moderator.*, user.username,
IF(user.displaygroupid = 0, user.usergroupid, user.displaygroup |
| 3165 | orinje_sunshine | localhost | orinje_vb4ad | Query | 115 | Locked | SELECT SQL_CALC_FOUND_ROWS node.nodeid AS itemid ,(node.nodeleft = 1) AS isroot, node.nodeid, node |
| 3169 | orinje_sunshine | localhost | orinje_vb4ad | Query | 107 | Locked | SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate, user. |
| 3171 | orinje_sunshine | localhost | orinje_vb4ad | Query | 102 | Locked | SELECT userid, username FROM orinje_user
WHERE userid != 0
AND
(
username = 'Gineereelt |
| 3172 | orinje_sunshine | localhost | orinje_vb4ad | Query | 101 | Locked | SELECT SQL_CALC_FOUND_ROWS node.nodeid AS itemid ,(node.nodeleft = 1) AS isroot, node.nodeid, node |
| 3174 | orinje_sunshine | localhost | orinje_vb4ad | Query | 98 | Locked | SELECT SQL_CALC_FOUND_ROWS node.nodeid AS itemid ,(node.nodeleft = 1) AS isroot, node.nodeid, node |
| 3175 | orinje_sunshine | localhost | orinje_vb4ad | Query | 82 | Locked | SELECT moderator.*, user.username,
IF(user.displaygroupid = 0, user.usergroupid, user.displaygroup |
| 3176 | orinje_sunshine | localhost | orinje_vb4ad | Query | 63 | Locked | SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate, user. |
| 3177 | orinje_sunshine | localhost | orinje_vb4ad | Query | 57 | Locked | SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate, user. |
| 3178 | orinje_sunshine | localhost | orinje_vb4ad | Query | 56 | Locked | SELECT
post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS |
| 3181 | leechprotect | localhost | leechprotect | Sleep | 22 | | |
| 3182 | orinje_sunshine | localhost | orinje_vb4ad | Query | 17 | Locked | SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate, user. |
| 3183 | orinje_sunshine | localhost | orinje_vb4ad | Query | 3 | Locked | SELECT
post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS |
| 3184 | orinje_sunshine | localhost | orinje_vb4ad | Query | 4 | Locked | SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate, user. |
| 3185 | orinje_sunshine | localhost | orinje_vb4ad | Query | 0 | Locked | SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate, user. |
| 3186 | root | localhost | | Query | 0 | | show processlist |