So now that the first boring blog entry is out of the way, I think it is time to get to the fun stuff. I will try and highlight as many customer success stories as possible, but NDA's make it non-trivial.
A few months ago, I visited a customer that was having significant performance problems on a very large (think 25K ) DSS style Oracle database. We found a number of issues that, once corrected, greatly improved performance the database, and especially for one key batch job. One issue we found was particularly interesting, and I'll detail it here since it is something that could be affecting many customers. It is a relatively simple example of DTrace (nothing earth shattering), but something that would have been exceedingly difficult prior to DTrace—and this truly speaks to the power of DTrace. I'll keep this as brief as possible, so let's get started.
This particular database made heavy usage of Oracle temporary tables (and each of the files is 100GB in size). The customer had already identified that the performance of the temporary tables was most likely a contributor to their overall problem, but diagnosing it further was proving difficult. Of course, the first thing we look at is I/O performance to the file systems and devices that house the temporary tables. I rarely dive in with DTrace from the get-go, but I tend to lean on conventional tools to help me decide how to best apply DTrace. Here is a snippet from iostat(1M) for the relevant devices.
Looking at that iostat(1M), I suppose the I/O performance could be better; but that isn't too shabby (the reads are really affecting our service times—spinning copper ain't fast). However, if I look at the Oracle AWR Report for temp tables in the instance, we can see that Oracle claims our I/O performance isn't even in the ballpark of what iostat(1M) is reporting (TEMP tables only show read I/O latency in AWR).
Well, iostat(1M) is showing only our time from bdev_strategy() to biodone(). We still have a number of layers of software we have to stroll through before we get there (file system, SVM, multi-pathing, etc.). The simplest way to get some idea of what is happening on our way out the box is to use DTrace of course.
Whoa, wait just a second. Something here is definitely not right. This isn't what I expected at all. Shouldn't Oracle be using the directio code path (ufs_directio_write)? The file systems are mounted with forcedirectio and we know that Oracle is using O_DSYNC; then directio should be a no-brainer, right?
Well, one thing I do know is that we can't get directio to a file with holes (let alone full concurrent directio). Why don't we take a quick peek to see if any of the files in question have holes?
Well then, it certainly seems like we may be on to something. Just for fun, let's look at lockstat(1M) (of course implemented as a DTrace consumer) to see if by chance this is causing us any grief in the form of lock contention (something we'll get without full concurrent directio semantics).
# lockstat -D 25 -w sleep 10
[SNIP]
R/W writer blocked by writer: 781 events in 10.066 seconds (78 events/sec)
Youch! Definitely no love here. So what we have is an issue with Oracle temporary tables, sparseness, and directio semantics. According to AskTom (which by the way I love), we see that this is by design.
The recommendation I made here was to create the tablespaces using conventional Oracle mechanisms (not temporary tables), drop the tablespace but retain the underlying storage, and to then create the temporary tablespaces using the REUSE option to the CREATE statement - this avoids the sparse file problem.
Once this was implemented, the batch job runtime dropped to just 45% of its original time (actually, the overall improvement with all recommendations (not detailed here) was that the batch job ran in about 1/5th of the time it previously took). Sweet.
Like I said earlier, this is a trivial example of DTrace in action, but something that would have been pure conjecture and tested by trial and error in the past. Getting to this recommendation took just a short time and was virtually guaranteed to be successful.
So if you are running Oracle and have temporary tablespaces, I would highly recommend trying the has_holes.d script from above to see if you could be impacted by this issue as well.
I wonder if the same happens (no direct io to files with holes) if we use Oracle's option: filesystemio_options = setall. I guess it's the same but ....
I rarely dive in with DTrace from the get-go, but I tend to lean on conventional tools to help me decide how to best apply DTrace.
That's the one thing that I'm glad to see written down somewhere! That's the trap with a tool as powerful as DTrace - everything is possible with it, but it's less than helpful without having an inkling of where to start looking.
Comments
I wonder if the same happens
I wonder if the same happens (no direct io to files with holes) if we use Oracle's option: filesystemio_options = setall. I guess it's the same but ....
I rarely dive in with
I rarely dive in with DTrace from the get-go, but I tend to lean on conventional tools to help me decide how to best apply DTrace.
That's the one thing that I'm glad to see written down somewhere! That's the trap with a tool as powerful as DTrace - everything is possible with it, but it's less than helpful without having an inkling of where to start looking.