Posting in large forum takes time with email enabled

[marksolesbury]

Support Request Template
What version of phpBB are you using? phpBB 3.3.14
What is your board's URL? https://northants4x4forum.com/
Who do you host your board with? HostGator
How did you install your board? I used the download package from phpBB.com
What is the most recent action performed on your board? Update from a previous version of phpBB3
Is registration required to reproduce this issue? Yes
Do you have any MODs installed? No
Do you have any extensions installed? Yes
What version of phpBB3 did you update from? phpBB 3.0.13
What extensions do you have installed? Change Logo
Clipboard Image
Collapsible Forum Categories
External Links Open in New Window
Lightbo
Password Strength
phpBB Media Embed PlugIn
What styles do you currently have installed? prosilver
What language(s) is your board currently using? British
Which database type/version are you using? I Don't Know
What is your level of experience? Comfortable with PHP and phpBB
What username can be used to view this issue? PhpbbSupport
What password can be used to view this issue? Support123!
What actions did you take (updating your board; installing a MOD, style or extension; etc.) prior to this problem becoming noticeable? Followed guide, removed files apart form config.php etc, uploaded fresh 3.3.14, migrated BD.
Please describe your problem. Posting in our main 'Northants4x4 Arms Pub' topic takes 10-15 seconds. This area has Topics: 12964 / Posts: 190378. Other areas with less posts take 1 second.
I have done some testing and found that If I disable 'email' board wide, the posting reduces to 1 second in this forum.
I have optimised the tables which had no impact.
Generated by SRT Generator

[warmweer]

I used the testing account you supplied and posted in the problematic forum and in another forum.
The second one was almost instantaneous.
The "problematic" forum was slower, but not really problematic, about 2 seconds which actually isn't bad considering the number of images that were loaded.

What's the PHP version you're using?

[KevC]

I wonder if you have a lot of subscribers and it has to queue up the notifications when the post happens.

[marksolesbury]

I used the testing account you supplied and posted in the problematic forum and in another forum.
The second one was almost instantaneous.
The "problematic" forum was slower, but not really problematic, about 2 seconds which actually isn't bad considering the number of images that were loaded.

What's the PHP version you're using?

8.3.6

You can start a new empty post and its the same - 2 seconds is quick though - I saw someone use the account and posted at the same time and I saw 10 seconds - Its odd that if I disable email its instant - If I disable email now, do you want to try again to confirm?

[marksolesbury]

I wonder if you have a lot of subscribers and it has to queue up the notifications when the post happens.

Even on a new topic - with no-one subscribed?

[KevC]

But they might be subscribed to the forum itself, not the topic.

Can can set up notifications to a new topic or post in a forum. If they don't visit regularly but just want to know when something pops up they might subscribe that way.

Someone who knows more than me about databases might be able to tell you if there's a flag somewhere saying how many people that might be.

[marksolesbury]

But they might be subscribed to the forum itself, not the topic.

I guess so - this forum has been dormant since covid and I want to reenergise it now - I'm happy to purge the subscriptions so that users start fresh.

We only have 1300 users, but a majority will be not used.

[warmweer]

You can start a new empty post and its the same - 2 seconds is quick though - I saw someone use the account and posted at the same time and I saw 10 seconds - Its odd that if I disable email its instant - If I disable email now, do you want to try again to confirm?

Posted a couple of times more and can't really notice a difference.

[marksolesbury]

with developer tools open I see posting.php take 6-7 seconds to load here, so maybe my 10-15 was over the top.

With email disabled board wide, the posting.php takes 300ms - same topic, same forum.

Its still noticeably slower than other areas - Maybe its time for a new Pub2.0 area.

[warmweer]

Posted again (twice) about 2 minutes again (after you enabled mail).
It was noticeably slower (between 3-4 seconds)

Navigating to another topic and then returning to the problematic topic was instantaneous.

Unfortunately I don't have a clue what the reason could be.

[marksolesbury]

Browsing is indeed instant - I googled this and its been reported before on larger forums - something must be looping though many records somewhere to cause the delay.

[Brf]

If it is forum subscriptions causing the problem, it would be your phpbb_forums_watch table.

[thecoalman]

This may or may not be helpful for your specific issue but it can help with overall performance on large or old forum.

It marks all topics read for everyone older than year, the topics_track table can be particularly large and contain records 20 years old if the forum is that old. This isn't necessarily a bug but very few users ever use the "Mark X read" links that would clean it up.

Code: Select all

DELETE FROM phpbb_topics_track WHERE mark_time< UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 YEAR));
DELETE FROM phpbb_forums_track WHERE mark_time< UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 YEAR));
UPDATE phpbb_users SET user_lastmark=UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)) WHERE user_lastmark<UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)) AND user_id != 1;

[marksolesbury]

I think I’d be happy to mark everything read for everyone just now.
Then it’s fresh for everyone - it’s an old board dating back to 2006 so has lots of legacy stuff on it.

[marksolesbury]

This may or may not be helpful for your specific issue but it can help with overall performance on large or old forum.

It marks all topics read for everyone older than year, the topics_track table can be particularly large and contain records 20 years old if the forum is that old. This isn't necessarily a bug but very few users ever use the "Mark X read" links that would clean it up.

Code: Select all

DELETE FROM phpbb_topics_track WHERE mark_time< UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 YEAR));
DELETE FROM phpbb_forums_track WHERE mark_time< UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 YEAR));
UPDATE phpbb_users SET user_lastmark=UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)) WHERE user_lastmark<UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)) AND user_id != 1;

This removed about 400K rows, but has not improved performance.

Posting.php still takes 5-7 seconds to return when on this forum, and 300ms on others.