- Joined
- Aug 3, 2004
- Messages
- 21,316
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.
__________________
Alex
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.
__________________
Alex
headache: ), but we are very grateful for those that do and can sort it all out!!
