Bookmarking a Post on a Large Topic creates Absurd Loading Times

bug
1

This thread in particular had been bugging me for a very long time. I asked, why is it taking from 1-3 minutes to load, if it even decides to load?

After seeing someone second this issue in this post, I decided to look into it: I removed all bookmarks from the thread with 4,500 posts.

It loaded instantly like any other post would.

I then asked myself, what other ways could I test this?

  • Irregardless of whether or not I bookmarked the first post or the latest post, it still took a while to load, albeit not minutes but around 10 seconds.
  • The effect was exacerbated on threads with more posts; I feel like there’s a certain threshold around 4000 posts that makes loading times this slow.
  • I tested this on other threads with more than 4000 posts.
  • The more bookmarks a topic has, the longer it takes to load.

I theorize that the more bookmarks you have on a topic with many posts, the longer it takes to load.

To repro: Open up a topic with many, many posts and bookmark one. It’s too bad that the referenced thread needs you to sign in to bookmark a post, and that the thread with the most amount of replies on this website is 925.

If I need to provide any more information, go ahead and ask.

4 Likes
2

Pretty sure I reported this to you before @martin in a different topic

2 Likes
3

Hmm yes you did, in this one Topics load slow or not at all when they have many replies and user has bookmark in them. Sorry looks like I dropped the ball on that one; I’ll move this issue to the top of my list.

2 Likes
4

This is also bad enough that it should be backported, if it exists in stable…

3 Likes
5

I created an account on the Roblox forum to look at What are you working on currently? (2020) - #5043 by FLYSLENDY04 - Cool Creations - DevForum | Roblox, and I can reproduce the issue there. Without a bookmark the topic loads in ~1.3s, with a bookmark it takes ~8s. I even encountered the 502 bad gateway:

image
image1906×287 24.2 KB

However, I made a topic with 5000 posts locally and made a bookmark, and it loads in the same time whether I have a bookmark in the topic or not :man_shrugging:. I will have to keep looking…the code that loads the bookmarks for a user in a topic is here:

https://github.com/discourse/discourse/blob/9aa2723d57cf9edaad0873ba64d3d1e5a9d7fdc7/lib/topic_view.rb#L443-L445

And the code that finds a bookmark for each post is here:

https://github.com/discourse/discourse/blob/9aa2723d57cf9edaad0873ba64d3d1e5a9d7fdc7/app/serializers/post_serializer.rb#L361-L364

To me this does not look crazy and performance intensive. user_post_bookmarks will only query the database once because it is memoized, so no N1 issue here. Also here is the topic view code to see if there is a bookmark at the topic level:

https://github.com/discourse/discourse/blob/9aa2723d57cf9edaad0873ba64d3d1e5a9d7fdc7/app/serializers/topic_view_serializer.rb#L190-L200

https://github.com/discourse/discourse/blob/9aa2723d57cf9edaad0873ba64d3d1e5a9d7fdc7/lib/topic_view.rb#L346-L354

Nothing suspicious there either…

2 Likes
6

Could it be a bug we already fixed in an older version? :thinking:

2 Likes
7

I don’t think so, the Roblox forum is on the version https://github.com/discourse/discourse/commit/2a268bd89b4eebe4941b98030db9d920ee98d406. Since then there have only been two bookmark-related commits which I did, neither of which do anything with the topic bookmark loading or serialization.

https://github.com/discourse/discourse/commit/0d63eb412404330b943f367c4cdbc2021b900995

https://github.com/discourse/discourse/commit/c3cede697dddeacabc6410ff4e4f8425805004cb

There must be some other weird coincidence at play I guess?

1 Like
8

I can reproduce this on BBS

image
image2114×549 153 KB

something is not right about that query that is tripping the planner. Try looking at the plan and fiddling with it a bit.

My guess is that some very very small changes will get this down to 1 millisecond for almost 4 seconds.

Note to all the readers out there, having mini profiler enabled is key here, it can help you find the problem query.

5 Likes
9

Thanks to @sam’s advice I found that this is because of a poor query plan which is executed on this line:

topic.posts.with_deleted.where(post_number: 1).first

The SQL generated contains a LIMIT 1 which makes it so the index_posts_on_topic_id_and_post_number index is never used, causing a huge delay. I am unable to reproduce this locally because I have barely any posts in the local database; basically the more posts in your entire Discourse forum the worse this issue gets. I will have a fix up shortly.

3 Likes
10

Closing this as a dupe of:

@martin you can post on the original topic there once sorted.

4 Likes