I have a webserver hosting about 300 websites and sometimes my CPU usage gets close to 100% and when I use "top" in my CENTOS to check who is using all CPU, I discover it's mysql. I would like then to know what website is causing that high mysql CPU usage. With Apache I can know exactly which virtual domain is consuming my cpu, ram, bandwidth... but I have no idea how to do that with mysql. Any idea?
You could check your slow query log. If it's CPU intensive, it may be some heavy hitting queries that take a while to process. Check to make sure your slow query threshold is set to a reasonable amount of time and then look at your log.
When your server is in the middle of a CPU spike, log in and do
show full processlist to look at the queries that are running at that moment. Is there a higher number of queries than normal? Is there a high number of slow queries than normal?
You could use
show open tables which will show you what's in the table cache, but it doesn't necessarily tell you what is using up all the resources, but rather which databases/tables are the most active.