The way it works is - the server is capable of actively processing 16 things at once. It may seem to do more, because what happens is threads 10,000-10,015 will get CPU time for a few milliseconds, then threads 10,016-10,031 will get time (although most threads on the server are 'asleep' at any given moment, so numbers won't be sequential, and some things have higher priority than others).
Private messages aside (far worse on Blue Moon than on Elliquiy, but both systems are pretty bad), the schema for handling threads is actually fairly decent, and I've done a lot of work making sure that they are fairly well optimized on both Blue Moon and Elliquiy proper. All I had to do was fix some indexes for messages.
So what happens is, when you make a post, it stores that post alongside a few indexes - basically directory lookups, like a phone book.
- The topic/thread ID you see in the URL bar is the most obvious one
- The post time is another important one (stored in seconds since 1970).
- Your user id is yet another important one. Had to spend some time specifically fixing 'view this user's posts' because yes it gets hairy.
- The post ID itself.
Looking at an index will return the data set associated with the value specified.
- Obviously, looking at a single second in time isn't going to return a lot of posts. Rarely more than the number returned by looking at a specific post ID.
- This leaves the data sets returned by users (people looking through post histories) and large threads.
The smaller this index gets pulled, sorted, and its data sent, the faster it is no longer taking up an thread. The sooner it isn't taking up a thread, the better off we are.
Right now, thread thrashing is being caused by the following events, in order of seriousness
1) Blue Moon's private messaging system. By far the worst offender.
2) Elliquiy's large threads. Occasionally bad enough that the site locks up without Blue Moon's help.
3) Elliquiy's PM system.
4) Blue Moon's large threads
5) Post histories of very active users.
Once more than sixteen of these get queued at once, if their results are not fully in the memory pool, the server goes kaput.
I've known this was occasionally an issue for awhile, actually, and altogether it threw a fairly nasty wrench into my new CMS plans because I didn't properly account for the size and scope of some people's conversations in my database schema. How do you cleanly solve this without adding more hardware? It's not an easy problem.
In any case it has reached a point where it's simply become untenable for us to remain with our current configuration. We need a new home.