MySql at 99%

[matjaz]

my website was running smoothly on 2.4 Mhz 512Mb RAM server. when i was checking processes using "top" command, mysql was going from 0% - 34% CPU, while it stayed in range 15-35% RAM.

now i moved the same website to a better server - dual 2.4 Mhz, 1GB RAM,... and it works well. but when checking "top" i noticed that mysql is going from 0% - 99% CPU, ram goes up to 1% only.

i asked the admin, why is it going up to 99% when it didnt do that on the first server. he said that he thinks that my code is not written well enough. ok, i dont agree here, because for what it does, dual CPU with 1GB RAM should handle much more than that.

the other thing why i dont agree is the fact that it went up to 34% before and now with 3x more power it goes to 99%. same code, same database. does this make sense?

i have to tell that it stays at 99% for 2-3 seconds but it does that on 10-20 seconds intervals, constantly. the website is working well as i can see.

so what do you suggest? should he fine-tune mysql somehow?

[Sxooter]

Databases, in general, max out one of three things, and the other two will not be maxed out. Those three things are CPU %, memory bandwidth, and I/O bandwidth.

On a machine with 512Meg ram and possibly faster I/O subsystem, it's likely you never used all your CPU because your database was waiting on either memory bandwidth or for the data to come around under the platters.

Now that you've got larger amounts of memory, once the data is loaded into it, the I/O isn't an issue any more, so the only thing left to max out is the CPU. That's normal.

What you want to compare is how each performs while under load.

If the dual 2.4 with 1 gig of ram still runs faster while consuming 99% cpu, then it's probably ok that it does.

[csn]

I'd say that's weird, but I had a similar problem with mysql when I moved to a larger, more power server. I installed 4.x and it would start using up all the available cpu soon after startup, even when it wasn't running any queries. I half-heartedly tried to figure out why, but just figured maybe it was buggy and/or sloppily coded, then shut it down and vowed to use PostgreSQL only on that server. PG hardly uses any resources at all 🙂.

[ultraslacker]

The cpu usage could be due to the threading model, which can mean that mysql becomes a cpu hog with smp - what is the server OS?

[matjaz]

RedHat

[Sxooter]

which kernel? I had major problems (not cpu spiking, but bdflush and friends going haywire) under the older 2.2.9 kernels, but upgrading to the latest 2.2.24 or so under RH 7.2 fixed them all.