Thanks to a generous gift from Apress, every 10th arrival tomorrow to Oaktable World will win a copy of

Pro Oracle Database 12c Administration

by  Darl Kuhn

schedule for Oaktable WOrld Day 2

khailey Uncategorized

A few vignettes from this mornings Oaktable World.

Kickoff from Mogens Nørgaard  on some of the origins of Oaktable Network.

John Beresniewicz , father of the ASH tree map, talking on ASH analytics

DevOps round table with Jonathan Lewis, Cary Millsap, James Murtagh, Eric Schrock and Kent Graziano

Gwen Shapira talking on Hadoop

Francisco Sanchez talking on cross platform converison

Riyaj Shamsudeen answering questions after his live hacking on RAC with truss pstack wireshark pmap strace presentation

Christian Antognini talking on row chaining and migration

It’s an Oracle World

Crowds waiting outside to get into Oaktable World

Iggy Fernandez comparing a Amazon NoSQL implementation to an equivalent Ebay implementation using an Oracle relational model.

Fabian Pascal talking about some of the short comings, should I say errors, in Codd’s relational model.

Riyaj Shamsudeen live hacking on RAC with truss pstack wireshark pmap strace

Women in Technology (WIT) discussion.

khailey Uncategorized

A few vignettes from this mornings Oaktable World.

Kickoff from Mogens Nørgaard  on some of the origins of Oaktable Network.

Fabian Pascal talking about some of the short comings, should I say errors, in Codd’s relational model.

Iggy Fernandez comparing a Amazon NoSQL implementation to an equivalent Ebay implementation using an Oracle relational model.

Riyaj Shamsudeen live hacking on RAC with truss pstack wireshark pmap strace

Women in Technology (WIT) discussion.

khailey Uncategorized

Here are some talks of interest during Oracle Open World other than Oaktable World  where I’ll be all  Monday and Tuesday of course

Publicly editable version of  OOW talks 

Please feel free to modify the above spread sheet and/or email me with talks to add to this blog post – thanks!

Sunday IOUG talks:

Oracle Open World

khailey Uncategorized

by Matt Hayward

One of the less obvious advantages of database virtualization is a reduction in the read I/O issued against the underlying physical storage (henceforth called “the SAN”) that ultimately stores the data for virtual databases.

In practice, Delphix prevents around 60% of all non-production database I/O* from ever being issued to the SAN with the Delphix cache.

This is possible because Delphix Server accommodates large amounts of RAM**, which is used as an auxiliary cache above and beyond the database buffer cache that resides on the database server.  When the database needs to read some data that is not present in the database buffer cache, an I/O is issued to the Delphix Server. Delphix then checks its own cache, and only passes the I/O request down to the SAN if the necessary data is not already in the Delphix cache.

Delphix cache hits bring dual performance benefits:

• Virtual databases I/O service times for reads are fast: in the range of a few hundred microseconds plus network latency.  These ~1 millisecond latencies are 5-20 times faster than traditional SAN random read access times.
• By serving these I/O requests from Delphix cache, the load on the SAN is reduced

Delphix’s minimum system configuration requires 16 GB of RAM, however most customers configure their Delphix Servers with 64 GB or more.  Thanks to these large caches, Delphix Servers consistently have a 60-70% cache hit ratio.

In the last two years, I’ve collected database I/O statistics from 469 production and non-production Oracle databases across 25 companies in a variety of industries and with diverse applications.  Studying these statistics gives the following findings for this particular sample:

• Non-production databases account for 70% of all databases

Focusing on these non-production databases, which are the first candidates for database virtualization:

• Reads accounted for 87% of database I/O, writes for only 13%.
• Grouped by company, the highest read proportion observed was 97%, the lowest 61%
• With Delphix’s typical 60-70% cache hit ratio, this means full virtualization of non-production environments would eliminate between:
  • 52.2 and 60.9% of total database I/O from the SAN

To give those findings a sense of scope, in the two years I’ve been working at Delphix, eliminating 60% of non-production I/O would amount to eliminating 59 petabytes of I/O.

To put that into perspective, Amazon Elastic Block Store charges $0.10 per million I/O requests.  Assuming an average I/O request size of 8 kilobytes it would cost you around$800,000 to do 59 petabytes of I/O in Amazon EBS.

That’s a truckload of I/O – literally: it takes around 120 milliwatt hours of energy to read 256 megabytes of data from disk, reading 59 petabytes would require around 29 megawatt hours – roughly equivalent to the energy in 2.55 tons of oil (or 25 tons of TNT*** if that’s more your style).

By using inexpensive server RAM as a secondary cache for virtual databases, Delphix can dramatically improve database read I/O performance, and eliminate 50-60% of non-production database I/O from the SAN.  This improves performance of virtual databases, and allows other applications to get more out of a shared SAN.

Footnotes:

* Delphix eliminates around 60% of I/O associated with the operation of non-production databases.  It also eliminates huge amounts of I/O on both production and non-production systems formerly used to create, copy, and restore full backups – although this is not quantified in this blog post.

** The upper bound on RAM assignable the Delphix Server is constrained by limitations of the hypervisor or underlying physical host long before it approaches limits inherent in the Delphix OS.

*** http://en.wikipedia.org/wiki/Tonne_of_oil_equivalent,http://en.wikipedia.org/wiki/Tons_of_TNT

khailey Uncategorized

by Matt Hayward

I’m often surprised at how small the buffer caches allocated to Oracle databases are. When I started as a DBA at Amazon in the early 2000′s, we were moving our Oracle databases to 32-bit Linux.  I shudder to recall all the crazy things we used to do, such as fiddling with the stack address space, to get an SGA over 1.2 GB or so.  It’s alarming to me that these techniques are still documented for Oracle 11gR2 – I hope no one is still doing this – if you are, you have my sympathies.

In 64-bit architectures, it would seem that there is no reason to have such a small buffer cache.  RAM prices have dropped precipitously, the datasets within databases themselves have grown like kudzu. Nonetheless, I still find small Oracle buffer caches wherever I go. The median buffer cache size I observe in our customer base is 1,040 megabytes – which would fit in my 2002 32-bit Linux machine without too much bother.

Here is a histogram of customer buffer cache sizes I’ve encountered since 2011:

The implication of unchecked dataset growth and relatively static buffer cache sizes is that a smaller and smaller proportion of the database is cached.  Here is a histogram showing the percentage of a database that can be cached, from the same customers as above (although here I’ve excluded databases smaller than 64 GB):

How much performance impact would increasing these tiny caches have? It is hard to say with certainty – but there are some clues.  Oracle has a cache advice feature that populates the v$db_cache_advice table.  This feature forecasts the reduction (or increase) in physical reads that would come from growing (or shrinking) the buffer cache.  The table includes forecasts for buffer cache sizes up to around twice the current size.

The results are often shockingly non-linear, here is an example table from a customer database showing the forecast proportion of physical reads as the buffer cache increases to twice the current size of 928 MB:

You can see that at around 1,600 MB of buffer cache physical reads drop off dramatically.  Evidently, the working set of the database would basically fit inside 1,600 MB, but not 1,520 MB. Increasing our buffer cache from 928 to 1520 gets us basically nothing (less than 1% reduction in physical reads), while going from 1,520 to 1,600 would eliminate over 90% of physical reads.

When I look at the output from v$db_cache_advice on customer databases I see a wide range of impacts from doubling the buffer cache, from basically no impact, to eliminating pretty much all read I/O:

It’s hard to know exactly what the working set size of a database will be, and you can see from the table above that the differences between caching nearly the entire working set and the entire working set can be enormous.  As discussed in a prior blog post, reads accounted for 87% of database I/O on the sample I have access to.

Of course, an upper bound on the size of the working set would be the size of the database.

Why wouldn’t you just cache the entire database?

Doing so would eliminate ~87% of database I/O to the SAN in typical cases.

One obvious reason is cost.  Assuming I’m interpreting this price list correctly, the cost difference for a single storage engine in a VMAX 40K with 48 GB of RAM, versus one with 256 GB of RAM, is $222,165 – or $1.04 per megabyte.  It’s not likely you’ll be able to spend a cool million in order to cache your 1 TB database.

Delphix uses cheap server RAM to cache database data.  HP, Cisco, and Dell all offer servers that can house 1 TB or more of RAM.  You can price out a Dell PowerEdge M820 with 32 cores and 1.0 TB or RAM for around $45,000.

Delphix’s cache consolidation feature stretches that server RAM a long way, getting much more benefit than you would if you placed it in the database servers themselves.  Just as Delphix consolidates shared physical storage blocks among the databases it manages, it’s cache also consolidates the blocks in memory.

Consider a typical scenario with a ~1 TB production database, that has several copies for:

• Development
• QA
• Staging
• Production firefighting
• Offloading production reports

If each of these copies were managed by the same Delphix Server with 1 TB of RAM, you would be able to cache the entire working set for all the systems:

As an aside, another nice feature of this kind of caching is that each database benefits from the warm Delphix cache created by the others.

vSphere 5.1 has a 1 TB limit for the RAM available to a single VM. Because Delphix is deployed as a virtual machine, it also inherits this 1 TB limit on its cache size.

This 1 TB limit is not very constraining as it turns out.  72% of databases I see at our customers are 1 TB or less, so this approach can cache the entirety of a substantial portion of all databases out there.

Summary:

A decade ago RAM was expensive and 32 bit architectures (when used) limited Oracle buffer cache sizes.  Despite exponential decreases in the cost of RAM, and the elimination of architectural barriers to assigning more RAM to Oracle, buffer cache sizes remain tiny as a proportion of the total database size.

Delphix’s consolidated cache feature combined with the low cost of server RAM makes it possible to put entire non-production database environments in cache at modest prices.

khailey Uncategorized

khailey Uncategorized

by Matt Hayward

Given a description of gas dynamics and the atmosphere, you would be hard to pressed forecast tornadoes. The term emergence denotes the phenomena of surprising behaviors arising in complex systems.  Modern storage systems are complex, and full of emergent behavior that make forecasting application I/O performance fiendishly difficult.

In collaboration with Kyle Hailey, Adam Leventhal, and others I’ve learned some rules of thumb for how to make accurate I/O performance forecasts.  I’ve also stepped on every rake imaginable along the way. For those of you who also may wish to forecast the storage performance an application will receive, this post summarizes some lessons learned.

When I began evaluating storage performance, I had a naive idea that the process would be like this:

I quickly discovered that the actual process was much more like this:

In going through this process, I’ve compiled a bestiary of performance anomalies you may expect to encounter if you interact with a variety of storage platforms, along with root causes for those anomalies and some explanatory notes. Following that are some approaches for designing I/O simulations or tests that avoid them.

 Bestiary of I/O Performance Anomalies

 Avoiding Anomalies While Testing

Here is a summary of how to avoid these anomalies, with details below:

• Use a real workload if possible
• When simulating, be sure to simulate the actual application workload
• Evaluate latencies using histograms, not averages
• Verify your performance tests give reproducible results
• Run test at the same time as the production application will run, and for sufficiently long durations
• Ensure the test data is similar to what the application will use and produce

Use a real workload if possible. Unfortunately, often this isn’t be possible. For example, you probably won’t be able to determine the exact workload of the month end close for the new ERP system while that system is being architected, which is when you’ll need to design and select the storage.

When you must simulate, be sure to simulate what the application actually does in terms of I/O. This means understanding the read and write mix, I/O sizes and rates, as well as the semantics of the I/O that is issued.  For example: are writes O_SYNC or O_DSYNC, is Direct I/O used?  fio is an amazing tool for performing I/O simulation and tests, it can reproduce most application workloads, has good platform support, and an active development and user community.

When measuring I/O performance, be sure to use histograms to evaluate latencies rather that looking just at averages. Histograms show the existence of anomalies, and can clarify the presence of caches as well as the actual I/O performance that the disks are delivering. See, for example, these two images from an actual test on customer system:

First a sequential read I/O test was run, followed by a random read I/O test. If we looked only at averages, we would have seen a sequential read latency of around 4 ms, quite good. Looking at the histogram distribution however, it is clear we are getting a mix of 10-20 ms disk latencies, and 0.5-2 ms latencies, presumably from cache. In the subsequent random I/O test we see the improbably good performance with an average of 1 ms and I/Os ranging as low as 100 microseconds. Clearly our working set has been mostly cached here – we can see the few actual random read disk accesses that are occurring by looking at the small bar in the 10-20 ms range.  Without histograms it would be easy to mistakenly conclude that the storage was screaming fast and we would not see latencies over 5 ms.  For this reason, at Delphix, we use the 95th percentile latency as the guideline for how storage is responding to our tests.  Again,fio is an excellent tool for I/O testing that reports latency histogram information.

Run tests multiple times to verify reproducibility. If your second and third test runs show different results than the first, none are good basis for making a forecast of eventual application performance. If performance is increasing for later tests, most likely your data is becoming cached. If performance moves up and down, most likely you are on shared infrastructure.

Since shared infrastructure is common, run at least one test at the same time as when the key workload will run. On shared infrastructure it is important to test at the time when the actual application performance will matter. For example, test during the peak load times of your application, not overnight or on a weekend. As an aside, I am occasionally misinformed by customers that the infrastructure is not shared, only to learn later that it is.

For read tests, ensure your test data size is comparable with the size of the eventual application data – or at least much larger than any intervening caches. For example, if you are developing a 1 TB OLTP system try to test over 1 TB of data files. Typical SANs have order 10 GB of cache shared among multiple users. Many operating systems (notably Linux and Solaris) will tend to use all available system RAM as a read cache. This suggests 100 GB would be the absolute minimum test data size that wouldn’t see substantial caching.

Run tests for long enough so that ramp up or dynamic workload detection changes don’t contribute substantially to your result. In practice, I find an adequate duration by running the test workloads over and over while progressively doubling the duration until I get two runs whose performance is within a close margin of one another.

Initialize your test files with data similar to what your application will use. This avoids first write penalties in the test, and ensures your tests are consistent with application performance when the storage uses compression.

Summary

Architects must often forecast the I/O performance an application will receive from existing storage. Making an accurate forecast is surprisingly tricky.  If you are trying to use a test workload to evaluate storage, there is a risk that the test will trigger some anomaly that makes the results invalid.  If you are lucky, these invalid results will be clearly impossible and lead you to do more testing, if you are unlucky they will appear reasonable problems will arise during the production roll out of the application.

An awareness of common root causes of I/O performance anomalies, and some rules of thumb for avoiding them while testing, can improve the accuracy of a performance forecast and reduce risks to an application roll out.

ping back: http://swasheck.wordpress.com/2014/10/15/storage-testing-with-sqlio-and-fio/

khailey Uncategorized

To get Delphix Express, the free version of Delphix, go to

and create an account, then log in. You should see a DelphixExpress folder.

See Full Demo of Delphix

What tools do you use? What are your favorites and why? As someone who has been working  Oracle performance tools for the last 15 years, I’m always interested in what the best tools are.

For the past 4 blog posts I’ve reviewed some tools I like such as Method-R tools, Confio, Lab128 and EMlite.

There are other tools that I especially like and are totally free such as Tanel Poder’s Moats and snapper which are two important tools for a DBA to know about.

Here is my list of tools that I’m aware of (what other tools are out there?)

Some of the prices I got off of programmers paradise, others are word of mouth, so any clarifications on prices would be appreciated. The tools marked “yes” are ones that I use or would use.  I would use Quest’s Performance Analyzer given the chance. I’ve never used it but from the demos of the tool, I like what I see.  I have never used Mumbai, but plan to and like again what I see in the demos. All the other tools marked “yes” I use. All the tools except “other stuff”, I consider reasonable tools for one job or another. Some are better than others in different ways. The “other stuff” tools I don’t see much point in. I generally consider OEM with diagnostic pack too expensive and slow, but hey, if customers have it, I’ll use it !  Plus diag pack comes with v$active_session_history and all of AWR which I can query directly with OEM.

On Enterprise tools I like this quote form http://www.infoworld.com/d/data-management/can-quest-foglight-hang-big-boys-601. It’s 5 years old but not a lot has changed since then:

The lack of decent tools to manage your databases on an enterprise level is just staggering. It’s hard to believe that nobody can cross the finish line on delivering a monitoring solution.

This looks interesting :http://windows.podnova.com/trends/oracle_performance_trace.html

Tracefile analyzer tools: http://ba6.us/node/177

this looks quite cool: http://jagjeet.wordpress.com/2012/09/20/sql-dashboard-v2/

another tool to check out http://dbspeed.com/product.html

Link from Yong Huang on sql command tools like Toad: http://yong321.freeshell.org/oranotes/ToadAndAlternatives.html

Tools that come with Oracle support:  http://blogs.oracle.com/Support/entry/support_tools_for_performance

• Tools – LTOM, OS Watcher, & HangFG
• Scripts – SlqTExplain, TracAnalyzer, PL/SQL Profiler

Login to My Oracle Support, Click the Knowledge Tab, On the left, Tools/training > Diagnostics

Image from  Robertson‏@dba_emc2

khailey Uncategorized

EMlight might not be as important now with Oracle 12c coming out and with Oracle’s EM Express, but EMlight is still pretty cool.  I  tried it about year  ago, and was impressed.  EMlight  is a light weight  re-write of Enterprise Manager that is dependable and improved. How is it improved? Let’s look at my favorite part, the “Top Activity” screen. The “Top Activity” screen has added a section on I/O latencies which is sorely missing from OEM’s performance page and Top Activity page. The OEM light version also includes  a list of the top events (as I did in DB Optimizer).  Very cool.

How is EMlight more dependable? With EMlight you just connect to the database and voila it works. It doesn’t require any of the brittle configuration files that OEM depends on nor the agents and processes that OEM depends on.

Here is an example of the To Activity screen in EMlight:

I tested version 1.2. Since then versions 1.3 and 1.4 have been released. I’ve meant to test these but haven’t yet and didn’t want to wait any longer before posting about this promising tool.

Just gave a quick look at 1.4 and looks pretty much the same but obvious improvements such as the “top activity” screen above now has a super responsive slider window to choose the aggregation time range, and you might notice in the above screen shot that CPU is missing in two of the 3 aggregates which is now fixed in 1.4 (I think the fix actually made it into 1.3 originally)

Check out more screen shots here http://obzora.com/screenshots.html

Here are a few other screen shots fro my testing:

Datafiles along with single block I/O latency which is a nice addition

I/O along with I/O latency (I don’t think OEM has every added I/O latency )

Buffer cache contents and advisory:

Archive file date times sizes and rates

Tables space usage and attributes
Top  Session, PL/SQL, SQL, Events  (kind of like the top activity page without the timeline)

khailey Uncategorized