How high can you go?

What’s the highest load average you’ve seen on a machine?

Our production databases are running on boxes with two dual core cpus, so we usually have load average of around 3. When someone is doing heavy processing on a machine, the load average goes up to 10 by the time one of the DBAs intervenes and stops the madness. When we had a huge problem tuning the memory for client processes and the machine was swapping like there is no tomorrow, we had a load average of 25 and that was the highest I’ve seen a machine go. Needless to say, at that moment response times were less than inspiring.

You can imagine my surprise when I got a phone call one Sunday morning from the application owner who said “Looks like one of our DBs has load average of 500. It could be a problem with the monitor, but can you take a look?”

Sure I can take a look, I’ve nothing better to do on Sunday mornings.

I took a look, and saw the following:
[oracle@productiondb]# uptime
11:15:58 up 162 days, 15:58, 2 users, load average: 546.06, 538.21, 522.55

Not a monitor bug then. But the machine seemed surprisingly responsive for the extravagant load average. I connected to the DB with no delays, ran few queries which returned within a reasonable time. Everything seemed fine except for the high load average numbers.

And the numbers were going up. 30 minutes later we were well over 600.

I tried running “top” – which promptly hanged; “ps” hangs too; “df” works and now I know it is not a disk space problem nor an NFS issue.

I peaked in the “/proc” directory and saw that many of the stuck processes are actually “ps” and “bash”. So the hanging ps is probably causing the high load average, not a result of the load average. We have 500 ps processes hanging there, which inflates the load average while letting the database function as usual. More and more ps processes are accumulating because we use ps in most of our automated monitors. Vmstat shows 50% iowait, but thats obviously related to the stuck processes because the DB shows no more than the usual io waits. So ps hangs while waiting for io? Interesting.

Why does ps hangs? Strace reveals the problem:
stat("/proc/538", {st_mode=S_IFDIR|0555, st_size=0, ...}) = 0
open("/proc/538/stat", O_RDONLY)= 6
read(6,<unfinished ...>

So ps gets stuck on process 538. I wonder what’s this process is doing. Of course, I can’t use ps, so lets go straight into the proc directory. Except that cd /proc/538 got stuck too. No way to know what it is then. Can I kill it? kill -9 538 doesn’t hang, but doesn’t do anything either. Only a reboot will clear it.

Now is the interesting part: Should we reboot a perfectly working database just because the OS is doing something interesting? Time for management decision. I escalate to my manager, who escalates to his, who calls our system administrator and the application owner.

Meanwhile the system administrator reveals more interesting information: Few hours earlier the DB moved to a new Netapp cluster. Is this the cause of the strange behavior? How can a move to a new storage can corrupt the process memory blocks so throughly? Well, Redhat will need to answer this question.

3 hours later I get the decision – reboot the server. We took a netdump of the machine and restarted it. 5 minutes late the DB is up and running again with the normal load average and ps works.

We sent the netdump to Redhat. It is now almost a month since the incident and there is still no word on what happened to process 538 and whether we can blame the new Netapp.

5 Unix tools that DBAs don’t use enough

Oracle runs on an operating system, very often this operating is a variant of Unix. I’m pointing out the obvious because I’ve worked with many excellent DBAs who were not aware of this fact. They were masters of using everything that Oracle provided, usually in the form of V$ and even X$ views, but completely ignored the fact that Oracle runs on Unix, and Unix can also supply lots of information that can be used to monitor, diagnose issues or automate tasks.

Here are few of my favorites, at no particular order:

1) sar and vmstat – reporting current and historical system informatio, including CPU utilization, IO, memory utilization, time spent looking for memory, load average, etc. I begin almost every performance diagnosis session by checking what these tools can tell me. They never tell the whole story, but they give good hints.

2) strace – This gives a system-call level trace of what a process is doing. You usually want to use it when a process hangs and you want to know exactly what it was doing when it hanged. The output looks like this:
open("/proc/25062/environ", O_RDONLY) = -1 EACCES (Permission denied)
stat("/proc/27800", {st_mode=S_IFDIR|0555, st_size=0, ...}) = 0
open("/proc/27800/stat", O_RDONLY) = 6
read(6, "27800 (pickup) S 1534 1534 1534 "..., 1023) = 224
close(6)

Think twice before running this on a busy production system, as this baby will slow the traced process to a crawl and generate tons of IO.

3) gdb -c – run this when you find a core file in an Oracle directory and you want to know who crashed.

4) find – I’ll need a separate post about this wonderful tool. My favorite usage example is for deleting old user traces that usually just take up space. Lets delete those that are over two weeks old:
find /orahome/ora6410g/admin/ITGST/udump/ -name "*.trc" -mtime +14 -exec rm {} \;
This is just an example, find can do much more useful tricks. Its one of those utilities I’ll take when I have to work as a DBA on a deserted island.

5) Perl – Thats the other tool to take to a deserted island. Its a bit large to be called a tool, its an operating system with programing language thrown in. I use it for everything – our backup procedure is written in perl, we automatically generate reports for our customers from perl, disk space usage projections, etc. I’d write a post about this too, but it’ll probably grow to be a book.

60 seconds about ps

If you use Unix, you’ve probably seen ps once or twice. Its one of the most useful tools in Unix. I use it to check if Oracle is up or not (by looking for smon process), check if a process I killed is dead or not, look for the parent of a process that is taking too much CPU, etc, etc.

I always knew that this tool has more options that just the good old ps -ef, but only today I found the time to really read the man page and see what they do. I found some cool stuff I want to share.

First, the selectors – you can ask ps to show just the processes that belong to a specific user (say, oracle) by running “ps -u oracle” will show all processes that belong to oracle.

There is also an option to select by command name, but it doesn’t work as expected, the command name is not what ps itself will show in the CMD column. So ps -fC oracle will show ora_smon_SID while ps -fC ora_smon_SID will show nothing.

Then, you can ask ps to show you certain columns:
ps -eo pid,ppid,pcpu,pmem,etime,time,comm,args will show you the process id, parent process if, cpu utilization, memory utilization, elapsed time, cpu time, command line and arguments.

And the last cool feature is sorting: you can sort by start time, user time, system time, page fault, user id and other useful parameters.
ps -ef –sort utime will show all the processes sorted by user time.

And combining everything I learned today, I can run:
ps -C oracle –sort cutime -o pid,ppid,pcpu,pmem,etime,time,comm,args
and find out that the oracle process that is taking most user time is ora_lms0_SID, but thats probably because I’m checking this on a rather unloaded system.

Key information is missing from the ps man page – it does not contain the list of parameters that –sort and -o (list of columns) will take. I found the lists here: http://www.ss64.com/bash/ps_keywords.html but I have to assume they exist on some man page, otherwise it is huge neglect by Unix documentation.