There's not much you can put in place to help a query like that, it's why search is turned off!
As for thread limits the people that post on those threads like them and they are part of the fabric of this community. Pete feels very strongly and I agree that we would only put limits in place as a last resort, we will exhaust our technical options first.
Correct we would NEVER get rid of them only roll them over into new threads
Good lord no! If I did it would be on another set of drives
Exactly
The memory is fine, the reason to add memory if your drives are strained is because paging can sometimes look like a drive problem That's not the case here.
There's really a lot more to this than even what I have said here. Standard vbulletin uses MyIsam tables which perform a table lock during an insert. One thing that would help a lot is to convert some tables to Innodb. The next version of Vbulletin is rumored to have Postgresql support so maybe we will go that route. We may look at having some modifications done. I HATE to do that because every upgrade then becomes so much harder.
We may add another DB server at some point but the problem there is the replication reduces efficiency, from what I have seen another DB server only gets you about a 30% increase.
We have the 33rd largest discussion board on the internet according to big-boards.com and the 12th largest Vbulletin board and we are bound to have some growing pains once in a while. I know at one point today we had well over 3000 people on line though something is working
Postgresql support in the rumored 4.0 release? I didn't think the 3.7 (still in beta?) incremental release supported Postgresql.
Alex - I figured you guys had to have logging turned off. I can only imagine how bad it would be if you turned it on.
I skimmed this waiting for the non-techie version. Then I went back and read every word of about half the post, skipped to near the bottom.We know there have been some issues with the boards and I wanted to give everyone an update. I've included a non-technical and a technical version. Since the server move we have run into several issues:
1. The code that was supposed to keep people from using the built in search and keep them on the board tracker search wasn't working right. Some people were able to access the built in search which quickly kills the boards.
Technical- Hows this for a query???
Count: 1 Time=1208.00s (1208s) Lock=0.00s (0s) Rows=8744.0 (8744),
SELECT
thread.threadid, thread.forumid, post.userid
FROM thread AS thread
INNER JOIN post AS post ON(thread.threadid = post.threadid )
WHERE post.postid IN(N,,{repeated 146966 times}N) AND thread.forumid IN(N) AND post.visible = N
Yes that is 1208 seconds
That is fixed and by all accounts performance has been better today.
2. The hard drives weren't fast enough- sometimes new isn't as much better as you would think
Technical- the old database server was 4 10k scsi's in a raid 0+1. The new one has 4 15k SAS in a raid 0+1. We are not seeing much difference at all. We are looking at adding 2 15k SAS's for the OS and having a 6 disk raid 0+1 or going with 2 drives for the OS and tying into our hosts fiber channel SAN.
It's going to take a week to sort that out.
3. We are getting a little big for the "stock" version of the software.
Technical- it doesn't scale all that darn well
We are considering bringing in some expert consultants that are specialists in mysql performance to look at the code and see what they can do. We know that long threads are becoming an issue. I'll just copy a quote from the vbulletin board to explain:
vb stores the threadid and dateline in the post table. Whenever a bunch of posts needs to be selected, vb filters the post table by threadid and orders the results by dateline, and takes a few of the posts (of course there is an index involved). In other words the query is similar to
SELECT * FROM post WHERE threadid=$threadid ORDER BY dateline LIMIT 8575,15
which means that mysql will have to throw away the first 8575 results and then return the next 15. If my understanding is correct, this is the reason why large threads are bad. We need only 15 rows, but mysql has to analyse thousands.
With some threads approaching 125,000 posts you can see the issue. We don't want to have to put thread length limits back so we are looking for options.
I skimmed this waiting for the non-techie version. Then I went back and read every word of about half the post, skipped to near the bottom.
There is NO part of this that non-techie people would understand!
So I'm going with what I originally figured.
1. Something's wrong. Wasps attacking the DIS.
2. If I know something's wrong, they know something's wrong and are working on it, poor techie people. God bless them using their RAID to get rid of the wasps. Hope the stuff doesn't make them wheeze.
3. It is better now, so they must have fixed it. No more danger of Mr. Webmaster Alex being stung. Hope the lingering smell of the RAID isn't making them sick.
Every single time the DIS has issues, I think of bunches of wasps attacking Mr. Webmaster Alex and him wildly spraying RAID around to prevent himself from being stung. It gets slow, I think, "damn wasps."
Thanks for all your hard work, Mr. Webmaster Alex!!!
Non technical summary
1 thing was broke we fixed it
2 things are still broke we are working on fixing them
Is that better?
The emails are going out. If you are not getting them it's your email provider blocking them and you either need to contact them or use a different email address
The emails will come from webmaster@disboards.com
Unless I'm mistaken, thread limits would LOCK threads after a certain number of posts, not clean threads out. I think cleaning threads out is a bad thing, since important information, that people might want to refer back to, would be lost.