Kevin Closson's Blog: Platforms, Databases and Storage

I was just taking a look at Curt Monash’s TDWI slide set entitled How to Select an Analytic DBMS when I got to slide 5 and noticed something peculiar. Consider the following quote:

Transistors/chip:  >100,000 since 1971

Disk density: >100,000,000 since 1956

Disk speed: 12.5 since 1956

Disk Speed == Rotational Speed?
The slide was offering a comparison of “disk speed” from 1956 and CPU transistor count from 1971 to the present. I accept the notion that processors have outpaced disk capabilities in that time period-no doubt! However, I think there is too much emphasis placed on disk rotational speed and not enough emphasis on the 100 million-fold increase in density. The topic at hand is DW/BI and I don’t think as much attention should be given to rotational delay. I’m not trying to read into Curt’s message here because I wasn’t in the presentation, but it sparks food for thought. Are disks really that slow?

Are Disks Really That Slow?
Instead of comparing modern drives to the prehistoric “winchester” drive of 1956, I think a better comparison would be to the ST-506 which is the father of modern disks. The ST506 of 1984 would have found itself paired to an Intel 80286 in the PC of the day. Comparing transistor count from the 80286 to a “Harpertown” Xeon yields an increase of 3280-fold and a clock speed improvement of 212-fold. The ST506 (circa 1984) had a throughput capability of 625KB/s). Modern 450GB SAS drives can scan at 150MB/s-an improvement of 245-fold. When considered in these terms, the hard drive throughput and CPU clock speed have seen a surprisingly similar increase in capability. Of course Intel is cramming 3280x more transistors in a processor these days, but read on.

The point I’m trying to make is that disks haven’t lagged as far behind CPU as I feel is sometimes portrayed. In fact, I think the refrigerator-cabinet array manufacturers disingenuously draw attention to things like rotational delay in order to detract from the real bottleneck, which is the flow of data from the platters through the storage processors to the host. This bottleneck is built into modern storage arrays and felt all the way through the host bus adaptors. Let’s not punish ourselves by mentioning the plumbing complexities of storage networking models like Fibre Channel.

Focus on Flow of Data, Not Spinning Speed.
Oracle Exadata Storage Server, in the HP Oracle Database Machine offering, configures 1.05 processor cores per hard drive (176:168).  Even if I clump Flash SSD into the mix (about 60% increase in scan throughput over round, brown spinning disks) it doesn’t really change that much (i.e., not orders of magnitude).

Junk Science? Maybe.
So, am I just throwing out the 3280x increase in transistor count gains I mentioned? No, but I think when we compare the richness of processing that occurs on data coming off of disk in today’s world (e.g., DW/BI) compared to the 80286->ST506 days (e.g., VisiCalc, a 26KB executable), the transistor count gets factored out. So we are left with 245-fold disk performance gains and 212-fold cpu clock gains. So, is it a total coincidence that a good ratio of DW/BI cpu to disk is about 1:1? Maybe not. Maybe this is all just junk science. If so, we should all continue connecting as many disks to the back of our conventional storage arrays as they will support.

Summary
Stop bottlenecking your disk drives. Then, and only then, you’ll be able to see just how fast they are and whether you have a reasonable ratio of CPU to disk for your DW/BI workload.

I added Real World Technologies to my blog roll. This site is loaded with good information for commodity computing systems-minded professionals! I love it!

I’m so flattered! I just got a call from corporate and it seems  they want me to join the Winter Corporation Web Seminar tomorrow so I can help out with the question and answer segment. As the Performance Architect on the product, I guess I better study up on my notes right quick 🙂

Joking aside, please follow the this link and sign up for the event. It should be informative. At the end, you can play “stump the host” too  🙂

Please fell free to read the Whitepaper in advance.

Two years ago I was working on a series of blog threads about Oracle on AMD Opteron processors. I made it perfectly clear at that time that I was a total fanboi of AMD dating back to my first experience with Opterons in the HyperTransport 1.0 timeframe. I had a wide variety of hardware in those days. That was then, this is now.

I’ve not yet had personal experience with the Xeon 5500 (Nehalem-EP) processors,  but I’m chomping at the bits to do so. I blogged about Nehalem with CSI interconnect technology nearly two years ago. I am a patient man.

I’m very exited about these processors as they represent the most significant technology leap in Intel processors since the jump from Pentium to Pentium Pro (the first Intel MCM cpu). But, all that aside, what does it mean for real workloads? From the things I heard first hand from engineers of HP’s ISS team it looks like this processor offers contentious workloads like Oracle a doubling of throughput on a core-for-core basis. After I heard that I started digging for independent measurements that back that up.

Although this Nehalem SAP Sales and Distribution Benchmark result was performed with Microsoft SQL Server, I know enough about the SDU test to know that it is a very contentious workload that is difficult to scale. I can’t say the boost will map one for one to Oracle, but I wouldn’t be surprised if it did. I like this result because it is nearly apples-to-apples and showing a 100% performance increase over Xeon 5400 “Harpertown.”  And, Harpertown CPUs are no slouches.

Systems based on Xeon 5500 processors are going to mow through Oracle workloads very nicely! As for packaging, I believe most servers are going to come in 2s8c and 4s16c configurations at first.

The other thing I like about these CPUs is the emergence of functional multithreading. Since these are NUMA systems it will be important to have processors that can get useful work done while a thread is stalled on remote memory. Not to be confused with Hyperthreading (Netburst), which didn’t do much if anything for Oracle workloads, the Nehalem (S)imultaneous (M)ulti-(T)hreading feature has proven helpful in a wide variety of workloads as this comprehensive paper shows.

Exciting!

I met someone at Rocky Mountain User Group Training Days 2009 who mentioned that they enjoyed my Little Things Doth Crabby Make…series ( found here). I was reminded of that this morning as I suffered the following Oracle Database 11g Automatic Storage Management (ASM) issue:

$ sqlplus '/ as sysdba'
SQL*Plus: Release 11.1.0.7.0 - Production on Thu Feb 19 09:33:53 2009
Copyright (c) 1982, 2008, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup

ASM instance started
Total System Global Area  283930624 bytes
Fixed Size                  2158992 bytes
Variable Size             256605808 bytes
ASM Cache                  25165824 bytes

ORA-15032: not all alterations performed
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DATA2"
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DATA1"

Ho hum. I know the disks are there. I’ve just freshly configured this system. After all, this is Exadata and configuring ASM to use Exadata couldn’t be easier as you simply list the IP addresses of the Exadata Storage Servers in a text configuration file. No more ASMLib sort of stuff. Just point and go.

SQL> select count(*) from v$asm_disk;

COUNT(*)
----------
24

See, even ASM agrees with me. I set up 12 disks for each diskgroup and viola, there they are.

KFOD
There is even a nice little command line tool that ships with Oracle Database 11g 11.1.0.[67] that reports what Exadata disks are discovered. This is a nice little tool. It shows that I have 12 Exadata “griddisks” (ASM disks really) of 20GB and another 12 of 200GB all within a single Exadata Storage Server  (for testing purposes). Note, it also reports a list of other ASM instances in the database grid.

$ kfod -disk all
--------------------------------------------------------------------------------
 Disk          Size Path                                     User     Group
================================================================================
 1:      20480 Mb o/192.168.50.32/data1_CD_10_cell06       <unknown> <unknown>
 2:      20480 Mb o/192.168.50.32/data1_CD_11_cell06       <unknown> <unknown>
 3:      20480 Mb o/192.168.50.32/data1_CD_12_cell06       <unknown> <unknown>
 4:      20480 Mb o/192.168.50.32/data1_CD_1_cell06        <unknown> <unknown>
 5:      20480 Mb o/192.168.50.32/data1_CD_2_cell06        <unknown> <unknown>
 6:      20480 Mb o/192.168.50.32/data1_CD_3_cell06        <unknown> <unknown>
 7:      20480 Mb o/192.168.50.32/data1_CD_4_cell06        <unknown> <unknown>
 8:      20480 Mb o/192.168.50.32/data1_CD_5_cell06        <unknown> <unknown>
 9:      20480 Mb o/192.168.50.32/data1_CD_6_cell06        <unknown> <unknown>
 10:      20480 Mb o/192.168.50.32/data1_CD_7_cell06        <unknown> <unknown>
 11:      20480 Mb o/192.168.50.32/data1_CD_8_cell06        <unknown> <unknown>
 12:      20480 Mb o/192.168.50.32/data1_CD_9_cell06        <unknown> <unknown>
 13:     204800 Mb o/192.168.50.32/data2_CD_10_cell06       <unknown> <unknown>
 14:     204800 Mb o/192.168.50.32/data2_CD_11_cell06       <unknown> <unknown>
 15:     204800 Mb o/192.168.50.32/data2_CD_12_cell06       <unknown> <unknown>
 16:     204800 Mb o/192.168.50.32/data2_CD_1_cell06        <unknown> <unknown>
 17:     204800 Mb o/192.168.50.32/data2_CD_2_cell06        <unknown> <unknown>
 18:     204800 Mb o/192.168.50.32/data2_CD_3_cell06        <unknown> <unknown>
 19:     204800 Mb o/192.168.50.32/data2_CD_4_cell06        <unknown> <unknown>
 20:     204800 Mb o/192.168.50.32/data2_CD_5_cell06        <unknown> <unknown>
 21:     204800 Mb o/192.168.50.32/data2_CD_6_cell06        <unknown> <unknown>
 22:     204800 Mb o/192.168.50.32/data2_CD_7_cell06        <unknown> <unknown>
 23:     204800 Mb o/192.168.50.32/data2_CD_8_cell06        <unknown> <unknown>
 24:     204800 Mb o/192.168.50.32/data2_CD_9_cell06        <unknown> <unknown>
--------------------------------------------------------------------------------
ORACLE_SID ORACLE_HOME
================================================================================
 +ASM1 /u01/app/oracle/product/db
 +ASM2 /u01/app/oracle/product/db
 +ASM3 /u01/app/oracle/product/db
<pre>

I know why ASM is trying to mount these diskgroups because I set the parameter file to direct it to do so.

SQL> show parameter asm_diskgroups;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups                       string      DATA1, DATA2

I suppose I should get some information about the diskgroups. How about names first:

SQL> select name from v$asm_diskgroup;
no rows selected

SQL> host date
Thu Feb 19 09:37:25 PST 2009

Idiot!  When you have several configurations “stewing” it is quite easy to miss a step. Today that seems to be forgetting to actually create diskgroups before I ask ASM to mount them.

SQL> startup force

ASM instance started
Total System Global Area  283930624 bytes
Fixed Size                  2158992 bytes
Variable Size             256605808 bytes
ASM Cache                  25165824 bytes

ORA-15032: not all alterations performed
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DATA2"

SQL>  select name from v$asm_diskgroup;

NAME
------------------------------
DATA1

SQL> host date
Thu Feb 19 09:44:21 PST 2009

Magic. I created the DATA1 diskgroup in a separate xterm and did a STARTUP FORCE.

Summary
Stupidity is one of those little things that doth crabby make. And, yes, the title of this blog post was a come-on. Who knows, however, someday there may be a flustered googler that’ll end up feeling crabby and stupid (like I do now)  🙂  after finding this worthless post.

According to this post on blogs.oracle.com, Information Management is hosting a Web Seminar on February 25, 2009 covering the Winter Corporation findings in a recent Exadata proof of concept.

The signup page for the event is here.

I’ll be attending. I’m always curious about what people are saying when they do these things. Go ahead, sign up and join me.

Several readers sent in email questions after reading the Winter Corporation Paper about Exadata I announced in a recent blog entry. I thought I’d answer one in this quick blog entry.

The reader’s email read as follows (I made no edits other than to remove the SAN vendor’s name):

I have read through the Winter Corporation paper regarding Exadata, but is it ok for me to ask a questio? We have an existing data warehouse on a 4 node 10g RAC cluster attached to a [ brand name removed ]   SAN array by 2 active 4GB ports on each RAC node. When we test with Orion we see nearly 2.9 gigabytes per second throughput but with RDBMS queires we never see more than about 2 gigabytes per sec throughput except in select count(*) situation. With select count(*) we see about 2.5GB/s. Why is this?

Think Plumbing First

It is always best to focus first on the plumbing, and then on the array controller itself. After all, if the supposed maximum theoretical throughput of an array is on the order of 3GB/s, but servers are connected to the array with limited connectivity, the bandwidth is unrealizable. In this case, 2 active 4GFC HBAs per RAC node demonstrate sufficient throughput for this particular SAN array. Remember, I deleted the SAN brand. The particular brand cited by the reader is most certainly limited to 3GB/s (I know the brand and model well)  but no matter because the 2 active 4GFC paths to each RAC node limit I/O to an aggregate of 3.2GB/s sustained read throughput no matter what kind of SAN it is. This is actually a case of a well-balanced server-to-storage configuration and I pointed out so in a private email to the blog reader who sent me this question. But, what about the reader’s question?

Orion is a Very Light Eater

The reason the reader is able to drive storage at approximately 2.9GB/s with Orion is because Orion does nothing with the data being read from disk. As I/Os are completed it simply issues more. We sometimes call this lightweight I/O testing because the code doesn’t touch the data being read from disk. Indeed, even the dd(1) command can drive storage at maximum theoretical I/O rates with a command like dd if=/dev/sdN of=/dev/null bs=1024k. A dd(1) command like this does not touch the data being read from disk.

SELECT COUNT(*)

The reason the reader sees Oracle driving storage at 2.5GB/s with a SELECT COUNT(*) is because when a query such as this reads blocks from disk only a few bytes of the disk blocks being read are loaded into the processor caches. Indeed, Oracle doesn’t have to touch every row piece in a block to know how many rows the block contains. There is summary information in the header of the block that speeds up row counting. When code references just one byte of data in an Oracle block, after it is read from disk, the processor causes the memory controller to load 64 bytes (on x86_64 cpus) into the processor cache. Anything in that 64-byte “line” can be accessed for “free” (meaning additional loads from memory are not needed). Accesses to any other 64-byte lines in the Oracle block causes subsequent memory lines to be installed into the processor cache. While the CPU is waiting for a line to be loaded it is in a stalled state, which is accounted for as user-mode cycles charged to the Oracle process referencing the memory. The more processes do with the blocks being read from disk, the higher processor utilization goes up and eventually I/O goes down. This is why the reader stated that they see about 2GB/s when Oracle is presumably doing “real queries” such as those which perform filtration, projection, joins, sorting, aggregation and so forth. The reader didn’t state processor utilization for the queries seemingly limited to 2GB/s, but it stands to reason they were more complex than the SELECT COUNT(*) test.

Double Negatives for Fun and Learning Purposes

You too can see what I’m talking about by running a select that ingests dispersed columns and all rows in a query after performing zero-effect filtration such as the following 16-column table example:

SELECT AVG(LENGTH(col1)), AVG(LENGTH(col8)), AVG(LENGTH(col16)) FROM TABX WHERE col1 NOT LIKE ‘%NEVER’ and col8 NOT LIKE ‘%NEVER’ and col16 NOT LIKE ‘%NEVER’;

This test presumes columns 1,8 and 16 never contain the value ‘NEVER’. Observe the processor utilization when running this sort of query and compare that to a simple select count(*) of the same table.

Other Factors?

Sure, the reader’s throughput difference between the SELECT COUNT(*) and Orion could be related to tuning issues (i.e., Parallel Query Option degree of parallelism). However, in my experience achieving about 83% of maximum theoretical I/O with SELECT COUNT(*) is pretty good. Further, the reader’s complex query achieved about 66% of maximum theoretical I/O throughput which is also quite good–when using conventional storage.

What Does This Have To Do With Exadata

Exadata offloads predicate filtering and column projection (amongst the many other value propositions). Even this silly example has processing that can be offloaded to Exadata such as filtration that filters out no rows and the cost of projecting columns 1,8 and 16. The database host spends no cycles with the filtration or projection. It just performs the work of the AVG() and LENGTH() functions.

I didn’t have the heart to point out to the reader that 3GB/s is the least amount of throughput available when using Exadata and Real Application Clusters (RAC). That is, with RAC the fewest number of Exadata Storage Servers supported is 3 and there’s no doubt that 3 Exadata Storage Servers do indeed offer 3GB/s query I/O throughput. In fact, as the Winter Corporation paper shows, Exadata is able to perform maximum theoretical I/O throughput even with complex, concurrent queries because there is 2/3rds of a Xeon 54XX “Harpertown” processor for each disk drive offloading processing from the database grid.

So, while Orion is indeed a “light eater”, Exadata is quite ravenous.

AdSense Nonsense

I don’t have a screen-shot for verification, but a blog reader sent me email notifying me that WordPress (the site that hosts my blog) is letting Google AdSense put advertisements for Netezza on my blog posts. The reader thought I was the one doing the AdSense but I assured him that it is not I. My blogging is a non-profit effort. It is WordPress and the AdSense nonsense is the “pay” for using a “free” site.

There’s No Such Thing as a Free Lunch

I knew WordPress did this sort of thing on occasion, but I had totally put it out of mind…until now. So, have no fear readers, I just used some of my very own bier money to pay WordPress so you don’t have to see any ads from Oracle’s competitors any more!

WinterCorporation has posted a paper covering a recent Exadata proof of concept testing exercise. Highlights of the paper are evidence of concurrent moderately complex queries being serviced by Exadata at advertised disk throughput rates. The paper can be found at the following link:

Measuring the Performance of the Oracle Exadata Storage Server

There is also a copy of the paper on oracle.com at this link: Measuring the Performance of the Oracle Exadata Storage Server and a copy in the Wayback Machine in case it ages out of oracle.com.

Quotable Quotes
I’d like to draw attention to the following quote:

14 Gigabytes per second is a rate that can be achieved with some conventional storage arrays — only in dedicated large-scale enterprise arrays, that would require multiple full-height cabinets of hardware – and would therefore entail more space, power, and cooling than the HP Oracle Database Machine we tested here. Additionally, with established storage architectures, Oracle cannot offload any processing to the storage tier, therefore the database tier would require substantially more hardware to achieve a rate approaching 14 GB/second.

Yes, it may be possible to connect enough conventional storage to drive query disk throughput at 14GB/s, but the paper correctly points out that since there is no offload processing with conventional storage the database grid would require substantially more hardware than is assembled in the HP Oracle Database Machine. One would have to start from the ground up, as it were. By that I mean a database grid capable of simply ingesting 14GB/s would have to have 35 4Gb FC host bus adaptors. That requires a huge database grid.

If I could meet the guys (that would be me) that worked on this proof of concept I’d love to ask them what storage grid processor utilization was measured at the point where storage was at  peak throughput and performing the highest degree of storage processing complexity.  Now that would be a real golden nugget.  One thing is for certain, there was enough storage processor bandwidth to perform the Smart Scans which consist of applying WHERE predicates, column projection and performing bloom filtration in storage. Moreover, the test demonstrated ample storage processor bandwidth  to execute Smart Scan processing while blocks of data were whizzing by at the rate of 1GB/s per Oracle Exadata Storage Server. Otherwise, the paper wouldn’t be there.

Maybe 1.0476 processors per hard drive ( 176:168  )will become the new industry standard for optimal processor to disk ratio in DW/BI solutions.

In the comment thread of one of my old posts about Orion a reader posted an example of a problem he was having with the tool.

# ./orion10.2_linux -run simple -testname mytest -num_disks 1
ORION: ORacle IO Numbers — Version 10.2.0.1.0
Test will take approximately 9 minutes
Larger caches may take longer

storax_skgfr_openfiles: File identification failed: /mnt/1201879371/Orion, error 4
storax_skgfr_openfiles: File identification failed on /mnt/1201879371/Orion
OER 27054, Error Detail 0: please look up error in Oracle documentation
rwbase_lio_init_luns: lun_openvols failed
rwbase_rwluns: rwbase_lio_init_luns failed
orion_thread_main: rw_luns failed
Non test error occurred
Orion exiting

Carry on Wayward Googler

I didn’t work the problem with the poster of this issue, but I do know the answer to the issue and for the sake of any future wayward googlers I’ll post up the solution. The solution was to set  fs.aio-max-nr in sysctl.conf file to 4194304. The value can be change through the /proc interface as well:

# echo 4194304 > /proc/sys/fs/aio-max-nr

Juan Loaiza (SVP Oracle Systems Technologies Div) is offering a webcast this Wednesday. Here are the details:

Webcast: Extreme Performance for Your Data Warehouse
Wednesday, January 28th, 9:00 am PST

Data warehouses are tripling in size every two years and supporting ever-larger databases with ever increasing demands from business users to get “answers” faster requires a new way to approach this challenge. Oracle Exadata overcomes the limitations of conventional storage by utilizing a massively parallel architecture to dramatically increase data bandwidth between database and storage servers. In this webcast, we’ll examine these limitations and demonstrate how Oracle Exadata delivers extremely fast and completely scalable, enterprise-ready systems.

The signup sheet is here:

January 28, 2009 Exadata Webcast

My old friend Matt Zito of  GridApp has made a blog entry entitled Where is Exadata. The post takes issue with some of the latest rants uttered by Chuck Hollis of EMC. It seems Chuck thinks Oracle Exadata Storage Server is dead or dying based on his best guess of the field adoption rate over the approximate 2,880 hours Exadata has been in production.

I grew tired of Chuck’s musings on the topic weeks ago so I didn’t want to call it out on my own. Matt makes a good point about the fact that Exadata requires Oracle Database 11g and most sites take the sensibly cautious approach toward adopting software that is newer then what they currently have. I’m not saying that is any sort of mitigation for Chuck’s beliefs. I am saying that people like Matt are awake at the wheel-as it were.

Matt does point out correctly that nowhere in Exadata marketing literature is EMC called out by name as competition. To the contrary Oracle and EMC share a tremendous install base. That doesn’t even need to be said. What Matt further points out is how seemingly paranoid Chuck is about Exadata. I too find it odd, but not sufficiently odd to make a blog entry specifically about the point. I, therefore, have not done so.

Just a quick entry to point to Chen Shapira’s blog. I recommend it. Oh, that means I need to take a moment to update my blog roll!

You Need to See What These Folks Have to Say

I must put out a plug for Oracle’s Maximum Availability Architecture (MAA) team and the fruits of their labor now that I have personally worked with them on projects for over a year…I’m sure it’s no credit to them that I should say so,  but honestly, this team is really, really sharp!

Not only is this paper covering migration to Exadata Storage Server helpful for the actual act of deploying Exadata into an existing Oracle DW/BI environment, but it also goes a long way to suggest how much simpler it surely must be than dumping out an Oracle database and loading it.

Go get some of those papers!

I just took a look at the RMOUG 2009 Training Days Schedule to find out what day/time I’m speaking about Exadata performance and architecture. I have to say that RMOUG is always one of my favorite conferences and I’m looking forward to this year. I’ll have 90 minutes ending at noon on Wednesday and if all goes well I won’t have ruined attendees’ appetites just in time for lunch!

I also noticed an unfortunate schedule conflict. During the same time slot friends and fellow Oaktable Network memebers Tim Gorman and Tanel Poder are also speaking. Choices, choices…

Details

Title: Oracle Exadata Storage Server. Introducing Extreme Performance for Data Warehousing.

Abstract: Kevin Closson will present an overview of the Oracle Exadata Storage Server and HP Oracle Database Machine architecture and internals including performance analysis of typical workloads the Exadata Storage Server is solely capable of accelerating. The presentation will include comparisons to the typical storage architectures supporting Oracle Database workloads today. Attendees will leave with a good understanding of why, and how, Exadata is what it is. Kevin will leave ample time for a fruitful question and answer session.