Improving Instance Performance (Megatopics, Database Size and Extreme Load)

I’ve got a site with a handful of >100K post mega topics. I told them that it was a mistake, but they wanted them anyway. They are complaining about performance issues. I’m hoping that I’ll soon be able to change the value back to the 10K default.

It did cause me to learn some stuff about database optimization, so that’s a bonus. :slight_smile:

5 Likes

I know what you mean, but I also have to admit that it can be awkward to split a topic in years, specially because we have a lot of conversation around people.

Imagine a topic about Trump in general or one about the United States in general. In our forum we have a topic about one of our club’s ex-presidents, one for the president nowadays, one for each football team player. You get the thing. It’s easy to split other topics by seasons, but not these ones. Sure, it’s not impossible, but quite impracticable.

Imagine a category about Trump in general or the United States in general.

No one is going to read >10000 posts in a topic straight through. They just aren’t. At some point, you can just take a breath and start anew. I suspect that these topics are more like chat than discussion anyway, and that few people read what happened yesterday, much less last week, last month, or last year.

3 Likes

OK, but my answer to this is

… you are opting into causing yourself considerable pain here for “reasons”.

5 Likes

Here is a data explorer query that mimics the query done getting a page of posts:

-- [params]
-- int :topic_id = 107216
-- int :offset = 10000

SELECT "posts"."id" FROM "posts" 
WHERE ("posts"."deleted_at" IS NULL) 
AND "posts"."topic_id" = :topic_id
AND "posts"."post_type" IN (1,2,3) ORDER BY "posts"."sort_order" ASC LIMIT 20 
OFFSET :offset

Here’s a normal topic:

Limit  (cost=1911.35..1915.38 rows=1 width=8) 

Here is a mega topic:

Limit  (cost=37475.88..37550.83 rows=20 width=8)
4 Likes

:+1:

I believe you guys, it’s just that our users are used like this and it’ll take a bit of effort to get there… but it’s doable.

Related to this, I was just reading @codinghorror post Natural breakpoints or "chapters" for long topics? and thought that this kind of table of contents but for topics only would be a nice solution to be able to gather and have visible a sequence of topics while browsing and replying only in a single one.

Don’t misunderstand, I did that work not to convince you, but to convince my client with the > 120K post topics who is complaining about performance!

My hope is that it’ll help you too. Good luck with that. :wink:

:clinking_glasses:

3 Likes

Very helpful! Thanks for sharing!

We do have concrete near term plans to tame the post_timings table but megatopics will be a source of considerable ongoing pain for many, many years.

:warning: and remember that is after @tgxworld did great work about a year ago to reduce the overall amount of work we do on megatopics, essentially switching to “low power mode” on these topics so they don’t hurt as much.

But don’t kid yourself: megatopics still hit HARD. :boom::boxing_glove:

5 Likes

Following your recommendations and while I try to figure out the other questions (acknowledging that you guys are working on the post_timings table), I’m proceeding to restore the defaults on topic size (while apologizing for my stupidity, why not).

With this in mind, I have some questions that I hope you could answer:

  1. Once the setting is restored, those topics will be closed. New ones will be created. However, is the existence of those older big topics dangerous to the site as a whole? Meaning, should I “split them” X times into smaller topics or as long as they are not active is ok?

  2. I saw the debate that @Paracelsus raised with the commodity for the users. With this in mind, I’m going to ask if it would be possible to have a setting to “Auto-Continue Topics that are closed due to max-topic size”. That is, when a mega-topic is automatically closed, would it be possible for it to open another with the same author, same title (with a number maybe at the end of the topic?) and the only content being a link to the previous one? (While adding the link to the new one on the closed one).

I know that it is kind of convoluted and maybe not wanted for everyone (that’s why I’m rising it as an optional setting) but for high-traffic sites with relevant topics it could be useful, I guess. wdyt?

1 Like

This is like asking “should we encourage people to smoke cigarettes by auto-buying them a new pack when they run out?” :wink:

Not unless they get a lot of traffic, which they should not if they are closed, I would think? See how it goes but stopping the bleeding of the active mega topics is step zero, so you are on a good path here :+1:

3 Likes

Just a follow-up… We’re trying now to deal with these mega-topics by splitting them in chunks of 10k post each. However, the system doesn’t seem to be willing to cope with our strategy :sweat_smile::

I get a “502 Bad Gateway” when selecting at least 8000 posts at a time and moving them to a new topic (haven’t tried with smaller amounts of posts). Is there a way to increase the capacity or another/better way to accomplish this? @codinghorror @pfaffman

The solution is to do it at the rails console, but I don’t know offhand how to do that.

Something like

old_topic=1
new_topic=2
Posts.where(topic_id: old_topic).where("post_number > 10000 and post_number < 20000").update_all(topic_id: new_topic, post_number=...)

but . . . . I think that you might have to put this in a loop to create the new post numbers. You’ll want to test your solution on a staging site. If you screw something up, you’ll be in a world of hurt.

If that’s not enough for you to figure out how to do it, then you’ll probably need to post in #marketplace. But I bet no one is going to read the old posts anyway and that you should just close them and leave them huge, perhaps even deleting or unlisting them unless you think they have some SEO value.

1 Like

Agree, what is the value of content that nobody reads? And which people are gonna actually sit down and read 10,000 posts from start to finish? :crazy_face:

It’s OK for some topics to be ephemeral chats which disappear entirely over time, what we’ve previously called “fast lane” versus “slow lane” traffic.

5 Likes

Small Update:

Since reported and agreed on closing everything above the mark (and re-establishing the limits) performance seems to be better, a lot better. We still get some “Due to extreme load, this is temporarily being shown to everyone as a logged out user would see it” but considerably lower.

With that said:

  • Thanks for the hard work looking into how to reduce that monster table, really appreciate it.
  • Are there any other “Performance Tips” or even “Setups” that we may be missing? Even if they are “advanced”, any help is appreciated.

Again, a huge thanks to everyone for helping and giving feedback.

3 Likes

Postgres 12 will help as it reduces table and index size by 20% in @falco’s testing.

4 Likes

Is there a target date for that yet?

1 Like

I started doing bechmarks today.

Gotta let it run for a while here on Meta so we can watch for performance regressions before rolling it out everywhere.

6 Likes

I’m sorry for rising this one up again, but this is a different issue from the PostGreSQL Migration. (Which I’m not able to do yet due to size).

Since last rebuild my Database Size won’t stop increasing:

image

Last rebuild was on May 17th and since then the DB Size won’t stop increasing, reaching 57.3GB and the big size is on the post_timings table.

My main issue with this is that I’m trying to do the PostGreSQL update (which will reduce index size by 20% but won’t solve this in the long run). And from the comments here from the Staff that size is not the norm, so it will keep increasing and becoming nightmareshly expensive. The more time passes, the more it increases and creates a loop that becomes hell to maintain. So my main issue prevails, is there a way to tackle this post_timings thing? Something to delete?

Can I compact tables or something?

Thanks to everyone for their help.

1 Like

There is no way around it at the time. If your forum is really big, it will have a big timings table.

Meta is a really old instance, but it is mid-sized and has a small 4GB post_timings table. In the other hand we host one instance that is less than 2 years old and the post_timings table should be over 100GB by now.

Hosting big forums will cost you more, and there are no ways around it today.

Maybe move your Database to a standalone $20 droplet (80GB disk) and put the web in another $10 droplet? $30 in monthly costs for what appears to be a quite big community sounds reasonable.

4 Likes

Thank you very much for your help, @Falco.

Welp, yeah, as I said, just asking because there isn’t magic when it comes to space. I’m looking into the division but then the app one will be too slow due to perf (long story, I’m taking note of the tests and will present them here later for everyone else to make use if they find them useful).

I did the test I asked you about regarding recovering a backup and stuff and I think that this can be a good situation to leverage from since what I can immediately see is that there is 30% less disk usage (I’m still running some test to check if there isn’t anything missing) but now there is an small issue with this approach so even though the immediate benefits are great this will generate some issues (even more because I don’t know if it is cached or not working at all in terms of stored images, and yes, the backup includes them).

I’m taking notes so that I can update the OP, my idea here would be to add a small series of notes for people that may worry about performance and all the things I’ve been modifying so far.