VB: Performance Issues.. how to fix (large sites)

[Mystikal]

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]

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]

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]

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]

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

[Freddie Bingham]

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]

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]

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]

My solution? I disable all attachments. :) I have 300+ online with no problems.

[Mystikal]

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 =\

[Martin64]

You could remotely host your attachments on another server if you have two boxes. I'm doing this for bandwidth usage reasons, but it would lower your server load as well, as the queries for the attachments are being run on the remote server, and the images are linked directly from the remote server.

If you're interested, there's a hack in Beta Hacks that does this. :)

[Smoothie]

So, is it better to have "Allow Duplicate Images" set to yes, or no?

[Xenon]

well, it depends on you.
if you want to save db space set it to no, if you want to have it faster... yes :)

here's an hack to optimize that part:
https://vborg.vbsupport.ru/showthread.php?s=&threadid=49306