Flarum forum running extremely slow after migration to Azure

threefivehundred

Pretty much at a loss here because I've been messing around with this for quite a while and can't seem to nail down the issue.

I migrated a small existing Flarum forum from a different cloud platform to Azure. Didn't make any major changes to the configuration, everything went pretty smoothly. The issue is that the forum is extremely slow. It takes about 8-10 seconds just to load the homepage. Loading any pages with posts takes about 4-5 seconds.

I checked a lot of different things so I'll list them here individually.

Forum size

This isn't a large forum by any means. We have under 15 users and something like 100 total posts. The SQL dump is like 300kb.

Hosting

The forum is hosted with Azure App Service + Azure Managed Database for App Service.

Database:    Burstable, B1s, 1 vCores, 1 GiB RAM, 20 GiB storage
App Service: Basic B1 1 Core 1.75 GiB RAM 10 GiB storage

The machine specs are not an issue, I've attempted to scale up both the DB and App Service to premium plans, and there was no noticeable performance difference. Besides, the previous cloud platform had a similar configuration, but the pages on the forum were loading >1s, so I'm pretty sure the issue is elsewhere

Database Performance

I first thought the database might not be performing well, so I ran a Sysbench test from the App Service, but it seems fine.

SQL statistics:
    queries performed:
        read:                            104902
        write:                           29972
        other:                           14986
        total:                           149860
    transactions:                        7493   (124.83 per sec.)
    queries:                             149860 (2496.60 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0232s
    total number of events:              7493

Latency (ms):
         min:                                   17.64
         avg:                                   32.03
         max:                                  501.91
         95th percentile:                       51.02
         sum:                               239998.10

Threads fairness:
    events (avg/stddev):           1873.2500/21.44
    execution time (avg/stddev):   59.9995/0.01

Extensions

I disabled all extensions except for tags and and the english language pack, and it did speed up a bit but not by much.

So I tried to set up a new forum from scratch, without migrating anything, on a new App Service and new DB, and I get the same issue (albeit a bit faster)

Here is the link to that forum: https://shfc-forum-app.azurewebsites.net

You can try posting something to see how long it takes and get an idea of what I mean.

Other info:

PHP Info: https://shfc-forum-app.azurewebsites.net/info.php

App Service:

Database:

Flarum Info:

Flarum core: 1.8.5
PHP version: 8.3.8
MySQL version: 8.0.36-cluster
Loaded extensions: Core, date, libxml, openssl, pcre, sqlite3, zlib, ctype, curl, dom, fileinfo, filter, ftp, hash, iconv, json, mbstring, SPL, session, PDO, pdo_sqlite, standard, posix, random, readline, Reflection, Phar, SimpleXML, tokenizer, xml, xmlreader, xmlwriter, mysqlnd, sqlsrv, pdo_sqlsrv, bcmath, calendar, exif, gd, gettext, gmp, imap, intl, ldap, mongodb, mysqli, odbc, pcntl, pdo_mysql, PDO_ODBC, pdo_pgsql, pgsql, redis, shmop, soap, sockets, sodium, sysvmsg, sysvsem, sysvshm, tidy, xsl, zip, Zend OPcache
+----------------------+---------+--------+
| Flarum Extensions    |         |        |
+----------------------+---------+--------+
| ID                   | Version | Commit |
+----------------------+---------+--------+
| flarum-flags         | v1.8.0  |        |
| flarum-approval      | v1.8.1  |        |
| flarum-tags          | v1.8.0  |        |
| flarum-suspend       | v1.8.1  |        |
| flarum-subscriptions | v1.8.0  |        |
| flarum-sticky        | v1.8.0  |        |
| flarum-statistics    | v1.8.0  |        |
| flarum-mentions      | v1.8.3  |        |
| flarum-markdown      | v1.8.0  |        |
| flarum-lock          | v1.8.0  |        |
| flarum-likes         | v1.8.0  |        |
| flarum-lang-english  | v1.8.0  |        |
| flarum-emoji         | v1.8.0  |        |
| flarum-bbcode        | v1.8.0  |        |
+----------------------+---------+--------+
Base URL: https://shfc-forum-app.azurewebsites.net
Installation path: /home/site/wwwroot
Queue driver: sync
Session driver: file
Mail driver: mail
Debug mode: off

luceos

You can use FriendsOfFlarum clockwork or the Sentry extension to identify loading bottlenecks. Especially clockwork is great for this, but sentry recently also introduced some metrics that help narrow things down.

Even with tools like that it might be hard to trace the actual issue. One should look at different metrics as well, like memory usage, swap usage and disk speed throttling. Many cloud providers limit the available disk speed based on the size of the node you have.

PS Managed Flarum hosting also runs on Azure (k8s to be precise). We decided to move away from the managed database option quite a while ago, because it didn't live up to our expectations. The database runs on its own machine now. I am not the brains behind our stack, so I will ask someone else in the team to respond here in case they have more to say to help out 🙂

threefivehundred

Thanks luceos

I installed clockwork and this is what is shows:

GET /forum/ (db queries 683 ms)

GET /discussions/3-test-post-1 (db queries 1365 ms)

What is happening during the Application Booting and Application Running phases? That seems to eat a considerable chunk of time...

luceos

threefivehundred try hitting /api/discussions directly, not sure clockwork is active then too. What the index page on load does is actually use an internal api client to retrieve the index discussions.
If you see a massive amount of db queries load time, then this must be a database issue. Either this is latency, the node is too meager for what it tries to do or the mysql server isn't using its memory sufficiently to perform well.

Oshvam

luceos Hi, in my case I'm on a managed VPS with similar specs, but I find that some discussions have an excessive load (It's a thread with more than 1000 replies), but the consumption is crazy. I don't know if there is any way to optimize this, it doesn't seem that any extension is the one that is generating extreme requests to the DB, it seems that it is the core of Flarum that is making excessive requests.

Any suggestions?

luceos

It still feels like an issue with optimization. This discussion has over 7k posts and loads TTFB at around 600ms: https://discuss.flarum.org/d/187-word-association-game.

Discuss seriously has no optimization whatsoever, it even runs many non-vanilla extensions as well.

Can you share your php flarum info please, just to check on versions of some of the extensions @Oshvam ?

Oshvam

luceos Yes, of course. I share it here:

Flarum core: 1.8.5
PHP version: 8.1.24
MySQL version: 5.7.44
Loaded extensions: Core, date, libxml, openssl, pcre, sqlite3, zlib, bcmath, calendar, ctype, curl, dom, hash, fileinfo, filter, ftp, gd, gettext, json, iconv, intl, SPL, mbstring, session, standard, exif, mysqlnd, PDO, pdo_mysql, pdo_sqlite, Phar, posix, Reflection, mysqli, SimpleXML, soap, sockets, sodium, tokenizer, xml, xmlreader, xmlwriter, xsl, zip, redis, Zend OPcache
+----------------------------------+---------------+--------+
| Flarum Extensions | | |
+----------------------------------+---------------+--------+
| ID | Version | Commit |
+----------------------------------+---------------+--------+
| flarum-flags | v1.8.0 | |
| flarum-tags | v1.8.0 | |
| flarum-approval | v1.8.1 | |
| flarum-suspend | v1.8.1 | |
| blomstra-gdpr | 0.1.0-beta.20 | |
| flarum-mentions | v1.8.3 | |
| flarum-subscriptions | v1.8.0 | |
| flarum-markdown | v1.8.0 | |
| afrux-forum-widgets-core | v0.1.7 | |
| fof-oauth | 1.6.10 | |
| fof-byobu | 1.3.6 | |
| ianm-level-ranks | 1.1.1 | |
| fof-follow-tags | 1.2.5 | |
| flarum-likes | v1.8.0 | |
| fof-masquerade | 2.1.4 | |
| yohtozze-markdown-paste | 1.1.0 | |
| rob006-last-post-avatar | 1.2.4 | |
| nodeloc-my-emoji | 1.0.1 | |
| nearata-twofactor | v2.2.1 | |
| nearata-login-notification | 1.0.0 | |
| nearata-internal-links-noreload | v2.2.0 | |
| matteocontrini-imgur-upload | v3.9.1 | |
| matteociaroni-public-suspensions | v1.0.0 | |
| justoverclock-custom-html-widget | 0.1.4 | |
| ianm-html-head | 1.2.3 | |
| glowingblue-redis-setup | 1.2.2 | |
| fof-recaptcha | 1.3.4 | |
| fof-polls | 2.2.3 | |
| fof-pages | 1.0.8 | |
| fof-nightmode | 1.5.3 | |
| fof-moderator-notes | 1.2.2 | |
| fof-links | 1.2.3 | |
| fof-linguist | 1.1.2 | |
| fof-ignore-users | 1.2.1 | |
| fof-html-errors | 1.1.2 | |
| fof-forum-statistics-widget | 1.2.1 | |
| fof-formatting | 1.0.3 | |
| fof-cookie-consent | 1.1.3 | |
| fof-best-answer | 1.4.5 | |
| fof-ban-ips | 1.1.1 | |
| flarumtr-mobile-search | v1.2 | |
| flarum-sticky | v1.8.0 | |
| flarum-statistics | v1.8.0 | |
| flarum-nicknames | v1.8.0 | |
| flarum-lock | v1.8.0 | |
| flarum-lang-spanish | 1.8.0 | |
| flarum-emoji | v1.8.0 | |
| flarum-bbcode | v1.8.0 | |
| ecnu-im-sticky-sidenav | v1.1.0 | |
| dhtml-composer | 2.1.5 | |
| davwheat-share | v1.1.0 | |
| davwheat-custom-sidenav-links | 1.0.1 | |
| datlechin-title-length | v0.1.1 | |
| datlechin-link-preview | v1.4.0 | |
| datlechin-add-like-controls | v1.0.6 | |
| darkle-fancybox | 1.1.3 | |
| clarkwinkelmann-roll-die | 1.0.0 | |
| clarkwinkelmann-post-bookmarks | 1.0.0 | |
| clarkwinkelmann-likes-received | 1.0.2 | |
| clarkwinkelmann-group-invitation | 1.0.2 | |
| clarkwinkelmann-circle-groups | 1.0.1 | |
| blomstra-usercard-stats | 0.1.2 | |
| askvortsov-moderator-warnings | v0.6.3 | |
| askvortsov-auto-moderator | v0.1.3 | |
| acpl-mobile-tab | 1.4.3 | |
| acpl-lscache | v2.3.1 | |
+----------------------------------+---------------+--------+

luceos

Oshvam my suggestion would be to create a staging environment with exactly the same data (or connected to the live database for now), then disabling half of the extensions to see if that speeds things up. If you don't see a single culprit when you come back to the 1st party flarum extensions, then this must be a database optimization issue.

Oshvam

luceos It definitely looks like a database optimisation problem. I've created some extra indexes and performance has improved considerably, although it's still far from optimal performance, but it's a step forward.

These are some of the indexes created:

ALTER TABLE posts ADD INDEX idx_user_id_type (user_id, type);

ALTER TABLE discussion_user ADD INDEX idx_user_id_discussion_id (user_id, discussion_id);

ALTER TABLE notifications ADD INDEX idx_user_id_type_created_at (user_id, type, created_at);

ALTER TABLE post_likes ADD INDEX idx_post_id_user_id (post_id, user_id);

ALTER TABLE post_mentions_post ADD INDEX idx_post_id_user_id (post_id, user_id);

I don't know if there is anything else I can do to improve my database performance, if you have any advice, I would be eternally grateful.