SLOB 2.2 Not Generating AWR reports? Testing Large User Counts With Think Time? Think Processes and SLOB_DEBUG.
I’ve gotten a lot of reports of folks branching out into SLOB 2.2 large user count testing with the SLOB 2.2 Think Time feature. I’m also getting reports that some of the same folks are not getting the resultant AWR reports one expects from a SLOB test.
If you are not getting your AWR reports there is the old issue I blogged about here (click here). That old issue was related to a Redhat bug. However, if you have addressed that problem, and still are not getting your AWR reports from large user count testing, it might be something as simple as the processes initialization parameter. After all, most folks have been accustomed to generating massive amounts of physical I/O with SLOB at low session counts.
I’ve made a few changes to runit.sh that will help future folks should they fall prey to the simple processes initialization parameter folly. The fixes will go into SLOB 18.104.22.168. The following is a screen shot of these fixes and what one should expect to see in such situation in the future. In the meantime, do take note of SLOB_DEBUG as mentioned in the screenshot:
Filed under: oracle
This is Part II in a series. Part I can be found here (click here). Part I in the series covered a very simple case of SLOB data loading. This installment is aimed at how one can use SLOB as a platform test for a unique blend of concurrent, high-bandwidth data loading, index creation and CBO statistics gathering.Put SLOB On The Box – Not In a Box
As a reminder, the latest SLOB kit is always available here: kevinclosson.net/slob .
Often I hear folks speak of what SLOB is useful for and the list is really short. The list is so short that a single acronym seems to cover it—IOPS, just IOPS and nothing else. SLOB is useful for so much more than just testing a platform for IOPS capability. I aim to make a few blog installments to make this point.SLOB for More Than Physical IOPS
I routinely speak about how to use SLOB to study host characteristics such as NUMA and processor threading (e.g., Simultaneous Multithreading on modern Intel Xeons). This sort of testing is possible when the sum of all SLOB schemas fit into the SGA buffer pool. When testing in this fashion, the key performance indicators (KPI) are LIOPS (Logical I/O per second) and SQL Executions per second.
This blog post is aimed at suggesting yet another manner of platform testing with SLOB–specifically concurrent bulk data loading.
The SLOB data loader (~SLOB/setup.sh) offers the ability to test non-parallel, concurrent table loading, index creation and CBO statistics collection.
In this blog post I’d like to share a “SLOB data loading recipe kit” for those who wish to test high performance SLOB data loading. The contents of the recipe will be listed below. First, I’d like to share a platform measurement I took using the data loading recipe. The host was a 2s20c40t E5-2600v2 server with 4 active 8GFC paths to an XtremIO array.
The tar archive kit I’ll refer to below has the full slob.conf in it, but for now I’ll just use a screen shot. Using this slob.conf and loading 512 SLOB schema users generates 1TB of data in the IOPS tablespace. Please note the attention I’ve drawn to the slob.conf parameters SCALE and LOAD_PARALLEL_DEGREE. The size of the aggregate of SLOB data is a product of SCALE and the number of schemas being loaded. I drew attention to LOAD_PARALLEL_DEGREE because that is the key setting in increasing the concurrency level during data loading. Most SLOB users are quite likely not accustomed to pushing concurrency up to that level. I hope this blog post makes doing so seem more worthwhile in certain cases.
The following is a screenshot of the output from the SLOB 2.2 data loader. The screenshot shows that the concurrent data loading portion of the procedure took 1,474 seconds. On the surface that would appear to be a data loading rate of approximately 2.5 TB/h. One thing to remember, however, is that SLOB data is loaded in batches controlled by LOAD_PARALLEL_DEGREE. Each batch loads LOAD_PARALLEL_DEGREE number of tables and then creates a unique indexes and performs CBO statistics gathering. So the overall “data loading” time is really data loading plus these ancillary tasks. To put that another way, it’s true this is a 2.5TB data loading use case but there is more going on than just simple data loading. If this were a pure and simple data loading processing stream then the results would be much higher than 2.5TB/h. I’ll likely blog about that soon.
As the screenshot shows the latest SLOB 2.2 data loader isolates the concurrent loading portion of setup.sh. In this case, the seed table (user1) was loaded in 20 seconds and then the concurrent loading portion completed in 1,474 seconds.That Sounds Like A Good Amount Of Physical I/O But What’s That Look Like?
To help you visualize the physical I/O load this manner of testing places on a host, please consider the following screenshot. The screenshot shows peaks of vmstat 30-second interval reporting of approximately 2.8GB/s physical read I/O combined with about 435 MB write I/O for an average of about 3.2GB/s. This host has but 4 active 8GFC fibre channel paths to storage so that particular bottleneck is simple to solve by adding another 4 port HBA! Note also how very little host CPU is utilized to generate the 4x8GFC saturating workload. User mode cycles are but 15% and kernel mode utilization was 9%. It’s true that 24% sounds like a lot, however, this is a 2s20c40t host and therefore 24% accounts for only 9.6 processor threads–or 5 cores worth of bandwidth. There may be some readers who were not aware that 5 “paltry” Ivy Bridge Xeon cores are capable of driving this much data loading!
NOTE: The SLOB method is centered on the sparse blocks. Naturally, fewer CPU cycles are required for loading data into sparse blocks.
Please note, the following vmstat shows peaks and valleys. I need to remind you that SLOB data loading consists of concurrent processing of not only data loading (Insert as Select) but also a unique index creation and CBO statistics gathering. As one would expect I/O will wane as the loading process shifts from the bulk data load to the index creation phase and then back again.
Finally, the following screenshot shows the very minimalist init.ora settings I used during this testing.
The recipe kit can be found in the following downloadable tar archive. The kit contains the necessary files one would need to reproduce this SLOB data loading time so long as the platform has sufficient performance attributes. The tar archive also has all output generated by setup.sh as the following screenshot shows:
The SLOB 2.2 data loading recipe kit can be downloaded here (click here). Please note, the screenshot immediately above shows the md5 checksum for the tar archive.Summary
This post shows how one can tune the SLOB 2.2 data loading tool (setup.sh) to load 1 terabyte of SLOB data in well under 25 minutes. I hope this is helpful information and that, perhaps, it will encourage SLOB users to consider using SLOB for more than just physical IOPS testing.
Filed under: oracle
This is a quick blog post to show SLOB users how to determine whether they are using the latest SLOB kit. If you visit kevinclosson.net/slob you’ll see the webpage I captured in the following screenshot.
Once on the SLOB Resources page you can simply hover over the “SLOB 2.2 (Click here)” hyperlink and the bottom of your browser will show the full name of the tar archive. Alternatively you can use md5sum(1) on Linux (or md5 on Mac) to get the checksum of the tar archive you have and compare it to the md5sum I put on the web page (see the arrow).
Filed under: oracle
I invite you to please read this report.
NAND Flash is good for a lot of things but not naturally good with write-intensive workloads. Unless, that is, skillful engineering is involved to mitigate the intrinsic weaknesses of NAND Flash in this regard. I assert EMC XtremIO architecture fills this bill.
Regardless of your current or future plans for adopting non-mechanical storage I hope this lab report will show some science behind how to determine suitability for non-mechanical storage–and NAND Flash specifically–where Oracle Database redo logging is concerned.
Please note: Not all lab tests are aimed at achieving maximum theoretical limits in all categories. This particular lab testing required sequestering precious lab gear for a 104 hour sustained test.
The goal of the testing was not to show limits but, quite to the contrary, to show a specific lack of limits in the area of Oracle Database redo logging. For a more general performance-focused paper please download this paper (click here). With that caveat aside, please see the following link for the redo logging related lab report:
Filed under: oracle
This is a hasty blog post to get SLOB 2.2 out to those who are interested.
In addition to doing away with the cumbersome “seed” table and procedure.sql, this kit introduces 5 new slob.conf parameters. By default these parameters are disabled.
This SLOB distribution does not require re-executing setup.sh. One can simply adopt the kit and use it to test existing SLOB databases. The following explains the new slob.conf parameters:DO_UPDATE_HOTSPOT=FALSE
When set to TRUE, modify SQL will no longer affect random rows spanning each session’s schema. Instead, each session will only modify HOTSPOT_PCT percent of their data.HOTSPOT_PCT=10
This parameter controls how much of each session’s schema gets modified when UPDATE_PCT is non-zero. The default will limit the scope of each session’s data modifications to a maximum of 10% of their data.THINK_TM_MODULUS=0
When set to non-zero this is a frequency control on how often sessions will incur think time. For example, if set to 7, every seventh SQL statement will be following by a sleep (think time) for a random amount of time between THINK_TM_MIN and THINK_TM_MAX. It’s best to assign a prime number to THINK_TM_MODULUS.THINK_TM_MIN=.1
The low-bound for selection of a random period to sleep when THINK_TM_MODULUS triggers a think time event.THINK_TM_MAX=.5
The high-bound for selection of a random period to sleep when THINK_TM_MODULUS triggers a think time event.Notes About Think Time
The resolution supported for think time is hundreds of a second. The following is a link to the SLOB 2.2 release tarball (md5 is be3612c50d134636a56ef9654b5865c5) :
The additional tarball (at the following link) has a slob.conf, simple.ora and awr.txt that show a way to have 256 sessions produce the following load profile (on 2s16c32t E5 Xeon):
Filed under: oracle, SLOB Tagged: Oracle, SLOB
I’m surprised to find that Google is not cleanly ranking the helpful set of blog posts by Oracle’s Maria Colgan on the Oracle Database 12c In-Memory Column Store feature so I thought I’d put together this convenient set of links. Google search seems to only return a few of them in random order.
Over time I may add other helpful links regarding Oracle’s new, exciting caching technology.Starter Information
The following are links to information about Oracle Database In-Memory on Real Application Clusters:
How could the brand of storage matter for an in-memory cache feature? Good question.
Fellow Oaktable Network member Christian Antognini has produced a very important article regarding how Oracle Database 12c In-Memory Column Store with Real Application Clusters is questionable unless using Oracle storage (Exadata, SPARC SuperCluster). I found Christian’s article very interesting because, after all, the topic at hand is an in-memory cache product (a.k.a., In-Memory Column Store). I fail to see any technical reason why Oracle wouldn’t support an in-memory product with blocks from any and all storage. It is in-memory after all, isn’t it? Please visit Christian’s article here: The Importance of the In-Memory DUPLICATE Clause for a RAC System.
Filed under: oracle
BLOG UPDATE 2014.09.11: Please note: the following is a link to a more recent update of the awr_info.sh script. This version adds DB Time, DB CPU and Logical I/O: click here. The MD5 sum for this version of awr_info.sh is: a28a38b11040bb94f08a8f817792c75c
The SLOB kit comes with a little script that extracts interesting information from the awr.txt file produced at the end of a SLOB test. This is just a quick blog entry to point folks to a patched version of awr_info.sh that works properly with all Oracle Database 11g releases as well as Oracle Database 12c.
Oracle changed AWR format in the 22.214.171.124 and 12c releases so the old awr_info.sh script (in the publicly available SLOB kit) has been faulty for some time now.
I have a release of SLOB in the works that will include this awr_info.sh as well as improved data loader and improvements to the driver script (runit.sh) that includes optional, tunable think time between iterations of the SLOB work loop in slob.sql. For the time being please get a copy of the patched version of awr_info.sh.New awr_info.sh Output
This version of awr_info.sh also gleans and outputs logical read (SGA buffer pool cached block accesses) data.
The following screen shot shows the patched awr_info.sh generating proper output for awr.txt files collected by SLOB databases running out of the 126.96.36.199, 188.8.131.52 and 12c releases.
The following picture is what Microsoft Excel looks like when I cut and paste the output of awr_info.sh. I’ve highlighted the new column for logical reads.
Yes, the above picture does show AWR output from a run where the top wait event was cell single block physical read. Exadata? Yes! That’s because SLOB users often share their testing results from the Exadata platform. However, I do not get enough Exadata AWR reports to work through all of the awr_info.sh issues related to Exadata. To that end, latency information is not calculated and presented as is the case with db file sequential read. For what it’s worth this particular AWR report shows Exadata single block reads serviced with average latencies of 507 microseconds ( 7233/14256602).Where To Get The Patch?
The following is a link to my syncplicity share with the README for this patch: Click here for the patch README.
Finally, the patched version of awr_info.sh is available here: Click here for the latest awr_info.sh script. Please verify the MD5 sum after you download this script to match a28a38b11040bb94f08a8f817792c75c.
Filed under: oracle
This is a quick blog post to help folks that are testing with SLOB at high user (session) counts. The situation may arise where you are testing SLOB on a large configuration, with or without SQL*Net, and the SLOB driver (runit.sh) is failing to produce Automatic Workload Repository (a.k.a AWR) reports.
This problem will generally be seen on RHEL 6 variants that implement the much maligned /etc/security/limits.d/90-nproc.conf method of preventing fork bombs. For more information on this configuration file please refer to Red Hat bug 919793.
If you are not getting AWR reports under the condition I describe then the problem is most likely due to 90-nproc.conf short circuiting the ulimit(3) tuning you’ve established.
As an example remedy, please consider the following settings I recommended to my colleagues at VCE for performance testing of the vBlock Specialized System for High Performance Databases:
Filed under: oracle