Hello. My host told me that I was using too much server resources and that I had to optimize my scripts. I looked around at my site, but all pages were generated very quickly. However, when I started looking at the generation time on phpBB, I noticed that many pages took more than one second to be generated, some up to 4 or 5 seconds! I have about 10,000 users and 63,000 posts in 8600 topics, so the forum isn't very big. However, when I reload the page, the generation time goes down to between 0.2 and 0.5 seconds. It seems like PHP does calculations much faster with some kind of caching, so I have to test on separate pages every time, but the first time generally takes more than one second to generate.
Some slow queries I found were:
memberlist.php (with no URL variables): 1.69731s
Code: Select all
FROM phpbb_users u
WHERE u.user_type IN (0, 3)
ORDER BY u.user_regdate ASC
Amazingly, when I reload the page, the same query takes 0.00024s to execute, but if I wait a few minutes and reload, it takes between 1 and 2 seconds. Generally, there is one query that slows down the page, and it seems like it's the page-specific queries (not the global ones, such as the ones getting config, user details etc) that are slow.
The index page (index.php) is particularly slow, the following query often takes about 4 seconds to execute (in one case, it took 10.17693s!):
Code: Select all
SELECT f.*, ft.mark_time, t.topic_title, t.topic_id, t.topic_last_post_id FROM (phpbb_forums f) LEFT JOIN phpbb_forums_track ft ON (ft.user_id = 2 AND ft.forum_id = f.forum_id) LEFT JOIN phpbb_topics t ON (f.forum_last_post_id = t.topic_last_post_id) ORDER BY f.left_id
If I reload the page quickly after the first time I load it, the query is extremely fast: 0.00071s is normal.
All pages work like this - they're slow on the initial load, but when I refresh them, they are very fast. However, the slow pages eat my server resources and I really need to find a solution to improve the speed. Any suggestions? Thanks in advance.