MySQL makes a server load and crashes

Ozz

This is the TOP stats from my vps:
http://img28.imageshack.us/img28/3528/clipboard014rg.gif

As you see server load is too much and its getting more then mysql service stops and everything is normal. This happens for 2 days, hosting company made a Linux kernel update maybe that can be the reason.

My VPS load was always too high and there is only 1 site got 10.000 unique visitors. I did so many optimizations on the mysql queries and also php. I cant afford a dedicated server at this time and I dont think it's not so much traffic.

What do you suggest?

bpat1434

mysql: 1.1%
root (httpd): 1.5%
105 processes: 101 sleeping, 2 running, 2 stopped
Each of the 4 CPUs is still over 95% idle

Seems normal to me....

Ozz

But load average 13.36 that means cpu working 1336% performance.
they are instant values httpd and mysql makes 99% frequently.
I sent this stats that maybe there are some values looks strange to someone.

bpat1434

I personally think there is something else behind it. Just because one program (or two) move to 99% utilization doesn't automatically make them the culprit. You'll notice that 13.36 is the load average in the past 1 minute. Yes that's a lot, but if in the past minute, mySQL and Apache aren't using 99% right then, or even utilizing a lot to even begin to point the blame at them. I'd look deeper.

I think you might be mixed up with your load averages. I'm no linux guru, but after reading this article, which explains load averages and how to calculate them, it says that it's an average of the CPU processes and que, not a percentage of utilization. On an aside, a cpu running at 1336% of its core-speed would burn up...

Since it seems you have shell access, try running the following commands:

For Memory: vmstat 5 5
For I/O: iostat 5 5
For CPU: prstat 5 5

See what they give you

Sxooter

Ozz wrote:
But load average 13.36 that means cpu working 1336% performance.
they are instant values httpd and mysql makes 99% frequently.
I sent this stats that maybe there are some values looks strange to someone.

Nope, not exatly what it means. When you have a load of 13.36, it means that on average, at any given time, you have 13.36 processes running or waiting to run.

Since your CPU load is so low, then you likely have a lot of processes waiting for I/O to happen.

More than likely what you need here is a better I/O subsystem. Lots of drives under a hardware RAID controller with battery backed cache on top of a RAID 10 set (i.e. 0+1).

What does iostat on your box show?

P.s. I just looked at your top output again, and it shows 0.0% I/O wait. I'd still suspect something goofy in the I/O system.

But you shouldn't be getting a load of 13.3 if you have no processes sleeping and only 2 running.

bpat1434

So I was on the right track for getting it solved? With asking him to run the iostat and prstat and vmstat functions? Sweet....

Ozz

I have sent commands you said via Putty:
vmstat 5 5
iostat 5 5
prstat 5 5

VMSTAT 5 5 result was:
procs memory swap io system cpu
r b w swpd free buff cache si so bi bo in cs us sy id
0 0 0 93516 199184 109800 3693636 0 0 85 146 118 106 2 1 98
0 1 0 93516 192768 110928 3698172 0 0 741 641 1007 2681 1 0 99
0 7 0 93516 192436 111552 3699388 0 0 264 261 508 1388 0 0 100
0 0 0 93516 266960 112216 3701804 0 0 420 341 631 1589 0 0 99
0 0 0 93516 268284 114028 3711040 0 0 962 785 998 2356 1 0 99

IOSTAT gives "-bash: iostat: command not found" error.
PRSTAT "-bash: prstat: command not found" error.

Something maybe disabled. How can I get that stats results?

Edit: My server software info:
Operating system Linux
Kernel version 2.4.20-021stab028.19.777-entnosplit
Machine Type i686
Apache version 1.3.34 (Unix)
PERL version 5.8.7
PHP version 4.3.11
MySQL version 4.0.25-standard
cPanel Build 10.8.1-RELEASE 30

Sxooter

Your OS isn't linux. Your kernel is. your OS is likely a Linux distro, and they can vary a LOT from one flavor to the next, so let us know what OS you're running as well as the kernel version.

In RedHat and Fedora Core, the iostat program is installed by the sysstat package.

Note that you may have some strange kernel bug. There was a lot of strangeness in the 2.4 kernels where you'd get REALLY huge spikes in kswapd or another kernel process when doing a lot of task switching fast. It was a cascading thing, where the busier the system became, the worse the effects got, and the whole server just spiraled into a giant swan dive...

Most of those issues seem to have been fixed in the 2.6 kernel.