Slow to load topics only

Get help with installation and running phpBB 3.2.x here. Please do not post bug reports, feature requests, or extension related questions here.
RockFeller2019
Registered User
Posts: 10
Joined: Tue Nov 05, 2019 10:18 pm

Slow to load topics only

Post by RockFeller2019 » Tue Nov 05, 2019 10:22 pm

Guys,
I updated a forum from 3.0.7 to 3.1.3 and then to 3.2.8 and everything went well (the errors I managed to fix). But now I'm facing a performance issue - and I can say with certainty that it's not a host issue. I tested on local host and live host.

After the upgrade everything went well except that clicking on a forum / subforum has an average time of 5 seconds for the page to load, while other pages load in 0.5 seconds. This only happens where you have a list of topics, such as forums, and also searches (active topics).

So I was sure that the problem is the phpbb_topics table and derivatives when I installed the Recent Topics by paybas extension and so ... the forum crashed!

While installing the extension and enabling to display recent topics in the index, I get the error: MYSQL server has gone away. So all my ACP is accessible and I can only go back to index by disabling the extension that loads recent topics in the index. The error I have is this:

Code: Select all


SQL ERROR [ mysqli ]

MySQL server has gone away [2006]

SQL

SELECT COUNT(t.topic_id) as topic_count FROM (phpbb_topics t) LEFT JOIN phpbb_topics_track tt ON (tt.topic_id = t.topic_id AND tt.user_id = 2) LEFT JOIN phpbb_forums_track ft ON (ft.forum_id = t.forum_id AND ft.user_id = 2) WHERE t.topic_id <> '0' AND t.topic_status <> 2 AND ((t.forum_id IN ('10', '1', '2', '3', '4', '5', '6', '7', '8', '9', '15', '14', '18', '22') OR (t.topic_visibility = 1 AND 1=0)))

BACKTRACE

FILE: (not given by php)
LINE: (not given by php)
CALL: msg_handler()

FILE: [ROOT]/phpbb/db/driver/driver.php
LINE: 997
CALL: trigger_error()

FILE: [ROOT]/phpbb/db/driver/mysqli.php
LINE: 196
CALL: phpbb\db\driver\driver->sql_error()

FILE: [ROOT]/phpbb/db/driver/factory.php
LINE: 329
CALL: phpbb\db\driver\mysqli->sql_query()

FILE: [ROOT]/ext/paybas/recenttopics/core/recenttopics.php
LINE: 745
CALL: phpbb\db\driver\factory->sql_query()

FILE: [ROOT]/ext/paybas/recenttopics/core/recenttopics.php
LINE: 288
CALL: paybas\recenttopics\core\recenttopics->gettopiclist()

FILE: [ROOT]/ext/paybas/recenttopics/event/listener.php
LINE: 88
CALL: paybas\recenttopics\core\recenttopics->display_recent_topics()

FILE: [ROOT]/vendor/symfony/event-dispatcher/EventDispatcher.php
LINE: 184
CALL: paybas\recenttopics\event\listener->display_rt()

FILE: [ROOT]/vendor/symfony/event-dispatcher/EventDispatcher.php
LINE: 46
CALL: Symfony\Component\EventDispatcher\EventDispatcher->doDispatch()

FILE: [ROOT]/phpbb/event/dispatcher.php
LINE: 62
CALL: Symfony\Component\EventDispatcher\EventDispatcher->dispatch()

FILE: [ROOT]/phpbb/event/dispatcher.php
LINE: 46
CALL: phpbb\event\dispatcher->dispatch()

FILE: [ROOT]/index.php
LINE: 248
CALL: phpbb\event\dispatcher->trigger_event()

This makes me believe that the problem is with the database, specifically in the phpbb_topics / posted / watch tables ... how can I fix this?

Remember that when you click directly on a topic, it loads quickly like all pages, 0.5s. The problem only happens where you have topic lists (even though they are limited to 15 topics per page).

phpBB version: 3.2.8
PHP version: 7.0.33
MYSQL version: MySQL(i) 5.7.23-23
Database size: 1.28GiB
Topics: 15.100

User avatar
John connor
Registered User
Posts: 2312
Joined: Fri Nov 14, 2014 5:14 pm
Location: U S Of A
Name: Aaron
Contact:

Re: Slow to load topics only

Post by John connor » Wed Nov 06, 2019 4:55 am

First, do you still have mods installed?

Second, have you cleared the board's cache since updating?

RockFeller2019
Registered User
Posts: 10
Joined: Tue Nov 05, 2019 10:18 pm

Re: Slow to load topics only

Post by RockFeller2019 » Wed Nov 06, 2019 5:24 am

John connor wrote:
Wed Nov 06, 2019 4:55 am
First, do you still have mods installed?

Second, have you cleared the board's cache since updating?
1 - There were no MODs in 3.0.x
2 - YES, I cleared the cache and I do it for every change.

I have tested other extensions that do not interact with topics and this works perfectly. But everything that involves loading topic lists drops MySQL or slows it down.

User avatar
david63
Registered User
Posts: 16718
Joined: Thu Dec 19, 2002 8:08 am
Location: Lancashire, UK
Name: David Wood
Contact:

Re: Slow to load topics only

Post by david63 » Wed Nov 06, 2019 7:15 am

Have you tried optimising your database via phpMyAdmin?

Usual caveat applies - backup database first.
David
Remember: You only know what you know and - you don't know what you don't know!
My CDB Contributions | How to install an extension
I will not be accepting translations for any of my extensions in Github - please post any translations in the appropriate topic.
No support requests via PM or email as they will be ignored

RockFeller2019
Registered User
Posts: 10
Joined: Tue Nov 05, 2019 10:18 pm

Re: Slow to load topics only

Post by RockFeller2019 » Wed Nov 06, 2019 9:40 am

david63 wrote:
Wed Nov 06, 2019 7:15 am
Have you tried optimising your database via phpMyAdmin?

Usual caveat applies - backup database first.
Yes, I already optimized the tables in Phpmyadmin, I even repaired but it didn't help in any way.

One thing I noticed is that some tables are innoDB and others myISAM but I don't know if this interferes with performance.

RockFeller2019
Registered User
Posts: 10
Joined: Tue Nov 05, 2019 10:18 pm

Re: Slow to load topics only

Post by RockFeller2019 » Wed Nov 06, 2019 5:13 pm

Any help about this?

User avatar
Mick
Support Team Member
Support Team Member
Posts: 21681
Joined: Fri Aug 29, 2008 9:49 am
Location: Cardiff

Re: Slow to load topics only

Post by Mick » Wed Nov 06, 2019 6:23 pm

Please fill out the Support Request Template and post it back here to enable us to assist you better.
"The more connected we get the more alone we become" - Kyle Broflovski

RockFeller2019
Registered User
Posts: 10
Joined: Tue Nov 05, 2019 10:18 pm

Re: Slow to load topics only

Post by RockFeller2019 » Wed Nov 06, 2019 7:13 pm

Support Request Template
What version of phpBB are you using? phpBB 3.2.8
What is your board's URL? Local host
Who do you host your board with? Local host
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.0.7
What extensions do you have installed? Ajax Shoutbox (work well)
Recent Topics (disabled after problems in load topic list in index)
What styles do you currently have installed? Prosilver
What language(s) is your board currently using? en
Which database type/version are you using? MySQL 5
What is your level of experience? New to PHP but not phpBB
What actions did you take (updating your board; installing a MOD, style or extension; etc.) prior to this problem becoming noticeable? After upgrade of 3.0 to 3.1 and 3.1 to 3.2 the specifics pages load very slow, this pages are: forums/subforuns (not category), search topics or any list of topics
Please describe your problem. Load slow in topic lists (recent topics, forums and subforuns pages, and search topics/active topics/unread topics).
Generated by SRT Generator

SUPPORT SECTIONS
Code Changes
Documentation
Knowledge Base
Support Forums
Support Request Template
Support Toolkit
Discord / IRC
International Support
CHECK OUT OUR SPONSOR
Amazing Themes made by Creators just like you.ads via Carbon
ADVERTISEMENTS
Need Hosting?
BlueHost.com • Web Hosting UK • HostMonster • FastDomain Hosting • Advertise on phpBB.com


© 2000, 2002, 2003, 2007 phpBB Limited • Contact Us • Advertise on www.phpbb.com
Header illustrations by Vlad Gerasimov • Designed by phpBB & subBlue • Ho

User avatar
JoshyPHP
Code Contributor
Posts: 1046
Joined: Mon Jul 11, 2011 12:28 am

Re: Slow to load topics only

Post by JoshyPHP » Thu Nov 07, 2019 1:28 am

The issue is with the query you listed. It causes MySQL to examine too many rows, it might trash its index buffer or exceed some kind of limits and the server bails. You can try allocating more memory to the database server, or maybe look for another similar extensions.

If you get a hold of the extension's developer, you can tell them not to use COUNT() on every page and rely on the topic counters in the forums table.
I wrote the thing that does BBCodes in 3.2.

RockFeller2019
Registered User
Posts: 10
Joined: Tue Nov 05, 2019 10:18 pm

Re: Slow to load topics only

Post by RockFeller2019 » Thu Nov 07, 2019 1:40 am

JoshyPHP wrote:
Thu Nov 07, 2019 1:28 am
The issue is with the query you listed. It causes MySQL to examine too many rows, it might trash its index buffer or exceed some kind of limits and the server bails. You can try allocating more memory to the database server, or maybe look for another similar extensions.

If you get a hold of the extension's developer, you can tell them not to use COUNT() on every page and rely on the topic counters in the forums table.
Hi Joshy!

I think you are right. But the problem of slowness happens without any extension installed either. When I install the extension I have MYSQL server has gone away, but when I don't have an extension enabled I am slow to open a subforum or a topic list.

This issue is reproduced on localhost and server (Hostgator), which makes me believe that something in the database is wrong.

I converted every type to myiSAM, but it didn't work. Some tables are utf8_bin and others are _cli, I don't know if these things affect performance.

User avatar
JoshyPHP
Code Contributor
Posts: 1046
Joined: Mon Jul 11, 2011 12:28 am

Re: Slow to load topics only

Post by JoshyPHP » Thu Nov 07, 2019 1:51 am

The first step would be to quantify what "slow" means. If you're on a shared host then the database will often be slow and overcrowded, so a page taking 500 ms isn't surprising. You can enable debug mode and use the "Explain" link at the bottom of the page to look at how long each query takes. If you have a lot of topics, some queries will be slow, that's an issue that's common to most forum software.
RockFeller2019 wrote:
Thu Nov 07, 2019 1:40 am
I converted every type to myiSAM, but it didn't work. Some tables are utf8_bin and others are _cli, I don't know if these things affect performance.
MyISAM is not good for active forums, you should use InnoDB for everything. The charset will not affect the forum's performance.
I wrote the thing that does BBCodes in 3.2.

RockFeller2019
Registered User
Posts: 10
Joined: Tue Nov 05, 2019 10:18 pm

Re: Slow to load topics only

Post by RockFeller2019 » Thu Nov 07, 2019 3:03 am

JoshyPHP wrote:
Thu Nov 07, 2019 1:51 am
The first step would be to quantify what "slow" means. If you're on a shared host then the database will often be slow and overcrowded, so a page taking 500 ms isn't surprising. You can enable debug mode and use the "Explain" link at the bottom of the page to look at how long each query takes. If you have a lot of topics, some queries will be slow, that's an issue that's common to most forum software.
RockFeller2019 wrote:
Thu Nov 07, 2019 1:40 am
I converted every type to myiSAM, but it didn't work. Some tables are utf8_bin and others are _cli, I don't know if these things affect performance.
MyISAM is not good for active forums, you should use InnoDB for everything. The charset will not affect the forum's performance.
I have this with debug enabled. Some screenshots, because there are so many queries:

Image

Image

Image

RockFeller2019
Registered User
Posts: 10
Joined: Tue Nov 05, 2019 10:18 pm

Re: Slow to load topics only

Post by RockFeller2019 » Thu Nov 07, 2019 3:07 am

The last query:

Image

Something went wrong

User avatar
JoshyPHP
Code Contributor
Posts: 1046
Joined: Mon Jul 11, 2011 12:28 am

Re: Slow to load topics only

Post by JoshyPHP » Thu Nov 07, 2019 3:15 am

Looks like you're missing some crucial indexes on phpbb_topics_posted and phpbb_topics_track.
I wrote the thing that does BBCodes in 3.2.

RockFeller2019
Registered User
Posts: 10
Joined: Tue Nov 05, 2019 10:18 pm

Re: Slow to load topics only

Post by RockFeller2019 » Thu Nov 07, 2019 3:18 am

JoshyPHP wrote:
Thu Nov 07, 2019 3:15 am
Looks like you're missing some crucial indexes on phpbb_topics_posted and phpbb_topics_track.
Does Mysql_upgrader.php solve this?

The database is very old, comes from mysql4 and updated from 3.0.7, I do not know what is happening.

Post Reply

Return to “[3.2.x] Support Forum”