What does this message from my hosting company mean?

[davio33]

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/con...ze-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 |

[kh99]

I should start by saying I don't know much about mysql admin so I don't know what you should do. But, it looks like the queries in the above list that say "Copying to tmp table" are from a "Threads" forum block. Maybe check the cache time on that and set it to more than 0. I suppose that could explain why the spike happens on the main page.