Kevin Closson's Blog: Platforms, Databases and Storage

Yes, the title of this blog entry is a come-on. I am ever-so-slightly apologetic (smiley face).

This post follows the longest dry spell in my blogging over the last five years. I haven’t posted since early January and thus I am quite overdue for the next installment in my series regarding the Oracle Database 11g Direct NFS clonedb feature. I set out to make the next installment yesterday but before doing so I visited the analytics for my blog readership to see what’s been happening. I discovered that essentially nobody comes to this blog through Exadata related search terms anymore. That surprised me. Indeed, for the first—what—two or so years after Exadata went into general availability the first page worth of Google search results always included some of my posts. I can’t find any of my Exadata posts in the first several pages Google spoon-feeds me now when I google “Exadata.” This isn’t a wounded-soul post. I do have a point to make. Humor me for a moment while I show the top twenty search terms that have directed readers to my blog since January 1, 2011.

So, as far as search terms go there seems to be a lack of traffic coming to this site for Exadata-related information. The page views for my Exadata posts are high, but the search terms are lightly-weighted. This means folks generally read Exadata-related material here after being directed for a non-related search term. Oh well. I’d ordinarily say, “so what.” However, it is unbelievable to me how many people ask me questions each and every day that would be unnecessary if not for a quick read of one of the entries I posted before Oracle Open World 2010. That post, entitled Seven Fundamentals Everyone Should Know Before Attending Openworld 2010 Sessions might be better named You Really Need to Know This Little Bit About Exadata Before Anyone Else Tries to Tell You Anything About Exadata. Folks, if you get a moment and you care at all about Exadata, please do read that short blog entry. It will enhance your experience with your sales folks or any other such Exadata advocates. Indeed, who wants to be introduced to a technology solution by the folks trying to sell it to you. Now, don’t get me wrong. I’m not saying Exadata sales folks are prone to offering misinformation. What I’m trying to say is your interaction with sales folks will be enhanced if you don’t find yourself in such remedial space as the very definition of the product and its most basic fundamentals. That leads me to point out some of the folks who have taken the helm from me where  Exadata blog content is concerned.

Oaktable Network Members Booting Up Exadata Blogging
Fellow Oaktable Network member Kerry Osborne blogs about Exadata, in addition to his current efforts to write a book on the topic. I’ve seen the content of his book in my role as Technical Editor. I think you will all find it a must-read regarding Exadata because it is shaping up to be a very, very good book. I have the utmost of respect for fellow Oaktable Network members like Kerry. In addition to Kerry, Fritz Hoogland (a recent addition to the Oaktable Network) is also producing helpful Exadata-related content.  Oracle’s Uwe Hesse blogs frequently about Exadata-related matters as well. So, there, I’ve pointed out the places people graze for Exadata content these days. But I can’t stop there.

We Believe the Oracle Data Sheets
The content I’ve seen in blogs seems to mostly confirm the performance claims stated in Oracle Data Sheet materials. Let me put it another way. We all know the latest Exadata table/index scan rates (e.g., 25 GB/s HDD full rack or 70GB/s combined Flash + HDD).  We’ve seen the Data Sheets and we believe the cited throughput numbers. I have an idea—but first let me put on my sarcasm hat.  I’m going to predict that the next person to blog about Exadata will start out by blogging something very close to the following:

My big_favorite_table has many columns and a bazillion rows. On disk it requires 200 gigabytes of storage but with mirroring it takes up 400 gigabytes. When I run the following query—even without Exadata Smart Flash Cache—it only takes eight seconds on my full-rack Exadata configuration to get the result:

 
SQL> select count(*) from big_favorite_table where pk_number < 0;
COUNT(*)
----------
0

Don’t get me wrong. It is important for folks to validate the Data Sheet numbers with their own personal testing. But folks, please, we believe the light-scan rates are what the marketing literature states.  I’m probably not alone in my desire to see blogs on users’ experience in solving particularly complex analytical data analysis problems involving vast amounts of data stored in Exadata. That sort of blogging is where social networking truly ads value—you know, going “beyond the Data Sheet.”

In Closing
So what does all this have to do with the infrequent nature of my blogging? Well, I’ll just have to leave that for a future entry. And, no, the Data Sheets on Exadata Database Machine are not inaccurate.

In Part I of my series on Oracle Database 11g Release 2 Direct NFS Clonedb, I offered short videos with a presentation and a demonstration of the feature. I received a significant amount of email which essentially asked the same few questions. So, instead of answering a bunch of email individually, I’ll address the questions here:

Q. Why is your demonstration modeled around cloning an RMAN backup stored in a file system? All Oracle customers use ASM!
A. I’ve put out Part I as an introduction to the technology. I’ll have more on ASM later. I’m certain that not all customers use ASM. Some might even comment on this post accordingly.

Q. What are the NFS server requirements for the CLONE_FILE_CREATE_DEST?
A. Any NFS mount that supports Oracle Database 11g Direct NFS. Now, this is a bit tricky. Since we are talking about test and development instances I’m not convinced it has to be a commercial-grade filer. After all, the only data that will be stored on NFS with this model is the changed block files (COW) and any new datafiles the test/development instance creates. I have tested on a simple HP Proliant storage server running Linux and exporting NFS shares, but that shouldn’t be misconstrued as a support statement.

Q. What is the My Oracle Support note number mentioned in the Part I?
A. The MOS note is 1210656.1. Keep an eye out for it.

Q. Where can I get the clonedb.pl script?
A. Once the MOS note is online the perl script will be available there. It is just a script that automates a few important tasks and generates SQL (very helpful by the way). I’ll offer a copy at the following link: clonedb.pl

Note, this copy of the script is suitable for clonedb usage with production 11.2.0.2 Oracle. After the performance patch (10403302) is applied this rendition of the script will not work. With that performance patch, the clonedb instance needs to boot with the new init.ora parameter clonedb set to true. The new script will generate the requisite text into the auto-generated clone init.ora.

Q. Does the RMAN backup need to be stored in an NFS mount?
A. No. The copy-on-write/thin-provisioning aspect of the feature is implemented in libodm.so. For this reason the CLONE_FILE_CREATE_DEST assignment needs to be to an NFS mount. That RMAN backup can be elsewhere. More on that later.

Q. What about hot RMAN backups and incremental backups?
A. As I mentioned in Part I, I’ll be going into more detail about such topics as how the snapshot features of commercial NFS filers can augment the Oracle Database 11g Direct NFS clonedb feature. I’ll go over hot backups in that post.

Summary
Folks, thus far the intent was to get introductory materials out so we can all end 2010 thinking about a new way to do something old. I’ve left questions unanswered because, after all, we are only at Part I (and a half) in the series.

Database Clones Without Storage Snapshot/Clone Technology? Yes, Of Course! You Knew That, Didn’t you?

Oracle Database 11g Release 2 has a bit of a “stealth feature” that few are aware of. The feature is called clonedb which is functionality built into Oracle Database 11g Direct NFS (DNFS). The best way to explain this feature is to pose a short list of questions:

1. Do you have NFS mounts?
2. Do you have DNFS enabled?
3. Do you have an RMAN backup?
4. Do you want to quickly and simply provision fully read/write database clones for development/test purposes?
5. Do you generally provision development/test instances using your vendor NFS snapshot/clone technology?
6. Do you find it too cumbersome to set up development/test clones using your vendor snapshot/clone technology?

If you say yes to most of these then you’ll appreciate the clonedb feature.

Database Clones Without Storage Snapshot/Clone Technology?
This is Part I in the series so at this stage I’ll clearly point out that with Oracle Database 11g Direct NFS clonedb functionality you can create a fully read/write clone database without storage snapshots or clones. Moreover, the clonedb feature is a thin-provisioning approach.

I could type a lot more words about this new feature, but this is a Part I blog entry and since I have a video presentation introducing the feature I’ll just offer a link to it:

• Video Power Point Presentation. A Short Introduction to Oracle Database 11g Release 2 Direct NFS Clonedb Feature

Summary
I’m excited about this feature. In terms of administrative effort, it is by far the easiest way to provision clones for development/test instances that I am aware of. In my assessment it is simple, stable and it performs. I don’t get to say that as often as I’d like to about technology.

Link to Part I (and a half)

Since the release of Oracle Database 11g I have made a few posts about Oracle NUMA awareness and the _enable NUMA_support parameter. There is an index of most of those posts here.

This is a really short blog entry about a little-known fact about the Oracle Database 11g Release 2 (11.2.0.2) default value for the _enable_NUMA_support initialization parameter in the Linux x86_64 port. The following is the if-then-else logic. There aren’t many initialization parameters (that I know of) that have so much logic around the default assignment.

At instance boot time, the booting foreground process performs “discovery” to see if there are Exadata Storage Servers available. If you strace instance startup you’ll see the following:

open(“/etc/oracle/cell/network-config/cellinit.ora”, O_RDONLY) = -1 ENOENT (No such file or directory)

Also at boot time the numa libraries are dynamically linked and API calls are used to determine how many NUMA nodes there are. If there are more than 4 NUMA nodes and Exadata Storage is discovered the _enable_NUMA_support parameter is set to TRUE.

I have systems that attach to both Exadata and NFS storage at the same time. I have databases that reside entirely in both storage types as well. For maintenance reasons I needed to sever away the Exadata storage. That’s why the above discovery call suffered ENOENT. That changed my default setting for _enable_NUMA_support and  in doing so my performance numbers changed dramatically because I was not explicitly setting _enable_NUMA_support = TRUE on the Sun x4800 system I was testing. The Sun x4800 is 8-socket Nehalem EX and 8-socket EX is something you don’t want to do without Oracle NUMA awareness. Well, at least not if the instance will be running on all processors.

I doubt any of you would ever run into this, but I thought it was worth a blog entry.

In part I of my recent blog series on Linux hugepages and modern Oracle releases I closed the post by saying that future installments would materialize if I found any pitfalls. I don’t like to blog about bugs, but in cases where there is little material on the matter provided elsewhere I think it adds value. First, however, I’d like to offer links to parts I and II in the series:

• Configuring Linux Hugepages for Oracle Database Is Just Too Difficult! Isn’t It? Part – I.
• Configuring Linux Hugepages for Oracle Database Is Just Too Difficult! Isn’t It? Part – II.

The pitfall I’d like to bring to readers’ attention is a situation that can arise in the case where the Oracle Database 11g Release 2 11.2.0.2 parameter use_large_pages is set to “only” thus forcing the instance to either successfully allocate all shared memory from the hugepages pool or fail to boot. As I pointed out in parts I and II this is a great feature. However, after an instance is booted it stands to reason that other processes (e.g., Oracle instances) may in fact use hugepages thus drawing down the amount of free hugepages. In fact, it stands to reason that other uses of hugepages could totally deplete the hugepages pool.

So what happens to a running instance that successfully allocated its shared memory from the hugepages pool and hugepages are later externally drawn down? The answer is nothing. An instance can plod along just fine after instance startup even if hugepages continue to get drawn down to the point of total depletion. But is that the end of the story?

What Goes Up, Must (be able to) Come Down
OK, so for anyone that finds themselves in a situation where an instance is up and happy but HugePages_Free is zero the following is what to expect:

$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Wed Sep 29 17:32:32 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL>
SQL> HOST grep -i huge /proc/meminfo
HugePages_Total:  4663
HugePages_Free:      0
HugePages_Rsvd:     10
Hugepagesize:     2048 kB

SQL> shutdown immediate
ORA-01034: ORACLE not available
ORA-27102: out of memory
Linux-x86_64 Error: 12: Cannot allocate memory
Additional information: 1
Additional information: 6422533
SQL>

Pay particular attention to the fact that sqlplus is telling us that it is attached to an idle instance! I assure you, this is erroneous. The instance is indeed up.

Yes, this is bug 10159556 (I filed it for what it is worth). The solution is to have ample hugepages as opposed to precisely enough. Note, in another shell a privileged user can dynamically allocate more hugepages (even a single hugepage) and the instance will be then able to be shutdown cleanly. As an aside, an instance in this situation can be shutdown with abort. I don’t aim to insinuate that this is some sort of zombie instance that will not go away.

BLOG UPDATE (09-JUN-2015): The link to the downloadable SLB tar archive has been updated below.

BLOG UPDATE (23-NOV-2010): Please note, I have updated the SLB tarball to address some irregularities found by readers during their testing. I need to update this post further as the numbers in the boxes below are begat of the previous SLB and are therefore no longer relevant.

A few years ago I was working a series on AMD Opteron, Hypertransport, NUMA and what that meant to Oracle. Along the way I put out the Silly Little Benchmark (SLB) as discussed in my post entitled Oracle on Opteron with Linux-The NUMA Angle (Part III). Introducing The Silly Little Benchmark. I’ve had a lot of requests recently for updated copies of SLB. If you are looking for SLB, please download it at the following link:

SLB (Silly Little Benchmark) Tar Archive 09 June 2015

New SLB Kit
I’d like to point out a couple of things about the new SLB tar archive.

1. The code has changed so results from this kit are not comparable to prior kits.
2. The kit now performs 30 seconds of random memory reads followed by 30 seconds of random memory writes.
3. The kit includes a wrapper script called runit.sh that runs SLB processes each with 512MB physical memory. The argument to runit.sh is a loop control of how many SLB processes to run upon each invocation of the benchmark.
4. The kit includes a README that shows how to compile the kit and also offers further explanation of item #3 in this list.

Previous SLB Blog Posts
The following are a few pointers to prior content that dealt with SLB in one way or the other.

• “Feel” Your Processor Cache. Oracle Does. Part I.
• Linux Bogomips, Or Is That Bogusmips. Part – I
• Oracle on Opteron with Linux-The NUMA Angle (Part VI). Introducing Cyclops.
• Kevin Closson’s Silly Little Benchmark is Silly Fast on Nehalem (Greg Rahn)

Some Recent SLB Results
The following are a few updated SLB results using the new kit.

The first recent result is from a 2s Westmere EP (Xeon 5600) system. I passed “6” into runit.sh to see what one socket’s worth of performance looks like.

$ ./runit.sh 6
Users: 6
Buffer area size  524288 KB  ADDR 0x2AD3ABA02010
Waiting for semaphore...
Total wops 239999724  Secs 30.1  Avg nsec/op 125 TPUT ops/sec 7978921.92
Total rops 399999540  Secs 30.1  Avg nsec/op 75 TPUT ops/sec 13300920.06
Buffer area size  524288 KB  ADDR 0x2B56949FA010
Waiting for semaphore...
Total wops 379999563  Secs 30.6  Avg nsec/op 80 TPUT ops/sec 12416873.45
Total rops 619999287  Secs 30.1  Avg nsec/op 48 TPUT ops/sec 20623014.23
Buffer area size  524288 KB  ADDR 0x2AAF02293010
Waiting for semaphore...
Total wops 239999724  Secs 30.2  Avg nsec/op 125 TPUT ops/sec 7939962.30
Total rops 459999471  Secs 30.1  Avg nsec/op 65 TPUT ops/sec 15257316.97
Buffer area size  524288 KB  ADDR 0x2AFADC4C9010
Waiting for semaphore...
Total wops 379999563  Secs 31.1  Avg nsec/op 81 TPUT ops/sec 12216920.78
Total rops 599999310  Secs 30.2  Avg nsec/op 50 TPUT ops/sec 19873638.29
Buffer area size  524288 KB  ADDR 0x2AEB7B430010
Waiting for semaphore...
Total wops 379999563  Secs 31.2  Avg nsec/op 82 TPUT ops/sec 12174302.22
Total rops 599999310  Secs 30.1  Avg nsec/op 50 TPUT ops/sec 19941302.38
Buffer area size  524288 KB  ADDR 0x2B6A80F63010
Waiting for semaphore...
Total wops 239999724  Secs 30.2  Avg nsec/op 125 TPUT ops/sec 7938049.67
Total rops 479999448  Secs 31.0  Avg nsec/op 64 TPUT ops/sec 15474601.85

Test Summary: Total wops 1859997861  Total rops  3159996366 Runtime seconds: 31 wops/s 59615316 rops/s 101281934

That was a bit bumpy. I re-ran it with affinity (taskset) and collected the following results:

$ taskset -pc 0-5 $$
pid 15320's current affinity list: 0-23
pid 15320's new affinity list: 0-5
$ sh ./runit.sh 6
Users: 6
Buffer area size  524288 KB  ADDR 0x2B28784C4010
Waiting for semaphore...
Total wops 379999563  Secs 31.0  Avg nsec/op 81 TPUT ops/sec 12238869.35
Total rops 499999425  Secs 30.2  Avg nsec/op 60 TPUT ops/sec 16580155.46
Buffer area size  524288 KB  ADDR 0x2B1241B67010
Waiting for semaphore...
Total wops 379999563  Secs 31.4  Avg nsec/op 82 TPUT ops/sec 12118541.38
Total rops 499999425  Secs 30.4  Avg nsec/op 60 TPUT ops/sec 16446948.61
Buffer area size  524288 KB  ADDR 0x2B4893BFD010
Waiting for semaphore...
Total wops 379999563  Secs 31.3  Avg nsec/op 82 TPUT ops/sec 12136661.49
Total rops 499999425  Secs 30.5  Avg nsec/op 60 TPUT ops/sec 16403891.60
Buffer area size  524288 KB  ADDR 0x2B94FD5AA010
Waiting for semaphore...
Total wops 379999563  Secs 31.0  Avg nsec/op 81 TPUT ops/sec 12272774.98
Total rops 519999402  Secs 30.9  Avg nsec/op 59 TPUT ops/sec 16820126.30
Buffer area size  524288 KB  ADDR 0x2B0D09454010
Waiting for semaphore...
Total wops 379999563  Secs 31.4  Avg nsec/op 82 TPUT ops/sec 12107983.29
Total rops 499999425  Secs 30.5  Avg nsec/op 61 TPUT ops/sec 16368642.72
Buffer area size  524288 KB  ADDR 0x2AAD4513E010
Waiting for semaphore...
Total wops 379999563  Secs 31.4  Avg nsec/op 82 TPUT ops/sec 12097160.14
Total rops 499999425  Secs 30.6  Avg nsec/op 61 TPUT ops/sec 16354937.65

Test Summary: Total wops 2279997378  Total rops  3019996527 Runtime seconds: 31 wops/s 72611381 rops/s 96178233

That result was a lot smoother and the wops (write ops per second) improved 22%. The rops, on the other hand, suffered a small 5% degredation. I’ll blog further about that in another post.

Other Results?
It sure would be nice if folks could try this out on other platforms. I’ve compiled and run it on Power6 so I know that it works on AIX 5L.

After my recent blog entry entitled   Configuring Linux Hugepages for Oracle Is Just Too Difficult! Isnt It? Part I, I engaged in a couple of email threads and a thread on oracle-l about when to employ hugepages.  In those exchanges I was amazed to find that it is still a borderline issue for folks. I feel it is very cut and dried and thus I prepared the following guidelines that more or less spell it out.

1. Reasons for Using Hugepages
   1. Use hugepages if OLTP or ERP. Full stop.
   2. Use hugepages if DW/BI with large numbers of dedicated connections or a large SGA. Full stop.
   3. Use hugepages if you don’t like the amount of memory page tables are costing you (/proc/meminfo). Full stop.
2. SGA Memory Management Models
   1. AMM does not support hugepages. Full stop.
   2. ASMM supports hugepages.
3. Instance Type
   1. ASM uses AMM by default. ASM instances do not need hugepages. Full stop.
   2. All non-ASM instances should be considered candidate for hugepages. See 1.1->1.3 above.
4. Configuration
   1. Limits (multiple layers)
      1. /etc/security/limits.conf establishes limits for hugepages for processes. Note, setting these values does not pre-allocate any resources.
      2. Ulimit also establishes hugepages limits for processes.
5. Allocation
   1. /etc/sysctl.conf vm.nr_hugepages allocates memory to the hugepages pool.
6. Sizing
   1. Read MOS 401749.1 for information on tools available to aid in the configuration of vm/nr_hugepages

To make the point of how urgently  Oracle DBAs need to qualify their situation against list items 1.1 through 1.3 above, please consider the following quote from an internal email I received. The email is real and the screen output came from a real customer system. Yes, 120+ gigabytes of memory wasted in page tables. Fact is often stranger than fiction!

And here is an example of kernel pagetables usage, with a 24GB SGA, and 6000+ connections ..  with no hugepages in use .

# grep PageT /proc/meminfo

PageTables:   123731372 kB

Link to Part III in this series:

Configuring Linux Hugepages for Oracle Database is Just Too Difficult! Isn’t It? Part – III.

I recommend a visit to David Kanter’s Real World Technologies site. David recently published an excellent article on Intel Sandy Bridge architecture.

Intel Sandy Bridge Architecture

Pay close attention to the diagram on page 2. You’ll see this architecture includes an on-die (uncore) PCI controller.

And here I am just chomping at the bits for Westmere EX!

David, sorry I wasn’t able to meet up with you at OOW 2010.

Many folks have been wondering about when or if Oracle will integrate servers based on the Intel Xeon 7500 (Nehalem EX) family of processors. As of this morning, there are two freshly-announced packaging options that include:

• Exadata Database Machine X2-8 HP Full Rack
• Exadata Database Machine X2-8 HC Full Rack

Both of these configuration options offer two 8-socket Xeon 7500 systems each with 64 processor cores and 1 TB of physical memory. Also included is support for 8 paths of 10GbE connectivity.

The HP/HC identifiers stand for High Performance and High Capacity respectively and relate to the hard drive options available in the Exadata Storage Servers. The High Performance option is based on the 15,000 RPM 600 GB SAS drives and the High Capacity storage option is based on 2TB 7,200 RPM SAS drives. No SATA option.

The two 8-socket servers are attached to 14 Exadata Storage Servers each based on the Xeon 5600 (Westmere EP) family of Intel processors with 12 hard drives and the same complement of Exadata Smart Flash Cache as was available in the previous generation of X2-2 offerings (384 GB per Storage Server).

I shouldn’t think folks are too surprised that Xeon 7500-based servers have found their way into Exadata packaging. Oracle has been shipping the Sun Fire x4800 for some time now as I blogged in my post entitled Will Oracle Ever Release Sun Servers Based On Westmere EP and Nehalem EX Processors? Yes. However, folks may be (pleasantly) surprised to hear that the Exadata Database Machine X2-8 will offer support for either Solaris 11 Express or Oracle Linux 5 Update 5 with the Unbreakable Enterprise Linux kernel.

The OOW folks informed me that they needed to move our session to a different room–again. So, if you are interested here are the new details:

ID#: S315110
Title: Optimizing Servers for Oracle Database Performance
Track: Database
Date: 20-SEP-10
Time: 17:00 – 18:00
Venue: Moscone South
Room: Rm 102

BLOG UPDATE: I should have peeked at my blog aggregator before posting this. I just found that my friend Greg Rahn posted about the same content on his blog earlier today. Hmmm.…plagerism!

Oracle Database 11g Release 2 Patchset 1 (11.2.0.2 Part Number E15732-03) is available as of today for x86 and x86_64 Linux as per My Oracle Support. This is not a blog post with a simple announcement of the software availability. I’d like to point out sometime related to this Patchset that I did not know until quite recently. I don’t apply Patchsets very often since having joined Oracle so I learned a few new things about patch application particularly as it pertains to 11.2.0.2.

Read This Before Touching 11.2.0.2!

I recommend reading MOS Note 1189783.1 – Important Changes to Oracle Database Patch Sets Starting With 11.2.0.2. There are two key topics this MOS note explains quite well:

• The reason behind why the 11.2.0.2 Patchset download for x86_64 is 4.9 Gigabytes in size
• More clarity on the concept of an “out-of-place upgrade”

I’ll wish I had read MOS note 1189783.1 before I trudged headlong into my first 11.2.0.1->11.2.0.2 upgrade effort!