|
|
Joined: Jun 2006
Posts: 3,837
Carpal Tunnel
|
|
Carpal Tunnel
Joined: Jun 2006
Posts: 3,837 |
Okay - I have been battling with my server ever since I upgraded to 7. My number of visitors is around the 300 mark at any one time (including bots etc.) so not too high. But the load is often around 6-20 during the day (currently 15+). /forums/ubbthreads.php seems to be hogging most of the resources on the server, which in turn knocks email back and so we get into a downward spiral. I am at a loss... It is not the backup Gizmo  as it is still daytime - but something is killing me and the server. The server is... 2 x Maxtor \ 160GB:SATA:7200RPM \ DiamondMax 10 2GB DDR 400 ECC Reg 2 x AMD \ 265 - dual core - 1.8ghz \ Opteron 265 Can anyone suggest anything, as something is not right and I feel like jacking the whole thing in - I can only take a certain number of days and this is been going on now for 6 weeks  (not all the time, but a fair chunk of it)
|
|
|
|
|
Joined: Jun 2006
Posts: 9,242 Likes: 1
Former Developer
|
|
Former Developer
Joined: Jun 2006
Posts: 9,242 Likes: 1 |
I'll see if I can catch it when it acts up. I've been on your server a couple times, as I am now, and the load is minimal. Currently a bit over 300 users online and the load average is around .5
|
|
|
|
|
Joined: Jun 2006
Posts: 9,242 Likes: 1
Former Developer
|
|
Former Developer
Joined: Jun 2006
Posts: 9,242 Likes: 1 |
A couple things I've noticed. The first one. Looking at your mysql_slow_query_log there are a couple of things that are really hitting MySQL hard. One is an old query from your w3t_Posts table, looks like a query on an old version that people are still getting to and running. It looks something like:
SELECT B_Number
FROM w3t_Posts
WHERE B_Approved = 'yes'
AND B_Status <> 'M'
AND B_Board IN ('board1','board2','etc')
AND B_PosterId = 'userid'
When that query runs, it's taking anywhere from 10-90 seconds from looking at the slow query log. So it looks like you have an older version that people are still going to and this query is executing.
The second slow query is one that I had noted earlier, and that's the option to show the total # of new posts. With the number of forums you have, including subforums, this is another query that is pretty slow. The reason being, is it appears that many of your users never visit some of the forums, so the number of new posts in some of them continue to grow. Even though they never visit them, that query is executing for every forum every time they visit the main page. There's no way to really optimize this, it's a resource hog and always has been, which is why it's noted that way in the control panel.
I can't say these are the reasons you're seeing the very high load average, but the slow query log has about 3 weeks of data i n it, and it has about 4000 entries. Meaning over the course of that time, there have been 4000 queries that have taken over 10 seconds to execute. And they all are the queries I mentioned above.
|
|
|
|
|
Joined: Jun 2006
Posts: 3,837
Carpal Tunnel
|
|
Carpal Tunnel
Joined: Jun 2006
Posts: 3,837 |
which is fine  I have had my server admins look at it - they all point towards threads Yesterday it was bad, and has been today. The load went as high as 60 at one point this afternoon (couple of hours ago)
|
|
|
|
|
Joined: Jun 2006
Posts: 3,837
Carpal Tunnel
|
|
Carpal Tunnel
Joined: Jun 2006
Posts: 3,837 |
I started typing before your reply.
I still have my old copy up - should that be taken down?
I have noticed that I still get spammers trying to autosign up at the old forums - so are other bots also hitting that and causing issues?
|
|
|
|
|
Joined: Jun 2006
Posts: 3,837
Carpal Tunnel
|
|
Carpal Tunnel
Joined: Jun 2006
Posts: 3,837 |
Even if we had no-subforums - many people would not visit all of them - if they have no interest in a team then this stays unvisted. Or are we simply talking about the (5) (15) on the forum pages? Looks like people are going to pages such as http://www.fansfocus.com/forum/ubbthreads.php?Cat=0 - which certainly is not redirecting....
|
|
|
|
|
Joined: Jun 2006
Posts: 3,837
Carpal Tunnel
|
|
Carpal Tunnel
Joined: Jun 2006
Posts: 3,837 |
|
|
|
|
|
Joined: Jun 2006
Posts: 9,242 Likes: 1
Former Developer
|
|
Former Developer
Joined: Jun 2006
Posts: 9,242 Likes: 1 |
I'd just replace the old ubbthreads.php file with a simple redirect to the new forums
<?php
header("Location: http://www.fansfocus.com/forums/ubbthreads.php");
?>
That should catch most of them that are going to the old forum.
Yeah, it's basically the (5) (15) on the forum pages that I was referring to. Probably 80% of the entries in the slow_query log are the queries to check the total # of new posts. For some of your users this is fine as it seems they visit just about every forum. But for many this query is take a long time to execute because they only visit a few of them, but it's still checking on all of them.
|
|
|
|
|
Joined: Jun 2006
Posts: 3,837
Carpal Tunnel
|
|
Carpal Tunnel
Joined: Jun 2006
Posts: 3,837 |
I would love to know how VB and others cope with this - as they have some massive sites that all use new post indicators.
Unless they are all running multiple servers LOL
|
|
|
|
|
Joined: Jun 2006
Posts: 9,242 Likes: 1
Former Developer
|
|
Former Developer
Joined: Jun 2006
Posts: 9,242 Likes: 1 |
It's not the new post indicators alone, those are fine. It's showing the total # of new posts in each forum. Didn't even know VB had this option, at least they didn't before I know of one other forum that has the feature and they have the same type of warning...use at your own risk There is no way to optimize this query. To show the total # of new posts, it has to do a query to count every post that is newer than your last visit timestamp. It uses an index to count, but if you have people that never visit forum A, and never mark all read, it's basically counting every post in the forum, every time they load the front page. It's not a bad query if all of your users are active and visit pretty much every forum, as they are quick. It gets worse when you have a large number of forums where your users only visit a small portion of them.
|
|
|
|
|
Joined: Jun 2006
Posts: 3,837
Carpal Tunnel
|
|
Carpal Tunnel
Joined: Jun 2006
Posts: 3,837 |
is there anyway to mark everyone as read?
|
|
|
|
|
Joined: Jun 2006
Posts: 9,242 Likes: 1
Former Developer
|
|
Former Developer
Joined: Jun 2006
Posts: 9,242 Likes: 1 |
Not anything that's available. You'd need a script that queries the last post time for every forum, and then go through every user and update FORUM_LAST_VISIT table for each user/forum.
Another thing I noticed is that your ft_min_word_len is set to 2 characters. The default MySQL is 4, they say 3 can be unstable, I imagine 2 is pretty drastic. Basically, MySQL is indexing every word that contains at least 2 characters, so this probably doesn't help matters when things get busy.
|
|
|
|
|
Joined: Jun 2006
Posts: 9,242 Likes: 1
Former Developer
|
|
Former Developer
Joined: Jun 2006
Posts: 9,242 Likes: 1 |
Just saw a spike. Load went up to about 10. This one was related to spamd. You had about 30 spamd processes running, each taking up a chunk of cpu. Went on for a few minutes, and then things started dropping back down to normal.
|
|
|
|
|
Joined: Jun 2006
Posts: 3,837
Carpal Tunnel
|
|
Carpal Tunnel
Joined: Jun 2006
Posts: 3,837 |
Thanks Rick
ft_min_word_len has been altered to 3.
Of course when it went high today then the database corrupted.
It was just getting depressing day after day...
Hopefully all this bits will assist.
You get to a point where all sense of logic goes out of the window - and you start to panic somewhat.....
|
|
|
|
|
Joined: Jul 2006
Posts: 2,143
Pooh-Bah
|
|
Pooh-Bah
Joined: Jul 2006
Posts: 2,143 |
Ian how old is your spamd. You're describing something that happened to us quite some time ago. It was something to do with the version of spamd we were running.
|
|
|
|
|
Joined: Jun 2006
Posts: 9,242 Likes: 1
Former Developer
|
|
Former Developer
Joined: Jun 2006
Posts: 9,242 Likes: 1 |
Understandable Ian. That's the one thing that's tough is there are many variables that come into play. With the ft_min_word_length change, I believe you need to restart mysql and also rebuild the indexes. However, this can take a while, so it's probably something you want to schedule at a slow time.
|
|
|
|
|
Joined: Jun 2006
Posts: 3,837
Carpal Tunnel
|
|
Carpal Tunnel
Joined: Jun 2006
Posts: 3,837 |
I restarted Mysql - not sure about rebuilding the indexes???
|
|
|
|
|
Joined: Jun 2006
Posts: 9,242 Likes: 1
Former Developer
|
|
Former Developer
Joined: Jun 2006
Posts: 9,242 Likes: 1 |
rebuilding the indexes is done by:
REPAIR TABLE ubbt_POSTS QUICK
However, you should close the forums before running this as it will take awhile to run and complete.
|
|
|
|
|
Joined: Jun 2006
Posts: 3,837
Carpal Tunnel
|
|
Carpal Tunnel
Joined: Jun 2006
Posts: 3,837 |
taken an hour to run so far LOL so much for it being 'quick'
|
|
|
|
|
Joined: Jun 2006
Posts: 3,837
Carpal Tunnel
|
|
Carpal Tunnel
Joined: Jun 2006
Posts: 3,837 |
I'm going to open the forums back up - this is taking far too long....
|
|
|
|
|
Joined: Jun 2006
Posts: 3,837
Carpal Tunnel
|
|
Carpal Tunnel
Joined: Jun 2006
Posts: 3,837 |
well I would do if anything would load - just getting a blank grey screen, when trying to load the CP or site in a new tab. Clearly it is not well
|
|
|
|
|
Joined: Jun 2006
Posts: 3,837
Carpal Tunnel
|
|
Carpal Tunnel
Joined: Jun 2006
Posts: 3,837 |
I didn't realise this would take over 2 1/2 hours - 10-15 minutes max - but not two hours plus. Can someone please let me know how long this might take please - as I do not really wish to sit up all night Many thanks.
|
|
|
|
|
Joined: Jun 2006
Posts: 16,301 Likes: 116
|
|
Joined: Jun 2006
Posts: 16,301 Likes: 116 |
Ya know ian, the only lag i see on your server is: 12:10:02 AM all 1.40 1.85 2.43 22.47 71.85
12:20:02 AM all 3.82 1.70 10.85 15.36 68.26
12:30:01 AM all 3.10 1.07 7.95 19.86 68.01
12:40:01 AM all 3.70 1.93 10.70 19.04 64.63
12:50:01 AM all 3.07 1.05 6.11 27.04 62.73
01:00:02 AM all 4.30 0.97 3.62 32.82 58.29
01:10:01 AM all 2.20 1.06 1.61 10.02 85.10
and 12:00:01 PM all 3.79 4.11 2.64 18.20 71.26
12:10:01 PM all 4.16 4.75 3.21 9.26 78.62
12:20:01 PM all 3.53 4.78 2.51 23.17 66.02
12:30:03 PM all 1.82 4.63 2.44 40.31 50.79
12:40:01 PM all 4.38 2.57 2.52 29.50 61.03
12:50:01 PM all 3.46 4.74 3.05 31.85 56.90
01:00:01 PM all 6.47 3.29 3.72 8.15 78.37
01:10:01 PM all 6.39 2.69 3.03 2.81 85.08
01:20:03 PM all 7.66 1.10 4.47 26.57 60.19
01:30:03 PM all 2.92 0.98 1.57 35.34 59.19
01:40:03 PM all 0.60 2.67 1.57 35.71 59.45
01:50:02 PM all 0.99 2.39 4.37 39.17 53.07
02:00:01 PM all 2.89 5.74 3.91 41.96 45.51
02:40:02 PM all 7.74 2.10 3.02 13.37 73.77
And during those times when I was actively monitoring it, the only process running, was vv... But i'm not commenting on that again weather you want to believe me or not, there is a schedual to your lag, it's at midnight and noon on your server.
|
|
|
|
|
Joined: Jun 2006
Posts: 9,242 Likes: 1
Former Developer
|
|
Former Developer
Joined: Jun 2006
Posts: 9,242 Likes: 1 |
You don't want to open the forums while it's rebuilding the indexes, you'll have all sorts of issues at that point. It's hard to say how long it's going to take however. I've done it on some forums with a couple hundred thousand posts and it's taken around a half hour...on others it's taken several hours.
|
|
|
|
|
Joined: Jun 2006
Posts: 3,837
Carpal Tunnel
|
|
Carpal Tunnel
Joined: Jun 2006
Posts: 3,837 |
vv does not run at noon  But I will get it checked out - but it should just be midnight.... At the moment I have had no mail running on my server for the last 3 hours, another site is down with a database error and so is webmail. This is getting beyond a joke....
|
|
|
|
|
Joined: Jun 2006
Posts: 9,242 Likes: 1
Former Developer
|
|
Former Developer
Joined: Jun 2006
Posts: 9,242 Likes: 1 |
That's why I had mentioned you probably want to run this during a slow period. It looks like your forums have been reopened and now you basically have a deadlock going on. The posts table is locked while it rebuilds the indexes and you've got many queries backed up behind it now. You need to turn the forums off until it's done.
|
|
|
|
|
Joined: Jun 2006
Posts: 3,837
Carpal Tunnel
|
|
Carpal Tunnel
Joined: Jun 2006
Posts: 3,837 |
Problem is I can't go to bed because it is still running - and I need mail up for my clients (who are already calling) - I didn't realise this would cause these issues.
I tried restarting qmail without joy.
|
|
|
|
|
Joined: Jul 2006
Posts: 4,057
|
|
Joined: Jul 2006
Posts: 4,057 |
Wish i could help, but i can only read about it. Hope you can get it sorted Once and for all. I wouldnt know where to begin mate. Good time to ask for a "Pissed Off" Mood to be added ?
BOOM !! Version v7.6.1.1
People who inspire me Isaac ME Gizmo
|
|
|
|
|
Joined: Jun 2006
Posts: 3,837
Carpal Tunnel
|
|
Carpal Tunnel
Joined: Jun 2006
Posts: 3,837 |
No - I haven't reopened them
|
|
|
|
|
Joined: Jun 2006
Posts: 9,242 Likes: 1
Former Developer
|
|
Former Developer
Joined: Jun 2006
Posts: 9,242 Likes: 1 |
You've got some queries building up from somewhere.
SELECT COUNT(*) as total, a.USER_ID, b.USER_DISPLAY_NAME as user
FROM ubbt_POSTS AS a,
ubbt_USERS....
I can only see part of the query. That's not a standard .threads query, so something is trying to query against the posts table even though it's closed.
|
|
|
|
|
Joined: Jun 2006
Posts: 3,837
Carpal Tunnel
|
|
Carpal Tunnel
Joined: Jun 2006
Posts: 3,837 |
Probably Ian's mod for autoposting - I have disabled it for now.... I am giving it 10 minutes and then running reboot -n
|
|
|
|
|
Joined: Jun 2006
Posts: 9,242 Likes: 1
Former Developer
|
|
Former Developer
Joined: Jun 2006
Posts: 9,242 Likes: 1 |
The indexes are rebuilding. If you reboot in the middle of it, you're going to end up causing more problems than the short term gain. You can run the mysqladmin processlist to see that it's still running.
|
|
|
|
|
Joined: Jun 2006
Posts: 9,242 Likes: 1
Former Developer
|
|
Former Developer
Joined: Jun 2006
Posts: 9,242 Likes: 1 |
Those queries are still backing up. From the looks of it, it's something that's running every 2 minutes.
|
|
|
|
|
Joined: Jun 2006
Posts: 3,837
Carpal Tunnel
|
|
Carpal Tunnel
Joined: Jun 2006
Posts: 3,837 |
There is nothing else running.... I have very few mods installed.
|
|
|
|
|
Joined: Jun 2006
Posts: 9,242 Likes: 1
Former Developer
|
|
Former Developer
Joined: Jun 2006
Posts: 9,242 Likes: 1 |
It sure looks like it's something automated. Either that, or someone has a stopwatch and is bring up a page that queries the database every couple of minutes
| 72260 | username | localhost | database | Query | 413 | Waiting for table | SELECT COUNT(*) as total, a.USER_ID, b.USER_DISPLAY_NAME as user
FROM ubbt_POSTS AS a,
ubbt_USERS |
| 72752 | username | localhost | database | Query | 271 | Waiting for table | SELECT COUNT(*) as total, a.USER_ID, b.USER_DISPLAY_NAME as user
FROM ubbt_POSTS AS a,
ubbt_USERS |
| 73390 | username | localhost | database | Query | 117 | Waiting for table | SELECT COUNT(*) as total, a.USER_ID, b.USER_DISPLAY_NAME as user
FROM ubbt_POSTS AS a,
ubbt_USERS |
The 413, 271, 117 are how long the queries have been waiting. There are roughly about 73 of them backed up now. And they keep going, about 120-150 seconds in between eachother. Never less than a 2 minute span between them.
I can't see the full query but they don't appear to be part of the default threads install at all.
|
|
|
|
|
Joined: Jun 2006
Posts: 16,301 Likes: 116
|
|
Joined: Jun 2006
Posts: 16,301 Likes: 116 |
Rick, take a look at his crontab, theres several things that run on an automated schedual; but I don't think any are for his forum specifically.
|
|
|
|
|
Joined: Jul 2006
Posts: 2,143
Pooh-Bah
|
|
Pooh-Bah
Joined: Jul 2006
Posts: 2,143 |
content islands rebuilding every 2 minutes or so?
|
|
|
|
|
Joined: Jun 2006
Posts: 3,837
Carpal Tunnel
|
|
Carpal Tunnel
Joined: Jun 2006
Posts: 3,837 |
that is what I thought - some are set to always build
Last edited by Ian; 02/06/2007 7:35 PM.
|
|
|
|
|
Joined: Jul 2006
Posts: 2,143
Pooh-Bah
|
|
Pooh-Bah
Joined: Jul 2006
Posts: 2,143 |
Rick, that sure looks like something that at minimum is selecting displayed name and post count. Top posters?
|
|
|
|
|
Joined: Jun 2006
Posts: 9,242 Likes: 1
Former Developer
|
|
Former Developer
Joined: Jun 2006
Posts: 9,242 Likes: 1 |
I found it...it's your custom top_posters.php script...and it's not very friendly. It looks like it's meant to show top posters within the last 30 days. It's running every 2 minutes and the query is very, very baaaadddd.
SELECT COUNT(*) as total, a.USER_ID, b.USER_DISPLAY_NAME as user
FROM {$config['TABLE_PREFIX']}POSTS AS a,
{$config['TABLE_PREFIX']}USERS AS b
WHERE a.USER_ID = b.USER_ID
AND POST_POSTED_TIME > $limittime
AND a.USER_ID != 1
GROUP BY USER_ID ORDER BY total DESC
limit {$config['TOP_POSTERS']}
It has a limit in it to only return a certain number of top posters, but MySQL still has to calculate who the top posters are. So it's going through all of the posts within the last 30 days, and grouping them by userid, ordering them, and then returning the results. On a decent size board, that query alone isn't all that great, running every 2 minutes, it's even worse. Whenever that runs it'll lock your posts and users table until it finishes.
Last edited by Rick; 02/06/2007 7:40 PM.
|
|
|
|
|
Bots
by Outdoorking - 04/13/2024 5:08 PM
|
|
|
|
|
|
|
0 members (),
681
guests, and
174
robots. |
|
Key:
Admin,
Global Mod,
Mod
|
|
|
|
![[Linked Image from navaho.infopop.cc]](http://navaho.infopop.cc/progress.gif)
