|
|
Joined: Oct 2002
Posts: 71
Power User
|
|
Power User
Joined: Oct 2002
Posts: 71 |
First some background. I've been running UBBThreads now for 8 months (upgraded from UBB that I ran for 4 years) and have the following: site: TDIClub Forums ( By IP as we just moved the server ) Processor: Dual PIII 1.13GHZ system Disks: Raid5 7200rpm IDE Drives Ram: 2GB ECC replies: 519400 topics: 61997 Users online (30min, not 10min): 100 logged in, 120-150 not Load average: 0.79, 0.49, 0.58 Referer check disabled persistent connections (same with them disabled) from tweaked my.conf: <br />set-variable = key_buffer=64M <br />set-variable = table_cache=1024 <br />set-variable = sort_buffer=4M <br />set_variable = read_buffer_size=2M <br />set-variable = max_allowed_packet=1M <br />set-variable = thread_stack=128K <br /> The problem is I still get varying results in performance. Many queries will queries will give results in under a second, but more an more are taking longer. Like this one: I've recorded it taking 11.233 seconds, but most of the time its less than .2 Not sure what to do...I've had the main page take 20 seconds ones. Thanks for any suggestions Fred PS, this doesn't always happen, I can track any sort of relationship with the slow downs, but they keep coming and going....
|
|
|
|
|
Joined: Feb 2002
Posts: 1,759
Addict
|
|
Addict
Joined: Feb 2002
Posts: 1,759 |
Have you ever optimized your database? That can make a HUGE difference if you have not done so. I almost have to optimize mine every few months to keep me at tip top speeds. But your server loads are not high at all, so I don't think the server is overworked or anything.
I would first try optimizing your db and see if that helps.
|
|
|
|
|
Joined: Oct 2002
Posts: 71
Power User
|
|
Power User
Joined: Oct 2002
Posts: 71 |
Optimizing via UBBThreads or otherwise? Via UBBThreads I do that at least weekly.
Anything else I should add to the my.conf file? 1/2 million records can't be that bad, I only get 800 or so new ones a day.
|
|
|
|
|
Joined: Dec 2000
Posts: 1,471
Addict
|
|
Addict
Joined: Dec 2000
Posts: 1,471 |
As i checked your board yesterday, there where about 150 anonymous users online. You can try to decrease the default thread per page and posts per page setting.
It will only affect anonymous users and newly registered users (who can adjust this setting in their profile).
It should decrease the load.
Another bottleneck could be the search. A search over all postings can bring down the hole board, especially if you have a many postings.
Maybe you can log the memory and cpu usage. Logging the search will also help.
|
|
|
|
|
Joined: Oct 2002
Posts: 71
Power User
|
|
Power User
Joined: Oct 2002
Posts: 71 |
Hmm, I'll try it at 15 and 15 down from 15 and 20. One thing my users use a lot of is "past 24 hours" of posts. This is the modification I did. Could it be as simple as this query not optimized and when I get a lot of people checking the past 24 hours, this could be bringing the forums to a grinding halt? PS, this is an extreme example of just topic I just tried to bring up with the following results: [] Generated in 28.104 seconds in which 28.04 seconds were spent on a total of 13 queries. Zlib compression enabled. [/] 
|
|
|
|
|
Joined: Dec 2000
Posts: 1,471
Addict
|
|
Addict
Joined: Dec 2000
Posts: 1,471 |
Yeah, that hack could cause this.
If several people run this queries at the same time, it will slow down all other queries also.
What you could do is to add "&debug=1" to your url.
You'll see all queries explained. Can you post the results of the "past 24 hours hack" query?
|
|
|
|
|
Joined: Oct 2002
Posts: 71
Power User
|
|
Power User
Joined: Oct 2002
Posts: 71 |
<br />Query: REPLACE INTO w3t_Online (O_Username , O_Uid , <br />O_Last , O_What , O_Extra , O_Read , O_Type) VALUES ('VW <br />Derf' , '91' , '1065192220' , 'dosearch' , '' , '' , 'r') <br />Query took a total of 0.001 seconds. <br /> <br />Query: SELECT Bo_Keyword FROM w3t_Boards WHERE <br />(Bo_Read_Perm LIKE '%-3-%' OR Bo_Read_Perm LIKE '%-1-%' OR <br />Bo_Read_Perm LIKE '%-2-%') <br />table type possible_keys key key_len ref rows Extra <br />w3t_Boards ALL 48 Using where <br />Query took a total of 0.001 seconds. <br /> <br />Query: SELECT t1.B_Number , t1.B_Main , t3.U_Username , <br /> t1.B_Subject , t1.B_Posted , t1.B_Board , t3.U_Color , <br />t1.B_Reged , t1.B_Icon , t2.Bo_Title , t2.Bo_Read_Perm , <br />t2.Bo_Cat , t1.B_Posterid , t1.B_Status , t1.B_AnonName , <br />t1.B_Body FROM w3t_Posts AS t1 , w3t_Boards AS t2 , <br />w3t_Users AS t3 left join w3t_Posts as hack on <br />t1.b_main=hack.b_main AND t1.b_number 1065105820 AND <br />t1.B_Posted < 4294967294 AND ( (t1.B_Board = 'UBB1') OR <br />(t1.B_Board = 'UBB2') OR (t1.B_Board = 'UBB3') OR <br />(t1.B_Board = 'UBB4') OR (t1.B_Board = 'UBB5') OR <br />(t1.B_Board = 'UBB6') OR (t1.B_Board = 'UBB7') OR <br />(t1.B_Board = 'UBB8') OR (t1.B_Board = 'UBB9') OR <br />(t1.B_Board = 'UBB10') OR (t1.B_Board = 'UBB11') OR <br />(t1.B_Board = 'UBB13') OR (t1.B_Board = 'UBB14') OR <br />(t1.B_Board = 'UBB15') OR (t1.B_Board = 'UBB16') OR <br />(t1.B_Board = 'UBB17') OR (t1.B_Board = 'UBB18') OR <br />(t1.B_Board = 'UBB19') OR (t1.B_Board = 'UBB20') OR <br />(t1.B_Board = 'UBB21') OR (t1.B_Board = 'UBB22') OR <br />(t1.B_Board = 'UBB23') OR (t1.B_Board = 'UBB24') OR <br />(t1.B_Board = 'UBB25') OR (t1.B_Board = 'UBB26') OR <br />(t1.B_Board = 'UBB27') OR (t1.B_Board = 'UBB28') OR <br />(t1.B_Board = 'UBB29') OR (t1.B_Board = 'UBB30') OR <br />(t1.B_Board = 'UBB31') OR (t1.B_Board = 'UBB32') OR <br />(t1.B_Board = 'UBB33') OR (t1.B_Board = 'UBB34') OR <br />(t1.B_Board = 'UBB35') OR (t1.B_Board = 'UBB36') OR <br />(t1.B_Board = 'UBB37') OR (t1.B_Board = 'UBB38') OR <br />(t1.B_Board = 'UBB39') OR (t1.B_Board = 'UBB40') OR <br />(t1.B_Board = 'UBB41') OR (t1.B_Board = 'UBB42') OR <br />(t1.B_Board = 'UBB43') OR (t1.B_Board = 'UBB44') OR <br />(t1.B_Board = 'NewsSubmissions') OR (t1.B_Board <br />= 'GenAuto') OR (t1.B_Board = 'RoadTrips') OR (t1.B_Board <br />= 'EventCalender') OR (t1.B_Board = 'sightings') ) ORDER BY <br />t1.B_Last_Post DESC LIMIT 26 <br /> <br />table type possible_keys key key_len ref rows Extra <br />t1 range w3t_Postsindex6,w3t_Postsindex7,w3t_Postsindex8,ID_ndx <br />w3t_Postsindex8 4 1112 Using where; Using filesort <br />Query took a total of 0.085 seconds. <br />I don't know how much searching goes on as well, but I suspect a fair bit. Hard to tell who is searching or just looking at "past 24 hours" in the "who's online section. If this query is done a lot, wouldn't this all be cached until the next post comes in a few minutes later?
|
|
|
|
|
Joined: Dec 2000
Posts: 1,471
Addict
|
|
Addict
Joined: Dec 2000
Posts: 1,471 |
[]Using where; Using filesort [/]
Your query doesn't use an index so it has to do a additional full table scan to order the results. But the query is executed quite fast at the moment.
The results will only be cached if the posts table hasn't changed in the mean time. But on an active boards, it changes nearly every second.
It would be a good idea to log all slow queries (option in your my.cnf), it usually helps to determine the bottleneck.
|
|
|
|
|
Joined: Oct 2002
Posts: 71
Power User
|
|
Power User
Joined: Oct 2002
Posts: 71 |
Ahh, I'll see if I can fix that. That said, anyone played with YASM on 6.4? Can somethign similar be done?
|
|
|
Donate to UBBDev today to help aid in Operational, Server and Script Maintenance, and Development costs.
Please also see our parent organization VNC Web Services if you're in the need of a new UBB.threads Install or Upgrade, Site/Server Migrations, or Security and Coding Services.
|
|
|
Posts: 87
Joined: December 2001
|
|
|
Forums63
Topics37,575
Posts293,931
Members13,824
| |
Most Online6,139
Sep 21st, 2024
|
|
Currently Online
Topics Created
Posts Made
Users Online
Birthdays
|
|
|
|
