Author |
Message |
Holger Board Member
Joined: 19 Jan 2009
Posts: 509 Location: Hanover
|
Posted: Sun Sep 19, 2010 10:48 am Post subject: Speedproblem |
|
|
Hi there!
I sometimes have a speedproblem, for example with loading the index.
This query takes nearly 5 seconds:
Code: | SELECT f.*, p.post_time, p.post_username, u.username, u.user_id, t.topic_id, t.topic_title, t.topic_last_post_id FROM ((( phpbb_forums f LEFT JOIN phpbb_posts p ON p.post_id = f.forum_last_post_id ) LEFT JOIN phpbb_users u ON u.user_id = p.poster_id ) LEFT JOIN phpbb_topics t ON t.topic_last_post_id = p.post_id ) WHERE t.topic_moved_id = 0 OR t.topic_moved_id IS NULL ORDER BY f.cat_id, f.forum_order |
Is there a way to optimize?
Why do I need t.topic_title? I dont show the topic title on the index.
Can I remove that from the query?
I have installed the Run Stats Mod, so I can see details on everything.
I have approx 270-275 queries on the index page, is that much?
On viewtopic it is 165.
Any ideas?
Thanks!
/Holger
|
|
Back to top |
|
|
Holger Board Member
Joined: 19 Jan 2009
Posts: 509 Location: Hanover
|
Posted: Mon Sep 20, 2010 5:39 am Post subject: Re: Speedproblem |
|
|
In my viewtopic.php this took 3.5 seconds out of 5.8:
Code: | UPDATE phpbb_topics SET topic_views = topic_views + 1 WHERE topic_id = 18984 |
Strange!
|
|
Back to top |
|
|
drathbun Board Member
Joined: 24 Jul 2008
Posts: 729 Location: Texas
|
Posted: Mon Sep 20, 2010 6:39 pm Post subject: Re: Speedproblem |
|
|
That can indicate that you have a locking problem. When an update is run, the row (or block) of data needs to be locked. If it's active, then your transaction has to wait for something else to complete. If this is a consistent problem, then you may have some bad code. If it's only an occasional issue, then your database may need to be tweaked for performance instead.
Also as a trick for the select: change topic_moved_id = 0 to topic_moved_id+0 = 0 instead. That makes sure that it doesn't try to use the wrong index for that query.
Quote: | I have approx 270-275 queries on the index page, is that much?
On viewtopic it is 165. |
Yes, that's way too much! That generally means you have installed a poorly written MOD that is running a query inside a loop. The index page here, even on a heavily modified board, runs between 14 and 17 queries. The viewtopic page runs between 22 and 25 queries.
_________________ phpBBDoctor Blog |
|
Back to top |
|
|
Holger Board Member
Joined: 19 Jan 2009
Posts: 509 Location: Hanover
|
Posted: Tue Sep 21, 2010 2:39 am Post subject: Re: Speedproblem |
|
|
I have the alterantive language description mod installed. That one generates a bunch of queries! One for each forum!
I will uninstall that one!
Thank you for your hints!
|
|
Back to top |
|
|
Holger Board Member
Joined: 19 Jan 2009
Posts: 509 Location: Hanover
|
Posted: Wed Sep 22, 2010 7:56 am Post subject: Re: Speedproblem |
|
|
drathbun wrote: | Also as a trick for the select: change topic_moved_id = 0 to topic_moved_id+0 = 0 instead. That makes sure that it doesn't try to use the wrong index for that query. |
This did not have any effect.
Quote: | I have approx 270-275 queries on the index page, is that much?
On viewtopic it is 165. |
Yes, that's way too much! That generally means you have installed a poorly written MOD that is running a query inside a loop. The index page here, even on a heavily modified board, runs between 14 and 17 queries. The viewtopic page runs between 22 and 25 queries.[/quote]
I could reduce the queries to approx 40 now
|
|
Back to top |
|
|
Holger Board Member
Joined: 19 Jan 2009
Posts: 509 Location: Hanover
|
Posted: Wed Sep 22, 2010 7:58 am Post subject: Re: Speedproblem |
|
|
Still this one takes 3.5 seconds otu of 4.8 on the index.php:
Code: | SELECT f.*, p.post_time, p.post_username, u.username, u.user_id, t.topic_id, t.topic_title, t.topic_last_post_id FROM ((( phpbb_forums f LEFT JOIN phpbb_posts p ON p.post_id = f.forum_last_post_id ) LEFT JOIN phpbb_users u ON u.user_id = p.poster_id ) LEFT JOIN phpbb_topics t ON t.topic_last_post_id = p.post_id ) WHERE t.topic_moved_id+0 = 0 OR t.topic_moved_id IS NULL ORDER BY f.cat_id, f.forum_order |
|
|
Back to top |
|
|
drathbun Board Member
Joined: 24 Jul 2008
Posts: 729 Location: Texas
|
Posted: Wed Sep 22, 2010 10:15 am Post subject: Re: Speedproblem |
|
|
What indexes do you have on phpbb_topics and phpbb_posts?
_________________ phpBBDoctor Blog |
|
Back to top |
|
|
Holger Board Member
Joined: 19 Jan 2009
Posts: 509 Location: Hanover
|
Posted: Wed Sep 22, 2010 10:19 am Post subject: Re: Speedproblem |
|
|
Here are screenshots
Description: |
|
Filesize: |
103.65 KB |
Viewed: |
1299 Time(s) |
|
Description: |
|
Filesize: |
84.87 KB |
Viewed: |
1291 Time(s) |
|
|
|
Back to top |
|
|
drathbun Board Member
Joined: 24 Jul 2008
Posts: 729 Location: Texas
|
Posted: Wed Sep 22, 2010 12:14 pm Post subject: Re: Speedproblem |
|
|
Do you know how to capture a query plan? using the explain command?
_________________ phpBBDoctor Blog |
|
Back to top |
|
|
Holger Board Member
Joined: 19 Jan 2009
Posts: 509 Location: Hanover
|
|
Back to top |
|
|
drathbun Board Member
Joined: 24 Jul 2008
Posts: 729 Location: Texas
|
Posted: Thu Sep 23, 2010 10:33 am Post subject: Re: Speedproblem |
|
|
A query explain is done in MySQL. It shows you what indexes will be used and how the tables will be combined together. Ideally you want to have an index for every table, and you want the "best" table to drive the query for optimal performance. I have not had time to review your index screen shots yet but will try this weekend.
_________________ phpBBDoctor Blog |
|
Back to top |
|
|
Holger Board Member
Joined: 19 Jan 2009
Posts: 509 Location: Hanover
|
Posted: Thu Sep 23, 2010 10:35 am Post subject: Re: Speedproblem |
|
|
Thanks a lot!
I will wait for your feedback!
|
|
Back to top |
|
|
drathbun Board Member
Joined: 24 Jul 2008
Posts: 729 Location: Texas
|
Posted: Thu Sep 23, 2010 2:32 pm Post subject: Re: Speedproblem |
|
|
I had a break and took a look at your existing indexes (the screen shots you posted earlier). You are missing an index on topic_last_post_id on the phpbb_topics table. That index is not present in a standard phpBB2 install, but I thought it was added later.
Do you know how to create this index? It might dramatically speed up this particular query process.
_________________ phpBBDoctor Blog |
|
Back to top |
|
|
Holger Board Member
Joined: 19 Jan 2009
Posts: 509 Location: Hanover
|
Posted: Fri Sep 24, 2010 2:34 am Post subject: Re: Speedproblem |
|
|
No I dont know how to create it!
|
|
Back to top |
|
|
Holger Board Member
Joined: 19 Jan 2009
Posts: 509 Location: Hanover
|
Posted: Fri Sep 24, 2010 2:39 am Post subject: Re: Speedproblem |
|
|
But we where able to reduce the time: [ Time: 0.5896s ][ Queries: 30 (0.1840s) ]
by commenting out everything that has something to do with the last post, i am not showing any info about the last post on the index.
So I think we do not need a new index.
But it would be interesting to know HOW it is created.
|
|
Back to top |
|
|
|