Kevin Closson's Blog: Platforms, Databases and Storage

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!