Measuring Performance – The Problem April 30, 2008

After I published “Yet Another Nice Myth”, my friend MosheZ sent me an angry email:
” STOP looking at top. You need actual application data if you want to validate application responsiveness.”

And as always, he is absolutely right. My managers fell for one of the oldest operations management errors – Confusing what can be easily measure with what they actually need to know.

They wanted to know if our users are unhappy with the application response times, what they could easily see was server CPU, and to simplify their lives, they decided that “high cpu = unhappy users”. Which is true often enough to make this simplification tempting, but not often enough to make this a serious operational strategy.

Measuring application performance is incredibly complex. Applications are complex, performance is difficult to define and even more difficult to define is “good performance”. I’ve been working in the field of application performance for 8 years now.  I spent the first two years of my career developing application monitoring tools, and another 6 years using them – first as operations manager and now as a DBA, and I can easily claim that this is still an open problem. Tons of tools, each and every one of them is doing it wrong.

Two problems I see with the tools I’ve looked at (besides managers taking cpu averages too seriously):

1.  Narrow view: Network monitors show you the network. System monitors show you cpu, io and memory. DB Monitors show you how the database wastes its time. End User Monitors should you slow web pages.  So you end up with at least 10 different tools (How many do you have in your IT organization? Do you even know?), and whenever one of them indicates something unusual, you have to juggle and correlate all of them to get a reasonably accurate idea of what is going on. Usually they show conflicting information that you never manage to fully correlate and explain. In any case, tons of intuition and guess work is involved.

Those different tools will be owned by different teams. So you are not correlating network monitors, storage monitors, db monitors and application monitors. You are correlating your understanding of the situation as you see it from db monitors with what you understand from the network manager who is trying to understand his network monitors simultaneously. Throw in the storage manager, application owners, angry customers, flustered managers and few consultants, and you no longer have rational correlation of facts, you have politics and finger-pointing.

Uncorrelated data means that understanding scope of issues is very difficult. DB server has 98% cpu, does it cause an application problem? if so, where? which users are affected? Or maybe application monitoring tool shows slow login – is it caused by web server? DB? LDAP? Maybe its the load balancer? or a switch? are there other applications affected by same issue? Is it a reoccurring issue?

2. Too much data, too little meaning: Often a problem of application layer monitors. Actually, this is not exactly a tool problem – application owners usually insist on having incredible (=absurd) amounts of data about their application performance and having no idea what to do with it later.

Here the tools diverge into two kinds, virtual users and real users. Each one of these tools has its own way of generating too much data.

Virtual User based tools require an application expert that will write a script to emulate a user visiting his website, performing various common operations. The you run this script from various locations and ISPs, usually every 5-15 minutes, the tool will collect information regarding response times for each file accessed by the script, often broken down to various network components (server time, network time, ssl, dns, etc).
Take all this data, multiply by 30 locations, running 12 times an hour, 24×7, and of course you want to keep this for something like 18 month, right? These tools also have the additional issue that you may be monitoring the wrong thing, or having a badly written script completely skew your results.

Real User tools will sit at your data center and capture pretty much the same information, but about the real users that are actually visiting your site. If your site is worth monitoring, this will generate much more information that Virtual Users tools (because you should have more real users than what monitoring will generate), this information will also be more “real” by definition. It will be more difficult to analyze, because virtual users lend themselves to nice time-series type analysis. But lets face it, no one does time-series on monitors. We have averages, and on good days – percentiles.

Now you got 50G of monitoring data. Do you really need all this? Do you know what to do with it? Is it meaningful to you? Thursday night, 5am GMT you see performance downgrade for users (real or virtual) accessing your search page from Japan. What do you do?

Do you have meaningful trends? Can your tool show you the slow-down that occurs 10 minutes after the hour, every hour? Can it show you that it is getting worth? Do you know if it is something you should worry about and investigate?  Application owners rarely have the ability to mine their own data. Do you have a tool that does it for you?

I’ve seen operators get alerts about performance issue every day at 3pm, it lasted for 10 minutes so it was dismissed at “momentary anomaly”, and not noticing that it happened same time every day for over a month, starting right after a new patchset was installed. It is easy to drown in too much data, especially data that you don’t immediately know how to process. After all, no one knows how to debug performance issues that last for 10 minutes. You need to know that it is repeating the same way every day in order to debug it.

If you have a performance monitoring tool that you are truly happy with, please comment and let us know! In general, I’m very interested in how you do your monitoring.

My friend Moshe works for BHive, a company that happened to develop a new monitor tool that promise to take some of the pain (and irrelevant data) out of the equation. He arranged for me a demo of their solution, and while it is still very very new and incomplete, it does take monitoring in an interesting direction. I’ll write a seperate post about that.

****

Exciting posts appeared on my RSS Reader yesterday:

Dom Brooks at OraStory upgraded production server to 11g and lived to tell the tale. Its the first production upgrade I’ve heard of and apparently it went well. Good news!

Greg Rahn at Structured Data wrote about data warehouse scalability.  This post is so good that I have no superlatives. Just go read it. Even if you don’t have data warehouse. Its a must.

Splitting Comma Seperated String to Rows April 15, 2008

Got the following email from one of my users:

I am trying to write a SQL select to get comma delimited values from one field in rows.

For example I have a Table T1
create table t1(a  varchar2(30),
b  varchar2(30));

insert into t1 values ('A','27.68%,2.78%,69.55%');

SELECT A, B FROM T1;
A       B
=====  =====
A  27.68%,2.78%,69.55%

Can you help me write a SQL which will give the following output?

A        PERCENT
======  ========
A   27.68%
A    2.78%
A   69.55%

My solution is rather ugly, but it works (on 10g and above) and makes no assumptions about the number of elements in the list :

With t2 as (select A,','||B||',' as B from t1)
SELECT A,SUBSTR(B,INSTR(B,',',1,LEVEL)+1,INSTR(B,',',1,LEVEL+1)-INSTR(B,',',1,LEVEL)-1)
FROM t2
CONNECT BY LEVEL <= length(regexp_replace('[^\,]',''))-2 ;

Senior DBA had a simpler solution: “Just replace all the commas with new lines”.

Walk to Work – California Style April 15, 2008

Now that the weather is nice again, I decided to save a bit on gasoline and walk to work.
Here is a map of my daily commute (slightly simplified):

My house is point A and my workplace is at point G.
It doesn’t take much knowledge of geometry or parity to realize that I have two completely unnecessary road crossings in my route. These road crossings are in fact completely unnecessary, and were not added to the route simply because I want to spend more time waiting for traffic lights to change.
There is no pedestrian crossing between points B and E, and there is no sidewalk between points D and G. So I either take the scenic route or I jaywalk on a very busy street during commute hour.

Chicken and egg problem: Americans never walk because their streets are so incompetently designed, or perhaps street design is incompetent because people who never walk don’t notice how annoying this is?

***
And for those of you who expected Oracle related content, I’ll send you to an excellent and thought provoking article, uploaded by Daniel Fink – the OptimalDBA.

Yet Another Nice Myth April 9, 2008

A while ago I explained Nice, what it can do and what it can’t. From this point, whenever someone misunderstood nice, I just pointed at my post.

But yesterday I ran into a misunderstanding that was not covered in the first post:

Boss: Server X is at 100% cpu. Can you renice process 4096?
Me: Sure thing boss. *clickety-click* Done
Boss: Why is cpu still at 100%?

Renice will not lower CPU utilization because:
1. It does not kill processes. After renicing the process will have lower priority, but it will still need the same amount of CPU it did before, and so do all the other processes in the system. At any given moment, our nice process will either wait for CPU or use CPU. Either way, we are still at 100% CPU.
2. You can’t lower the priority to a point that the CPU will prefer to sit idle instead of running that process. Which is exactly what you ask for if you expect renice to affect CPU utilization.

The Linux OS will not let the CPU run on IDLE if someone wants it. It makes absolutely no sense to make processes wait while the CPU is not doing anything.

Boss: Sysadmin types always say that 100% CPU is not necessarily a bad thing, but we are DBAs and we know that if a machine is at 100% CPU it means trouble.

I hope we can all agree that what we want to prevent is processes having to wait for CPU. Ideally, every process that needs CPU will get it immediately. In this sense, 100% CPU is indeed a bad thing, because either processes are already waiting for CPU (look at load average!) or it can happen any second. But it is not because 100% cpu is the problem, it is because waiting processes are the problem.

Suppose you have a classic OLTP system – lots of tiny, fast, transactions. Usually the system runs at around 60% CPU on its single CPU server, but this morning someone ran a huge cpu-intensive report on this system. You know that this report runs for 60 minutes on an empty system. He begs you not to kill the process, but he doesn’t mind waiting. Now you have two options:

1. Let this process run as usual. Since it is a large report, it could be scheduled for more than its fair share of CPU. Taking maybe 80% of cpu time, leaving 20% for our tiny transactions. Process will finish in 75 minutes. On average each tiny transaction will take three times longer than usual. 75 of 100% cpu, bad performance and complaints.
2. Renice the process. Now it will get 40% of CPU time. Tiny transactions get 60% (which is all they need). Report takes 150 minutes, tiny transactions take as usual. 150 minutes of 100% cpu, no performance issues.

Obviously the second scenario is much better. Even if you spend twice as much time in 100% CPU. So maybe 100% cpu is not always evil, and renice is useful even when it doesn’t lower the cpu utilization.

Boss: But look at Top! Things look terrible! How do we know that everything is OK?

It is important to remember that viewing the CPU world through “top” tends to skew our perspective a bit. Top gives CPU utilization aggregated according to its refresh rate – 3 seconds by default. Now during these 3 seconds, 1.2 seconds were used by huge report (40%!) and 2.8 seconds used by 20 processes pooling all those tiny 2 ms transactions. Each process will show close to 0.05% CPU. It looks scary – big bad report taking so much CPU leaving almost nothing for other processes. But each time a transaction wanted to run it got 100% of the CPU. It just finished very very fast, and the big report stayed.

How do we REALLY know that everything is OK?
I trust Oracle on this and go to Active Session History tables (or EM report), find the transaction processing sessions, check out the wait events and make sure CPU is reasonable.

Boss: But what do we do about CPU?

So, 100% CPU is not good, but we can’t use renice to solve this. What can we do?
Killing the offending process is a crude but effective solution. Rumor says SQL Profiles that will allow you to alter the execution plan of the query, maybe making it easier on the CPU. I never tried it. Of course, the ultimate solution is to optimize the report to use less CPU, and in my experience it is easier to convince developers to optimize after you killed the report several times. In worst case, buying more CPU power is always an option.

Obfuscated SQL April 5, 2008

I’m a closet C programmer, and an avid follower of the Obfuscated C Contest . SQL is an interesting and diverse language. No reason why database professionals can’t have their own obfuscated code.

So I shared my idea. And the renowned community organizer Eddie Awad made it an official contest! And the wonderful SQL gurus all contributed the scariest queries you’ve ever seen, and now it is time to vote!.

Rob van Wijk deserves a special reward for having the first entry – less than a day after the contest was announced! And as Paul Gallagher noted, it is pretty enough to be on a t-shirt. I think that Rob’s entry set the tone for the rest of the competition – XML, regexps, chr, ascii, connect by and model became prerequisites for any self respecting  obfuscated query.  Rob also included  use of pivot,multiset, messages in the code (‘I LOVE SQL’) and silly math tricks that I enjoyed much (where ln(34e5)=exp(ascii(‘@’)-2e2) is so much nicer than where 1=0 ). I was a bit disappointed that I managed to decipher most of the query and its tricks in an hour or two. Next year we expect worse

Shoblock thought that OTN is full of obfuscated queries and stepped up with an example. I beg to differ. Not any plain old horrible query deserves to be an obfuscated entry. A true obfuscated query must be horrible and cleverly impressive at the same time. It should make you say “wow” as well as “yuck” and hopefully help you learn something other than how not to write code while you read it.

Laurent Schneider raised the bets by submitting a query that runs on 10.1.0.5 only and contains no white spaces. It deserves a reward for most tricks with fewest characters and also for scariest query. I would not want to meet this monster in my source control on a dark night. I cheated a bit and got an in-depth tour of the query from the master himself. I hope Laurent will blog his explanation, but if he won’t I’ll do it. For now, I’ll point out the use of double connect by, dburitype and stats_mode that make the query unique. I’ll also mention that the obscure version was chosen not just to annoy me. 10.1 is the only version where one can use the model clause and still have group by sort the results of the query.

Frank Zhou gets the award for the most useful query. While the other entries only print nice messages, Zhou’s entry solves four different problems! As far as pure obfuscation goes, I think this entry is less impressive than others. Of course, the good and clear formating also helped in making this just too readable to be obfuscated.

Volder’s entry arrived just in time before the deadline, and it is both pretty and clever.  I think it deserves the overall winner award. The query contains all the required tricks and then adds new ones. It uses comments for prettier formatting, it uses XQuery, it uses subquery factoring and nested groups in regular expression. I’m still working on understanding all of it, but I’m sure in few hours I’ll get it and be a better person for it (if the headache won’t get me first). Volder, any chance you’ll be finishing the layout so we can see the turtle? The formatting is still great even without the turtle. The use of lots of horizontal spaces to separate the sun from the tree makes it very difficult to real.

I just remembered that no prize was mentioned. Any suggestions? A bottle of Stoli Crystal maybe?

XML Alert Log April 4, 2008

By now everyone discovered the new alert log in 11g, XML format and all. For the 11g challenged, I’ll point at the new official way of viewing your alert log: Say hello to ADRCI and bye bye to vi, less, more, tail and grep. Except that I’m not saying bye to vi any time soon, I’m saying “VI, meet XML Alert log. I hope you two will be good friends and get along fine because you’ll be seeing each other a lot in the near future”.

When I tried to look at the XML alert log with an XML viewer, it turned out that the alert log (and listener log) are not XML documents at all. At least not well formed one. Well formed XML document should contain one root element which contains all other elements. Oracle logs contain many MSG elements, all at top level.

I also noticed that Oracle failed to release an xml schema, or any other document describing the format of the alert logs and would allow proper parsing of it (proper – not based on guesses and reverse engineering).

Also, it is interesting to note that each error message element contains many attributes, bloating the alert log to a worrying size. I really could live without knowing that every single message in my alert log was written by org_id=’oracle’. On the other hand, knowing the PID is nice. The fact that all this information is kept as attributes of MSG element and not as child elements should make it easier to keep related messages together when using XML PATH to get information out of the file.