Topic: Forum SLOW to load after migration

I've just switched to dedicated server, transferred everything using CPanel.

Everything works except when opening a forum is slow.
Try it yourself and you'll get what I mean.
http://www.raymond.cc/forum/

General Forum has the most topics and posts. When I click on General Forum, it takes quite a while to get in. As for forum that has less topics and posts such as Linux, it loads immediately.

This never happened before in my old server. Can anyone suggest what to check? Here is what I get in Admin.

Environment
Operating system: Linux
PHP: 4.4.7 - Show info
Accelerator: N/A

Database
MySQL 4.1.22-standard-log
Rows: 149249
Size: 7.7 MB

Although it says N/A for accelerator, but the tech guys informed me that they've set up eAccelerator.

Re: Forum SLOW to load after migration

Enable debug mode and show queries, see if a specific query is lagging.

3 (edited by sirena 2007-11-19 21:18)

Re: Forum SLOW to load after migration

You may also like to check on what your mods are doing - I notice your board has quite a few and they can be coded poorly.

What, for example, is the query count involved in opening that General Forums page when that topic preview mod is involved?

You should also be able to confirm the existence of a PHP cache by having a look at your phpinfo output under the 'Show info' link under Admin | Environment.

Also your page load pulls in several off-site javascripts - sometimes they may also be a problem too.

Who is your host, BTW?

Re: Forum SLOW to load after migration

Smartys: I've enabled debug mode. When I enter General Forum, it says "[ Generated in 24.319 seconds, 7 queries executed ]". It's definitely slow, but how do I check which specific query is lgging?

sirena: Here are the mods I am currently using. I don't think it's the mods that is causing the problem because it used to work perfectly before the migration.
1. Adsense after First Post
2. PunRewrite
3. Topic Preview
4. AntiBot

Where should I check to confirm the existence of a PHP cache? I got in the Show info but loads of info appearing...
My host is www.webhostingbuzz.com

Thanks guys.

Re: Forum SLOW to load after migration

Smartys: I've enabled debug mode. When I enter General Forum, it says "[ Generated in 24.319 seconds, 7 queries executed ]". It's definitely slow, but how do I check which specific query is lgging?

Enable PUN_SHOW_QUERIES

Also, paste the contents of your viewforum.php here, that might also help.

Re: Forum SLOW to load after migration

Opps, missed that one out. I've enabled it. Please check it for me? Thanks

Re: Forum SLOW to load after migration

This is the culprit! But I don't understand what is it...

29.83341 seconds
SELECT p.message AS fpost, p.id AS fpost_id, v.message AS lpost, v.id AS lpost_id, t.id, t.moved_to, t.last_post_id FROM topics AS t LEFT JOIN posts AS p ON (t.id=p.topic_id OR t.moved_to=p.topic_id) LEFT JOIN posts AS v ON (t.last_post_id=v.id AND t.moved_to IS NULL) WHERE t.forum_id=1 GROUP BY t.id ORDER BY sticky DESC, last_post DESC LIMIT 0, 30

8

Re: Forum SLOW to load after migration

Which mod is that?

Re: Forum SLOW to load after migration

Uh oh.. It's Topic Preview...

But can anyone tell me why this problem start appearing after I've migrated to a different server?

Re: Forum SLOW to load after migration

You would have to run an EXPLAIN on that query on both servers to see where exactly the issue is.

Re: Forum SLOW to load after migration

How do I do that Smartys?

Re: Forum SLOW to load after migration

Smartys, this is what the guys at my server gave me. sponsored1 is my old server and server1.raymond.cc is the new server.

sponsored1:
##################
mysql> explain SELECT p.message AS fpost, p.id AS fpost_id, v.message AS lpost, v.id AS lpost_id, t.id, t.moved_to, t.last_post_id FROM topics AS t LEFT JOIN posts AS p ON (t.id=p.topic_id OR t.moved_to=p.topic_id) LEFT JOIN posts AS v ON (t.last_post_id=v.id AND t.moved_to IS NULL) WHERE t.forum_id=1 GROUP BY t.id ORDER BY sticky DESC, last_post DESC LIMIT 0, 30;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 1552938
Current database: raycc_forum

+----+-------------+-------+--------+---------------------+---------------------+---------+----------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------------+---------------------+---------+----------------------------+------+---------------------------------+
| 1 | SIMPLE | t | ref | topics_forum_id_idx | topics_forum_id_idx | 4 | const | 509 | Using temporary; Using filesort |
| 1 | SIMPLE | p | ALL | posts_topic_id_idx | NULL | NULL | NULL | 7029 | |
| 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 4 | raycc_forum.t.last_post_id | 1 | |
+----+-------------+-------+--------+---------------------+---------------------+---------+----------------------------+------+---------------------------------+
3 rows in set (0.05 sec)

mysql>
##################

server1.raymond.cc
##################
mysql> explain SELECT p.message AS fpost, p.id AS fpost_id, v.message AS lpost, v.id AS lpost_id, t.id, t.moved_to, t.last_post_id FROM topics AS t LEFT JOIN posts AS p ON (t.id=p.topic_id OR t.moved_to=p.topic_id) LEFT JOIN posts AS v ON (t.last_post_id=v.id AND t.moved_to IS NULL) WHERE t.forum_id=1 GROUP BY t.id ORDER BY sticky DESC, last_post DESC LIMIT 0, 30;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 127838
Current database: raycc_forum

+----+-------------+-------+--------+---------------------+---------------------+---------+----------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------------+---------------------+---------+----------------------------+------+---------------------------------+
| 1 | SIMPLE | t | ref | topics_forum_id_idx | topics_forum_id_idx | 4 | const | 494 | Using temporary; Using filesort |
| 1 | SIMPLE | p | ALL | posts_topic_id_idx | NULL | NULL | NULL | 7048 | |
| 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 4 | raycc_forum.t.last_post_id | 1 | |
+----+-------------+-------+--------+---------------------+---------------------+---------+----------------------------+------+---------------------------------+
3 rows in set (0.00 sec)

mysql>
##################

13

Re: Forum SLOW to load after migration

raymond wrote:

sirena:
Where should I check to confirm the existence of a PHP cache? I got in the Show info but loads of info appearing...

You should see a heading in the phpinfo output that looks similar to this if eAccelerator is there:

eAccelerator eAccelerator support enabled
Version  0.9.5 
Caching Enabled  true 
Optimizer Enabled  false 
Memory Size  16,777,180 Bytes 
Memory Available  16,772,608 Bytes 
Memory Allocated  4,572 Bytes 
Cached Scripts  1 
Removed Scripts  0 
Cached Keys  0 

etc etc

Having a PHP cache at work can certainly speed up PHP-dependent pages, sometimes even when you have poorly performing or problematic code on your page like you seem to.

But as a temporary fix until you sort the problems out, I would suggest that you comment out the Topic Preview mod on your live forum.

I'd also suggest that you set up a test punbb site somewhere else on your server, load all your mods etc onto it, inc the Topic preview mod, and debug it all there.

When you have it figured out all its bugs, re-introduce the mod into your main forum. It's no fun for anyone (esp forum visitors) to be debugging server problems on a live forum smile

Re: Forum SLOW to load after migration

raymond: I don't know. But if that query is taking that long to make, it's bad wink

Re: Forum SLOW to load after migration

Thanks. I will restore it and see how is it...