We have a DB server which is constantly utilised above 95% above.
This is becoming nuisance when the monitoring team frequently calls to check on it. Frankly I do not know what to tweak or even interpret the outputs.
I noticed constant 30 to 60% in wio column of the cpu utilisation.
There are several disks showing 70 - 90% busy. I have attached the system activity output.
It seems the disks are busy, so what should I do? These are virtual disks presented from EVA8000 storage. Users at times complain of slow performance.
How to about troubleshooting this problem?
It seems like, besides buying more CPUs, you might need to trace the more popular queries to see if they are all triggering index plans. Doing it wrong eats up CPU and makes the disks busy, too -- table scans, page splitting, or just backwards plans. If you can, check for excessive lock waits. It may seem primitive, but many waits are released, in the end, by polling resources, which can be costly.
Also, see which processes are the biggest CPU hogs. Compare a ps -efx across a minute. Check system stats, to see if some executables are exec'd excessively. If a process is called a lot but runs an instant, you have all that startup and shutdown cost but it will never show on ps.
Make sure maintenance and overhead processes are not running too often. Conversely, some RDBMS optimizers need to be run, so tables and indexes are not excessively fragmented, and planning statistics are accurate.
Your cpu and disk are highly loaded, so it does not take much to start a backlog, and then it takes long for it to dissipate. Maybe there are overhead processes you can add scripting to, to make them hold off until loading is low. Are there batch and maintenance processes that can run from a remote host, perhaps slower but acceptably and with lower local overhead?