Register Members List Search Today's Posts Mark Forums Read

Reply
 
Thread Tools
  #1  
Old 25 Sep 2009, 13:42
cobaku cobaku is offline
 
Join Date: Oct 2005
Unoptimized query

is below query normal it sometimes takes 27 seconds on some big forum sections which kills performance like crazy

from what i understand it only counts number of threads in certain forums
yes there are alot of threads on my forum but is there anything i can do to optimize this query, there is 1 more but i can live with the other.

Somehow i feel like noone can help me.



Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

Reply With Quote
  #2  
Old 25 Sep 2009, 15:06
Dean C's Avatar
Dean C Dean C is offline
 
Join Date: Jan 2002
Real name: Dean Clatworthy
How many threads are we talking? Also where did you pull that query from?
__________________
Dean Clatworthy - Web Developer/Designer
Reply With Quote
  #3  
Old 25 Sep 2009, 15:26
sockwater's Avatar
sockwater sockwater is offline
 
Join Date: Apr 2008
Originally Posted by Dean C View Post
How many threads are we talking? Also where did you pull that query from?
forumdisplay.php line 720 in 3.8.4.
Reply With Quote
  #4  
Old 25 Sep 2009, 16:23
Dean C's Avatar
Dean C Dean C is offline
 
Join Date: Jan 2002
Real name: Dean Clatworthy
I don't have a copy of vB's database handy to check, so check this index doesn't already exist. Try creating an index on the thread table with these fields in this order:

INDEX(forumid, sticky, visible)

Saying that, that query shouldn't be be particularly show. Can you also paste the full profiling output.
__________________
Dean Clatworthy - Web Developer/Designer
Reply With Quote
  #5  
Old 25 Sep 2009, 16:40
kmike kmike is offline
 
Join Date: Oct 2002
The query is already using that index, according to EXPLAIN in the first post:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

It's just that the number of matching rows is too big.
I'm afraid nothing could be done from the vBulletin side of things. Maybe some MySQL server tweaking will help to speed up that query.
Reply With Quote
  #6  
Old 25 Sep 2009, 19:35
Dean C's Avatar
Dean C Dean C is offline
 
Join Date: Jan 2002
Real name: Dean Clatworthy
Originally Posted by kmike View Post
The query is already using that index, according to EXPLAIN in the first post:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

It's just that the number of matching rows is too big.
I'm afraid nothing could be done from the vBulletin side of things. Maybe some MySQL server tweaking will help to speed up that query.
Yep I did say check the index doesn't already exist


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

__________________
Dean Clatworthy - Web Developer/Designer
Reply With Quote
  #7  
Old 25 Sep 2009, 19:46
Adrian Schneider's Avatar
Adrian Schneider Adrian Schneider is offline
 
Join Date: Jul 2004
All code optimizations aside, you could edit this forum so it only shows threads from the past 6 months or year instead. This should significantly reduce the # of threads and still utilize the index.
Attached Images
File Type: jpg cutoff.JPG (5.9 KB, 20 views)
Reply With Quote
  #8  
Old 25 Sep 2009, 20:18
kmike kmike is offline
 
Join Date: Oct 2002
Oh yes.
Hopefully the default sort order for that forum is by the last post time, otherwise the index wouldn't work.
Reply With Quote
  #9  
Old 25 Sep 2009, 21:53
cobaku cobaku is offline
 
Join Date: Oct 2005
thanks so much for the replies , i think i understand the solution from the replies
i am on it right now

that forumid had 120.000 threads

--------------- Added 26 Sep 2009 at 20:53 ---------------

i could only do siradrian's solution and it works.

Do you think this mod will help me with only single query
http://www.vbulletin.org/forum/showthread.php?t=210897
it looks a little hard.

Last edited by cobaku; 26 Sep 2009 at 20:53. Reason: Auto-Merged DoublePost
Reply With Quote
  #10  
Old 28 Sep 2009, 11:11
Adrian Schneider's Avatar
Adrian Schneider Adrian Schneider is offline
 
Join Date: Jul 2004
Afraid not. That mod is just to help automate common administrative queries. The easiest solution to implement is changing the option I mentioned above.

If you do not want to do that (and honestly I don't see why not...) then here is my thought.... Why on earth would you want to browse through 120,000 threads? The data does not get lost; you can still search and find it. I don't see people going past about 5-10 pages. Maybe 20 if they are bored. People use searching.

If your forum is that busy, where you cannot restrict posts by the past 6-12m because users will want to browse further, then I would suggest that you create some subforums and shift the content there instead. This way you can index by forumid and get way faster results without losing any user experience.

Anyway, if you insist on returning huge datasets back to the user, then here are more ideas:

Upgrade your hardware - This is generally expensive and will only solve the problem temporarily.

Implement caching - If you use 3rd party caching, I suppose it's of medium difficulty to set up. If you use MySQL's query cache, it would be pretty simple. In this example, all you'd need to do is some normalization on the linux timestamp it uses in the field list. If you round it to the nearest thousand or even hundred, it would be utilized. The way it's set up now, it can't be used. What this would do is have the query results re-used (instant repeat queries) until the data changes and invalidates it.

Again, especially if you use MySQL query caching, this is a band-aid solution.

Implement Sphinx -This is quite difficult / expensive to set up, but it's probably the most beneficial in terms of performance and growth support. Sphinx is a search daemon which is often used to replace keyword searching, but it can also perform full data scans VERY quickly. You can have it scan through hundreds of millions of rows in the time it would take MySQL to scan tens of thousands. For a board your size, it would be bringing the data back in under a tenth of a second easily.
Reply With Quote
  #11  
Old 28 Sep 2009, 22:37
cobaku cobaku is offline
 
Join Date: Oct 2005
i was already using your suggested solution, but i feel like i have to eliminate the problem completely.
thanks for your amazing detailed reply. I will definitely try sphinx.
i am using query caching and will also search what normalization on the linux timestampis

i am currently stuck at configuring
what i am trying to optimize is this

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

what i have to configure is
at /etc/sphinx/sphinx.conf
below


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

i found an example at http://www.vbulletin.org/forum/attac...8&d=1210237129 i hope it works

--------------- Added 29 Sep 2009 at 00:05 ---------------

ok it seems this config thing is much bigger than i can ever understand

was worth to try

but i may still implement search sphinx

Last edited by cobaku; 29 Sep 2009 at 00:05. Reason: Auto-Merged DoublePost
Reply With Quote
  #12  
Old 29 Sep 2009, 01:40
Adrian Schneider's Avatar
Adrian Schneider Adrian Schneider is offline
 
Join Date: Jul 2004
You'll have to make code modifications and play with the configuration quite a bit to get it to work. It's definitely not simple to set up. If I get some time in the future (probably not anytime soon, but maybe within a few months) I can post a tutorial for this.

However, I'm still of the opinion that browsing through over 10,000 rows of anything is not useful.

All normalization of the timestamps means is rounding... since the timestamp will increase every second, it can never re-use queries in the cache. If you round a few digits on the timestamps, the cache can be utilized.
Reply With Quote
  #13  
Old 29 Sep 2009, 06:39
Dean C's Avatar
Dean C Dean C is offline
 
Join Date: Jan 2002
Real name: Dean Clatworthy
Originally Posted by SirAdrian View Post

However, I'm still of the opinion that browsing through over 10,000 rows of anything is not useful.
Certainly not to find out newpost counts
__________________
Dean Clatworthy - Web Developer/Designer
Reply With Quote
  #14  
Old 29 Sep 2009, 10:09
cobaku cobaku is offline
 
Join Date: Oct 2005
i never knew biggest boards uses sphinx indexing
other than search.

Thanks alot for the big tip.
I somehow managed to setup a sphinx search altough it does not list my older posts yet i have to fix it soon

I am quite happy with my new search system and may try to achive that indexing heavy queries with sphinx

Last edited by cobaku; 07 Oct 2009 at 07:11.
Reply With Quote
Reply



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


New To Site? Need Help?

All times are GMT. The time now is 07:05.

Layout Options | Width: Wide Color: