Slow "Delete Forever"

HD3D

Deleting Discussions or Posts Forever is too slow

What could be the reason? It takes more than a minute to delete 1 discussion and Oops something went wrong error appears upong deleting 5-10 discussions.

Flarum core: 1.8.5
PHP version: 8.2.22
MySQL version: 10.7.3-MariaDB-log
Loaded extensions: Core, date, libxml, openssl, pcre, sqlite3, zlib, bcmath, ctype, curl, dom, fileinfo, filter, ftp, gd, gettext, hash, iconv, intl, json, mbstring, SPL, session, pcntl, standard, mysqlnd, PDO, pdo_mysql, pdo_sqlite, Phar, posix, random, Reflection, mysqli, shmop, SimpleXML, soap, sockets, sodium, sysvsem, tokenizer, xml, xmlreader, xmlwriter, zip, redis, gmp, Zend OPcache
+------------------------------------------+------------+--------+
| Flarum Extensions                        |            |        |
+------------------------------------------+------------+--------+
| ID                                       | Version    | Commit |
+------------------------------------------+------------+--------+
| flarum-flags                             | v1.8.0     |        |
| flarum-tags                              | v1.8.1     |        |
| flarum-approval                          | v1.8.1     |        |
| flarum-suspend                           | v1.8.1     |        |
| flarum-mentions                          | v1.8.3     |        |
| flarum-subscriptions                     | v1.8.0     |        |
| flarum-sticky                            | v1.8.0     |        |
| flarum-markdown                          | v1.8.0     |        |
| afrux-forum-widgets-core                 | v0.1.7     |        |
| fof-user-directory                       | 1.3.3      |        |
| fof-follow-tags                          | 1.2.6      |        |
| ianm-twofactor                           | 1.1.2      |        |
| fof-oauth                                | 1.6.10     |        |
| fof-impersonate                          | 1.1.1      |        |
| fof-byobu                                | 1.3.6      |        |
| flarumite-simple-discussion-views        | 1.2.3      |        |
| flarum-likes                             | v1.8.0     |        |
| the-turk-stickiest                       | 3.0.1      |        |
| the-turk-miserable-users                 | 1.0.2      |        |
| the-turk-diff                            | 1.1.2      |        |
| sycho-private-facade                     | v0.1.16    |        |
| swaggymacro-only-starter                 | 0.6.6      |        |
| serakoi-flarumstaffbadge                 | 1.2.2      |        |
| rob006-last-post-avatar                  | 1.2.4      |        |
| ramesh-dada-bbcode-alerts                | 6.21       |        |
| nearata-related-discussions              | 1.4.0      |        |
| nearata-prevent-double-posting           | 1.1.0      |        |
| nearata-maintenance-mode                 | 1.0.0      |        |
| nearata-copy-code-to-clipboard           | v2.2.0     |        |
| mickmelon-coloured-usernames             | 0.1.1      |        |
| matteocontrini-imgur-upload              | v3.9.1     |        |
| matteociaroni-public-suspensions         | v1.0.0     |        |
| malago-ads                               | 0.3.2      |        |
| luceos-minis                             | 0.1-beta.1 |        |
| katosdev-automore                        | 1.0        |        |
| justoverclock-welcomebox                 | 2.0.2      |        |
| justoverclock-staff-members-widget       | 0.1.3      |        |
| justoverclock-purify                     | 0.1.7      |        |
| justoverclock-feedback                   | 0.1.9      |        |
| justoverclock-edit-posts                 | 0.1.1      |        |
| justoverclock-discussion-info            | 0.1.5      |        |
| justoverclock-country-flags              | 1.0.0      |        |
| justoverclock-be-active-user-indicator   | 0.1.0      |        |
| justoverclock-auto-post-count-badge      | 1.0.0      |        |
| ianm-log-viewer                          | 0.1.1      |        |
| ianm-follow-users                        | 1.4.8      |        |
| glowingblue-password-strength            | 4.0.0      |        |
| foskym-sorts-for-user-directory          | v0.3.2     |        |
| fof-username-request                     | 1.2.4      |        |
| fof-user-bio                             | 1.4.0      |        |
| fof-subscribed                           | 1.1.4      |        |
| fof-socialprofile                        | 1.1.6      |        |
| fof-recaptcha                            | 1.3.4      |        |
| fof-profile-image-crop                   | 1.1.6      |        |
| fof-polls                                | 2.2.4      |        |
| fof-pages                                | 1.0.8      |        |
| fof-nightmode                            | 1.5.3      |        |
| fof-moderator-notes                      | 1.2.2      |        |
| fof-merge-discussions                    | 1.4.1      |        |
| fof-links                                | 1.2.3      |        |
| fof-linguist                             | 1.1.2      |        |
| fof-ignore-users                         | 1.2.1      |        |
| fof-geoip                                | 1.4.6      |        |
| fof-forum-statistics-widget              | 1.2.1      |        |
| fof-formatting                           | 1.0.3      |        |
| fof-filter                               | 1.2.0      |        |
| fof-drafts                               | 1.2.11     |        |
| fof-disposable-emails                    | 1.0.0      |        |
| fof-best-answer                          | 1.4.5      |        |
| fof-anti-spam                            | 1.1.2      |        |
| flarum-statistics                        | v1.8.0     |        |
| flarum-lock                              | v1.8.0     |        |
| flarum-lang-english                      | v1.8.0     |        |
| flarum-emoji                             | v1.8.0     |        |
| flarum-bbcode                            | v1.8.0     |        |
| dhtml-composer                           | 2.1.5      |        |
| datlechin-title-length                   | v0.1.1     |        |
| datlechin-signup-button                  | v0.1.1     |        |
| datlechin-scroll-buttons                 | v1.1.0     |        |
| datlechin-keyboard-shortcuts             | v0.1.1     |        |
| clarkwinkelmann-username-blacklist       | 1.0.0      |        |
| clarkwinkelmann-popular-discussion-badge | 1.1.1      |        |
| clarkwinkelmann-no-email-notifications   | 1.0.0      |        |
| clarkwinkelmann-mass-actions             | 1.1.2      |        |
| clarkwinkelmann-mailing                  | 1.1.0      |        |
| clarkwinkelmann-group-list               | 1.0.0      |        |
| clarkwinkelmann-emojionearea             | 1.0.0      |        |
| clarkwinkelmann-discussion-open-position | 1.1.0      |        |
| clarkwinkelmann-discussion-bookmarks     | 2.0.1      |        |
| clarkwinkelmann-circle-groups            | 1.0.1      |        |
| blomstra-usercard-stats                  | 0.1.2      |        |
| blomstra-user-filter                     | 0.1.4      |        |
| blomstra-sort-order-toggle               | 0.2.0      |        |
| askvortsov-pwa                           | v3.4.1     |        |
| askvortsov-moderator-warnings            | v0.6.3     |        |
| askvortsov-discussion-templates          | v0.8.3     |        |
| afrux-news-widget                        | v0.1.1     |        |
| acpl-mobile-tab                          | 1.4.4      |        |
| acpl-lscache                             | v2.3.1     |        |
+------------------------------------------+------------+--------+
Queue driver: redis
Session driver: redis (Code override. Configured to file)
Scheduler status: Active
Mail driver: mail
Debug mode: off

luceos

HD3D please check storage/logs for any errors and post the output of php flarum info.

HD3D

luceos info posted.

There are no logs recorded during the Delete Forever process.

Edit:
Getting this after turning on debug mode.

clarkwinkelmann

HD3D if there's nothing else logged, then the request is probably being killed by the webserver for taking too long, thus we don't get any insight as to what takes so long inside the request. You could try again with the browser network tab open to check the HTTP status of the response.

There's probably an extension interacting with an external service, or a problem with the database server. The best would be to disable some extensions and see if the problem goes away. Do you have access to a development server where you could clone your website? Cloning the website would also inform us whether this is specific to the set of extensions or specific to the original production server. Then you could easily disable half the extensions to try to isolate the origin of the problem. It's more tricky in production as disabling extensions could cause issues with active users. I'd start by trying to disable the caching extension since it wouldn't cause any downtime.

Are you deleting discussions one by one, or multiple at the same time (I see you have Mass Actions in the extension list)

HD3D

clarkwinkelmann This is what happened when the Oops something went wrong appeared while deleting discussions forever

I can try cloning the site and disabling extensions, I do have full access to my web server.

I usually use Mass Actions to delete multiple discussion.

HD3D

luceos

clarkwinkelmann I'd start by trying to disable the caching extension since it wouldn't cause any downtime.

That didn't help, I will try disabling some more extensions but after cloning the site.

HD3D

clarkwinkelmann luceos Disabled half extensions with no success.

I even switched from mariadb 10.7.3 to 11.3 though it improved the speed but didn't solve the issue.

Not only deleting discussions forever but selecting Best Answer also takes around 10-15 seconds

HD3D

clarkwinkelmann

luceos

Wellwisher

Mariadb slowlog:

# Query_time: 32.984021  Lock_time: 0.014961  Rows_sent: 0  Rows_examined: 14974218
# Rows_affected: 0  Bytes_sent: 0
SET timestamp=1726442872;
delete from `notifications` where `type` in ('discussionRenamed', 'newPost', 'newDiscussionInTag', 'newPostInTag', 'newDiscussionTag', 'byobuPrivateDiscussionCreated', 'byobuPrivateDiscussionReplied', 'byobuRecipientRemoved', 'byobuPrivateDiscussionAdded', 'byobuMadePublic', 'newDiscussionByUser', 'newPostByUser', 'discussionCreated', 'discussionMerged', 'selectBestAnswer', 'awardedBestAnswer', 'bestAnswerInDiscussion', 'discussionLocked') and `subject_id` = 571849;

"The slow query you're facing is a result of examining a large number of rows—over 14 million (Rows_examined: 14974218)—in the notifications table, leading to long query execution times (Query_time: 32.984021). There are a few steps you can take to optimize this query"

How do I fix it?

luceos

HD3D can you click on one of the failed entries and copy the part that says "headers" > "response"?

luceos

HD3D in your screenshot you clicked the "response" tab on the right hand side, click the one called "headers" and look for "Response headers", especially status code, but more if possible.

HD3D

luceos

POST /api/discussions/53373 undefined
Host: www.domain.org
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:129.0) Gecko/20100101 Firefox/129.0
Accept: */*
Accept-Language: en-US,en;q=0.5
Accept-Encoding: gzip, deflate, br, zstd
Content-Type: application/json; charset=utf-8
Content-Length: 2
Referer: https://www.domain.org/?sort=oldest&author=1kakak
X-CSRF-Token: O8DvrLSLzvQIVk6WzmIBD2dlC7oE6BdQVlQVO7ID
X-HTTP-Method-Override: DELETE
Origin: https://www.domain.org
Sec-Fetch-Dest: empty
Sec-Fetch-Mode: cors
Sec-Fetch-Site: same-origin
Connection: keep-alive
Cookie: _ga_7J2ZMQ7998=GS1.1.1725450776.41.1.1725451533.0.0.0; _ga=GA1.1.678884654.1723849783; flarum_remember=2xeuV88CEi0yS4tQL3xEdrcd93dZXLy2p9X878kp; flarum_nightmode=2; flarum_lscache_vary=O8DvrLSLzvQIVk6WzmIBD2dlC7oE6BdQVlQVO7ID; flarum_session=TBsXWHE76FbHz9250fWWapt4rasAvaFCQEBdnTcKw; cf_clearance=PjosF2UyJ3qVUtAOf.Ci12u61l_4yNTJ8l1DpKa47y4-1725584577-1.2.1.1-GUF7UeOaSsrx35sUfzgWiaqMQYmFXEHD5XkJxHJXjnnQcrXVX9fLNKpbsJ6GNdKsd4vxR0abaIRaGfoi2chttsUVhsTWLxYRMUwvyoR0QWZjy4NSfzol4P7Ese8KOszTXKsC1XWZfWavl_l50l6NwMjVzlpnwn6j0tOIFEmtArJqPqanPNQihIVLNMCbboZWeff.XERhZb6sYLM6FLwm9Tr__LJIRVs.Vow4TknqzaM7BERoQZJsZrIcgbKNUofHm0bQWJTeNvHrN1V_vUL_8BCtAlZQRl.uWyloKZQH6wd8xW12qEqtgsKcctVHH2xi6ygxovnpi4WgRNfRo_AeK5.Ms63lKs_eAbj0qkfuI2MA

Wellwisher

HD3D was clarkwinkelmann-mass-actions disabled on your clone site and did this have any difference on the performance of post deletions?

HD3D

Wellwisher I disabled it even on the main site but it made no difference.

Really hard to find what's causing the "Delete Forever" take forever.

Wellwisher

HD3D

Sounds like this will be a process of elimination due to lack of error codes.
Before you poke holes in the sever setup, I'd put in some work of ruling out if an extension is causing the issue.
Clark has suggested this to be within the realm of possibilities.

You mentioned that u've turned off 50% of ur extensions (on ur clone), I'd be tempted to turn off all of them one by one to see if there's a culprit.

There's a few things you can look at in your sever configuration but again, I'd keep it simple to begin with.

A wise man once said to me (he was an SMF Dev) that wherever there's a computer problem, 99.99% of the time, the fault is located between the "keyboard" and "the chair". This is basically any human being who's responsible for adding a character of code in flarum. Hence why I say, rule out extensions first. 😂

clarkwinkelmann

I had a look at my own install and it seems like there are no indexes on either the type or subject_id columns on the notifications table.

But the concern here isn't just the indexes, I don't think it's really expected to have that many notifications stored in the table. How big is that forum in terms of number of users, activity and how old the forum is?

I don't remember what the logic is for clearing notifications. Maybe they just pile up and don't get every removed? 😬

luceos

clarkwinkelmann I don't remember what the logic is for clearing notifications. Maybe they just pile up and don't get every removed?

I think this is actually the case. Even those that are marked read are still kept in the table.

Wellwisher

clarkwinkelmann How big is that forum in terms of number of users, activity and how old the forum is?

453k Discussions, 758k Posts & 45k users
I joined in Jan 2022, HD's been running it well before then.
The place is super active daily.

Purging certain notifications every month from the database could solve the issue.
I know large SMF forums would often run cron jobs to get rid of junk every month.
SMF also had limited built in features to clean up and optimise database.
Flarum may need to adopt a similar method. Maybe there's already an extension that does this?

HD3D

clarkwinkelmann luceos That's a nightmare for large communities, hope to have a better solution

Wellwisher Purging certain notifications every month from the database could solve the issue.

That's what I did for now and seems like it fixed lots of issues.

Wellwisher I joined in Jan 2022

Don't hesitate to contact me there anytime : )

luceos It was 1.2GB, I purged the older notifications than 6 months and now it is ~2,649,689 InnoDB utf8mb4_unicode_ci 275.8 MiB

luceos

Wellwisher how big is the notifications table in your community?

Wellwisher

luceos mine's peanuts compared to HD's 😂
@HD3D I will hit you up at some point. Thanks bud.

It's important to note, my forum was a transfer from SMF.
My tiny community has been on Flarum since June, 2022

Users 6.9K
Discussions 8.0K
Posts 127.1K

Total rows: 37878