Kevin Closson's Blog: Platforms, Databases and Storage

Now that’s cool. I see the header photo over at the Mark Rittman Oracle Blog is a photo of that cool impromptu “Oaktable” they put up for us at UKOUG. That was a great conference.

Oh, by the way, Mark Rittman has a really good blog.

A Quick Announcement About NAS

If you, or anyone in your datacenter, is interested in Scalable NAS, this enterprisestorageforum.com article may be of interest. Additionally, if you are interested you can sign up here for a web demonstration of Scalable NAS. Note, the same sign up sheet will allow you to sign up for web demonstrations of PolyServe’s Database Utility for Oracle as well.

Note, clustered storage is really catching on and I think it should be of interest to any forward-looking DBA, Oracle IT Architect, Storage Administrator or Unstructured Data Administrator.

It is possible that NAS will be/should be a part of your Oracle deployement at some point.

Dave Moore discusses the relatively new set of OS performance data collections scripts on his webpage here.  Dave writes:

OS Watcher is a utility provided on MetaLink (Note 301137.1) primarily for support issues in a RAC environment. I must admit I was captivated by the name and wondered if I could use this tool instead of expensive 3rd party products for monitoring key operating system metrics. The verdict is “no” and I was less than impressed.

OS Watcher is a series of shell scripts that run on AIX, Solaris, HP-UX and Linux. Simple commands such as ps, top, vmstat, netstat and others are executed at regular intervals and their output is appended to a file in a directory specific to that command.

I have not personally taken the time to play with these scripts (I have my own), but I can read the tea leaves. Oracle support will most likely start asking for this data for any problem you might be having (regardless of whether you have a performance related problem). It might be smart to start collecting this data so you don’t hear something like, “Please reproduce the problem after installing OSW.” Just a thought.

I’ll see if I can arrange a test of how heavy the collection of this data is and blog on what I find. I read through the scripts and it looks like some pretty heavy collection. I never liked performance monitoring tools that carry a heavy “tare weight”. Did any of you use CA Unicenter in the old days?

If you have a Metalink account the toolkit is available here.

Fun Weekend Ahead
Our cool lab manager has reconfigured the DDN storage allocated to my cluster of DL585s so that I get 65 spindles per LUN. This particular cluster had been hobbled with LUNs derived from only 16 spindles. Looks like I get to do some reconfiguration of RAC on the cluster this weekend. The good thing is that the cluster also has a Texas Memory Systems Solid State Disk (Another PolyServe Partner) configured for Redo logging. I should be able to get some good performance and stability readings.

This cluster always has ASM and PolyServe CFS set up side-by-side. Makes for good fun.

Learning Something New
Learning Oracle is a never ending effort. “OK, tell me something I didn’t know”, you say? You may know this bit I’m about to blog about, but I sure didn’t. I don’t know when the Database Writer I/O profile changed, but it has—somewhere along the way.

I have a simple test of 10gR2 (10.2.0.1) on RHEL4 x86_64 using filesystem files. I was using strace(1) on the single DBWR process I have configured for this particular instance. The database uses an 8KB block size and there are no variable block sizes anywhere (pools are not even configured). The workload I’m running while monitoring DBWR is quite simple. I have a tablespace called TEST created using Oracle Managed Files (OMF)—thus the peculiar filenames you’ll see in the screen shots below. I have 2 tables in the TEST tablespace and am simply looping INSERT INTO SELECT * FROM statements between the 2 tables as the stimulus to get DBWR busy.

In the following screen shot you’ll see that I took a look at the file descriptors DBWR is using by listing a few of them out in /proc//fd. The interesting file descriptors for this topic are:

• FD 18 – System Tablespace
• FD 19 – UNDO Tablespace
• FD 20 – SYSAUX Tablespace
• FD 23 – The TEST Tablespace

NOTE: Please right click the image to open in a viewer. Some of you readers have reported a problem, but we’ve found that it is as simple as clicking it. I need to investigate  if that is something that wordpress is doing.

I have purposefully set up this test to not use libaio, thus filesystemio_options was not set in the parameter file. In the next screen shot I use grep to pull all occurrences of the pwrite(1) system calls that DBWR is making that are not 8KB in size. Historically there should be none since DBWR’s job is to clean scattered SGA buffers by writing single blocks to random file offsets. That has always been DBWR’s lot in life.

So, as strace(1) is showing, these days DBWR is exhibiting a variation of its traditional I/O profile. In this synchronous I/O case, on this port of Oracle, DBWR is performing synchronous multi-block writes to sequential blocks on disk! That may seem like trivial pursuit, but it really isn’t. First, where are the buffers? The pwrite system call does not flush scattered buffers as do such routines as writev(),lio_listio() or odm_io()—it is not a gathered write. So if DBWR’s job is to build write batches by walking LRUs and setting up write-lists by LRU age, how is it magically finding SGA buffers that are adjacent in memory and bound for sequential offsets in the same file? Where is the Twilight Zone soundtrack when you need it? For DBWR to issue these pwrite() system calls requires the buffers to be contiguous in memory.

Of course DBWR also behaved in a more “predictable” manner during this test as well as the following screen shot shows:

Is This A Big Problem?
No, I don’t think so—unless you’re like me and have had DBWR’s I/O profile cast in stone dating back to Version 6 of Oracle. All this means is that when you are counting DBWR write calls, you can’t presume they are always single block. Now that is something new.

Porting
I often point out that Microsoft has always had it quite easy with SQL Server. I’m not doing that pitiful Microsoft bashing that I despise so much, but think about it. SQL Server started with a fully functional version of Sybase (I was a Sybase fan) and brought it to market on 1 Operating System and only 1 platform (x86). Since then their “porting” effort as “exploded” into x86, x86_64 and IA64. That is the simple life.

This DBWR “issue” may only be relevant to this Oracle revision and this specific port. I can say with great certainty that if Sequent were still alive, the Sequent port would have used one of the gathered write calls we had at our disposal in this particular case. With Oracle, so much of the runtime is determined by porting decisions. So the $64,000 dollar question is, why isn’t DBWR just using writev(1) which would nicely clear all those SGA buffers from their memory-scattered locations?

Things That Make You Go Hmmmmm

I’ll try not to make a habit of referencing a comment on my blog as subject matter for a new post, but this one is worth it. One of my blog readers posted this comment. The question posed was what performance effect there would be with DSS or OLTP with the Sun CoolThreads architecture—given it has a single FPU shared by 8 cores. The comment was:

I have heard though that the CoolThread processors are not always great at supporting databases because they only have a single floating point processor? Would you see this as a problem in either a OLTP or DSS environment that don’t have any requirement for calculations that may involve floating points?

Since this is an Oracle blog, I’ll address this with an Oracle-oriented answer. I think most of you know how much I dislike red herring marketing techniques, so I’ll point out that there has been a good deal of web- FUD about the fact that the 8-core packaging of the CoolThreads architecture all share a single floating point unit (FPU). Who cares?

Oracle and Floating Point
The core Oracle kernel does not, by and large, use floating point operations. There are some floating point ops in layers that don’t execute at high frequency and therefore are not of interest. Let’s stick to the things that happen thousands or tens of thousands of times per second (e.g., buffer gets, latching, etc, etc).  And, yes there are a couple of new 10g native float datatypes (e.g., BINARY_FLOAT, BINARY_DOUBLE), but how arithmetic operations are performed on these are a porting decision.  That is, the team that ports Oracle to a given architecture must choose whether the handling of data types is done with floating point operations or not. Oracle documentation on the matter states:

The BINARY_FLOAT and BINARY_DOUBLE types can use native hardware arithmetic instructions…

Having a background in port-level engineering of Oracle, I’ll point out that the word “can” in this context is very important. I have a query out about whether the Solaris ports do indeed do this, but what is the real impact either way?

At first glance one expect that an operation like select sum(amt_sold) to benefit significantly if the amt_sold column was defined as a BINARY_FLOAT or BINARY_DOUBLE, but that is just not so. Oracle documentation is right to point out that machine floating point types are, uh, not the best option for financial data. The documentation reads further:

These types do not always represent fractional values precisely, and handle rounding differently than the NUMBER types. These types are less suitable for financial code where accuracy is critical.

So those folks out there that are trying to market against CoolThreads based largely on its lack of good FPU support can forget the angle of poor database performance. It is a red herring. Well, ok, maybe there is an application out there that is not financial and would like to benefit from the fact that a BINARY_FLOAT is 4 bytes of storage whereas a NUMBER is 21 bytes. But there again I would have to see real numbers from a real test to believe there is any benefit. Why? Remember that accesses to a row with a BINARY_FLOAT column is prefaced with quite a bit of  SGA code that is entirely integer. Not to mention the fact that it is unlikely a table would only have that column in it. All the other adjacent columns add overhead in the caching and fetching of this nice, new small BINARY_FLOAT column. All the layers of code to parse the query, to construct the plan, to allocate heaps and so on are mostly integer operations. Then to access each row piece in each block is laden with cache gets/misses (logical I/O) and necessary physical I/O. For each potential hardware FPU operating on a BINARY_FLOAT column there are orders of magnitude more integer operations.

All that “theory” aside, it is entirely possible to actually measure before we mangle as goes the cliché. Once again, thanks to my old friend Glenn Fawcett for a pointer to a toolkit for measuring floating point operations.

Why the Passion?
I remember the FUD marketing that competitors tried to use against Sequent when the infamous Pentium FDIV bug was found. That bug had no affect on the Sequent port of Oracle. It seems that was a subtle fact that the marketing personnel working for our competitors missed because they went wild with it. See, at the time Sequent was an Oracle server power house with systems based deep at the core with Intel processors (envision a 9 square foot board loaded with ASICS, PALs and other goodies with a little Pentium dot in the middle). Sequent was the development platform for Unix Oracle Parallel Server and Intra-node Parallel Query. Oracle ran their entire datacenter on Sequent Symmetry systems at the time (picture 100+ refrigerator sized chassis lined up in rows at Redwood Shores) and Oracle Server Technologies ran their nightly regression testing against Sequent Symmetry systems as well. Boring, I know. But I was in Oracle Advanced Engineering at the time and I didn’t appreciate the FUD marketing that our competitors (whose systems were RISC based) tried to play up with the supposed impact of that bug on Oracle performance on Sequent gear. I do not like FUD marketing. If you are a regular reader of my blog I bet you know what other current FUD marketing I particularly dislike.

More to the point of CoolThreads, I’ve seen web content from companies using what I consider to be red herring marketing against the SunFire T[12]000 family of servers. I am probably one of the biggest proponents of fair play out there and suggesting CoolThreads technology is not fit for Oracle due to poor FPU support is just not right. Now, does that mean I’d choose a SunFire over an industry standard server? Well, that would be another blog entry.