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!

Hmm - it took me about a sec (just like on average other websites)

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...

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...

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 :).

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 ;-)

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

Its not the latest version, I will try to upgrade... thank you folks =)

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.

Yes, please report here.

----

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

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...

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...

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

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.

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...