We just recently upgraded to 6.4 and Im now being told we're using a heck of a lot of cpu. How 'normal' are these numbers and what can be done to scale back this cpu hit like this?
last pid: 65592; load averages: 0.42, 0.37, 0.33 up 23+15:53:37 06:31:58
63 processes: 3 running, 60 sleeping
CPU states: 53.9% user, 0.0% nice, 36.0% system, 0.0% interrupt, 10.1% idle
Mem: 50M Active, 496M Inact, 137M Wired, 48K Cache, 112M Buf, 319M Free
Swap: 2032M Total, 2032M Free
PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND
38076 mysql 45 0 19064K 15200K RUN 405:00 49.80% 49.80% mysqld
At the time, I had
There are 72 registered and 18 anonymous users online.
With on line settings set to 30 minutes. I have since scaled that back to 10 minutes.
Input is sorely needed, we can NOT afford a dedicated server here.
I am totally guessing here but did you check to use "persistant connections"? I was told one time that it could lead to problems on some larger boards...
BSD box. 63 processes, only 3 running. Load is under one, which means that over the last few minutes you had almost no processes that had to wait in queue. Your not using much memory to speak of.
It does look like your mysql is maybe using a bit more CPU than you would like, but that top snippet shows us nothing really.
Next time you run into this we'll need more than what you've shown. All that says is that mysql is using a lot of process. Doesn't say who is using mysql or what they are using it for. Doesn't help us troubleshoot.
Here's what will. -
Next time this happens have them log into mysql and run the following
show processlist;
Then post the entire processlist as it appears. That will show which query is slow, who is running it, and what kind of connections you have. We can go from there, it'll help a lot.
It would also help a lot if you posted a link to the site so we can take a look at your settings, useage patters, etc.
DrChaos , persistant connections are off. We're not using them.
[]navaho said:
BSD box. 63 processes, only 3 running. Load is under one, which means that over the last few minutes you had almost no processes that had to wait in queue. Your not using much memory to speak of.
It does look like your mysql is maybe using a bit more CPU than you would like, but that top snippet shows us nothing really.
Next time you run into this we'll need more than what you've shown. All that says is that mysql is using a lot of process. Doesn't say who is using mysql or what they are using it for. Doesn't help us troubleshoot.
Here's what will. -
Next time this happens have them log into mysql and run the following
show processlist;
Then post the entire processlist as it appears. That will show which query is slow, who is running it, and what kind of connections you have. We can go from there, it'll help a lot.
It would also help a lot if you posted a link to the site so we can take a look at your settings, useage patters, etc. [/]
Im not very familiar with using commands in mysql, I merely type : [:"red"]show processlist; [/] as it appears in red ?
ysql> show processlist ;
+-------+--------+--------------------------------+---------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+--------+--------------------------------+---------+
| 87665 | admin | localhost | rebuild | Query | 0 | NULL | show processlist |
| 87722 | dbuser | hosting23.digitalprinceton.net | rebuild | Query | 0 | Sorting result |
SELECT B_Number
FROM w3t_Posts
WHERE B_Topic = 1
AND B_Board |
| 87723 | dbuser | hosting23.digitalprinceton.net | rebuild | Query | 0 | Sorting result |
SELECT B_Number
FROM w3t_Posts
WHERE B_Topic = 1
AND B_Board |
+-------+--------+--------------------------------+---------+
There are only two executed queries, which should not be the problem.
You should run this command when you're experiencing problems with the server (high cpu usage). The processlist should be a lot longer then.
Ok, 47 users on line tonight. Forum goes down.
I did show processlist; and got two items.
One command shows 'sleep' the other is query.
?? What to think! I have no idea !
Mhh, that doesn't sound bad. No query is locked and the mysql server shouldn't be busy with two queries at the same time.
I found this thread searching for "high CPU MySQL" and discovered that all the threads about this had to do with FreeBSD. We were having exactly the same problem, and had optimized our tables, dropped our full-text indexes, researched all our queries and everything else, and even with no slow queries, and few processes running, we were at 98% CPU usage.
Eventually I found two key links, and the solution:
http://dev.mysql.com/doc/mysql/en/freebsd.htmland
http://jeremy.zawodny.com/blog/archives/000697.htmlThe solution: update FreeBSD to Release 5, or re-compile MySQL with WITH_LINUXTHREADS=yes
This appears to have solved the problem for us.
Tac