Go Back   vb.org Archive > Community Discussions > Forum and Server Management
FAQ Community Calendar Today's Posts Search

Closed Thread
 
Thread Tools Display Modes
  #1  
Old 11-30-2008, 02:50 AM
ZomgStuff ZomgStuff is offline
 
Join Date: Feb 2007
Posts: 469
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default Is it time to move on from shared hosting?

So I can't tell if bluehost.com has some really outdated hardware, or am I really causing them server performance issues. Basically they said if I cause them problems one more time they're permanently suspending my account. I've apparently running a lot of slow SQL queries.

A little information about my site
URL: http://forum.zomgstuff.net/
Total Threads: 16,353
Total Posts: 160,968
Total Members: 9,477
Average Daily Unique Members: ~105
Data Usage: 128.15 MB
Index Usage: 51.23 MB
Size of database SQL backup: ~135mb

I also have quite a bit of plugins as well.

Server average load times used to be 2-4 seconds, but after I forced guests to register to view thread contents, it's currently 1.3 seconds.

I've looked at the sql_sloq_queries log in my hosting and I see dozens of daily entries. Most of them say the "slow" script ran for an average of 3-4 seconds.

Here's a couple of examples of slow queries.
Code:
# Fri Nov 28 13:19:35 2008
# Query_time: 2  Lock_time: 0  Rows_sent: 4  Rows_examined: 32
use zomgstu1_theforums;
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,
  spamlog.postid AS spamlog_postid,
  deletionlog.userid AS del_userid, deletionlog.username AS del_username, deletionlog.reason AS del_reason,
  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(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid, infractiongroupid
		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 spamlog AS spamlog ON(spamlog.postid = post.postid)
  LEFT JOIN deletionlog AS deletionlog ON(post.postid = deletionlog.primaryid AND deletionlog.type = 'post')
		LEFT JOIN editlog AS editlog ON(editlog.postid = post.postid)
		LEFT JOIN postparsed AS postparsed ON(postparsed.postid = post.postid AND postparsed.styleid = 72 AND postparsed.languageid = 1)
		LEFT JOIN sigparsed AS sigparsed ON(sigparsed.userid = user.userid AND sigparsed.styleid = 72 AND sigparsed.languageid = 1)
		LEFT JOIN sigpic AS sigpic ON(sigpic.userid = post.userid)
		WHERE post.postid IN (0,168362,168390,168583,168584)
		ORDER BY post.dateline
Code:
# Sat Nov 29 20:35:29 2008
# Query_time: 6  Lock_time: 0  Rows_sent: 1000  Rows_examined: 14889
use zomgstu1_theforums;
SELECT postid
  FROM post AS post
  INNER JOIN thread AS thread ON(thread.threadid = post.threadid)
  WHERE post.userid = 1
  AND thread.forumid IN(104,2,90,102,131,12,429,430,426,69,431,18,44,432,132)
  ORDER BY post.dateline DESC
  LIMIT 1000

# Sat Nov 29 20:59:22 2008
# Query_time: 3  Lock_time: 0  Rows_sent: 4958  Rows_examined: 4958
use zomgstu1_theforums;
SELECT postid, wordid,
  CASE intitle
  WHEN 0 THEN score
  WHEN 1 THEN score + 5
  WHEN 2 THEN score + 5 + 5
  ELSE score
  END AS score
		FROM postindex
		WHERE wordid IN (0,1126,2261,5755)
Basically do you think I've outgrown shared hosting, or do I just need to make my scripts more efficient and optimize them?
Closed Thread


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 12:41 AM.


Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2024, vBulletin Solutions Inc.
X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.03670 seconds
  • Memory Usage 2,209KB
  • Queries Executed 11 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)ad_showthread_beforeqr
  • (2)bbcode_code
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)navbar
  • (3)navbar_link
  • (120)option
  • (1)post_thanks_box
  • (1)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (1)post_thanks_postbit_info
  • (1)postbit
  • (1)postbit_onlinestatus
  • (1)postbit_wrapper
  • (1)showthread_list
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_threadedmode.php
  • ./includes/functions_post_thanks.php 

Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids_threaded
  • showthread_threaded_construct_link
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • fetch_musername
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • post_thanks_function_can_thank_this_post_start
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete