CentOS - Jun 2011 - how to find out what is causing high mysql cpu load

Hi all,

I have a CentOS 5.6 xen domU that is used for hosting several Apache
virtual hosts that use MySQL.

Lately, this domU has been having performance issues and I've noticed
web pages loaded from this server opening slower than usual.

When I run 'top' I see mysqld constantly consuming 10-60% of CPU.

sar reports average of 10-20 for %user, 5 for %system, 0.2 for %iowait
and 10-20 for %steal

When looking at 'xm top' in dom0 (running also CentOS 5.6) most of the
other domUs have CPU(%) near zero and only this domU is reported to
consume CPU more than 10%.

The strange thing is, that when I run 'mytop', I do not see that many
queries: I see avg of 4 users "sleeping" and occasionally some other
user running a query. qps is mostly 0. No slow queries. Key efficiency
is 97%.

Here is my /etc/my.cnf:

max_connections = 300
max_user_connections = 300
wait_timeout = 180

Nothing interesting in /var/log/mysqld.log. I tried rebooting both the
dom0 and the domU which made no difference.

Questions:

1. How to debug further why mysqld is taking so much cpu?

2. If I've understood correctly, the high steal% value reported by sar
means that the hypervisor is giving cpu time for some other domU. How
to find out why and where this cpu time is stolen to?

Regards,
Peter