NB! This is a bit on the long side.
Ubb.threads 6.2.3
Xserve 2Gb RAM, 2 * 1 Ghz processors.
-------
We are running a number of UBB classic sites (10) off a largish SUN box, and we are now in the process of moving these to a new server (a Xserve is anyone is interested) running UBB.threads.
This has proven to be a interesting experience, as these boards are reasonably large, the first one had around 40.000 registered users, and a few hundred thousand messages, with around 100.000 pageviews per day for this one alone.
After tuning MySQL the board ran at a reasonable speed, but adding the second board slowed things down considerably.
Again we tuned the boards by only showing categries on the "main view" page, tracing new messsages only in thread view etc, all the stuff we found on the support boards. It all helped, but not enough.
Further digging has lead me to what I consider a design problem - and I am interested in others opinions on how to fix this.
On the second board, with 9000 registered users all tracking shows me that the most heavily requested page is the "main view" page, so I concentrated on this.
Turning on debug info in the footers, and adding &debug=1 shows me that one specific query takes up a little less than half of all database query time on this page. - and the most frequent query in the processlist. The query is
"
Query: SELECT U_Username , U_Number FROM mojo_Users WHERE U_Approved='yes' ORDER BY U_Number DESC LIMIT 0 , 1 "
Looking at this querey with "SHOW PROCESSLIST" gives the following information
"Query: SELECT U_Username , U_Number FROM mojo_Users WHERE U_Approved='yes' ORDER BY U_Number DESC LIMIT 0 , 1
table ,type ,possible_keys ,key ,key_len ,ref ,rows ,Extra
mojo_Users ,range ,App_ndx ,App_ndx ,9,7846 ,where used; Using filesort "
Query took a total of 0.698 seconds. " - this out of 2.2 seconds, probably due to the combinations of a full table scan, the number of hits, and the filesort of these, all to get one users name.
After looking at the source this query is used to find the name of the boards newest user.
I am now getting worried, as this query seems to be extremely inefficient, and as far as I can see it will only consume more and more time as the number of users increase, ie. it's not scaleable, and serves a not terribly important function (IMHO).
All stats from the server indicates that it is CPU bound, (and MySQL bound) at this time, no problems with disk/swapping etc.
---
I can see 3 options for handling this :
1. Rewrite the query (a big job, and a pain every time we upgrade the UBB code).
2. Chop out this query and its results from the "main view"
3. Upgrade MySQL to version 4, where the query cache in theory could speed up the query considerably.
But before I do anything - any views on this from other users? (Especially the MySQL upgrade one)
Tor-Arne Gisvold
