Kevin Closson's Blog: Platforms, Databases and Storage

Yes, shamelessly, I’m still groveling for more votes. My Suggest-A-Session vote count is at a modest 45! Come on folks, I know we can do better than that. If you don’t get there and vote I fear there is no way for me to get a podium for this topic unless you all collectively petition the executives to relinquish one of their speaking slots to me…and that simply doesn’t happen!

https://kevinclosson.wordpress.com/2010/06/04/do-it-yourself-exadata-level-performance-really/

or

https://mix.oracle.com/oow10/proposals/10589-do-it-yourself-exadata-level-performance

I sometimes wonder if folks think I prefer poorly-tuned SQL. After all, I like hardware and everyone knows how effective it is to throw hardware at a poor query plan.

I think everyone in the world has already blogged it but I can’t resist the me-too excitement. On June 10 my fellow Oak Table Network members Jonathan Lewis and Kyle Hailey are offering a unique SQL tuning presentation. Read about it here:

The Ultimate SQL Tune-Off

But, honestly, does anyone really believe there is enough hardware in existence to throw at some of the more gruesome, poorly-tuned SQL I’ve seen? No. Bad SQL (plans) can cost hundreds of thousands percent increase in cost. No matter how fast your hardware is it certainly can’t mitigate potentially tens of millions more physical I/O than is really necessary. These guys know how to help you identify and fix such crippling SQL problems. But so does SQL Tuning Advisor.

What Does This Have To Do With Exadata?
So, no, not even highly-optimized solutions like Exadata and the Sun Oracle Database Machine are able to obviate SQL tuning. The Database Machine includes all the power of Oracle Database 11g and as we pointed out in the Winter Corporation paper covering early Exadata performance we routinely rely on SQL Tuning Advisor.

No, there is no such thing as sufficiently powerful hardware to handle SQL problems. Let’s see what we can learn in the Lewis v. Hailey webcast.

Oracle Mix is hosting “Suggest-a-Session” for OOW 2010. I thought that sounded like fun so I did the following…see what you think:

Do-It-Yourself Exadata-level Performance?

Since my blog has been essentially dormant for 2 months I expect about 43 click-throughs and no more than 42 votes 🙂

And, yes, I will kick up my blogging again soon and may even explain why my blogging has ceased for these 2 months…

I’ve been stranded in Europe for 4 days and the situation persists!  Needless to say I haven’t been thinking that much about blogging 😦

I do have a post nearly ready to go about booting 4s48c Opteron 6100 systems with _enable_NUMA_support set to TRUE. There are some caveats, and some very significant benefits as well. I’ll post that as soon as my situation improves…

By the way, no I have not taken the time to learn the pronunciation of the Volcano. I’ve opted for the military moniker: E15.  🙂

Earlier this week I was taking a gander at what in-bound traffic my site was getting. One referrer stood out in my initial perusal. While I don’t know Gavin Soorma, just a bit of reading on his blog led me to conclude that this site is a jewel worth visiting:

Gavin Soorma

I received several emails from readers reporting that AnandTech moved the page I referred to in yesterday’s post about Intel Xeon 5600 (Westmere EP) / AMD Opteron 6100 (Magny-Cours). I fixed the link on that post so feel free to click through and get to the article there if you wish.

This is just a quick blog entry to point out that I’ve renamed one of my index pages to include categorization of Oracle Database 11g Database File System (DBFS) related topics. The newly-named page can be found at the following URL:

Kevin Closson Blog: DBFS / CFS / NFS / ASM Related Topics

The freshly indexed posts I’ve added to that page are:

• Oracle Database 11g Database File System (DBFS). Common DBFS Problems and Solutions. Part II.
• Oracle Database 11g Database File System (DBFS). Common DBFS Problems and Solutions. Part I.
• Oracle Database 11g Database File System (DBFS) Is Not A Popular Topic. Yet.
• Oracle Database File System (DBFS). It’s Not An “Exadata Feature.” – Part I.

I routinely get asked which blogs I frequently read. I list some of them in my blogroll. It just dawned on me that even though I have Tim Hall’s ORACLE-BASE blog listed in my blogroll I should make a special note that Tim’s blog is a really, really helpful resource.

There are a lot of blogs I read that I don’t mention. For obvious reasons I don’t currently want to link to Oracle’s competitors’ blogs. I wouldn’t want them to get a any boost in readership for free now, would I?

One tricky thing to figure out is how to treat Oracle’s “coopetitors” if you will. Now that Oracle owns a hardware company the lines are really fuzzy. I think it use to be that only Microsoft fell squarely into both the competitor and (quasi) partner space since both Microsoft and Oracle were software companies and both brought RDBMS products to market but neither were hardware companies (forgetting for the moment the Xbox). I refer to Microsoft as (quasi) partner of Oracle because Oracle offers products on Windows and unless I’m particularly naïve Microsoft takes no position directly against customers running Oracle on Windows. Indeed, others in the same scenario have played the “if you load product XYZ we won’t support your OS” trick. Now that Oracle is a hardware and software company I have to get direction from corporate on what acronyms (e.g., IBM, HP, etc), when spoken, will find me getting my mouth washed out with soap.

I’m finding that one of the more confusing aspects of Oracle Database 11g Database File System (DBFS) for folks to understand is the difference between access and administration permissions.

The Oracle Databse 11g Database File System (DBFS) documentation discusses the “fuse” group, the allow_other DBFS mount option and the /etc/fuse.conf file but all this information doesn’t draw a clear distinction between administrative permissions and access permissions.

• Administrative Permissions. All mention the fuse group (and any mention of permissions settings on /dev/fuse) is related to permissions necessary to administer the file system (e.g., mount and unmount file systems, etc)

• Access Permissions. The allow_root and allow_other dbfs_client mount options are related to permissions to access the mounted file systems and contents of the file systems.

If the allow_other mount option is passed—when using dbfs_client to mount a file system—then other, ordinary Linux users can access file system contents as per the normal Linux file system permissions. Consider the following example. I’ll show that I have a DBFS file system mounted at /data. Next, as root, I’ll create a file called joe.txt and then modify its ownership to joe and group joe:

# mount | grep /data
dbfs on /data type fuse (rw,nosuid,nodev,max_read=1048576,default_permissions,allow_other,user=oracle)
# ls -ld /data /data/FS1 /data/FS1/joe.txt
drwxr-xr-x  4 root   root 0 Feb 10 17:30 /data
drwxrwxrwx 76 oracle dba  0 Feb 10 17:27 /data/FS1
-rw-r--r--  1 joe    joe  0 Feb 10 17:27 /data/FS1/joe.txt

Next, I’ll switch user (with login) to the user joe and list joe’s file. It is owned by joe and all directory hierarchy permissions back to /data permit joe’s access, so it should be no problem:

# su - joe
$
$ id
uid=1001(joe) gid=1005(joe) groups=1005(joe)
$ ls -l /data/FS1/joe.txt
ls: /data/FS1/joe.txt: Permission denied

OK, so something is obviously wrong. Yes, there is. It is a known bug (8976604) and there is a very simple work-around. I don’t like blogging about known bugs, but I think someone will likely find this via a Google search. Allow me to explain the work-around

The work-around for bug 8976604 is to supply to pass-through-fuse-options parameter at mount time. Consider the following example:

$ $ORACLE_HOME/bin/dbfs_client dbfs@ --pass-through-fuse-options -o allow_other,direct_io /data < /opt/oracle/dbfs/passwd.txt &
[1] 2931
$ Password:
$ su - joe
Password:
$
$ id
uid=1001(joe) gid=1005(joe) groups=1005(joe)$ ls -l /data/FS1/joe.txt
total 0
-rw-r--r--  1 joe    joe  0 Feb 10 17:27 /data/FS1/joe.txt

There, joe is happy now that he can see the file he owns.

I haven’t made a blog entry in about two weeks but that is not due to of lack of topics. I get a constant flow of email from readers with requested topics that they’d like to see covered in this blog.

I’m speaking at Hotsos Symposium 2010 next month and my presentation consists of a deep dive into a lot of the sorts of topics readers ask me to blog about. I’ve posted the abstract below.  The abstract is also posted at the Hotsos Symposium 2010 speaker page.

According to the speaker schedule I’m presenting in a time slot adjacent to Tom Kyte’s session about PL/SQL. So, if you are one of the 3 or so people who, for one bizarre reason or another, decide not to see Tom, perhaps you can attend my session. We’ll all be able to stretch out as there will be plenty of room 🙂

Here’s the abstract:

Ten Years After Y2K And We Still “Party Like It’s 1999”

Whether you call it “the two-thousands”, the “Ohs”, “The Naughties” or “The Aughts”, the first decade of this millennium is over and it ushered in a significant amount of new technology related to Oracle. Don’t be alarmed, this is not one of those worthless technical chronology presentations. After all, is there anyone who isn’t aware that the decade started with the introduction of Real Application Clusters—and thus the demise of the large, central server—and finished with Oracle acquiring Sun Microsystems? This presentation has nothing to do with any of that! In spite of how much technology has changed, we really do still seem to be stuck in the 1990s. The following is sample of some of the topics I’ll be diving into—deeply.

• We still think a CPU is a CPU.
• We still think memory is memory.
• We still think bulk data loading is a disk-intensive operation.
• We still think direct-attached storage is for “small” systems.
• We still think database==structured and file system==unstructured.
• We still think NUMA is niche technology.
• We still think NFS is a file serving protocol.

And, of course, I’ll be talking a bit about Oracle Exadata Storage Server and the Oracle Database Machine.

In my recent post entitled Oracle Database 11g Database File System (DBFS) Is Not A Popular Topic. Yet., I discussed how I use Google search terms as a way of monitoring what sorts of problems people are running into with new technology such as (DBFS).

This is part one in a series of posts where I’ll offer some tips regarding the more common DBFS-related problems one might encounter in early testing. The first I’ll tackle is “Transport endpoint is not connected.”

Error Text: Transport Endpoint is not connected.
This error string occurs when attempting to access a mounted DBFS file system. The error condition arises when there is no dbfs_client currently executing that is associated with the file system.

In the following boxes I’ll show two examples of how this error can be encountered. First, I’ll show that I have a DBFS file system mounted and that an attempt to change directories to the mount point results in “Transport endpoint is not connected”, such as:

$ mount | grep dbfs
dbfs on /data type fuse (rw,nosuid,nodev,max_read=1048576,default_permissions,allow_other,user=oracle)
$ cd /data
-bash: cd: /data: Transport endpoint is not connected

A simple ps command shows there is no dbfs_client process executing:

$ ps -ef | grep dbfs_client
oracle    1694 21941  0 09:49 pts/11   00:00:00 grep dbfs_client

This error condition is quite simple to rectify. You simply need to restart the dbfs_client process:

$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib
$ export ORACLE_SID=DBFS1
$ 
$ ORACLE_HOME/bin/dbfs_client dbfs@ -o allow_root,direct_io /data < /opt/oracle/dbfs/passwd.txt &
[1] 2501
$ fuse: bad mount point `/data': Transport endpoint is not connected

Sorry, I had to be sneaky in order to show one of the other conditions that raises the error. So, yes, to rectify this error condition you do have to restart dbfs_client, however, you need to first unmount the dead mount. You’ll see in the following box that I use fusermount –u to unmount and then execute dbfs_client and without any further action I can list the contents of the file system.

$ fusermount -u /data
$ $ORACLE_HOME/bin/dbfs_client dbfs@ -o allow_root,direct_io /data < /opt/oracle/dbfs/passwd.txt &
[1] 2931
$ Password:

$ ls -l /data/FS1/TEST
total 0
-rw-r--r-- 1 oracle oinstall 0 Dec 30 13:39 511b
-rw-r--r-- 1 root root 0 Dec 30 13:40 test.out

Dead Mount, Living DBFS Instance.
If you look in the boxes above you’ll notice that dbfs_client was not executing (the real root of the problem) but the instance was running. Since dbfs_client is an OCI program all I had to do was kill it to create this scenario. If, however, you suffer this error while dbfs_client is executing then I recommend involving support because that would be a bug.

I find that keeping my finger on the blogosphere pulse is a good way to ascertain the adoption rate of new Oracle features such as Oracle Database 11g Database File System (DBFS).  I see that Tim Hall has posted DBFS content in his typically excellent style!

I have a few posts on the topic of DBFS. Amongst the following list is a post (“Hidden Content?”) that will direct you to a webcast I delivered to IOUG Exadata Special Interest Group. The webcast is very informative because I introduce the concept of “injecting” files into the file system. Injecting? Yes, imagine transferring files into an Ext3 file system on a remote host without that file system even being mounted, anywhere! Cool technology and very efficient.

• Staging Data For ETL/ELT? Flat Files Appear Magically! No, Load Time Starts With Transfer Time.
• Something to Ponder? What Sort of Powerful Offering Could a Filesystem in Userspace Be?
• Oracle Database File System (DBFS). It’s Not An “Exadata Feature.” – Part I.
• Oracle Database File System (DBFS) on Exadata Storage Server. Hidden Content?

Google Search Terms
Another web aspect I monitor when it comes to new Oracle features is what search engine terms are being thrown at Google. For example, the following DBFS-related searches are likely search terms for folks that are testing the water with DBFS and/or suffering any problems getting it to work:

I aim to post a few blog entries with troubleshooting tips for some of the more common DBFS-related problems that customers might hit. The first entry I’ll make will cover the more common “Transport endpoint is not connected” error string that is returned under certain situations when trying to access DBFS mounts.

So, yes, the title of this post was a come-on. Oracle Database 11g Database File System is a new feature. It takes time for the blogosphere to catch up. But, as I’ve pointed out, there are good, trustworthy, bloggers posting content…like Tim Hall.