Trying to optimize query in forumdisplay.php

[RyanD789]

Hello all!

I am trying to find a way to optimize the query that comes from forumdisplay.php:

Example:

SELECT COUNT(*) AS threads, SUM(IF(thread.lastpost > 1187817369

...

[RyanD789]

I'm looking to replicate_db_table only the necessary tables for vbulletin fulltext searching, so that I can increase the speed of replication since the replication thread is only single threaded.
...

[RyanD789]

As I said, it was working fine yesterday, and then suddenly not today. I had even fewer users on today than yesterday, and yet it's been a problem all day today.

Turning it off just isn't...

[RyanD789]

Right now, we have an average of between 2500-3500 users on concurrently. (having less today that yesterday actually, yet it became a problem today).

If I show the information to just members...

[RyanD789]

Thanks you for the suggestions, however, those settings only seemed to make a difference to the "Who's Online" feature that we actually have turned off, rather than the "display logged...

[RyanD789]

Today, around noon, the who's online feature began causing massive db load (please read this post carefully before simply suggesting to leave it off, or turn it off for better performance).
...

[RyanD789]

FYI... I disabled the member listing for now and my forum is running 50 times better and faster. Seriously. Those member listing queries were killing the server!

Anybody with a large forum...

[RyanD789]

Nevermind. It is from the member listing. Takes 10 - 12 seconds on average! Killing me. Am I the first one? Any fixes besides disabling the member listing?

Ryan

[RyanD789]

This is in my slow query log... over and over and over, except the LIMIT increments by 30 each time. It takes 12 seconds to run each time it runs (and it constantly runs). Maybe not coincidentally, I...

[RyanD789]

Woops... I'm an idiot. I just found it in the EDIT OPTIONS part of the User CP. I don't know how I missed it.

Ryan

[RyanD789]

Is there a user-based option that will turn off viewing of avatars? It would be nice if a user had an option in their USER CP to turn off VIEWING of avatars if they want the pages to load quicker or if...

[RyanD789]

Yes, I did that.

I found that this is probably related to the fact that I added a Juniper hardware product we put in front of the network for caching, etc. that caused VBulletin to think...

[RyanD789]

Yes, and it is fine. It's got to be something else.

Does anybody else have problems with APC like this?

Ryan

[RyanD789]

Some members said they waited 40 minutes and it still wasn't working.

And if it was a time problem, wouldn't the strikes table still show 5 tries from each acount before locking them out?...

[RyanD789]

This morning everybody got the "You must wait 15 minutes to try again since you tried too many times to enter your password" type of error. Even on their first try.

I thought it...

[RyanD789]

Oh, do you mean edit any option so VB will reload the setting table? It seems to be working now. I changed it back to value=1.

Ryan

[RyanD789]

Hmm... I manually changed this setting to value = 1 and it didn't seem to work. I then changed it to -1 and it worked, but that doesn't seem right. What am I missing?

Edit any options? Not...

[RyanD789]

I think I found it. Change the value to 1 where the varname = fulltextsearch in the setting table. (After manually running the Alter queries to add fulltext to the tables.)

Anyplace else...

[RyanD789]

Yes, how do we manually change the Search Type flag? Not how do we run the Alter queries, but change the Search Type to Fulltext without going through VB control panel?

Thanks!
Ryan...

[RyanD789]

Thanks for the quick reply. My problem is that I want both: quality results and fast responses. I will PAY for hardware to achieve it, but that takes time install.

I will see what happens...