phpBB2Refugees.com Logo
Not affiliated with or endorsed by the phpBB Group

Register •  Login 

Continue the legacy...

Welcome to all phpBB2 Refugees!Wave Smilie

This site is intended to continue support for the legacy 2.x line of the phpBB2 bulletin board package. If you are a fan of phpBB2, please, by all means register, post, and help us out by offering your suggestions. We are primarily a community and support network. Our secondary goal is to provide a phpBB2 MOD Author and Styles area.

Speedproblem

Goto page 1, 2  Next
 
Search this topic... | Search phpBB2 Discussion... | Search Box
Register or Login to Post    Index » phpBB2 Discussion  Previous TopicPrint TopicNext Topic
Author Message
Holger
Board Member



Joined: 19 Jan 2009

Posts: 509
Location: Hanover


flag
PostPosted: 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


flag
PostPosted: 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


flag
PostPosted: 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! icon_eek.gif 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


flag
PostPosted: 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


flag
PostPosted: 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! icon_eek.gif 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 icon_biggrin.gif
Back to top
Holger
Board Member



Joined: 19 Jan 2009

Posts: 509
Location: Hanover


flag
PostPosted: 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


flag
PostPosted: 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


flag
PostPosted: Wed Sep 22, 2010 10:19 am 
Post subject: Re: Speedproblem

Here are screenshots


index_phpbb_topics.jpg
 Description:
phpbb_topics
 Filesize:  103.65 KB
 Viewed:  1299 Time(s)

index_phpbb_topics.jpg



index_phpbb_posts.jpg
 Description:
phpbb_posts
 Filesize:  84.87 KB
 Viewed:  1291 Time(s)

index_phpbb_posts.jpg


Back to top
drathbun
Board Member



Joined: 24 Jul 2008

Posts: 729
Location: Texas


flag
PostPosted: 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


flag
PostPosted: Wed Sep 22, 2010 12:29 pm 
Post subject: Re: Speedproblem

No, but I use the Run Stats Mod. Maybe you mean that?!
http://www.phpbbhacks.com/download/5806

/Holger
Back to top
drathbun
Board Member



Joined: 24 Jul 2008

Posts: 729
Location: Texas


flag
PostPosted: 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


flag
PostPosted: 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


flag
PostPosted: 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


flag
PostPosted: Fri Sep 24, 2010 2:34 am 
Post subject: Re: Speedproblem

No I dont know how to create it! icon_redface.gif
Back to top
Holger
Board Member



Joined: 19 Jan 2009

Posts: 509
Location: Hanover


flag
PostPosted: 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
Display posts from previous:   
Register or Login to Post    Index » phpBB2 Discussion  Previous TopicPrint TopicNext Topic
Page 1 of 2 All times are GMT - 4 Hours
Goto page 1, 2  Next
 
Jump to:  

Index • About • FAQ • Rules • Privacy • Search •  Register •  Login 
Not affiliated with or endorsed by the phpBB Group
Powered by phpBB2 © phpBB Group
Generated in 0.0933 seconds using 18 queries. (SQL 0.0181 Parse 0.0139 Other 0.0613)
phpBB Customizations by the phpBBDoctor.com
Template Design by DeLFlo and MomentsOfLight.com Moments of Light Logo