Kevin Closson's Blog: Platforms, Databases and Storage

Blog Changes
Now that I’ve left HP/PolyServe, I’ve gotten a few emails from readers looking for files that I linked to that resided on HP/PolyServe systems. I’ve also gotten quite a few emails about pages found by search engines, but don’t load (Error 404) once you come to my site. As for the latter, there are a good number of posts that I’ve taken offline. Sorry, but that is just how things go. As for the files content, I’ll try to make them available on the OakTable website. The first to get this treatment is the Silly Little Benchmark that I spoke of in some of my NUMA-related posts such as Oracle on Opteron with Linux–The NUMA Angle. Introducing the Silly Little Benchmark.

I’ll be fixing those refrences. In the meaintime, here is a link to the Silly Little Benchmark.

There is a difference between jumping ship and making an orderly transfer from one ship to another. I tend to think of jumping ship as something one does when it is sinking. I’m not jumping ship.

For the last 6 years at PolyServe, and 10 years before that at Sequent Computer Systems, I’ve had the honor to work with some of the brightest software folks around. See, the brain power behind PolyServe is a lot of the heavy-hitting Unix kernel engineers from Sequent Computer Systems. They left after IBM bought Sequent. I joined them shortly thereafter, leaving Veritas to do so. I think it is fitting to quote my long time friend and fellow OakTable Network member, James Morle. In Oracle Insights: Tales of the Oak Table, James says:

…around the same time Sequent appeared with a good story, great attitude, and some of the best software technicians I have worked with from a hardware company.

I know what he means. But I’m leaving. I did my part at PolyServe, HP acquired us. Getting taken up in a corporate takeover means a new adventure. When faced with a new adventure it sometimes makes sense to open up to other, sometimes better, opportunity. This is one of those cases.

Next week I start a new chapter. I’ll be taking a role in Oracle’s Server Technologies group. Fortunately for me, I’ve had pretty close ties with a lot of these folks dating back to the mid-1990s and my work in Sequent’s Advanced Oracle Engineering group.

So, I’m transferring to a different ship. A ship that I am very familiar with. A ship with crew that I respect and with whom it’ll be an honor to work.

An old Sueqnet bdudy of mnie snet the fowolling to me tdoay

I cdnuolt blveiee taht I cluod aulaclty uesdnatnrd waht I was rdanieg. The phaonmneal pweor of the hmuan mnid, aoccdrnig to a rscheearch at Cmabrigde Uinervtisy, it dseno’t mtaetr in waht oerdr the ltteres in a wrod are, the olny iproamtnt tihng is taht the frsit and lsat ltteer be in the rghit pclae. The rset can be a taotl mses and you can sitll raed it whotuit a pboerlm. Tihs is bcuseae the huamn mnid deos not raed ervey lteter by istlef, but the wrod as a wlohe.

…hmmm…he must be a regular reader of my blog I guess 🙂

Our OakTable Wunderkind, Tanel Poder, has started blogging about Oracle Database 11g Automatic Memory Management. Do pay his site a visit! In fact, his site is worth a regular visit.

One of my fellow OakTable Network members sent me email about this these guys today. I had never heard of them. But then I’m no sleuth since I didn’t hear about PANTA Systems until they audited a very nice Infiniband-enabled TPC-H last year. I’m talking about TILERA. Today TILERA announced a 64-core x86 compatible processor. Since this processor is marketed for embedded applications such as network switches and so on I won’t bore you with a long blog entry. One point I found very interesting about this processor, however, is that it has 2 10GbE interfaces on the chip itself as opposed to jumping over PCI. Now that is one heck of a way to hose down the cores! 

Come to think of it, maybe this is worth blogging. I did see recently that Intel has an 80-core (not x86 compatible though) chip in demo state. Sun, of course, has the Niagra and now there is this 64-core chip where each core can run its own copy of Linux. Even though the TILERA isn’t intended for servers, it represents technology that doesn’t seem that far off. Interesting stuff.

In Part II of this small series of blog entries on DBWR, a reader made note of the fact that the test I was using suffered log file switch (checkpoint incomplete) wait events and wanted an explanation as to why those were happening. I’d like to answer that and cover a couple of mistakes I made as well.

When I configured the OLTP database for that set of tests I sized the online redo logs small enough to ensure I’d get some log switches into the overall mix as well. My rationale for that move was to take measurements at the varying DBWR counts with a workload that put DBWR under as much duress as possible. To that end, some may consider the test too artificial and honestly I don’t blame them. Afterall, the remedy for log file switch (checkpoint incomplete) is to either make the redo logs larger or address DBWR’s ability to flush buffers. Of course the approach for the latter would be either a faster I/O subsystem or improve DBWR’s ability to do his job-which is what this thread is about. So that begs the question, what are the numbers if redo logs are large enough to rule out checkpoint bottlenecks. Good question. After changing my redo logs to very large sizes I ran another set of tests that lead me to believe that on systems with a small number of processor cores and a normal DBWR workload (e.g., reasonable checkpoints) there is no appreciable difference in throughput when going with as many as 1 DBWR per processor core. The results surprised me…read on…

Admissions of Error
The first of two admissions of error that is. After reading my own blog entries on this thread a couple of times I believe I need to clarify some things. I’ve stated matter-of-factly that over-configuring DBWR processes will impact performance. Well, that is true as my test example showed but is it a completely true statement? No. I met a guy once that couldn’t admit when he was wrong. I didn’t like him much.

Here is the situation: over-configuring DBWR processes is not going to impact performance if your workload isn’t a challenge for DBWR functionality. I have myriads of proof that normal DBWR workloads can be handled by multiple DBWR processes without any problem. It’s more likely the case that if your system has unnecessary multiple writers and you reconfigure to a single writer you wont see any difference. That’s because multiple writers in and of themselves is not a bad thing. And most production systems do not torture the I/O subsystem like I do here. My intent was simply to dig into the topic a bit deeper. That leads me to admission number 2.

Earlier in this series I mentioned the cache effect of multiple writers. Well, I was digging around some audited TPC-C full disclosure reports and noticed the practice of setting db_block_checksum=FALSE. This jarred my memory to the fact that since Oracle9i, DBWR has in fact been peeking at block contents by default. Let me explain. There was a day when DBWR’s accesses to memory differed significantly from that of foreground processes. Those days were pre-Oracle9i and in those releases DBWR didn’t peek at the contents of buffers. Instead, DBWR’s work consisted of figuring out what to write, writing it and performing the necessary housekeeping so that the written buffer would have the correct state. To that end, DBWR processes manipulated much more SGA metadata than did foreground processes. While that is still true to a large degree, Oracle9i changed that situation because DBWR now peeks at buffer contents.

With Oracle9i and later releases, DBWR processes peek at the contents of the buffers to calculate the block checksum and then store the value in the block itself. When re-read, the checksum is calculated by the reading process to ensure the block hadn’t been corrupted after it was flushed from the SGA. Now don’t get me wrong, these buffer reads are nowhere as data intensive as what foreground processes do for a living. Consider, for instance, a block of data manipulated by a foreground process servicing a payroll application. The foreground process exercises myriads of code that DBWR never would (SQL, transaction layer, etc), touches shared resources DBWR never would (shared pool, redo, etc) and most importantly, manipulates the row pieces in the block. When DBWR flushes the buffer with block check summing, it will peek at the contents of the block which will require the memory be installed into DBWR’s processor cache. That wasn’t the case “in the old days.” DBWR used to only manipulate metadata.

With db_block_checksum=FALSE, DBWR doesn’t peek at the contents of the block. When DBWR simply writes a buffer (without peeking into it), the contents of that buffer do not get installed into the processor cache. The page is in DBWR’s address space, but that is not the same as a memory access. Don’t get me wrong, I’m not saying db_block_checksum=TRUE is miserably expensive, because I haven’t tested the difference on modern systems so it I don’t know. To be honest, I wouldn’t run a database without it because it sure seems like good insurance. I would think systems with really large processor caches would see the most performance hit, but that would just be my best guess. After all, most commodity systems have small processor caches that are simply overwhelmed long before any of these concepts come into play. As for huge systems, well, I’ve been unfortunate to have been out of that space for a few years. It’s good to have friends, so I’ll quote the comment my old buddy Glenn Fawcett made on Part I of this series:

I agree… One DBWR process per cpu is not sage advice.

On large NUMA based servers, such as the Sun E25K, Oracle will by *default* create one dbwr process per system board. This allows the DBWR process to search LRUs out of local memory which is very efficient… Kevin could explain the benefits of this far better than I could.

The best advice I could give on “db_writer_processes” is don’t set it AT ALL. Let Oracle figure out the best setting.

Glenn knows really big systems due to his role in Sun’s SAE and other roles.

I think all this was my long-winded way to say that it is good to dive into DBWR topics, but it is best to remember that things like db_block_checksum defaults change from time to time. Further, there should be no panic over having multiple writers and finally, mistakes happen.

Interesting to see this eWEEK.com interview finally show up. It sure sounds different when it is presented there; as if this was the complete conversation. In fact, this was a small part of a long set of interviews back in February 2007. I wish now that I would have been clearer about what ‘production’ means. There are production applications at the core of the enterprise and there are production applications at the edge. Any application that can be developed and deployed using any open source database would most certainly not require Oracle’s carrier-grade edition (Enterprise Edition) were it to be developed and deployed with Oracle. That is, any application that can be deployed with an open source database could be deployed with a limited-feature edition of Oracle such as Standard Edition (or even perhaps Standard Edition One). On the contrary, it is unreasonable to think that one could migrate enterprise-class applications to any of the open source stuff out there (or even most of the closed-source stuff for that matter). I think I’ve made that point pretty clear in my thread about EnterpriseDB for warehousing and many other posts here in the blog.

We are all at different skill and technical sophistication levels so this post will look like fluff to some readers. This is just a quick post to show a clear depiction of a SAN configuration that wouldn’t do anyone any favors in an Oracle environment.

I showed some snapshots of the lab gear at my disposal in this blog entry. There are a lot of SANs in the lab here, but I can’t say which brand of SAN array I’m blogging about today, and honestly, this problem is not necessarily the architecture of the particular SAN array controller in question. Yes, this is a low-end array, but the concepts I’m blogging about are relevant through the high-end. The following vmstat(1) output shows one of the problems I’m blogging about:

# vmstat 10
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
 3  3      0 31577944  81948 653352    0    0    96   597   13    34  0  7 89  4
 5  4      0 31550968  82204 653096    0    0 73732 89913 2919 57992  0 10 60 30
 0  4      0 31576760  82332 652968    0    0 74555 98056 2942 55904  0 10 60 30
 1  6      0 31553824  82460 652840    0    0 80289 85870 2831 61435  0 10 63 28
 0  4      0 31576968  82460 652324    0    0 79466 85397 2860 60537  0 10 62 28
 8  0      0 31573368  82588 654260    0    0 41279 46733 1917 36035  0 32 53 15
 8  0      0 31572896  82588 653744    0    0 11613 13344 1239 13387  0 49 46  5
 5  0      0 31572528  82588 654260    0    0  8875  9039 1193 10727  0 47 49  4
 7  0      0 31572280  82588 653744    0    0  6559  7057 1146  9823  0 47 49  4
 6  0      0 31572176  82588 653744    0    0  6556  7048 1156  9121  0 50 47  3
 4  0      0 31572072  82588 654260    0    0  5736  5956 1136  7955  0 47 50  3
 6  0      0 31571576  82588 654260    0    0  4205  5529 1126  7851  0 47 51  2
 8  0      0 31571584  82588 654260    0    0  5960  5264 1132  7982  0 48 49  3
 7  0      0 31570896  82652 654712    0    0  4239  5167 1119  8086  0 47 51  2
11  0      0 31569968  82652 654712    0    0  5116  4995 1124  7437  0 55 43  2
 7  0      0 31570184  82684 653648    0    0  4782  5082 1125  7880  0 54 44  2

This vmstat(2) output shows that Oracle was getting upwards of 165MB/s (via a single 2Gb FCP path) of combined read/write throughput until the array cache reached its saturation point. At that point, the throughput of the array was relegated to the bandwidth of the spindles being accessed.

Its All About Balance
I have a pretty simple view of storage and it is worth quoting myself:

Physical I/O is a necessary evil. Bottlenecking downwind of the SAN array cache is silly. Bottlenecking above the disks is foolishness.

Bottlenecking The Array (Silliness)
Configuring LUNs with insufficient spindle count to handle the cache miss (read-through) and write-back overhead is what I call bottlenecking the SAN array. This sort of bottleneck is strictly a configuration issue and therefore falls into the silliness category. The vmstat(1) output above is an example of bottlenecking the array. That is, the array could certainly deliver more throughput, but the spindle count was holding it back. Again, I’m not going to talk about what vendor’s SAN array controller it was because as it turns out any of them will act this way under these circumstances. I had a LUN of some 500GB that consisted of very few spindles so it was no surprise to me that the throughput was lousy. This LUN had to be configured this way due to other constraints on the array’s usage (read Kevin had to share some hardware). I got to live the pain I blogged about recently in this post about capacity versus spindle count. I could configure a more reasonable number of drives and get around this performance problem, but only up to the point where the tables turn and the array starts to bottleneck the disks.

Bottlenecking The Disks (Foolishness)
What do I mean by this? Well, most modern SAN arrays will bottleneck long before your application realizes the full bandwidth potential of the all the drives the array can support. I call this bottlenecking the disks and it falls into the foolishness category. This is basic foolishness on the SAN array vendor’s part. Why build storage arrays with horrible imbalance between capacity and performance?

Let me go over a typical case of a SAN array that bottlenecks the disks. Here is a link to some technical detail of a high end array. This particular array supports roughly 140TB of capacity when configured with the maximum spindle count (1024) of 146GB drives. On page 3 of the document, the vendor states the first figure related to bandwidth by citing the internal bandwidth of 15GB/s. What does that mean to the servers connected to this array? Reading further (pg. 15) the document states that there is some 10.6GB/s of aggregate data bandwidth and 5.3GB/s bandwidth for control. This means that if you get all the king’s horses and all the king’s men to work it out, you could feed 10.6GB/s of data to your servers. Indeed, 10.6GB/s is a great deal of bandwidth. So what am I talking about? Taking another look at page 15 we see the vendor’s claims that a single 146GB drive can deliver 99.9MB/s ( max theoretical). If you wanted to drive all these spindles at full throttle, they would theoretically deliver 99.9GB/s (1024 disks X 99.9MB/s) which is much less than the maximum theoretical data bandwidth of the array. In fact, if you drove “only” about 108 of these spindles at full throttle you’d saturate the array. I quoted the word only because 108 is a lot of disks, but that is only 10% of what the array supports.

If Oracle needs to do a physical I/O, let’s not bottleneck somewhere in the pipe! Think about it this way, in the technology stack I’m discussing (Oracle, high-end SAN array, etc), hard drive technology represents the simplest and least advanced component. That is, while hard drives are faster than they were 10 years ago, they have not fundamentally changed. They are still round and brown and they spin. Wouldn’t you think homely old disk drives would be the bane of performance? They aren’t. If we could drive all our disks at their maximum throughput, we’d be in a much better place performance-wise.

Summary
Hard drives are miserably low-tech necessary evils. Will we ever get a storage architecture where the more sophisticated components don’t make matters worse? Yes, we will. I’ll tell you all more as time passes. In the meantime, I bet dollars to donuts that the paltry 128 drives used in the TPC-H benchmark I blogged about were being driven at or near full bandwidth. That, my dear readers, is cool.

Not a Record, But It Is In My Book
Not a throughput record that is. This new result at 39,614 QphH comes within 2% of Oracle’s previous top ranking in the 300GB scale. Why would Oracle publish a result that doesn’t even beat their own previous result? Price performance is the answer. I also find the deployment architecture to be very interesting.

Oracle still hold the top ranking at 300GB with their December 2006 result of 40,411 QphH at a price/performance of $18.67 $$/QphH. This new result, however, comes in at only $12.57 QphH-33% cost savings for essentially the same performance! What did Oracle do to reduce the cost? Did they use fewer RAC nodes to reduce their own licensing cost? Did they use a better, cheaper SAN? Did they use a cheaper, faster RAC interconnect? No, none of that. In fact:

• Both results used an 8 node cluster of blade servers
• Both results used Infiniband for the RAC cluster interconnect
• Both results used “networked storage”

So what gives?

No SAN.
The December 2006 result of 40,411 QphH used a $393,765 configuration consisting of some 448 disks in a SAN with 32 MSA 1000 controllers (not including spares). This new result, on the other hand, did not use a SAN at all. Instead, there were 8 blade servers (the RAC cluster) attached to 16 storage blades. The storage blades were running RHEL5 and presenting block storage via SRP over Infiniband to the 8 node RAC cluster. What’s that? Yes, Infiniband-a single, unified connectivity model for both RAC interconnect and storage. With this type of storage, Oracle was able to drive the same performance (yeah, I know, within 2%) with only 128 hard drives. All told, the storage configuration was priced at $141,158-a healthy 64% reduction in storage cost!

This is a very, very good day-but not for Manly Man.

I couldn’t be happier!

According to this businesswire.com piece, EnterpriseDB made quite the splash at LinuxWorld San Francisco 2007 by introducing a clustered version of EnterpriseDB called GridSQL for EnterpriseDB. It’s no surprise that a product based on open source would get honorary mention at LinuxWorld, but I don’t care about that.

What I’m blogging about is the fact that I’m already seeing blogs and press pieces that clump GridSQL in with Oracle Real Application Clusters (RAC). What’s the problem? There is a cluster involved and both products use a cluster so they must be birds of a feather.

Po-tay-toe / Po-tah-toe
Oracle Real Application Clusters is a shared-disk architecture. This GridSQL product is yet another shared-nothing implementation as is IBM’s DB2 UDB EEE (cousin of SP2). Mainframe DB2 is shared-everything of course. Also in the shared-nothing camp are: Teradata, Informix XPS, Microsoft SQL Server with Distributed Partitioned Views (Egad!), Sybase Navigation Server (there’s a blast from the past), Greenplum and the hardware niche guys like DATAllegro and Netezza. Ingres r3 takes a shared everything approach like Oracle RAC and mainframe DB2, but I’m not sure what those folks are up to these days.

Everyone Else Is Doing It
Simply put, the fastest way to get a clustered database out the door is to implement shared-nothing. Take your regular database engine, throw some replication and data shipping in there and poof-you have a shared nothing database. I think everyone got the memo back in 1986 when Michael Stonebraker put his foot down and said that shared nothing was the way to go-in spite of the fact that shared nothing architectures include the necessary evil of data shipping. That Stonebraker paper was written a long time ago and most of the data points in it are OLTP-minded. For instance, I’ll quote Stonebraker:

Consider the number of messages which an SN [shared nothing] system must incur in a typical high transaction processing environment. The example consists of a data base with N objects subject to a load consistin entirely of transactions containing exactly k commands, each affecting only one record. (For TP1 the value of k is 4). For any partitioning of the data base, these k commands remain single-site commands. Suppose that there exists a partitioning of the data base into non-overlapping collections of objects such that all transactions are locally sufficient [WONG83]. Such a data base problem will be termed delightful. Most data base applications are nearly delightful. For example, the TP1 in [ANON84] has 85% delightful transactions.

Using TP1 (a batch mode ATM transaction workload) as a case in point for the claim that most applications will get along nicely (thus the term delightful) in a shared-nothing database! Wow, that was then and this is now. Folks, today’s applications are built on large numbers of tables and complex joins. The reason shared-nothing is nothing like RAC is because instead of only shipping functions (or tasks) and lock messages to the clustered nodes, as is the case with RAC, shared-nothing requires the shipping of data. And as soon as you have a problem with too much data shipping you are required to reload and repartition your database to mitigate the problem. Try getting your partitioning right with an application that has 1,000 tables and 1,300 indexes. Ever chew on crushed glass?

OK, let me try it this way. Let’s say shared-nothing is in fact the best approach for data warehousing. Greenplum puts it this way :

Most of today’s general-purpose relational database management systems are designed for Online Transaction Processing (OLTP) applications. By default, business intelligence applications have inherited this less than optimal architecture. The reality is that BI workloads are fundamentally different from OLTP transaction workloads and therefore require a profoundly different architecture.

So in this view it is one or the other. DSS or OLTP, you choose.

So humor me for a moment. Let’s say for instance that a shared-nothing architecture product on the same hardware outperforms RAC by a flat 50% across all measurements. Nifty! Now let me ask, where is your OLTP? If some shared-nothing product does in fact out perform RAC for data warehousing, that benefit gets canceled out by the fact that the same shared-nothing architecture cannot do OLTP at all. At least not ERP. I won’t even touch the fact that most ERP apps are a bit of an amalgam of both OLTP and DSS-style accesses.

If anyone was going to have phenomenal success with shared nothing it would be IBM with DB2 UDB EEE given its long heritage (SP2) of shared nothing. What do the top 10 audited clustered TPC-H results tell us?

1. 100GB Scale. Oracle chooses not to put much effort in this scale of the benchmark. Neither does IBM really since their last entry there was a 2003 result.
2. 300GB Scale. Of the top 10, Oracle holds spots 1-6. In fact, Oracle’s RAC result in the 6^th position has stood since September 2005 so IBM’s shared-nothing product has had ample time to trump at least that result. Instead, the most recent DB2 result at this scale was in 2004 which oddly didn’t even beat their own prior 2003 result in position 7 of the top ten! How bizarre-especially for a fundamentally better architecture for DSS-style workloads! Oracle has held the top spot in this scale since December 2006 so there is clearly no leap-frogging going on. The 300GB scale is a clear lock-down on the part of RAC.
3. 1000GB Scale. Here again RAC has held the top position for many months-10 months to be exact. That is plenty of time for any product of superior architecture to advance in the list. Oracle holds the number 3 slot at this scale as well.
4. 3000GB Scale. Oracle RAC took the top slot at this scale just 2 months ago with a result that more than doubles the IBM DB2 UDB number at only 15% higher cost!
5. 10000GB Scale. Here IBM hold the top spot-out of only two entries. Two years elapsed from the time Oracle entered this scale of the benchmark and IBM took the lead.

Based upon points 2-4 in the list above it seems shared-nothing is not somehow inherently superior to shared-disk architecture even for the supposed preferred workload which is warehousing!