Recent server issues

362. biggest forum on the internet, sounds like it needs good hardware and good connections.
Might explain why it is not always avaible when I want to log on.

The forum does a lot of good against woo, so it would be worth expending the hardware on. Probably best value for the money.
 
vBuilletin makes some very inefficient queries, for one thing. Particularly it doesn't deal well with huge threads or members with huge numbers of posts. The other thing is the server needs more RAM (and ideally to be a 64-bit kernel so that a single process can grab a larger amount of RAM too). Almost invariably when the "no DB connections left" messages start, it's because we're thrashing swap. I've tried various things to tune the db to use VM better, but once one of those giant "runs for several hundred seconds" queries kicks in, we usually end up dropping people.

ETA: if we didn't drop people, it probably wouldn't recover on its own, and it would be hung until rebooted (or until someone restarted Apache and/or the database, at least).

Does vBulletin have support for sever-side caching?Or at least Apache?This could decrease number of connection,but not sure how efficient with vB or Joomla it is/would be.

Or just new hardware,where new server would be dedicated for database and old one for website(PHP).

P.S:And I am ,I suspect, Mr.Obvious (Klimax)...
 
ETA: if we didn't drop people, it probably wouldn't recover on its own, and it would be hung until rebooted (or until someone restarted Apache and/or the database, at least).
Speaking of dropping people, why not cull old unused user accounts from the database?

Is there a query that you can use to determine the last time an account was used? I see by looking through the members list that there are a large number of accounts with 0 posts. If a script could be formulated such that {IF post count = 0 AND time last logged on > 365 days ago THEN delete the account} that would free up a modicum of space, wouldn't it?
 
Speaking of dropping people, why not cull old unused user accounts from the database?

Is there a query that you can use to determine the last time an account was used? I see by looking through the members list that there are a large number of accounts with 0 posts. If a script could be formulated such that {IF post count = 0 AND time last logged on > 365 days ago THEN delete the account} that would free up a modicum of space, wouldn't it?

I don't see how that would make a significant difference to the issues we're seeing.
 
From a forum ranking website (by metric), this forum is the 362nd largest forum on the internet. 362 out of hundreds of thousands. That's a big forum.

Before we start the self-congratulations, we are well below the Delta Goodrem forum.:(
 
vBuilletin makes some very inefficient queries, for one thing. Particularly it doesn't deal well with huge threads or members with huge numbers of posts. The other thing is the server needs more RAM (and ideally to be a 64-bit kernel so that a single process can grab a larger amount of RAM too). Almost invariably when the "no DB connections left" messages start, it's because we're thrashing swap. I've tried various things to tune the db to use VM better, but once one of those giant "runs for several hundred seconds" queries kicks in, we usually end up dropping people.

ETA: if we didn't drop people, it probably wouldn't recover on its own, and it would be hung until rebooted (or until someone restarted Apache and/or the database, at least).
Thanks, Terry. That does explain a fair bit. I appreciate your situation, and you have my full support!

Clearly the root cause is the vBulletin software. So just throwing more hardware at it (i.e. the Microsoft option) is unlikely to make a huge dent in it. That could be money and effort wasted just now.

The grizzled old coder here says that you will get far more bang for your buck by tracking down and "efficientising" the noticeable software roadblocks you have already identified. We have been this path before, hence the limited Search results, etc.

It seems clear that long topics and high post-count users are RAM hogs. That suggests vBulletin is making routine calls on MySQL that return giant tables in memory, possibly containing non-normalised data. (This would also show as high disk IO counts - disk thrashing.) My first "programmer analyst" question would be: Why are all these records back to the year dot needed every time in routine queries? Surely they would be expected to consume huge amounts of memory per instance, and yet it is highly unlikely all the records will ever be required for subsequent processing. Sounds like the vBulletin designers never allowed that these numbers could really get so high.

So I would look at reducing that memory demand in software before trying to accommodate it in hardware. If that can be done, you reduce RAM consumption per user and lower overall resource demand (i.e. RAM and disk IO) on your existing unmodified platform, allowing you to defer heavy-duty upgrades until things are calmer and planned. And paid for. :)

For example, would the most recent 1000 records be sufficient under most common circumstances? Or could a much smaller recordsize be requested, to be used as an index into one or two selected bigger records as required? (SQL programmers can stop laughing now! You suggest better code solutions, OK?) More to the point, can you re-code the guts of vBulletin at all anyway?

Good luck!
 
vBuilletin makes some very inefficient queries, for one thing. Particularly it doesn't deal well with ... members with huge numbers of posts. <snip>

Ever thought about banning members with too many posts? But give them permission to create a sock puppet.

Or if our forum has too many posts then remove all old threads. Give a link to the archive on the main menu. The archive would have these deleted threads.
 
Well,what about to start locking threads at 5000 range?There are some active.
Or even lower loke from 2000 or so...
 
I clicked on this thread and got a database error. How Ironic. =/
 
Thanks, Terry. That does explain a fair bit. I appreciate your situation, and you have my full support!

Clearly the root cause is the vBulletin software. So just throwing more hardware at it (i.e. the Microsoft option) is unlikely to make a huge dent in it. That could be money and effort wasted just now.

Not sure I agree that throwing more hardware at the situation is unwarranted.

The grizzled old coder here says that you will get far more bang for your buck by tracking down and "efficientising" the noticeable software roadblocks you have already identified. We have been this path before, hence the limited Search results, etc.

Of course, that's optimization 101. But this is effectively shrinkwrap software. We are in a world of hurt if we start changing vBulletin, and I for one am not up for supporting a fork of a licensed commercial product just because we happen to be able to read the PHP.

It seems clear that long topics and high post-count users are RAM hogs. That suggests vBulletin is making routine calls on MySQL that return giant tables in memory, possibly containing non-normalised data. (This would also show as high disk IO counts - disk thrashing.) My first "programmer analyst" question would be: Why are all these records back to the year dot needed every time in routine queries?

They aren't, but you have to look at all the posts in a thread to generate the thread display. Or at least look at an index containing all the posts.
 
Well,what about to start locking threads at 5000 range?There are some active.
Or even lower loke from 2000 or so...

There are 8 threads that are open with more than 5k posts. I'll ask for them to be... TERMINATED!
 
I help run a vB forum that isn't quite as big as this one (not as many posts), but has about the same number of active users and will range from 350 to over 700 active users at any given time.

If you want you can send me a PM and I can share a few things we've done to help tune things up and get the max performance out of our server.. we're running on a single box with 2 Xeon processors, 2GB of RAM and mirrored SCSI drives and a throttled connection, and we rarely get slowdowns so I think we've done a good job of maximizing our resources without going to extremes.

Though eventually you get to the point that you need more than one physical server, but without knowing more details it's hard to be more helpful. Have you determined what the bottleneck is, sounds like it's mysql?

Anyway, if you are interested Terry shoot me a PM, or I can share in-thread if you don't mind the technical stuff in here.

In response to Zep, it's not quite as bad as all that, throwing more hardware if done properly can make a huge difference, there are some truly massive forums, I am a moderator on one (though I'm not really active anymore) that has 4-5000 active members normally, and lots more during really active times, so the software does scale, it's just a harsh reality of having hundreds of thousands of people hitting refresh every 30 seconds on a server where the pages are by necessity dynamically generated.

You are right though about identifying the bottlenecks, in my optimization quests I talked to a few vB users who had done just that; they'd basically gone through and rewritten some of the SQL queries to be more efficient and a few of the pieces of the software. That's tough though, not everyone has that kind of time (let alone skill, writing great queries isn't easy) to dedicate to their forum.
 
Thats crazy talk! Tachyons! we need more tachyons!

Captain! I've giving you every ounce of power this tub can muster. The engines will blow at any minute! We need more Berilium crystals captain Kirk!
 
We could just get rid of all the crazy people...that would free up a lot of bandwidth. Although I think there would only be like..5 people left. :)
 

ISF - Join now!

Every member here is approved by hand. No bots, no spam, just people who care about evidence and honest debate.

Membership is free!

Create your free account

Back
Top Bottom