Hacker News Reader

Comment by webvet | Hacker News Reader

Let me try to answer: Slow - as in average Top loads (on CentOS) staying between 1 and 2 pretty much throughout the day; closer to 2 rather than to 1, peaking ALL the way up to 10 and even beyond several times a day, for periods as long as 15 to 30 minutes.

Storage engine: MyISAM

View on HN

Do you have some other measurements? slow_log, queries without indexes, low key cache usage? Load is just one metric and can be very deceiving. Do you have any backup system which hits the disks at the same time, batch jobs, or something similar? Depending on your workload, "load" can vary - it just means the writes are being queued up.

It's definitely not a good sign, but try to get more specific. I've seen servers doing heavy network I/O with "normal" load over 5 times the number of cores.

Also, unless you're doing loads of selects and very few modifications, you could gain a lot by switching to InnoDB, or XtraDB, rather than MyISAM.

Have to second this. You should monitor load for sure... but depending on how many cores you are running a load of 1 or 2 could be absolutely nothing in the grand scheme of things.

Personally, the most important metric for us is average query response time.

Additionally as others have stated where you can use memcache or some other key/value store you should be working on implementing that.

The spike to 10+ sounds like it might be io blocking rather than CPU (which would be stuff like queries). If you look at your time wait (%wa) in top, and the output of iostat, you should be able to get an idea. If you're on VPS systems with older SATA drives that could be the bottle neck. If the Kernel is waiting for the disk(s) to be able to write more data to the storage bus/buffers it will block the storage process which will result in blocking for any application trying to access storage. As more and more processes block they'll go into poll/sleep loops and all those instructions will seem to spike the CPU load and make the CPU look busy even if the CPU is just sitting there saying "storage is still busy" over and over again. You probably have 1.00 to 2.00 true CPU load (queries, etc) which isn't that bad. But, you generally want to keep your CPU load below 1.00/per cpu. Otherwise, there is CPU level blocking. If you were swapping a lot (which you can also tell from top) that would aggravate and storage subsystem overloading. I would just post the output from top and iostat during one of the spikes here and see what people say rather than hiring an expensive consultant. You could probably find an experienced sysadmin to look at it as a favor, as well. They should be able to tell you quickly if you really need someone to look at your MySQL and app code to address this. Or, if you just need to add more nodes to your MySQL cluster (or look at something else like a NoSQL type setup).

We've suspected that our problem is mainly of I/O, very close to what you've described above.

>I would just post the output from top and iostat during one of the spikes here and see what people say rather than hiring an expensive consultant.

Thanks for the suggestion. Will try and do just that soon.

Reply on news.ycombinator.com