Index Pages Loading Very Slow

Get help with installation and running phpBB 3.2.x here. Please do not post bug reports, feature requests, or extension related questions here.
Inky Floaters
Registered User
Posts: 9
Joined: Tue Jun 30, 2020 3:52 pm

Index Pages Loading Very Slow

Post by Inky Floaters »

Support Request Template
What version of phpBB are you using? phpBB 3.2.3
What is your board's URL? https://www.vinylengine.com/turntable_forum/
Who do you host your board with? Hivelocity Dedicated Server
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? No
Do you have any MODs installed? No
Do you have any extensions installed? Yes
What version of phpBB3 did you update from? phpBB 3.2.1
What extensions do you have installed?
Avatar resize 1.0.0
National Flags 2.1.6
phpBB Gallery 3.2.2
phpBB Gallery Add-on: ACP Cleanup 1.2.0
phpBB Gallery Add-on: ACP Import 1.2.0
phpBB Gallery Add-on: Exif 1.2.0
Profile side switcher 1.0.1
Stop Forum Spam
What styles do you currently have installed? prosilver, custom
What language(s) is your board currently using? english
Which database type/version are you using? MySQL 5
What is your level of experience? Comfortable with PHP and phpBB
What actions did you take (updating your board; installing a MOD, style or extension; etc.) prior to this problem becoming noticeable? No answer given
Please describe your problem. Loading an index page (main or subforums) has an initial 2+ second delay, while forum topics, the gallery index and non phpBB3 pages are closer to 0.5 seconds (or less).

I've tried optimising the tables, tweaking SQL and PHP, switching to prosilver, removing adverts and various load options, but nothing seems to help.

Is something loaded in index pages, but not in topics? Or is one of the extensions known to cause load issues?

Any suggestions welcome :)
Generated by SRT Generator
User avatar
Mick
Support Team Member
Support Team Member
Posts: 22545
Joined: Fri Aug 29, 2008 9:49 am
Location: Cardiff

Re: Index Pages Loading Very Slow

Post by Mick »

The easiest way to test the extensions is to disable them all and, if that fixes your speed issue, enable them one at a time until you find the culprit if any.
"The more connected we get the more alone we become" - Kyle Broflovski©
User avatar
Brf
Support Team Member
Support Team Member
Posts: 52018
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: Index Pages Loading Very Slow

Post by Brf »

Your index and viewforums are loading immediately for me, but I am blocking your ads.
Inky Floaters
Registered User
Posts: 9
Joined: Tue Jun 30, 2020 3:52 pm

Re: Index Pages Loading Very Slow

Post by Inky Floaters »

Mick wrote:
Tue Jun 30, 2020 4:36 pm
The easiest way to test the extensions is to disable them all and, if that fixes your speed issue, enable them one at a time Until you find the culprit if any.
Thanks for pointing out the obvious :oops:

I'd tried disabling one extension at a time, but nothing helped.

However...I just disabled ALL the extensions (I forgot how easy it was after years of phpBB2 mods) and it fixed the load issues.

Switching back on one extension at a time it was fine until the flag extension was enabled.

It's weird as the topics contained flags and loaded fine with the extension enabled.

Thanks for that :) Now to find out what is up with the flags extension...
User avatar
warmweer
Jr. Extension Validator
Posts: 4750
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Bel ... gium

Re: Index Pages Loading Very Slow

Post by warmweer »

National Flags 2.1.6 >>> phpBB 3.2.2
Check the changes between 3.2.2 and 3.2.3 - that might give a clue and if that's the cause, then the fix is probably simple.
Spelling is freeware, which means you can use it for free.
On the other hand, it is not open source, which means you cannot change it or publish it in a modified form.
Inky Floaters
Registered User
Posts: 9
Joined: Tue Jun 30, 2020 3:52 pm

Re: Index Pages Loading Very Slow

Post by Inky Floaters »

warmweer wrote:
Tue Jun 30, 2020 5:43 pm
National Flags 2.1.6 >>> phpBB 3.2.2
Check the changes between 3.2.2 and 3.2.3 - that might give a clue and if that's the cause, then the fix is probably simple.
Thanks for the suggestion. I've also now tried national flags 2.1.7 and it has the same issue :( The issue didn't appear after upgrading, but over time. I think it's something to do with the number of users now with flags (about 115K) and related queries on index pages, despite having flags turned off for those pages?

As I was trying to fix any issues before moving to 3.3, I think I'll just upgrade my test forum to 3.3 and see which extensions work with it, and how much of my style needs changing, then come back to the flag extension later.
User avatar
RMcGirr83
Recognised Extension Developer
Posts: 21133
Joined: Wed Jun 22, 2005 4:33 pm
Location: Your display
Name: Rich McGirr

Re: Index Pages Loading Very Slow

Post by RMcGirr83 »

As the national flag extension author if you want to try and debug that please let me know as I believe I may have an answer for you. It is probably this query that is taking too long

https://github.com/rmcgirr83/nationalfl ... #L200-L207
In times of change, learners inherit the earth, while the learned find themselves beautifully equipped to deal with a world that no longer exists - Eric Hoffer
Former Modifications/Extensions Team Member | My extensions | My extensions are updated regularly on github
Appreciate the extensions/mods/support then buy me a beer
All requests for support via PM will be ignored
User avatar
RMcGirr83
Recognised Extension Developer
Posts: 21133
Joined: Wed Jun 22, 2005 4:33 pm
Location: Your display
Name: Rich McGirr

Re: Index Pages Loading Very Slow

Post by RMcGirr83 »

FWIW I just installed it on a test forum with 50000 users. The query in question took about .25 seconds to run and that's with all users having a flag set. Not sure why it's taking your forum so long to load because of that extension.
phpBB Version 3.3.0
PHP version 7.4.0
Database Version mySQL(i) 10.4.10-MariaDB

What are your versions? Maybe that has something to do with it. May also be that I'm the only one on the test forum too. Not sure.
In times of change, learners inherit the earth, while the learned find themselves beautifully equipped to deal with a world that no longer exists - Eric Hoffer
Former Modifications/Extensions Team Member | My extensions | My extensions are updated regularly on github
Appreciate the extensions/mods/support then buy me a beer
All requests for support via PM will be ignored
User avatar
JoshyPHP
Code Contributor
Posts: 1125
Joined: Mon Jul 11, 2011 12:28 am

Re: Index Pages Loading Very Slow

Post by JoshyPHP »

That forum has 483,609 users according to the stats. If there's no index on user_flag, MySQL will read the entire users table whether they have a flag set or not. I'm not sure it's worth adding an index if that's the only page that would use it.

IMO just cache that query for a day and never think about it again, kinda.
I wrote the thing that does BBCodes in 3.2+.
User avatar
RMcGirr83
Recognised Extension Developer
Posts: 21133
Joined: Wed Jun 22, 2005 4:33 pm
Location: Your display
Name: Rich McGirr

Re: Index Pages Loading Very Slow

Post by RMcGirr83 »

JoshyPHP wrote:
Wed Jul 01, 2020 12:20 am
IMO just cache that query for a day and never think about it again, kinda.
Brilliant, that took page build time from .4246 seconds to .1445 seconds. Thanks JoshyPHP :)

Code: Select all

Before: 0.14513s | After: 0.14559s | Elapsed [cache]: 0.00045s | Elapsed [db]: 0.24808s
In times of change, learners inherit the earth, while the learned find themselves beautifully equipped to deal with a world that no longer exists - Eric Hoffer
Former Modifications/Extensions Team Member | My extensions | My extensions are updated regularly on github
Appreciate the extensions/mods/support then buy me a beer
All requests for support via PM will be ignored
Inky Floaters
Registered User
Posts: 9
Joined: Tue Jun 30, 2020 3:52 pm

Re: Index Pages Loading Very Slow

Post by Inky Floaters »

I found a post in the national flags support yesterday that suggested adding an index for user_flag.

https://www.phpbb.com/customise/db/exte ... pic/197101

Unfortunately I still got a 2+ second delay before any index would load. As I mentioned earlier, it may be because 115K users now have flags?

I'm also stuck on PHP5.6 at the moment as some pages are served by a CMS that also needs updating when I get the time (it's just a hobby).
User avatar
RMcGirr83
Recognised Extension Developer
Posts: 21133
Joined: Wed Jun 22, 2005 4:33 pm
Location: Your display
Name: Rich McGirr

Re: Index Pages Loading Very Slow

Post by RMcGirr83 »

Try also doing this change within the file ext/rmcgirr83/nationalflags/core/nationalflags.php

FIND

Code: Select all

$result = $this->db->sql_query_limit($this->db->sql_build_query('SELECT', $sql_array), $this->config['flags_num_display']);
CHANGE TO

Code: Select all

$result = $this->db->sql_query_limit($this->db->sql_build_query('SELECT', $sql_array), $this->config['flags_num_display'], 0, 86400);
use a strict text editor to make that change (like notepad++).

This will cache the query for one day which should improve the load time significantly.

Before: 0.10250s | After: 0.10296s | Elapsed [cache]: 0.00047s | Elapsed [db]: 0.27948s
In times of change, learners inherit the earth, while the learned find themselves beautifully equipped to deal with a world that no longer exists - Eric Hoffer
Former Modifications/Extensions Team Member | My extensions | My extensions are updated regularly on github
Appreciate the extensions/mods/support then buy me a beer
All requests for support via PM will be ignored
Inky Floaters
Registered User
Posts: 9
Joined: Tue Jun 30, 2020 3:52 pm

Re: Index Pages Loading Very Slow

Post by Inky Floaters »

I tried your suggestion, then tried 2.1.8 from github, but it still slows down indexes to a crawl in my forum.

FWIW since I disabled national flags not only does the forum load much quicker (even after clearing the cache), but my CPU usage has dropped quite a bit too :o

cpu_usage.png
User avatar
MedCo
Registered User
Posts: 81
Joined: Tue Jul 14, 2015 4:09 pm
Contact:

Re: Index Pages Loading Very Slow

Post by MedCo »

@Inky Floaters: Hello, may I ask why you have removed all copyright notices for phpBB?
User avatar
RMcGirr83
Recognised Extension Developer
Posts: 21133
Joined: Wed Jun 22, 2005 4:33 pm
Location: Your display
Name: Rich McGirr

Re: Index Pages Loading Very Slow

Post by RMcGirr83 »

Inky Floaters wrote:
Thu Jul 02, 2020 2:40 pm
I tried your suggestion, then tried 2.1.8 from github, but it still slows down indexes to a crawl in my forum.

FWIW since I disabled national flags not only does the forum load much quicker (even after clearing the cache), but my CPU usage has dropped quite a bit too :o


cpu_usage.png
How many users have a flag in the users_table? The caching of the SQL query should have reduced the load considerably.
In times of change, learners inherit the earth, while the learned find themselves beautifully equipped to deal with a world that no longer exists - Eric Hoffer
Former Modifications/Extensions Team Member | My extensions | My extensions are updated regularly on github
Appreciate the extensions/mods/support then buy me a beer
All requests for support via PM will be ignored
Post Reply

Return to “[3.2.x] Support Forum”