Mysql high cpu usage problem

Robert Watson 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 mailing list