miniBB ® miniBB®
miniBB Support Forums
 | Forums | Register | Reply | Search | Statistics | Manual |
Specific miniBB Support Forums / Specific /   

1 million topics: 16 seconds to load page.

Page  Page 1 of 2:  1  2  Next »  
Author Samuel Vignoli
Registered
#1 | Posted: 2 Feb 2007 07:08 
Hi folks!

Does anybody know why this link takes 16 seconds to load?

http://forum.comprar-livro.com.br/1_0.html


Other forums (with less topics) open ok.

Maybe too many posts at the same (topic_last_post_time)?

Thanks in advance!

Author tom3223
Guest
#2 | Posted: 2 Feb 2007 09:32 
Hmm - it took me about a sec (just like on average other websites)

Author tom322
Registered
#3 | Posted: 2 Feb 2007 09:35 | Edited by: tom322 
How big is the database in MB? It seems it's slower when I open other sections (but about 6 sec maybe). I think everything depends on the hardware the site is hosted on...

Author Samuel Vignoli
Registered
#4 | Posted: 2 Feb 2007 09:58 | Edited by: Samuel Vignoli 
tom322
P4 3.2 - 2GB ram...

topics table:
Data 104,986 KB
Index 70,816 KB
Total 175,802 KB

posts table:
data: 650,389 KB
Index 35,855 KB
Total 686,244 KB

ps> I added a field (item_id) to topics table. It's an UNIQUE.

tom3223
Hmm - it took me about a sec (just like on average other websites)
maybe query cache =)

please try to navigate on page 2, 3, 400, etc...

Author tom322
Registered
#5 | Posted: 2 Feb 2007 10:14 | Edited by: tom322 
Well, it's a huge database as it seems (good for a lifetime ;). I assume it's not a shared host, is it?

For me it's about 5-6 seconds (to go to not cached page) on a cable modem. I'm not a programmer but maybe perhaps the pagenav function could be enhanced somehow (IF at all technically possible).

-----------------------

BTW. Looking at http://www.big-boards.com/ (the list of the 25 biggest online forums) 1 million topics would bring you on the list [assuming one topic has on average 19 replies]. That would be huuugggge I think.

And by the time a forum reaches 1 million topics there will be better and a lot of faster servers so that each operation will execute faster :).

Author Paul
Lead Developer
#6 | Posted: 2 Feb 2007 11:27 | Edited by: Paul 
I think it's up to mySQL / hardware. Latest version of miniBB uses just 2 very simple and native SQL requests to build the topics section page.

So be sure first you are running the latest release.

What kind of "item_id" have you added? 2 unique fields in one table may also be the reason.

Obviously P4 is not the right thing for such kind of data... get a mainframe ;-)

Author Samuel Vignoli
Registered
#7 | Posted: 2 Feb 2007 12:23 
tom322
those topics were generated by a robot.

Its a book site. Every book have a page and and the page comments (click on "Comentários) are generated by its topic.

Paul
What kind of "item_id" have you added?

book id. Like "1853251947" above.

The slow query is (got with show processlist):
SELECT topic_id, topic_title, topic_poster, topic_poster_name, topic_time, topic_status, posts_count, sticky, topic_views, topic_last_post_id
FROM forumlv_topics
WHERE forum_id = '1'
ORDER BY sticky DESC , topic_last_post_id DESC
LIMIT 100 , 50

tom322
not a shared host, is it?
nop! Dedicated!

root]# uptime
16:23:43 up 29 days, 5:37, 3 users, load average: 0.15, 0.22, 0.22

Author Samuel Vignoli
Registered
#8 | Posted: 2 Feb 2007 12:27 
Its not the latest version, I will try to upgrade... thank you folks =)

Author Paul
Lead Developer
#9 | Posted: 2 Feb 2007 13:26 | Edited by: Paul 
Sam

Be sure in the database forum_id, sticky, topic_last_post_id fields are indexed and contain keys.

It all looks strange overall because thread page, for example, loads up ultra-fast. But the query types are all the same. I would suspect there is something wrong with topics table...

Let us know if you will find a solution :-) I think something could be related to the "item_id" field definitely.

Author tom322
Registered
#10 | Posted: 2 Feb 2007 13:38 
Yes, please report here.

----

PS. My 1-year old forum only has 997,000 topics to get 1 million ;).

Author Paul
Lead Developer
#11 | Posted: 2 Feb 2007 13:56 
By the way...

Looking at big-boards.com (the list of the 25 biggest online forums) 1 million topics would bring you on the list [assuming one topic has on average 19 replies]. That would be huuugggge I think.

I guess other forums (and the biggest miniBB-based community within years) already met this criteria some time ago. AFAIK we already reported big-boards.com about that but nobody never got a reply from them. It may look that this site is lobbying some interests...

Author tom322
Registered
#12 | Posted: 2 Feb 2007 14:12 
AFAIK we already reported big-boards.com about that but nobody never got a reply from them. It may look that this site is lobbying some interests...
I tend to agree with that...

Author Samuel Vignoli
Registered
#13 | Posted: 3 Feb 2007 07:31 
can't find a solution... here goes some debug information:

SELECT topic_id
FROM forumlv_topics
WHERE forum_id = '1'
ORDER BY sticky DESC , topic_last_post_id DESC
LIMIT 100 , 50

50 rows in set (9.98 sec)


SELECT topic_id
FROM forumlv_topics
WHERE forum_id = '1'
LIMIT 100 , 50
50 rows in set (0.00 sec)

SELECT topic_id
FROM forumlv_topics
WHERE forum_id = '1'
ORDER BY topic_last_post_id DESC
LIMIT 100 , 50

50 rows in set (9.71 sec)


SELECT topic_id
FROM forumlv_topics
WHERE forum_id = '1'
ORDER BY sticky DESC
LIMIT 100 , 50
50 rows in set (8.88 sec)

--------------------------------
I also tried to remove item_id index, change from UNIQUE to INDEX... but it keeps slow... =/

Here goes actual index for topics table...

PRIMARY topic_id
topic_last_post_id UNIQUE (it was INDEX yesterday, when I posted this topic)
item_id UNIQUE
forum_id INDEX
sticky INDEX
posts_count INDEX
topic_last_post_time INDEX
topic_views INDEX

Author Paul
Lead Developer
#14 | Posted: 3 Feb 2007 09:46 
I don' t know what could be the reason, but I know a lot of indexes also slow down the execution. You may try to avoid keeping index for 'sticky' an 'posts_count' fields (the only place where they might be used is statistics page), if you have no 'sticky' topics, index is also not necessary.

Any words, try to keep indexes only for forum_id and topic_last_post_id at least and see what happens.

Author Samuel Vignoli
Registered
#15 | Posted: 4 Feb 2007 17:48 | Edited by: Samuel Vignoli 
Paul

Any words, try to keep indexes only for forum_id and topic_last_post_id at least and see what happens.


i tried it! But...


mysql> SELECT topic_id
-> FROM forumlv_topics
-> WHERE forum_id = '1'
-> ORDER BY topic_last_post_id DESC
-> LIMIT 100 , 50;

50 rows in set (16.96 sec)

weird!

mysql Ver 12.22 Distrib 4.0.26, for pc-linux-gnu (i686)

Have no idea about whats going on... this should be very very fast....

ps> I already tried REPAIR TABLE...

Page  Page 1 of 2:  1  2  Next » 
Specific miniBB Support Forums / Specific / 1 million topics: 16 seconds to load page. Top

Your Reply Click this icon to move up to the quoted message

 Short link for this topic:

 ?
You are welcome to post anonymously, by entering a nickname with no password (if the similar Username has not been taken yet), or by leaving both fields empty. If you have a forums account, you can also sign in from this page without posting a message, or sign in and post at once.


Before posting, make sure your message is compliant with our forum posting rules. If not, it may be locked or deleted with no explanation.
 
 
miniBB Support Forums Powered by Forum Software miniBB ® Features  Requirements  Demo  Download  Showcase  Gallery of Arts
Compiler  Premium Extensions  Premium Support  License  Contacts
Proceed with the Captcha add-on: protect your miniBB-forums from the automated spam and flood.
Captcha Addon for miniBB