Archive for the 'oracle' Category

Announcing My Employer-Related Twitter Account

When I tweet anything about Amazon Web Services it will be on the following twitter handle:  https://twitter.com/ClossonAtWork (@ClossonAtWork).

If you’re interested in following my opinions on that twitter feed, please click and follow. Thanks.

Announcing SLOB 2.4! Integrated Short Scans and Cloud (DBaaS) Support, and More.

This post is to announce the release of SLOB 2.4!

VERSION

SLOB 2.4.0. Release notes (PDF): Click Here.

WHERE TO GET THE BITS

As always, please visit the SLOB Resources page. Click Here.

NEW IN THIS RELEASE

  • Short Table Scans. This release introduces the ability to configure SLOB sessions to perform a percentage of all SELECT statements as full table scans against a small, non-indexed table. However, the size of the “scan table” is configurable.
  • Statspack Support. This version, by default, generates STATSPACK reports instead of Automatic Workload Repository (AWR) reports. This means that SLOB testing can be conducted against Oracle Database editions that do not support AWR–as well as the ability to test Enterprise Edition with fewer software licensing concerns. AWR reports can be generated after a simple modification to the slob.conf file.
  • External Script Execution. House-keeping of run results files and the ability to, for example,  issue a remote command to a storage array to commence data collection is introduced by the EXTERNAL_SCRIPT feature in SLOB 2.4.

ADDITIONAL CHANGES

SLOB 2.4 has been tested on public cloud configurations to include Amazon Web Services RDS for Oracle. SLOB 2.4 changes to slob.conf parameters, and other infrastructure, makes SLOB 2.4 the cloud predictability, and repeatability, testing tool of choice as of SLOB 2.4.

ADDITIONAL INFO

Please see the SLOB 2.4 Documentation in the SLOB/doc directory. Or, click here.

ACKNOWLEDGEMENTS

The SLOB 2.4 release came by way of non-trivial contributions from the SLOB community. I’m very thankful for the contributions and want to point out the following value added by several SLOB user community folks:

  • Chris Osborne (@westendwookie). Chris provided a functional prototype of the new SLOB 2.4 Scan Table Feature. Thanks, Chris!
  • Christian Antognini (@ChrisAntognini): Chris provided a functional prototype of the new SLOB 2.4 support for STATSPACK! Thanks, Chris!
  • James Morle (@JamesMorle). James has helped with several scalability improvements in slob.sql based on his astonishing high-end SLOB testing. With thousands of sessions attached to a dozen or more state-of-the-art Xeon hosts connected to NVM storage led to several issues with proper start/stop synchronization and thus impacting repeatability. James also created the new SLOB 2.4 EXTERNAL_SCRIPT feature. As always, thanks, James!
  • Maciej Przepiorka (@mPrzepiorka): Maciej conducted very thorough Beta testing and enhanced the EXTERNAL_SCRIPT feature in SLOB 2.4. Thanks, Maciej.
  • Martin Berger (@martinberx): Martin conducted significant Standard Edition testing and also enhanced the SLOB/misc/awr_info.sh (SLOB/misc/statspack_info.sh) script for producing performance data, in tuple form, from STATSPACK. Thanks, Martin!

AWS Database Blog – Added To My Blog Roll

This is just a brief blog post to share that I’ve added the AWS Database Blog to my blogroll.  I recommend you do the same! Let’s follow what’s going on over there.

Some of my favorite categories under the AWS Database Blog are:

 

 

Readers: I do intend to eventually get proper credentials to make some posts on that blog. All in proper time and with proper training and clearance.

SLOB Use Cases By Industry Vendors. Learn SLOB, Speak The Experts’ Language.

For general SLOB information, please visit: https://kevinclosson.net/slob.

Introduction

This is just a quick blog entry to showcase a few of the publications from IT vendors showcasing SLOB. SLOB allows performance engineers to speak in short sentences. As I’ve pointed out before, SLOB is not used to test how well Oracle handles transaction. If you are worried that Oracle cannot handle transactions then you have bigger problems than what can be tested with SLOB. SLOB is how you test whether–or how well–a platform can satisfy SQL-driven database physical I/O.

SLOB testing is not at all like using a transactional test kit (e.g., TPC-C). Transactional test kits are, first and foremost, Oracle intrinsic code testing kits (the code of the server itself). Here again I say if you are questioning (testing) Oracle transaction layer code then something is really wrong. Sure, transactional kits involve physical I/O but the ratio of CPU utilization to physical I/O is generally not conducive to testing even mid-range modern storage without massive compute capability. This is where vendors and dutiful systems experts rely on SLOB.

Recent SLOB testing on top-bin Broadwell Xeons (E5-2699v4) show that each core is able to drive over 50,000 physical read IOPS (db file sequential read).  On the contrary 50,000 IOPS is about what one would expect from over a dozen of such cores with a transactional test kit because the CPU is being used to execute Oracle intrinsic transaction code paths and, indeed, some sundry I/O.

SLOB Use Cases By IT Vendors

The following are links and screenshots from the likes of FlashGrid, Red Hat, DellEMC, HPE, Nutanix, NetApp, Pure Storage, IBM, Nimble Storage showing some of their SLOB use cases. Generally speaking, if you are shopping for modern storage–optimized for Oracle Database–you should expect to see SLOB results.

FlashGrid

The first case I’d like to share is that of a solution built by FlashGrid. This solution is all about using AWS EC2 instances, along with FlashGrid technology and best practices for Real Application Clusters,  in the AWS Cloud. I am not an expert on Flash Grid and am merely reporting their usage of SLOB as can be seen in the following paper and blog post:

I do recommend getting a copy of this paper!

FlashGrid Characterizing Real Application Clusters Performance with SLOB in the AWS Cloud (EC2 instances)

VMware

VMware showcasing VSAN with Oracle using SLOB at: https://blogs.vmware.com/apps/2016/08/oracle-12c-oltp-dss-workloads-flash-virtual-san-6-2.html.

screen-shot-2017-02-10-at-11-48-06-am

VMware Using SLOB to Assess VSAN Suitability for Oracle Database

VMware has an additional publication showing SLOB results at the following URL: https://blogs.vmware.com/virtualblocks/2016/08/22/oracle-12c-oltp-dss-workloads-flash-virtual-san-6-2/

VCE

The VCE Solution guide for consolidating databases includes proof points based on SLOB testing at the following link: http://www.vce.com/asset/documents/oracle-sap-sql-on-vblock-540-solutions-guide.pdf.

screen-shot-2017-02-10-at-4-57-10-pm

VCE Solution Guide Using SLOB Proof Points

Nutanix

Next is Nutanix with this publication: https://next.nutanix.com/t5/Server-Virtualization/Oracle-SLOB-Performance-on-Nutanix-All-Flash-Cluster/m-p/12997

Figure 2: Nutanix Using SLOB for Platform Suitability Testing

Nutanix Using SLOB for Platform Suitability Testing

NetApp

NetApp has a lot of articles showcasing SLOB results. The first is at the following link: https://www.netapp.com/us/media/nva-0012-design.pdf.

screen-shot-2017-02-10-at-12-08-22-pm

NetApp Testing FlexPod Select for High-Performance Oracle RAC with SLOB

The next NetApp article entitled NetApp AFF8080 EX Performance and Server Consolidation with Oracle Database also features SLOB results and can be found here: https://www.netapp.com/us/media/tr-4415.pdf.

Figure 4: NetApp Testing the AFF8080 with SLOB

NetApp Testing the AFF8080 with SLOB

Yet another SLOB-related NetApp article entitled Oracle Performance Using NetApp Private Storage for SoftLayer can be found here:  http://www.netapp.com/us/media/tr-4373.pdf.

Figure 5: NetApp Testing NetApp Private Storage for SoftLayer with SLOB

NetApp Testing NetApp Private Storage for SoftLayer with SLOB

When searching the NetApp main webpage I find 11 articles that offer SLOB testing results:

netappslob

Searching NetApp Website shows 11 SLOB-Related Articles

HPE

Hewlett-Packard Enterprise offers an article entitled HPE 3PAR All-Flash Acceleration for Oracle ASM Preferred Reads which models performance using SLOB. The article can be found here: http://h20195.www2.hpe.com/V2/getpdf.aspx/4AA6-3375ENW.pdf?ver=1.0

screen-shot-2017-02-10-at-12-13-56-pm

HPE Using SLOB For Performance Assessment of 3PAR Storage

Pure Storage

In the Pure Storage article called Pure Storage Reference Architecture for Oracle Databases, the authors also show SLOB results. The article can be found here:

https://support.purestorage.com/@api/deki/files/1732/=Pure_Storage_Oracle_DB_Reference_Architecture.pdf?revision=2.

screen-shot-2017-02-10-at-12-22-24-pm

Pure Storage Featuring SLOB Results in Reference Architecture

Nimble Storage

Nimble Storage offers the following blog post with SLOB testing results: https://connect.nimblestorage.com/people/tdau/blog/2013/08/14.

Figure 9: Nimble Storage Blogging About Testing Their Array with SLOB

Nimble Storage Blogging About Testing Their Array with SLOB

IBM

There is an IBM “8-bar logo” presentation showing SLOB results here:  http://coug.ab.ca/wp-content/uploads/2014/02/Accelerating-Applications-with-IBM-FlashJAN14-v2.pdf.

screen-shot-2017-02-10-at-12-28-45-pm

IBM Material Showing SLOB Testing

Kernel.org

I also find it interesting that folks contributing code to the Linux Kernel include SLOB results showing value of their contributions such as here: http://lkml.iu.edu/hypermail/linux/kernel/1302.2/01524.html.

screen-shot-2017-02-10-at-12-32-27-pm

Linux Kernel Contributors Use SLOB Testing of Their Submissions

Red Hat

Next we see Red Hat disclosing Live Migration capabilities that involve SLOB workloads: https://www.linux-kvm.org/images/6/66/2012-forum-live-migration.pdf.

screen-shot-2017-02-10-at-12-35-23-pm

Red Hat Showcasing Live Migration with SLOB Workload

Dell EMC

DellEMC has many publications showcasing SLOB results. This reference, however, merely suggests the best-practice of involving SLOB testing before going into production:

http://en.community.dell.com/techcenter/extras/m/white_papers/20438214.

screen-shot-2017-02-10-at-12-39-47-pm

DellEMC Advocate Pre-Production Testing with SLOB

An example of a detailed DellEMC publication showing SLOB results is the article entitled VMAX ALL FLASH AND VMAX3 ISCSI DEPLOYMENT GUIDE FOR ORACLE DATABASES which can be found here:

https://www.emc.com/collateral/white-papers/h15132-vmax-all-flash-vmax3-iscsi-deploy-guide-oracle-wp.pdf.

screen-shot-2017-02-10-at-12-47-19-pm

EMC Testing VMAX3 All-FLASH with SLOB

Another usage of SLOB by Dell EMC can be found at the following link: http://www.principledtechnologies.com/Dell/VMAX_250F_PowerEdge_R930_Oracle_perf_0417_v3.pdf. This paper is a partner effort with Principled Technologies and it showcases a VMAX 250F All-Flash Array performance characterization with SLOB.

Dell EMC Partnering with Principled Technologies: SLOB Testing with VMAX 250F All-Flash

I took a moment to search the main DellEMC website for articles containing the word SLOB and found 76 such articles!

screen-shot-2017-02-10-at-12-49-23-pm

Search for SLOB Material on DellEMC Main Web Page

Red Stack Tech

Red Stack Tech offer DBaaS and even showcase the ability to test the platform for I/O suitability with SLOB:

http://www.redstk.com/services/cloud-technology-poc/

screen-shot-2017-02-14-at-8-03-49-am

Red Stack Tech Offering SLOB Testing as Proof of Concept

Non-Vendor References

Although not a vendor, it deserves mention that Greg Shultz of Server StorageIO and UnlimitedIO LLC lists SLOB alongside other platform and I/O testing toolkits. Greg’s exhaustive list can be found here: http://storageioblog.com/server-and-storage-io-benchmarking-resources/.

storageio

 

Summary

More and more people are using SLOB. If you are into Oracle Database platform performance I think you should join the club! Maybe you’ll even take interest in joining the Twitter SLOB list: https://twitter.com/kevinclosson/lists/slob-community.

Get SLOB, use SLOB!

 

 

 

 

SLOB 2.3 Data Loading Failed? Here’s a Quick Diagnosis Tip.

The upcoming SLOB 2.4 release will bring improved data loading error handling. While still using SLOB 2.3, users can suffer data loading failures that may appear–on the surface–to be difficult to diagnose.

Before I continue, I should point out that the most common data loading failure with SLOB in pre-2.4 releases is the concurrent data loading phase suffering lack of sort space in TEMP. To that end, here is an example of a SLOB 2.3 data loading failure due to shortage of TEMP space. Please notice the grep command (in Figure 2 below) one should use to begin diagnosis of any SLOB data loading failure:

screen-shot-2016-12-27-at-3-37-20-pm

Figure 1

And now, the grep command:

screen-shot-2016-12-27-at-3-37-42-pm

Figure 2

 

Yes, Storage Arrays Can Deduplicate Oracle Database. Here Is Exactly Why It Doesn’t Matter!

I recently had some cycles on a freshly installed Dell EMC XtremIO Storage Array. I took this opportunity to prepare a blog entry about the never-ending topic of whether or not storage arrays are able to reduce physical data capacity through deduplication of blocks in Oracle Database.

Of Course There Is Duplicate Data In Oracle Datafiles

Before I continue, let me say something that may come as a surprise to you. Yes, Oracle Database has duplicate blocks in tablespaces! Yes, modern storage arrays can achieve astonishing data reduction rates through deduplication–even when the only data in the array is Oracle Database (whether ASM or file systems)!

XtremIO computes and displays global data reduction rate. This makes it a bit more difficult to show the effect of deduplication on Oracle Database because averages across diverse data makes pin-point focus impossible. However, as I was saying, I took some time on a freshly-installed XtremIO array and collected what I hope will be interesting information on the topic of deduplication.

Please take a look at Figure 1. To start the testing I created a 4TB XtremIO volume, attached it as a LUN to a test host and then created an XFS file system on it. Please be aware that the contents of an Oracle datafile is precisely the same whether stored in ASM or in a file system file. After the file system was created I used the SLOB database creation kit (SLOB/misc/create_database_kit) to create a small database with Oracle Database 12c. As Figure 1 shows, the small database consumed 11.83GB of logical space in the 4TB volume. However, since the data enjoyed a slight deduplication ratio of 1.1:1 and a healthy compression ratio of 3.3:1 for a 3.6:1 data reduction ratio, only 3.27GB physical space was consumed in the array.

markup-gui-after-slob-database-created

Figure 1

The next step in the testing was to consume the majority of the 4TB file system with a BIGFILE tablespace. Figure 2 shows the DDL I used to create the tablespace.

shell-create-3900g-ts

Figure 2

Figure 3 shows the file system file that corresponds to the tablespace created with DDL in Figure 2.

sqlplus-bigfile-info-and-host-to-ls

Figure 3

After creating the 3.9TB BIGFILE tablespace I took a screenshot of the XtremIO GUI Dashboard. As Figure 4 shows, there was no deduplication! Instead, the data was compressed 4.0:1 resulting in only 977.66GB physical space being consumed in the array. So why in the world would I blog the opposite of what I said above? Why show the array did not, in fact, deduplicate the 3.9TB datafile? The answer is in the fact that I said there are duplicate data block in tablespaces. I didn’t say there are duplicate blocks in the same datafile!

gui-after-bigfile-cr

Figure 4

To return the array to the state prior to the BIGFILE tablespace creation, I dropped the tablespace (including contents and datafiles thus unlinking the file) and then used the Linux fstrim(8) command to return the space to the array as shown in Figure 5.

shell-fstrim-after-drop-bigts

Figure 5

Once the fstrim command completed I took another screenshot of the XtremIO GUI Dashboard as shown in Figure 6. Figure 6 shows that the array space utilization and data reduction had returned to that of what was seen before the BIGFILE tablespace creation.

gui-after-bigfile-drop-and-fstrim

Figure 6

OK, Now For The Duplicate Data

The next step in the testing was to fill up the majority of the 4TB file system with SMALLFILE tablespaces. To do so I created 121 tablespaces each consisting of a single SMALLFILE datafile of 32GB. The output shown in Figure 7 is from a data dictionary query to display the size of each of the 121 datafiles and how the sum of these datafiles consumed 3.87TB of the 4TB file system.

shell-after-creating-smalfile-tablespaces

Figure 7

That’s Duplicate Data

Once the file system was filled with SMALLFILE datafiles I took another screenshot of the XtremIO GUI Dashboard. Figure 8 shows that the SMALLFILE datafiles enjoyed a deduplication ratio 81.8:1 combined with a compression ratio of 3.8:1 resulting in a global data reduction rate of 306.9:1. Because of the significant data reduction rate only 12.68GB of physical space was consumed in the array in spite of the 3.79TB logical space (the sum of the SMALLFILE datafiles) being allocated.

gui-after-smallfile-cr

Figure 8

So here we have it! I had a database created with Oracle Database 12c that consisted of 121 32GB files for roughly 3.8TB database size yet XtremIO deduplicated the data down by a factor of 82:1!

So arrays can deduplicate Oracle Database contents! Right? Well, yes, but it matters none whatsoever. Allow me to explain.

Oracle datafiles consist of initialized blocks but vast portions of that initialized content is the same from file to file. This fact can be seen with simple md5sum(1) output. Consider Figure 9 where you can see the output of the md5sum command used to compute Oracle datafile checksums but only after skipping the first 8,692 blocks (8KB blocks). It’s the first approximate 68MB of each datafile that is unique when a datafile is freshly initialized. Beyond that threshold we can see (Figure 9) that the rest of the file content is identical.

markup-shell-with-md5sum

Figure 9

Thus far this blog post has proven that initialized, but empty, Oracle Database datafiles have duplicate data. As the title of this post says, however, it does not matter.

Introduce Application Data To The Mix

Figure 10 shows the commands I used to populate each of the 121 tablespaces with a single table. The table has the sparse characteristic we are all accustomed to with SLOB. That is, I am only creating a single row in each block. Moreover, I’m populating each of these 121 tables with the same application data! This is precisely why I say deduplication of Oracle Database doesn’t matter because it only holds true until any application data is loaded into the data blocks. Figure 10 shows this set of DDL commands.

shell-load_tablespaces-script

Figure 10

After populating the blocks in each of the 121 tables (each residing in a dedicated SMALLFILE tablespace) with blocks containing just a single row of application data I took another screenshot of the XtremIO GUI Dashboard. Figure 11 shows how putting any data into the data blocks reverts the deduplication. Why? Well, remember that the block header of every block has the SCN of the last change made to the block. For this reason I can put the same application data in blocks and still have 100% unique blocks–at least at the 8KB level.

Please note that the application table I used to populate the 121 tables does not consume 100% of the data blocks in each of the SMALLFILE tablespaces. There were a few blocks remaining in each tablespace and thus there remained a scant amount of deduplication as seen in Figure 11. Most XtremIO customers see some insignificant deduplication in their Oracle Database environments. Some even see significant deduplication–at least until they insert data into the database.

gui-after-filling-smallfiles

Figure 11

In a follow-up post I’ll say a few words about the deduplication granularity and how it affects the ability to achieve small amounts of deduplication of unused space in initialized data blocks. However, bear in mind that the net result of any deduplication of Oracle Database data files is that the only space that can be deduplicated is space that has never had application data in it. After all, a SQL DELETE command doesn’t remove data–it only marks it as free in the block.

Summary

I don’t think there are that many Oracle shops that have an urgent need for data reduction of space that’s never been used to store application data. I could be wrong. Until I find out either way, I say that yes you can see deduplication of Oracle Database datafiles but it doesn’t matter one bit.

 

 

 

 

 

 

 

How Many ASM Disks Per Disk Group And Adding vs. Resizing ASM Disks In An All-Flash Array Environment

I recently posted a 4-part blog series that aims to inform readers that, in an All-Flash Array environment (e.g., XtremIO), database and systems administrators should consider opting for simplicity when configuring and managing Oracle Automatic Storage Management (ASM).

The series starts with Part I which aims to convince readers that modern systems, attached to All-Flash Array technology, can perform large amounts of low-latency physical I/O without vast numbers of host LUNs. Traditional storage environments mandate large numbers of deep I/O queues because high latency I/O requests remain “in-flight” longer. The longer an I/O request takes to complete, the longer other requests remain in the queue. This is not the case with low-latency I/O. Please consider Part I a required primer.

To add more detail to what was offered in Part I,  I offer Part II.  Part II shares a very granular look at the effects of varying host LUN count (aggregate I/O queue depth) alongside varying Oracle Database sessions executing zero-think time transactions.

Part III begins the topic of resizing ASM disks when additional ASM disk group capacity is needed.  Parts I and II are prerequisite reading because one might imagine that a few really large ASM disks is not going to offer appropriate physical I/O performance. That is, if you don’t think small numbers of host LUNs can deliver the necessary I/O performance you might be less inclined to simply resize the ASM disks you have when extra space is needed.

Everything we know in IT has a shelf-life. With All-Flash Array storage, like XtremIO, it is much less invasive, much faster and much simpler to increase your ASM disk group capacity by resizing the existing ASM disks.

Part IV continues the ASM disk resizing topic by showing an example in a Real Application Clusters environment.

 


DISCLAIMER

I work for Amazon Web Services. The opinions I share in this blog are my own. I'm *not* communicating as a spokesperson for Amazon. In other words, I work at Amazon, but this is my own opinion.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,961 other followers

Oracle ACE Program Status

Click It

website metrics

Fond Memories

Copyright

All content is © Kevin Closson and "Kevin Closson's Blog: Platforms, Databases, and Storage", 2006-2015. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Kevin Closson and Kevin Closson's Blog: Platforms, Databases, and Storage with appropriate and specific direction to the original content.

%d bloggers like this: