Shared Hosting Not Cutting it?

[elvinelol]

Hello,

I've been having issues with queries being held for 4secs + at given times for my site. I'm a live streamer and can have anywhere from 50-1000 viewers watching. If I post a link to a thread some people get "database error" messages. I assume that's because HostMonster as a 15 user limit on SQL requests. While this shouldn't be an issue since most request are passed within milliseconds from what I'm told. I'm thinking about leaving HostMonster and moving to a vps or a dedicated. My community is growing at a faster rate than I expected.

My question is if my conclusions are correct that it's due to being on a shared hosting server vBulletin runs slow at times and can even hang SQL queries to cause me to hit my database limit set in place by my host. In doing so they also throttle my site.



Some info about my site traffic, 430.83 MB Disk Space used. 32.42 MB vBulletin SQL Database size. Also, I did run VSa - ChatBox and was curious would that also cause hangups in queries? I've since turned it off to see if it would run better, but I think it's best for me to move to vps or dedicated.

Slow queries log created for over 1 sec am I getting attacked from the pattern of these log creation times?



Site: www.elvinelol.com

Thanks for the help in advance!

[final kaoss]

Self hosted chat's are a main cause for lag/performance issues. I would suggest you switch your chat system to a 3rd party provider to see if it helps.

Tinychat for example.

[elvinelol]

Originally Posted by final kaoss

Self hosted chat's are a main cause for lag/performance issues. I would suggest you switch your chat system to a 3rd party provider to see if it helps.

Tinychat for example.

Even when I disabled the chat plugin these still happened.

[Simon Lloyd]

It seems to me that those queries belong to a cron job (scheduled task) because of the regularity of times.

[elvinelol]

# Fri Feb  1 21:35:10 2013
# Query_time: 2.246202  Lock_time: 0.000078 Rows_sent: 0  Rows_examined: 1
use fromthe4_vbulletin;
SET timestamp=1359779710;
UPDATE cron
		SET nextrun = 1359783300
		WHERE cronid = 29 AND nextrun = 1359779700

# Fri Feb  1 21:35:17 2013
# Query_time: 4.330291  Lock_time: 0.000187 Rows_sent: 0  Rows_examined: 6
use fromthe4_vbulletin;
SET timestamp=1359779717;
UPDATE blog AS blog,blog_aggregate_temp_1359779700 AS aggregate
	SET blog.views = blog.views + aggregate.views
	WHERE blog.blogid = aggregate.blogid

# Fri Feb  1 21:35:22 2013
# Query_time: 4.497857  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
use fromthe4_vbulletin;
SET timestamp=1359779722;
DROP TABLE IF EXISTS blog_aggregate_temp_1359779700

# Fri Feb  1 21:35:40 2013
# Query_time: 9.925802  Lock_time: 3.490327 Rows_sent: 17  Rows_examined: 17
use fromthe4_vbulletin;
SET timestamp=1359779740;
SELECT *
  FROM datastore
  WHERE title IN ('smiliecache','bbcodecache','banemail','ranks','options','bitfields','attachmentcache','forumcache','usergroupcache','stylecache','languagecache','products','pluginlist','cron','profilefield','loadcache','noticecache','activitystream')

# Fri Feb  1 21:35:47 2013
# Query_time: 6.873099  Lock_time: 4.622681 Rows_sent: 0  Rows_examined: 0
use fromthe4_vbulletin;
SET timestamp=1359779747;
SELECT *
  FROM session
  WHERE userid = 0
  AND host = '66.249.73.132'
  AND idhash = '021beeb527c7c95112d9c8c95547709a'
  LIMIT 1

# Fri Feb  1 21:35:52 2013
# Query_time: 7.974552  Lock_time: 1.364796 Rows_sent: 1  Rows_examined: 1
use fromthe4_vbulletin;
SET timestamp=1359779752;
SELECT *
  FROM session
  WHERE sessionhash = 'cdd25f1b226e193ff2acd18325c3a616'
  AND lastactivity > 1359778844
  AND idhash = '6aeb64ee0ea98ee32e7312b54577a0ec'

# Fri Feb  1 21:35:52 2013
# Query_time: 11.414806  Lock_time: 4.626423 Rows_sent: 0  Rows_examined: 0
use fromthe4_vbulletin;
SET timestamp=1359779752;
SELECT *
  FROM session
  WHERE userid = 0
  AND host = '72.223.6.85'
  AND idhash = 'aaebcd6f362ba57662b1cc3b0ee9b424'
  LIMIT 1

# Fri Feb  1 21:36:33 2013
# Query_time: 5.683098  Lock_time: 0.000054 Rows_sent: 0  Rows_examined: 1
use fromthe4_vbulletin;
SET timestamp=1359779793;
UPDATE cron
		SET nextrun = 1359779820
		WHERE cronid = 32 AND nextrun = 1359779760

# Fri Feb  1 21:40:01 2013
# Query_time: 1.195400  Lock_time: 0.000120 Rows_sent: 1  Rows_examined: 1
use fromthe4_vbulletin;
SET timestamp=1359780001;
SELECT IF(thread.visible = 2, 1, 0) AS isdeleted,
  post.pagetext AS description,
  thread.*
  FROM thread AS thread
  LEFT JOIN post AS post ON(post.postid = thread.firstpostid)
  WHERE thread.threadid = 99

# Fri Feb  1 21:47:43 2013
# Query_time: 2.975253  Lock_time: 0.806965 Rows_sent: 1  Rows_examined: 1
use fromthe4_vbulletin;
SET timestamp=1359780463;
SELECT *
  FROM session
  WHERE userid = 0
  AND host = '173.199.119.59'
  AND idhash = '1d41bce29a10b8fdb3d72b2fbac134e8'
  LIMIT 1

# Fri Feb  1 21:47:55 2013
# Query_time: 5.401879  Lock_time: 3.340980 Rows_sent: 17  Rows_examined: 17
use fromthe4_vbulletin;
SET timestamp=1359780475;
SELECT *
  FROM datastore
  WHERE title IN ('smiliecache','bbcodecache','mailqueue','bookmarksitecache','options','bitfields','attachmentcache','forumcache','usergroupcache','stylecache','languagecache','products','pluginlist','cron','profilefield','loadcache','noticecache','activitystream')

# Fri Feb  1 21:50:45 2013
# Query_time: 4.390896  Lock_time: 2.244348 Rows_sent: 17  Rows_examined: 17
use fromthe4_vbulletin;
SET timestamp=1359780645;
SELECT *
  FROM datastore
  WHERE title IN ('smiliecache','bbcodecache','mailqueue','bookmarksitecache','options','bitfields','attachmentcache','forumcache','usergroupcache','stylecache','languagecache','products','pluginlist','cron','profilefield','loadcache','noticecache','activitystream')

# Fri Feb  1 21:52:27 2013
# Query_time: 1.704731  Lock_time: 0.189938 Rows_sent: 2  Rows_examined: 15
use fromthe4_vbulletin;
SET timestamp=1359780747;
SELECT
  post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS isdeleted,
  user.*, userfield.*, usertextfield.*,
  icon.title as icontitle, icon.iconpath,
  avatar.avatarpath, NOT ISNULL(customavatar.userid) AS hascustomavatar, customavatar.dateline AS avatardateline,customavatar.width AS avwidth,customavatar.height AS avheight,
  editlog.userid AS edit_userid, editlog.username AS edit_username, editlog.dateline AS edit_dateline,
  editlog.reason AS edit_reason, editlog.hashistory,
  postparsed.pagetext_html, postparsed.hasimages,
  sigparsed.signatureparsed, sigparsed.hasimages AS sighasimages,
  sigpic.userid AS sigpic, sigpic.dateline AS sigpicdateline, sigpic.width AS sigpicwidth, sigpic.height AS sigpicheight,
  IF(user.displaygroupid=0, user.usergroupid, user.displaygroupid) AS displaygroupid, infractiongroupid
  ,blog_user.entries
		FROM post AS post
		LEFT JOIN user AS user ON(user.userid = post.userid)
		LEFT JOIN userfield AS userfield ON(userfield.userid = user.userid)
		LEFT JOIN usertextfield AS usertextfield ON(usertextfield.userid = user.userid)
		LEFT JOIN icon AS icon ON(icon.iconid = post.iconid)
		LEFT JOIN avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN customavatar AS customavatar ON(customavatar.userid = user.userid)
  LEFT JOIN editlog AS editlog ON(editlog.postid = post.postid)
		LEFT JOIN postparsed AS postparsed ON(postparsed.postid = post.postid AND postparsed.styleid = 3 AND postparsed.languageid = 1)
		LEFT JOIN sigparsed AS sigparsed ON(sigparsed.userid = user.userid AND sigparsed.styleid = 3 AND sigparsed.languageid = 1)
		LEFT JOIN sigpic AS sigpic ON(sigpic.userid = post.userid)
  LEFT JOIN blog_user AS blog_user ON (blog_user.bloguserid = post.userid)
		WHERE post.postid IN (0467,1485)
		ORDER BY post.dateline

This was one of the newest logs, I'm not familiar with cron jobs. Are these done on vB or on the server? Should I assume my host cannot handle the demands of my site?

http://pastebin.com/4p9SaJzY

Example of another, but much larger log.

[snakes1100]

Admincp -- Scheduled Tasks --> Scheduled Task Manager

It doesnt mean the server your on cant handle it, but the fact that the host has most likely limited your resources you can use in the shared server & the fact its most likely over sold to begin with.

Im also guessing that their server isnt optimized, nor is it setup hardware wise to better utilize sql.

The session query time & its locking, is a indication of the above.

[syrus.xl]

Being on Shared Hosting and running shoutboxes or chatrooms, just will not play well - most will have limitations so you do not start draining resources and causing server load issues with other users on the same server as you. External hosted chat is better when on shared hosting.

If you really need to host it yourself, then you will need to go for either a VPS or dedicated box. I personally run vBulletin from a VPS, without any problems - then again you have full resources to yourself and not sharing with anyone. I spent the laswt 2 years with managed vBulletin hosting with www.urljet.com - on a VPS.