OS bottle necksPosted: June 17, 2007
I’m reading “Oracle Insights” these days, and I simply can’t recommend it enough. The book is a collection of articles written by very smart and experienced DBAs, the articles range over many different topics, but most center around issues with performance and tuning. Every article contains countless insights and ideas, most of them are so practical you can try them at work the next day. But the best thing is that the book is so well written, so readable and so fun that like any good book you can barely put it down, you will find yourself laughing out loud and crying over broken systems. Its really nothing like any Oracle book you ever read.
Gaja Vaidyanatha wrote an article called Compulsive Tuning Disorder about the right and wrong way to tune system, and inside this article was a little gem about identifying OS bottlenecks. For me, this short section alone was worth the price of the book.
Vaidyanatha advocates using two tools for collecting metrics – sar and vmstat, and to look at just two metrics – CPU run queue and memory scan rate. He claims that these are the only two metrics that are important for database tuning – how many processes are waiting for CPU at a given time, and how long the memory manager takes before finding more memory. He then goes on a bit about the exact meaning of these metrics, what the ideal values are and a bit on the important insights you get from the CPU utilization breakdown that sar gives (For example, don’t add processing power if you have lots of iowait).
So simple, and it makes total sense. My life has been changed and improved after reading about 2 pages. Tomorrow I’ll add monitors to track these metrics on our production servers and see if it is as good in practice as it is in theory. I just love finding simple reasonable ideas that are written so well.