Million post thread

[FTL]

I'd like to start a million post just for fun thread on my new forum, however, I wonder if I'd hit capacity limits in XF and the database as it grows towards this number of posts. The posts will have to be short and not have any attachments to minimise the load on the system. Does anyone know?

I'm using the latest XF 225, MySQL 8023. I'm on PHP 806, because the latest 807 trashed the test forum on my home PC. Thankfully, I could revert back to 806 though and restore normal operation.

 

[nocte]

Should not be a problem.

 

[Paul B]

TBH, I don't think a thread with 220,000 posts is at all something we've considered. I would certainly consider a 10,000 post thread to be very large (though not unreasonably so). 220k is essentially an order of magnitude larger and larger than many of our customers' entire sites. I'd consider XF.com to be a decent size forum and that would compare to having about 15% of all of our posts in one thread.

So yes, it's very possible that some less common operations (which often require rebuilds of certain information across the whole thread) will fall over at that level.

 

[nocte]

We have a million post thread and common operations like deleting posts don't cause any problems.

 

[FTL]

We have a million post thread and common operations like deleting posts don't cause any problems.

Serious? That's epic! Yet, Mike, one of the developers has said that there could be problems, in @Brogan's post above. Perhaps it depends on what versions of the software are installed?

What version of XF, database and PHP are you running, if that's not confidential info?

Are you ok to post the link to it, I've gotta see what it looks like!

 

[Paul B]

All things being equal, it will be more to do with server spec's and set up.

 

[nocte]

We have a big forum with a lot of traffic and several servers. As @Brogan suggests, a small forum with a small server and only 1 large thread, might experience problems tough. I remember, there were also missing database indexes in older XF versions, which slowed down large threads and that we added manually. But AFAIK all necessary indexes were added to XF core already.

 

[FTL]

All things being equal, it will be more to do with server spec's and set up.

I'm on AWS, so I don't think that will be a problem. It could permanently cost me more money per month though with the extra database storage.

@Mike as one of the developers, your advice would be greatly appreciated.

 

[FTL]

We have a big forum with a lot of traffic and several servers. As @Brogan suggests, a small forum with a small server and only 1 large thread, might experience problems tough. I remember, there were also missing database indexes in older XF versions, which slowed down large threads and that we added manually. But AFAIK all necessary indexes were added to XF core already.

Oh, we're starting small, but the community will grow and we've got many subforums with 27 threads and 156 posts in just 4 days, with only 20 members.

AWS will expand with demand (and costs on my wallet, bless) so I don't think that will be a problem.

 

[rdn]

Several big xf2 forum I have access has largest thread with post count:
74k - site1
80k - site2
84k - site3

Site 4 having the most active threads
Without any issues.

Top 8:


All site are just using each own single Dedicated Server.

 

[MattW]

The biggest issue I've encountered with large threads is when they are deleted. One forum I host deleted a thread with 100,000 posts in it, and I had to increase the memory allocated to PHP to 4GB in order for the job to complete. Looking at the MySQL queries being ran at the time, it was all the related reactions causing the slow down and issues.

 

[FTL]

The biggest issue I've encountered with large threads is when they are deleted. One forum I host deleted a thread with 100,000 posts in it, and I had to increase the memory allocated to PHP to 4GB in order for the job to complete. Looking at the MySQL queries being ran at the time, it was all the related reactions causing the slow down and issues.

So, deleting those big threads took a big performance hit on the rest of the forum so everyone noticed a slowdown? Sounds like an issue, but only a one-off one. The forum could just be closed for a bit to cope with it.

The memory issue is more significant, though. That was only a tenth of the number of posts that I'm aiming for and you needed 4GB. If it's a linear relationship, then that would be 40GB for a million, which I don't know where that would come from. Granted, I'm not aiming at deleting that thread, but still, it's storing up a potential problem.

From the looks of it, there certainly will be issues with such a big thread. I look forward to Mike's response, as he's a developer, so will understand this from a deeper perspective than any of us.

 

[MattW]

So, deleting those big threads took a big performance hit on the rest of the forum so everyone noticed a slowdown? Sounds like an issue, but only a one-off one. The forum could just be closed for a bit to cope with it.

No, the forum was still running fine, when I say slow down, it was the slow down of the actual job to delete the thread and posts. I also had to increase the max_execution time for PHP to allow it to complete. Prior to increasing these, it would error with a 500 error, and then leave a job in the xf job queue.

 

[Walter]

Largest thread on one of my forums has 325k posts, no problems or delays when removing or moving posts.

 

[Mendalla]

We have a thread that broke 52K posts recently, but it's a bit of a chit-chat/game thing so rarely need to do any kind of moderation work in it, so no deleting or moving posts. Our next longest is 20K and it has had some moves done in the past, I think, but not since achieving this length. Again, it's a social thread rather than anything too serious. Both are threads dating to our startup 7 years ago and both have been in continuous use, hence the length.

 

[FTL]

Thanks for all the feedback people. However, none of your threads are anywhere near a million posts long, so there might still be a limit somewhere, I suspect MySQL. I'd really like a dev to come on here and answer the question, hopefully definitively. I might open a ticket if I have to.

 

[Paul B]

I'd really like a dev to come on here and answer the question, hopefully definitively. I might open a ticket if I have to.

I already posted an answer from one of the developers so opening a ticket won't provide a different answer.

 

[FTL]

I already posted an answer from one of the developers so opening a ticket won't provide a different answer.

It gives an indication that there could be problems, but still not definitive for a million as it's still way larger than what was discussed there, so the problems might be more severe. Could the whole site grind to a halt, maybe? I certainly want to avoid that. I just wonder if MySQL has any table limits that would be breached by it, in particular.

@rdn's reply looks promising, though.

@Mike I'd be really grateful if you could please give us some more detail here.

 

[Mike]

Honestly what I said stands. I was saying that there may be problems with certain actions. If you came to us and asked if we would recommend a huge thread like this, we'd say it's absolutely not something we'd recommend and you might run into issues.

There are certain actions that may require us to fetch details about every post in a thread, for example. (Deleting the thread being the obvious case but there may be other actions.) A huge thread means more data to fetch from MySQL and process with PHP, so there could be memory and processing time issues here, which may lead to PHP exiting before completing (or theoretically, high load from this process leading to a cascading failure). There are other actions that could potentially lead to virtually every post in the thread being updated (such as when deleting a post or some inline moderation actions). This could cause load issues.

There is no definitive "this will fall over". Every post in a thread creates some additional amount of work in order to complete certain actions. Whether this has issues in your scenario depends on far to many things such that no one could really say whether you might have problems. It might be totally fine. It might be totally fine until one day, an action in this thread leads to a load spike that leads to a cascading failure. Who knows.

You'd be better creating a whole forum with threads for individual discussions rather than one monolithic thread, both from a performance and a usability perspective.

 

[Xon]

I've got a thread with 441000+ replies. That thread has driven some of the design choices in Threadmarks due to the crazy size. Some ~5900 threadmarks, ~5 million words of creative content at time of writing.

We have a million post thread and common operations like deleting posts don't cause any problems.

If the thread is very active; deleting older posts in that thread can cause locking/time-out issues. I wrote a small add-on which allows me to limit deleting posts to X number of minutes (I picked 2 days) because of this.

Post Delete Time Limit

Permission to limit how old a post/thread can be and still be deleted, separate from the stock XenForo edit time window permission Introduces 1 new permissions: Time limit on deleting own messages (minutes) (default 0, never can delete) Note...

xenforo.com

The performance issues for long threads mostly appear when you add/delete posts early in long threads.

Hard deleting posts is much worse than soft-deleting. Bulk moderation tools make it very easy to trigger potentially expensive thread metadata rebuilds which involve locking potentially large number of post rows.