2

I have a Solaris 10 x86_64 system running MySQL 5.5. Under heavy usage times, we're getting very slow responses from the database: slow queries running into minutes that normally return in sub-second times. CPU utilization is in the 60-70% range. Load average regularly gets well into the 20s, infrequently into the 40s, and I've seen it up to the 50s. (Two four-core CPUs with HyperThreading enabled.) It acts like an I/O problem, as if it's waiting on disks to write, but I'm not seeing any indications that there is any actual I/O problem. Average disk wait times are consistently 0, average wait queues are in the 0.2-0.3 range, and disk busy percentages occasionally creep into the 15% area. (All of this as according to sar.)

The storage is a zfs zpool of 5 zdev mirrors of two SAS drives. I do not have an intent log device, but I don't see that as being an issue with this workload.

What am I missing?

wfaulk
  • 6,878
  • 7
  • 46
  • 75
  • can you put up a snapshot of vmstat while the load is increasing? – Matt Sep 18 '13 at 18:06
  • I think I may have lied about the CPU utilization. Looking back at some old vmstat logs, I'm seeing what look like sub-second bursts of 100% CPU utilization, which don't seem to show up well in prstat or top. I'll have to wait for another burst, but that may have been the right question to ask. – wfaulk Sep 18 '13 at 18:40
  • Also does the server generally recover on it's own or do you need to restart db's/app servers to clear things out? – Matt Sep 19 '13 at 08:54
  • We generally needed to kill database connections for things to clear out. If we discovered the problem early on, it would sometimes manage to clear on its own. – wfaulk Sep 28 '13 at 00:38
  • Sorry I haven't been as active on this question as I would have liked. We ended up seriously accelerating our move to a different machine and that took up basically all of my time. The new, faster hardware, plus a lot of code optimization, was able to get us past the problem. Still a little confused by the symptoms of the problem, though. – wfaulk Sep 28 '13 at 00:40

2 Answers2

1

Wanted to give you a more Solaris-like answer:

On a multiprocessor/multicore box you cannot really use CPU load for much. Initially use mpstat rather than prstat / top if you want to see if one of your cores occasionally gets into full utilization.

If mpstat has 8 lines of output it means you have 8 CPU cores and then any process that you see in prstat consuming more than 12.5% CPU resources (100/8) is likely to be CPU bound. To test if this is really so you can use prstat -L -p <pid> to see if any individual thread of that process hits 12.5% because then you know for sure that the process is CPU bound. You have quite a few available CPU cores on your box yet you should always remember that any single processing thread can only live on one CPU core. In order for MySQL to utilize your box it becomes a matter of how good it is to divide the work on multiple threads. If there's a single hot thread in MySQL then your powerful machine doesn't help much.

Also you can find quite a few people that will advise to turn Intel HyperThreading off on Linux/Solaris server-workload because it actually performs better without it. YMMM. From what I understand Intel HyperThreading is excellent for the desktop type of workload but for a server that is only supposed to do one thing and do that fast it may work detrimental to performance.

There are at least a dozen routes you can go from here so it is somewhat difficult to advise you before you have more facts.

unixhacker2010
  • 836
  • 8
  • 7
  • These are good points, but I'd already considered them by the time I posted the question. MySQL was running with something like 3000 threads, and no individual one or small set were hot spots. – wfaulk Sep 28 '13 at 00:43
0

You mention that the load average is regularly into the 20s and sometimes into the 40s or 50s. Then you mention you have 16 available processors ((2cpus x 4 cores) x 2) so a load average in the 20s means your processes are fighting for CPU time and in the 40s or 50s you have a large amount of wait.

CPU utilization and CPU load don't map together all that well, but I would expect to see a bit higher utilization unless things are not threading correctly.

There is another post that explains load average well using a scenario of traffic on a bridge:

Understanding Linux CPU Load

The short is - in a perfect world your load average would never go higher than your count of processors (16 in your case).

I this most likely the system just being overtaxed rather than a ZFS or MySQL issue.

** Edit, I see there was a comment added that said you are seeing some 100% CPU utilization. This would also fall in line with processes just waiting around for CPU time.

Chad
  • 96
  • 3