For ongoing monitoring you could consider installing munin. It will gather information every 5 minutes and generates graphs that will allow you to see where the bottlenecks are. I also use sar which can be run in background mode gathering data to disk. This gives quite detailed infomation on what the bottleneck is. To what processes where running in the past you will need the process accounting package.
You should check out why MySQL is so loaded, if you already have 2 or 4 cores that's probably not your problem. A good, cleverly optimized db system will be able to use much more ram before maxing out CPU time. Some things to look at before thinking about hardware upgrades are:
- table indexes
- slow queries
- tmp tables settings
- (edited) OPTIMIZE TABLE
- (edited) database fragmentation (see marcoc's answer)
- enough I/O capacity on the system
I am just guessing, but it seems that your system is spending too much time on the db itself (not much in I/O and not at all swapping or the like), so you probably should check out your indexes... maybe the db is just busy scanning tables because it hasn't the proper indexes to use for the queries you run (if that's the case, adding hardware won't help)
After that, if you still need more horsepower, I'd add more ram to the system, maybe up to 4GiB per core (ie, 16GiB if you run 4 cores), but that really depends on your dataset size and access patterns (mostly read, mostly write, etc...)
Best Answer
The "top" utility is in the "procps" package on RHEL/CentOS systems.