Mysql high cpu usage problem
rwatson at freebsd.org
Sat Nov 13 02:45:35 PST 2004
On Thu, 28 Oct 2004, Omer Faruk Sen wrote:
> I have a system with apache+php+mysql. The system was fine for a long
> time but my problem started a few weeks ago.
What version of FreeBSD are you running? MySQL on multiple processors
with relatively small transactions that hit the cache typically becomes a
benchmark of kernel IPC performance. A high percentage of kernel time
suggests that this is the case in your environment. There are a couple of
places the kenrel could be burning that:
- Performing copies of data provided by user space; this is a hard to
avoid cost for stream based transaction data with MySQL, as zero copy
tricks don't help much.
- Lock contention in kernel -- time spent spinning waiting for a mutex to
be released. In 4.x, this is a pretty immutable cost, and has to do
with running the entire kernel under a Giant lock. In 5.x, this is
improving but still in progress.
- Some other unidentified activity.
Knowing which version of FreeBSD will help us make recommendations about
how to improve performance. With FreeBSD 5.x, you might find "systat
-vmstat 1" and "top -S" useful in determining where kernel time is being
spent. In particular, if you see large amounts of time spent in the
netisr thread, ithreads, or just in normal user threads. You might find
that enabling or disabling adaptive mutexes (options NO_ADAPTIVE_MUTEXES,
and/or removing ADAPTIVE_GIANT) might change the behavior of the system.
In my experience, with Giant off the network stack in 5.3, adaptive
mutexes still help, but less than with Giant on the stack.
Depending on your workload, HTT may help or hurt. I've generally found
that for MySQL, it hurts somewhat, but you have a blend of work above that
isn't pure MySQL. Regardless, it's probably an interesting variable to
explore net throughput of the system.
Robert N M Watson FreeBSD Core Team, TrustedBSD Projects
robert at fledge.watson.org Principal Research Scientist, McAfee Research
More information about the freebsd-performance