Server Optimization

[FreeLunchRoom]

I need some help with server optimization. We already have a thread over at vbulletin.com:

http://www.vbulletin.com/forum/showthread.php?t=262464

But it's really not doing anything. Do you know of a company that can help or can you guys share some of your my.cnf settings?

We have 50,000+ members, over 1.3 million posts, and an average of 250 new members a day.

Whenever we have over 550 members simultaneously on the site, our server load can shoot up to 20+ which is insane. Our hardware is pretty well set up, one ded server for the db and one for the forum. Again, all the technical specs are in that vbulletin thread.

ANY help would be appreciated.

[SEOvB]

i know this is gonna sound stupid, but did you make the DB specific changes to the DB server and the http optimizations to that server?

[Ted S]

Since you're already getting help with the server setup let's delve into what's being run to make you have issues so much earlier than the "average" site.

Do you have any hacks installed? Multiple templates? Custom code you wrote?

When you say 600 memebrs, is that including guests or not? What's your total activity and guests to member ratio?

Have you run any sort of slow-query report to see what (if anything) is getting caught up?

[FreeLunchRoom]

i know this is gonna sound stupid, but did you make the DB specific changes to the DB server and the http optimizations to that server?

Yup.

[Angel-Wings]

<a href="http://forge.mysql.com/projects/view.php?id=44" target="_blank">http://forge.mysql.com/projects/view.php?id=44</a>

did you try using that tool already ?

[FreeLunchRoom]

Do you have any hacks installed? Multiple templates? Custom code you wrote?

Yes, vbulletin blog, vbseo, ibproarcade, flashchat and vbadvanced. Installed but not inuse: mass pm, mgc chatbox, payment system lite.

When you say 600 memebrs, is that including guests or not? What's your total activity and guests to member ratio?

Yes, that includes guests. Out of 600 users, I would say 475 - 525 are registered members.

Have you run any sort of slow-query report to see what (if anything) is getting caught up?

No, not yet...where can I do this? Is this within vbulletin?

--------------- Added 1205304360 at 1205304360 ---------------

http://forge.mysql.com/projects/view.php?id=44

did you try using that tool already ?

No, I will try it tonight and report back tomorrow.

[andrewkhunn]

What kind of a connection do you have between the two servers?

[FreeLunchRoom]

What kind of a connection do you have between the two servers?

I can't remember off the top of my head but it's not the problem. The server load is what is really bothering me. Just spikes when there are a lot of users on it. We're talking server load averages of 50 and higher.

[Marco van Herwaarden]

How do you know it is not (part of) your problem if you don't even remember?

[FreeLunchRoom]

How do you know it is not (part of) your problem if you don't even remember?

The servers are literally right next to each other....so it should be more than enough. ;)

--------------- Added 1205364396 at 1205364396 ---------------

From doing more research it looks like our PM table are so huge its becoming corrupt...the database structure formating is odd...has anyone tried other db formats?

[andrewkhunn]

Your posts aren't making much sense. How big is your PM table?

Also, the connection between the two servers matters because if connections between Apache and MySQL are being routed through 10mbps wire, you're going to have load issues while processes stack on top of one another waiting for communication.

I was experiencing symptoms similar to what you are describing when my servers were unknowingly auto-negotiating at 10mbps. Once I got upgraded to 100mbit everything evened back out. Also, their physical distance has little/nothing to do with it.

--------------- Added 1205374091 at 1205374091 ---------------

Also, please run this script and copy/paste the output here (between 2 code brackets): http://forge.mysql.com/projects/view.php?id=44

[FreeLunchRoom]

Your posts aren't making much sense. How big is your PM table?

Also, the connection between the two servers matters because if connections between Apache and MySQL are being routed through 10mbps wire, you're going to have load issues while processes stack on top of one another waiting for communication.

I was experiencing symptoms similar to what you are describing when my servers were unknowingly auto-negotiating at 10mbps. Once I got upgraded to 100mbit everything evened back out. Also, their physical distance has little/nothing to do with it.

--------------- Added 1205374091 at 1205374091 ---------------

Also, please run this script and copy/paste the output here (between 2 code brackets): http://forge.mysql.com/projects/view.php?id=44

It is 100mbit. Will run the query and paste it. But we rebuilt the PM table and seems to have cooled down a bit.

[andrewkhunn]

Also, what kind of hard drives do you have? I know they're RAID1, but what interface and rotational speed?

[Marco van Herwaarden]

Also are you sure that they have (and are using!!) a direct connection. Seen before that 2 servers next to eachother where routing their inter-server traffic over the outside internet connection.

[Princeton]

In addition to the server suggestions at vbulletin.com.

Upgrade MySQL.

Make sure you have the latest Apache 2 - we also had problems with a particular version. (Don't remember which one.)


Check the # of queries running on your site (per page) ...

Check each custom query and make sure
1)__ they are optimized
2)__ are using index whenever possible
3)__ ensure that SELECTs are retrieving from the SLAVE server and not the MASTER (check processlist)

NOTES
- only use necessary plugins - those that add value to your site
--- I recommend removing the arcade.
- vbseo has all their queries fetching from MASTER. :down:
- vbadvanced - I'm sure there's some queries that are going to MASTER when they should be going to SLAVE.