Huge performance problem - Refreshing takes 20sec or longer

m4v3rick

Refreshing a discussion takes far too long.

If I open a discussion it will be loaded within 1-2 seconds. But if I refresh the same page I have to wait 20 seconds or longer.
It is faster to go back to "All Discussions" and open the discussion again than refreshing it.

I tried it with many different browser (Chrome, Firefox, Edge, Opera, DuckDuckGo, Bromite) but no difference. Since Tuesday I am also using Cloudflare but refreshing didn't really improve.

Webserver: nginx/1.22.0

Flarum information


Flarum core 1.3.0
PHP version: 8.1.6
MySQL version: 10.3.31-MariaDB-0+deb10u1
Loaded extensions: Core, date, libxml, openssl, pcre, zlib, filter, hash, json, pcntl, Reflection, SPL, session, standard, sodium, mysqlnd, PDO, xml, calendar, ctype, curl, dom, mbstring, FFI, fileinfo, ftp, gd, gettext, iconv, exif, mysqli, pdo_mysql, Phar, posix, readline, shmop, SimpleXML, sockets, sysvmsg, sysvsem, sysvshm, tokenizer, xmlreader, xmlwriter, xsl, zip, Zend OPcache
+------------------------------------+---------+--------+
| Flarum Extensions                  |         |        |
+------------------------------------+---------+--------+
| ID                                 | Version | Commit |
+------------------------------------+---------+--------+
| flarum-flags                       | v1.3.0  |        |
| flarum-approval                    | v1.3.0  |        |
| flarum-tags                        | v1.3.0  |        |
| flarum-suspend                     | v1.3.0  |        |
| flarum-markdown                    | v1.3.0  |        |
| flarum-likes                       | v1.3.0  |        |
| afrux-forum-widgets-core           | v0.1.7  |        |
| flarum-subscriptions               | v1.3.0  |        |
| fof-byobu                          | 1.1.6   |        |
| v17development-seo                 | v1.8.0  |        |
| therealsujitk-gifs                 | v4.1.1  |        |
| sycho-move-posts                   | v0.1.6  |        |
| nearata-no-self-likes              | v1.2.0  |        |
| justoverclock-welcomebox           | 1.3.6   |        |
| justoverclock-last-post-useravatar | 0.1.8   |        |
| justoverclock-events-countdown     | 0.1.8   |        |
| justoverclock-country-flags        | 0.2.1   |        |
| justoverclock-contactme            | 0.2.3   |        |
| fof-username-request               | 1.0.1   |        |
| fof-user-directory                 | 1.2.3   |        |
| fof-upload                         | 1.2.2   |        |
| fof-terms                          | 1.1.2   |        |
| fof-split                          | 1.0.1   |        |
| fof-spamblock                      | 1.0.2   |        |
| fof-sitemap                        | 1.0.3   |        |
| fof-share-social                   | 1.1.0   |        |
| fof-recaptcha                      | 1.1.0   |        |
| fof-pretty-mail                    | 1.0.1   |        |
| fof-polls                          | 1.0.5   |        |
| fof-pages                          | 1.0.4   |        |
| fof-nightmode                      | 1.2.2   |        |
| fof-merge-discussions              | 1.3.0   |        |
| fof-links                          | 1.1.1   |        |
| fof-linguist                       | 1.0.4   |        |
| fof-ignore-users                   | 1.0.2   |        |
| fof-html-errors                    | 1.0.0   |        |
| fof-forum-statistics-widget        | 1.0.2   |        |
| fof-formatting                     | 1.0.2   |        |
| fof-follow-tags                    | 1.1.3   |        |
| fof-drafts                         | 1.1.2   |        |
| fof-disposable-emails              | 1.0.0   |        |
| fof-default-user-preferences       | 1.0.0   |        |
| fof-cookie-consent                 | 1.1.0   |        |
| fof-bbcode-details                 | 1.0.0   |        |
| fof-ban-ips                        | 1.0.2   |        |
| fof-analytics                      | 1.0.0   |        |
| flarum-sticky                      | v1.3.0  |        |
| flarum-statistics                  | v1.3.0  |        |
| flarum-mentions                    | v1.3.0  |        |
| flarum-lock                        | v1.3.0  |        |
| flarum-lang-german                 | 1.3.0   |        |
| flarum-lang-french                 | v3.7.0  |        |
| flarum-lang-english                | v1.3.0  |        |
| flarum-emoji                       | v1.3.0  |        |
| flarum-bbcode                      | v1.3.0  |        |
| darkle-fancybox                    | 1.1.0   |        |
| clarkwinkelmann-likes-received     | 1.0.2   |        |
| clarkwinkelmann-emojionearea       | 1.0.0   |        |
| clarkwinkelmann-circle-groups      | 1.0.1   |        |
| askvortsov-moderator-warnings      | v0.6.1  |        |
| askvortsov-categories              | v3.0.6  |        |
| afrux-online-users-widget          | v0.1.6  |        |
| acpl-mobile-tab                    | 1.1.0   |        |
+------------------------------------+---------+--------+
Base URL: 
Installation path: 
Queue driver: sync
Mail driver: smtp
Debug mode: off

Could anyone support here please? Thanks!

clarkwinkelmann

m4v3rick can you share a public link where we can experience the issue?

Does it happen for every discussion on the forum or only some of them?

m4v3rick

clarkwinkelmann

The less posts, the better.
Discussions with many posts:
https://forum.wirsansoizburg.at/d/135-was-wurde-aus-allgemein
https://forum.wirsansoizburg.at/d/75-ofb-a-nationalteam

Discussions with just a few posts:
https://forum.wirsansoizburg.at/d/567-37-daouda-guindo
https://forum.wirsansoizburg.at/d/284-frauenfussball-allgemein

luceos

Is this performance issue there when you disable all 3rd party extensions?

m4v3rick

luceos disable all 3rd party extensions?

Is there a command for that or do I have to disable one after the other manually?

m4v3rick

luceos

Darkle

Okay I found the problem.
"Likes" Extension is causing the massive latency on refresh.

We have around 440 members and around 430k likes.

I had a similiar issue once with the Like Counter extension beta: https://discuss.flarum.org/d/23878-like-counter-extension-beta/36 from @johnlewissims.
@askvortsov identified it and I had to switch that extension off.

@clarkwinkelmann's extension Likes Received is what I am using now which raised some mariaDB update errors on my server: https://discuss.flarum.org/d/24489-likes-received/31 But I am fine staying at the current mariaDB version.

Back to the Likes-Extension: what can I do? If I switch it off and refresh a discussion with more than 10k posts it is getting refreshed immediately. Community really loves it so switching it off is no option I guess.

Darkle

m4v3rick Manually would be preferable, so you can also detect at the same time the extension that is causing the issue.

luceos

You can change the settings table key extensions_enabled, as long as it's valid json array it should be safe to do.

luceos

m4v3rick "Likes" Extension is causing the massive latency on refresh.

This is the flarum/likes extension? If so we need to investigate this as soon as possible and patch it.

Never mind, the issue seems obvious. For a discussion every like is always loaded, with a very vast amount of likes this indeed won't perform well. I'll push this to the top of our todo.

m4v3rick

luceos This is the flarum/likes extension?

Correct. If I turn it off, everything works really smooth and fast.

luceos I'll push this to the top of our todo.

Thanks for your help!

Edit:
And if you need some testing. Let me know - happy to help.

m4v3rick

luceos For a discussion every like is always loaded, with a very vast amount of likes this indeed won't perform well. I'll push this to the top of our todo.

Will the flarum/likes extension be fixed before a new flarum version or with a new flarum version?

m4v3rick

Guess that's a no: https://discuss.flarum.org/d/27852-flarum-v1-release-announcements/17

Any news to this issue?

SychO

m4v3rick I investigated the issue a few weeks ago. I remember the discussions you linked had little to no likes, which leads me to believe the problem is something else.

The best would be to install the fof/clockwork extension to analyse why requests are taking so long to load.

SychO

m4v3rick SychO

m4v3rick

SychO

Well the first link i posted has 5k posts and almost 12k likes. Is that little to no likes? The 2nd link I posted has around 7k posts with 11k likes.

Also if I switch the like extension off it works smooth.

I will try clockwork if you are sure that's not much likes.

JoshyPHP

My apologies for butting in a conversation I know nothing about but this line, is it run whenever a post is saved or when its data is sent? If the extension runs a grouping query per post then you could easily run into catastrophic performance issues once the number of rows exceed the amount of free RAM. That's not something you'd notice during development, unless your test board is on a small VPS of sorts.

clarkwinkelmann

JoshyPHP the code you linked is not compatible with Flarum 1.x, but yes it would have run one time for each post returned by the REST API or part of the preloaded payload.

SychO

m4v3rick I'm not saying it isn't the likes extension btw. Just that based on the discussions linked it shouldn't be a problem with loading all likes of a post, but probably something else.

m4v3rick Well the first link i posted has 5k posts and almost 12k likes. Is that little to no likes?

I'm guessing 12k is across all posts of the discussion? you have to look at the posts visible when the discussion is loaded. which is about 20 posts, and across all first loaded 20 posts, there seem to be less than 20 likes.

To better diagnose the issue it would be best to install fof/clockwork and take a look at what's happening. Hit meup on discord if you need help on how to share the right clockwork produced information (should make a tutorial or smth cause it's not straightforward to use)

JoshyPHP

Oh right, I was reading from the wrong extension's repository. 😳

Your extension stores the number of likes in the users table, so of course it shouldn't have any measurable impact on SQL performance.

Wellwisher

What happens if you only disable these two extensions:

nearata-no-self-likes
clarkwinkelmann-likes-received

m4v3rick

Wellwisher clarkwinkelmann-likes-received

That one I already disabled because the maria DB update broke the refreshing command. Dunno why but I already discussed it in another thread.

Haven't tried no-self-likes and will give it a shot.
Edit: Just tried it, but didn't help.

clarkwinkelmann

It's a known issue that the flarum/likes extension doesn't scale well, because it loads the list of all likers for each post.

However we have yet to find out exactly how bad it is, and what the best solution is. I have personally not troubleshooted this and based on the earlier replies above, it seems like those who tried replicating didn't have a good enough data set.

The easiest solution would be to only compute the total amount of likes, save it in the database, and then show that number on the post instead of the list of people who liked. Currently the database structure and code for this doesn't exist. Then the existing relationship for likers would have to be modified to only return a subset of the likers if we want to preserve the same UI as currently. And finally a new API endpoint would have to be added to load the list of likers on demand if we want to preserve that feature when clicking "More".

The easiest short-term solution would be to create a fork of the likes extension that drops the likers and only shows the total. It would be the easiest implementation. I don't think we should drop the list of likers from the core extension, it's a nice UX of the current extension. But this could then become an option in the Likes extension so that high activity forums can hide the list of likers entirely to improve performance further.

My "likes received" extension already does the pre-calulation of like count, but only for user profiles, it doesn't change how likes are counted and returned for individual posts.

luceos

I have actually been working on a tentative attempt to fix it (https://github.com/flarum/framework/compare/dk/likes/load-only-necessary-likes?expand=1), however I will need to test this against a dataset that's large enough to notice the changes in performance before this can be reviewed. As such this won't be in the upcoming release yet and I'm careful stating it will be in the one thereafter as it might contain backward breaking changes..