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!