The Arcive of Official vBulletin Modifications Site.

Mystikal · #1 02-25-2003, 07:25 PM

Recently we've had huge problems with our dedicated server and had been unable to figure out what the culprit was. Even after running ApacheBench we'd still be confused =\

After taking a look at a lot of the slow queries that were happening, we realized something. It took some pages over 21 seconds to process because there were basically no indexes on attachment or post.

See.. we have this thread called "The Chick Thread", and it's absolutely full of attachments, 3000+ replies, 80,000+ views, etc. It really was killing the server.

Me and a friend took a look at the attachment table and the queries that were being ran, and added this index from the mysql command prompt. Obviously adding an index on a 900 MB attachment table wouldn't work from phpMyAdmin.

CREATE INDEX uin_fdat ON attachment (userid, filedata[20])

It took over 30 minutes to build this index.

This sped our site up like you wouldn't believe. Though, there were still slowdowns, so we took a look at the post table and added this index, which also took a little bit to build.

CREATE INDEX presorted ON post (threadid,visible,dateline,postid).

Since then we've had no server troubles whatsoever and have had 300+ users hitting the forums at the same time and have had no such spikes. Before this our server was crashing every hour!

Now I don't know if this was a fluke, but I'm pretty sure these indexes did a lot... although I'm hoping someone will prove me wrong here.

Just letting you guys know.

Xenon · #2 02-25-2003, 07:46 PM

well, but indexes on large fields aren't as good..

there is a new hack from s.molinar which increases the attachment thing in a nice way..
also some othere queries have been optimized in the past, maybe you look into some of the older optimization hacks, there are quite a few out there

Mystikal · #3 02-25-2003, 07:57 PM

Can you link me?

Freddie says that the post one was a good idea, and that boards w/ large number of attachments would benefit from this. He says that turning off duplicate image checking will also solve the problem.

Freddie Bingham · #4 02-25-2003, 08:22 PM

I said you could add the attachment index but I would prefer for you to just disable duplicate image checking or install s.m.'s hack.

I did not comment on the other index.

Mystikal · #5 02-25-2003, 08:27 PM

Yea you did you said you could add it if you want

Freddie Bingham · #6 02-25-2003, 08:30 PM

Quote:

Originally posted by Mystikal
Yea you did you said you could add it if you want

Yes, you can take the wrong approach to fix the problem.

As for your other index, what query/file in vB are you hoping to fix with it?

Mystikal · #7 02-25-2003, 08:36 PM

Actually it fixes the duplicate image checking problem, or seemed to, because I went back and checked, and it was on. So I'm not sure why you're saying it's the wrong approach. It fixed our problem. Obviously it was the right approach. Of course, removing dup image checking would have worked too, but now we dont have to

As for the post index.. I'll check later i gotta jet for a tennis meeting... it was my other coder that put that on there, but i remember checking and it helped a LOT.

Freddie Bingham · #8 02-25-2003, 08:41 PM

Quote:

Originally posted by Mystikal
Actually it fixes the duplicate image checking problem, or seemed to, because I went back and checked, and it was on. So I'm not sure why you're saying it's the wrong approach. It fixed our problem. Obviously it was the right approach. Of course, removing dup image checking would have worked too, but now we dont have to

As for the post index.. I'll check later i gotta jet for a tennis meeting... it was my other coder that put that on there, but i remember checking and it helped a LOT.

The right approach is the method in which the hack by s.m. takes and the same method I put into vB3 about 3 months ago.

Erwin · #9 02-25-2003, 09:46 PM

My solution? I disable all attachments.

I have 300+ online with no problems.

Mystikal · #10 02-25-2003, 10:12 PM

Our forum relies on attachments so that really wasn't an option... but if it was I would do the same for sure!

I never even heard of a hack by SM or the approach you took in VB 3.. but I'll be glad to use it if you say it's the right approach

But still, if it fixed the problem, it seems like a correct approach to me =\

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.03815 seconds Memory Usage 2,248KB Queries Executed 13 (?)
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 (1)ad_showthread_firstpost (1)ad_showthread_firstpost_sig (1)ad_showthread_firstpost_start (2)bbcode_quote (1)footer (1)forumjump (1)forumrules (1)gobutton (1)header (1)headinclude (1)navbar (3)navbar_link (120)option (1)pagenav (1)pagenav_curpage (1)pagenav_pagelink (10)post_thanks_box (10)post_thanks_button (1)post_thanks_javascript (1)post_thanks_navbar_search (10)post_thanks_postbit_info (10)postbit (10)postbit_onlinestatus (10)postbit_wrapper (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_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 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 pagenav_page pagenav_complete tag_fetchbit_complete forumrules navbits navbits_complete showthread_complete
Messages:

The Arcive of Official vBulletin Modifications Site.

It is not a VB3 engine, just a parsed copy!