Re: CPU usage, need some help.
It's best to start with optimizing the queries, since even a machine with 16 cores and 32GB of memory can be brought to halt by poor queries, schema design and index usage.
MySQL has a slow query log that you can enable that will log queries based on some execution time threshold that you configure. In practice however, it usually either captures too many or too few of your queries, but its not a bad place to start looking. Also, the SHOW FULL PROCESSLIST command will show you all the queries which are currently running and how long they've been running.
Once you've identified a query you think might be problematic, you can post to the forums and ask for help tuning it. For example, say you have the following query that you suspect may be running slow:
SELECT id FROM users WHERE login = 'tristan';
To provide people with the most information up front, you should provide results of the following commands:
EXPLAIN SELECT id FROM users WHERE login = 'tristan';
SHOW TABLE STATUS LIKE 'users';
SHOW CREATE TABLE 'users';
SHOW INDEXES FROM 'users';
If you do all of those things for each table in your query, that's enough information to really be able to diagnose what's happening.
Once you have your queries optimized, you can look into server tuning. Just post your my.cnf along with your machine specs (RAM, CPU, etc). Also, it helps if you can describe how you're using the database in terms of thing like how often you're reading or writing, if you're sorting or joining often vs. just doing primary key lookups, etc.
I hope that helps